#SQL

Сьогодні таке наробив, що аж не віриться, але ж воно працює! Є в мене в трекеру такий графік, як на ілюстрації: він корисний, щоб зрозуміти, яка типова тривалість того чи іншого тегу.

Модель даних тут найпростіша: Sample * ←→ * Tag. В минулому з SwiftData, щоб отримати таку статистику, доводилося йти за списком проб, перебирати їхні теги та рахувати. Через повільність я дивився тільки на проби за останній місяць. Але тепер все це сидить в SQLite в схемі sample ← tagging → tag. Ну, думаю, тепер все можна все зробити в SQL без всіляких переборів. Вийшов такий запит на 40 рядків.

1. В серці запита сидять віконні функції. Це єдиний спосіб в рядку отримати доступ до інших рядків. Як-от потрібно зробити, щоб дізнатися, що послідовність тегу почалася чи перервалася: для цього є віконна функція LAG(), якою можна підгледіти значення для попереднього рядка.

2. Але тільки початок, бо якось потрібно відокремити ті послідовності. Тут я знайшов в Інтернеті такий чаклунський підхід, як COUNT(*) FILTER (...) OVER (...), який для кожного рядка рахує всі попередні рядки, де почалася чи закінчилася послідовність. (Чому COUNT(*) рахує саме попередні рядки, а не всі? Бо в цьому випадку “вікном” є все від початку таблиці до даного рядка. Та ще езотерика.) В результаті отримуємо таблицю, де все ще по рядку на кожну пробу, але кожна послідовність має унікальний номер.

3. Далі, як можна здогадатись, групуємо за номером, та отримуємо довжину кожної послідовності. (До речі, спочатку я думав, що PARTITION BY isTagged буде достатньо для цього, та ніяких номерів не потрібно. Але виявилося, що PARTITION ділить всю таблицю, не зважаючи на порядок рядків.)

4. Ну і тепер все зовсім просто — групуємо за довжиною, отримуємо частоту. Це ми вміємо!

5. Успіх! Така реалізація дійсно спритніше за “обʼєктну”, та ще й встигає опрацювати всю базу, а не тільки останній місяць.

Завтра випускаю нову збірку трекера. Зміни доторкнулися головної частини — алгоритму пропонування тегів. Частково повернув ту логіку, що була до міграції на SQLite, а також додав нові круті речі.

Для початку, зробив, щоб по змахуванню модала редагування він зберігався. Раніше скасовувався. Скасувати все ще можна — кнопкою — але збереження набагато частіша дія. (Ну та й звісно, якщо нічого не змінювати, то нічого й не збережеться.) Також повернув функцію “скопіювати з минулого”, яка зникла після редизайну.

Але головне — це покращений алгоритм пропонування. До того він спирався тільки на час (тобто пропонувалися теги, що були відмічені раніше в ту саму годину та день тижня.) Бо це просто легше було запрограмувати на SQL. Зараз, якщо вже є обрані теги, то я вибираю з бази суміжні з ними. До речі, хоч через це підбірка тегів постійно змінюється, завдяки анімації наочно зрозуміло, що пішло та що додалося — тому ти не губишся в цих змінах.

Я почав з того, що витягував суміжні теги до кожного обраного, та поєднував за частотою. Але це дає дивні результати; умовно якщо обрати теги desktop, coding, то в пропозиціях будуть gaming як суміжний із desktop та laptop (!) із coding.

Тому треба було знаходити теги, суміжні до всіх обраних відразу. Що не так легко, бо тегування зберігаються в табличці tagging - знайти суміжний до одного тегу це один join, але якщо до декількох — не плодити ж джойни? Винайшов чудове рішення, що знаходить всі пінги, де зустрічалися всі обрані теги:

SELECT ts, count(*) cnt
  FROM tagging
  WHERE tagId IN (1,2,3)
  GROUP BY ts
  HAVING cnt = 3

Залишається результат заджойнити назад до tagging та вибрати всі теги, що збігаються за часом відмітки.

PS. Після роботи дуже важко перемкнутися на власний проєкт. Голові потрібно відпочити, а коли відпочинеш — вже пізно щось починати. Тому я для себе знайшов, що краще займатися власним проєктом першим чином рано вранці. Тоді до початку роботи встигаю відпочити.

Потрапила на очі фраза “хто ж пише SQL, коли вже н-цять років існують ORM?” Хотілося прокоментувати. Тут є цікавий розподіл.

Головна міць реляційних баз даних — це, власне, ота реляційна модель, яку ніхто (включаючи мене) досконало не розуміє. (Пропоную задати собі питання — про яке відношення йдеться?) Але суть в тому, що ми можемо обʼєднувати розрізнені факти, щоб отримати сукупний результат.

Наразі існує достатньо нереляційних баз, щоб побачити, що ця можливість не є універсальною. От в OpenSearch все, що ми можемо — це шукати документи за критеріями та агрегувати. Все інше — дорого (та реалізується тим самим пошуком.) У CoreData немає COUNT. В DynamoDB взагалі кожний запит треба задумати наперед.

А у реляційній базі всі дані доступні для поєднання. Роби з ними що хочеш. Причому поєднання ще й буде ефективним — це і є основна пропозиція будь-якої реляційної бази. Та хто працює з аналітикою, бачив ті кілометрові запити на SQL, які генерують звіт з двадцяти таблиць з усіх околиць компанії. (Користуюся нагодою прорекламувати наш продукт Coupler, який допомагає зібрати ті дані з розрізнених джерел.)

Розробники зазвичай бєкають на такі запити, наче вони поступаються іншим програмам. Втім правда в тому, що реалізувати ту ж логіку без SQL було б довше, вийшло б складніше, та ще й працювало б повільно.

При тому ж для розробки типових застосунків складних запитів не потрібно. Ми маємо справу або з окремими записами, або з колекціями записів, та збереження стану — далеко не найцікавіше місце логіки. Тому я радий, що є ORM, та вони дозволяють нам робити запити з нашої зручної мови програмування загального призначення. ORM - для застосунків, SQL - для звітів та відношень. А головне, одне іншому не заважає!

Коли вже довелося писати SQL, який в один екран більше не влазить, не все втрачено. Є підходи до того, щоб його взяти під контроль та, наприклад, забезпечити підтримку в майбутньому замість переписування.

• Навчитися робити розрізи. Розріз (View) - це фактично збережений запит. Ззовні виглядає як таблиця. Якщо у вас складний багатоповерховий запит, можна його декомпонувати — винести з нього розрізи, та тоді кожний етап буде легше зрозуміти окремо.

• Навчитися використати CTE CTE - Common Table Expressions (“спільні оголошення таблиць”, певно) - як розріз, але оголошений в межах поточного запиту. Це перший крок до структурованих запитів. Спочатку через WITH оголошуєш допоміжні запити, а потім в головному запиті використовуєш як звичайні таблиці. Чомусь вважається просунутою технікою SQL, хоча насправді в CTE немає нічого складного.

• Опанувати параметризовані запити. Чого точно не варто робити, так це будувати SQL шаблонами. По-перше, це небезпечно. По-друге, параметризований запит (Prepared statement) дає БД можливість відстежувати та оптимізувати повторювані запити, навіть коли їхні параметри змінюються.

• Тести! А чом би й ні? Побудували дані, перевірили результат. Звісно, легше це робити, коли не потрібно кожний стовпчик заповнювати явно. Тому в Go мені з цим важко, зате в Ruby - легко. Сьогодні дізнався, що й тести безпосередньо для СКБД теж існують — наприклад, pgTAP для PostgreSQL.