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

🤖🚫 AI-free content. This post is 100% written by a human, as is everything on my blog. Enjoy!

Пости з тегом #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. Успіх! Така реалізація дійсно спритніше за “обʼєктну”, та ще й встигає опрацювати всю базу, а не тільки останній місяць.