Стендап Сьогодні 📢 Канал в Telegram @stendap_sogodni

Пости з тегом #SQL

04.12.2024

Дев-адвент 4: віконні функції у SQL

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

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

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

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

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

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

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

30.01.2025

Завтра випускаю нову збірку трекера. Зміни доторкнулися головної частини — алгоритму пропонування тегів. Частково повернув ту логіку, що була до міграції на 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. Після роботи дуже важко перемкнутися на власний проєкт. Голові потрібно відпочити, а коли відпочинеш — вже пізно щось починати. Тому я для себе знайшов, що краще займатися власним проєктом першим чином рано вранці. Тоді до початку роботи встигаю відпочити.