Оволодіння SQL віконними функціями: Комплексний посібник

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

Що таке віконні функції?

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

По дорозі ми часто натрапляємо на перешкоди або виклики, які, здавалося б, важко розв’язати існуючими інструментами чи техніками у нашому “інструментарії”. Одним із таких випадків, з яким стикається багато з нас, є обмеження “агрегаційних функцій”, які використовуються з операціями GROUP BY. Можливо, вас попросили розрахувати нове поле для кожного рядка у ваших даних, що природно виключає операції агрегації. Можливо, вам було сказано знайти накопичувані(комулятивні) суми (running totals), середні значення чи інші статистичні показники для вхідних даних, які постійно змінюються (тобто нестатичні дані).

На перший погляд SQL віконні функції (Window Functions) можуть здатися просто ще одним набором команд у великому арсеналі SQL. Однак вони мають сховану силу, своєрідну прихований пензлик, який перетворює рядки даних в полотно можливостей. Сьогодні ми вирушаємо, щоб розгадати ці потужні функції, зняти верхні шари і розкрити мистецтво та ефективність, які вони приносять у аналізі даних.

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

Коли мені слід використовувати віконні функції (ELI5)

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

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

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

3.Вам потрібно знайти найбільший або найменший блок в певному розділі.
Скажімо, у вас є блоки, впорядковані за рядами за кольором, і ви хочете знайти найбільший блок в кожному ряду. Віконна функція допомагає вам розглядати кожен ряд окремо та вибирати найбільший блок в кожному з них.

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

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

Віконні функції – це операції SQL, які виконують обчислення для набору рядків, що пов’язані із поточним рядком. На відміну від агрегаційних функцій, вони не призводять до того, що рядки об’єднуються в один вихідний ряд — рядки зберігають свої окремі ідентичності. Віконні функції можуть виконувати обчислення для набору рядків, що пов’язані із поточним рядком. Їх називають віконними функціями, оскільки вони виконують обчислення для “вікна” рядків. Наприклад, ви можете хотіти розрахувати накопичену суму продажів або визначити найвищий бал в групі.

Перегляньте зображення нижче, щоб побачити, як вони порівнюються з агрегаційними функціями.

Анатомія віконної функції

Багато людей намагаються ускладнено пояснити віконні функції, тому моя мета – зробити це для вас максимально простим!

Уявіть, що ви їдете на туристичному автобусі і дивитесь у вікно. Ви бачите речі одна за одною, правда? Віконні функції SQL працюють трошки схоже на це. Вони розглядають ваші дані ряд за рядом, але (і ось це круто) вони пам’ятають, що вони вже бачили раніше і що буде далі. Це як мати фотографічну пам’ять під час огляду пам’яток!

• Функція: Це може бути SUM, AVG, MAX або будь-яка інша функція, яка вам потрібна. Зазвичай це є серцем (математичної) операції, яку ви хочете виконати! Ці функції подібні до звичайних агрегаційних функцій, але не зменшують кількість повернутих рядків.
  
• OVER(): Ця частина дозволяє SQL зрозуміти, що ми збираємося зробити щось особливе, готуючи сцену для нашої віконної функції. OVER() – це кутовий камінь віконних функцій в SQL. Це дозволяє нам визначити “вікно” або підмножину даних, яку функція буде обробляти.
  
• PARTITION BY: (Необов’язково) Якщо ви хочете виконати розрахунки на конкретних частинах (групах) ваших даних, так ви вказуєте SQL розділити речі. Якщо не вказано PARTITION BY, функція розглядає всі рядки результату запиту як єдиний розділ. Вона працює подібно до GROUP BY, але, в той час як GROUP BY агрегує дані, PARTITION BY цього не робить, вона просто групує дані для віконної функції.
  
• ORDER BY: (Необов’язково) Воно сортує рядки всередині кожного розділу. Якщо не вказано ORDER BY, функція розглядає всі рядки розділу як єдину групу.

Давайте розглянемо це в актуальному коді SQL:

SELECT column_name,
       WINDOW_FUNCTION(column_name) OVER (
           PARTITION BY column_name
           ORDER BY column_name
           RANGE/ROWS BETWEEN ... AND ...
       )
FROM table_name;

Ось вам і все! У вас є високорівневий огляд того, як працюють віконні функції. Звісно, ми хочемо розглянути деякі основні приклади, щоб все це з’єднати, тому ми розглянемо їх далі.

Приклади для легшого засвоєння

Уявімо, що у нас є деякі прості дані про продажі та елементи цих продажів.

1.Накопичені суми

SELECT
  SaleID,
  Salesperson,
  SaleAmount,
  SaleDate,
  SUM(SaleAmount) OVER (ORDER BY SaleDate) AS RunningTotal
FROM Sales;

Це розрахує накопичену суму SaleAmount для кожного рядка впорядкованого за SaleDate. Результати показані нижче. Зверніть увагу на новий стовпчик, який називається RunningTotal! Ось вам із чим – ми тільки що створили новий стовпець! Ви, можливо, бачили це десь як “розраховане поле”.

2. Кумулятивні підсумки (за продавцем/ By SalesPerson)

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

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

SELECT
  SaleID,
  SaleDate,
  Salesperson,
  SaleAmount,
  SUM(SaleAmount) OVER (PARTITION BY Salesperson ORDER BY SaleDate) AS CumulativeSalePerPerson
FROM Sales;

Якщо ми вивчимо нове поле “CumulativeSalePerPerson”, то патерн стає трошки важчим для виявлення, але, як тільки ми дійдемо до третього рядка, він стає набагато яснішим. “Аліса” здійснила першу продажу в рядку 1 на “300”, потім вона здійснила ще одну продажу на “200” у третьому рядку, тому її кумулятивний продаж на той момент становив “500”. Так само Боб мав продажі, представлені в другому і п’ятому рядках, тому він не досягає “450” до п’ятого рядка, де він отримує продаж на “300” для додавання до своїх попередніх “150”. Це настільки просто! Уявіть, як важко було б придумати, як це зробити за допомогою звичайного SQL-запиту, це було б НЕМОЖЛИВО!

3.Ранжування продажів за сумою продажів

Тепер уявіть, що у нас йде конкурс продажів, щоб визначити, який продавець може отримати найбільше перемог (впіймати найбільшу рибу).

Зрозуміло, нам б хотілося мати простий спосіб ранжування. Зазвичай, за допомогою звичайного запиту, ви можливо спробуєте просто використовувати ORDER BY SaleAmount DESC, але тоді ми втратимо існуючий порядок решти даних. Тут на допомогу приходить функція RANK()!

SELECT
  SaleID,
  Salesperson,
  SaleAmount,
  SaleDate,
  RANK() OVER (ORDER BY SaleAmount DESC) AS SaleRank
FROM Sales;

Як ми бачимо, в восьмому рядку, Аліса впіймала найбільшу рибу на “450”, розмістивши її на першому місці! Вона також мала риболовлі в 3-му, 5-му, 10-му, 12-му і 14-му “рейтингу”.

4.Рухоме середнє (Moving Average) (3-денний) суми продажів (SalesAmount)

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

З метою простоти цього прикладу ми використовували 3-денне ВІКНО (рухоме середнє за 3 дні), але це може бути так само 7-денне (тижневий КС), 30-денне (місячний) або будь-який інший період часу, який ви вирішите розглядати! (Примітка: ці віконні функції можуть бути довгими в одному рядку, тому не забудьте розділити їх з пробілом для належного оформлення коду).

SELECT SaleID, Salesperson, SaleAmount, SaleDate,
        AVG(SaleAmount) OVER (
                      ORDER BY SaleDate
                     ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
                            ) AS MovingAverage

FROM Sales;

Ми використовували ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING, щоб дивитися на попередній день та наступний день після кожного рядка (тобто наше “Вікно”). Це, ймовірно, найбільш актуальна причина того, чому хтось взагалі додумався назвати ці функції саме так!

Тепер, коли ми зробили кілька дій з віконними функціями: найважливіше питання, яке ви, можливо, задаєте собі…

“Навіщо це взагалі має значення?”

Чому віконні функції мають значення
Основна відмінність між віконними функціями та агрегаційними функціями GROUP BY полягає в тому, що, в той час як агрегаційна функція повертає єдиний результат для кожної групи рядків (наприклад, сума або середнє значення групи), віконна функція повертає результат для кожного рядка, часто відносно інших рядків у вікні (наприклад, накопичена сума на кожному рядку). Серед моїх студентів це часто стало КЛЮЧЕВИМ для розуміння того, як працюють віконні функції, і, що ще важливіше, ЧОМУ ВОНИ НАСПРАВДІ МАЮТЬ ЗНАЧЕННЯ.

Володіння віконними функціями SQL подібно до додавання потужного інструмента до вашого інструментарію маніпулювання даними. Вони надають розширені можливості для складного аналізу даних та звітності, дозволяючи вам отримувати уявлення та приймати обґрунтовані рішення. Чи то розраховуючи накопичені суми, ранжуючи результати чи порівнюючи окремі рядки з агрегованими метриками набору даних, віконні функції є НЕЗАМІННИМИ. Приймайте їх у свою подорож SQL, і ви відзначите, що ваші запити незабаром досягнуть нових висот ефективності та ясності!

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

Фактично іноді бувають випадки, коли ви можете ВИКОРИСТОВУВАТИ або віконну функцію, або підзапит для виконання тієї ж самої задачі. Справжнє володіння SQL вимагатиме від вас бути вправним у кількох методах досягнення відповіді і вибору найкращого шляху вперед, що також передбачає розгляд того, який з них є найефективнішим з точки зору оптимізації запиту (більше про це у іншій частині серії “Володіння SQL”).

Типи віконних функцій

Тепер, коли у нас є надійний вступ до віконних функцій, ми повинні приділити час, щоб розглянути, які смаки віконних функцій доступні для розширення нашого репертуару.

Агрегатні віконні функції

Ці функції схожі на звичайні агрегаційні функції, але не зменшують кількість повернутих рядків. До прикладів належать SUM(), AVG(), MIN(), MAX(), COUNT().

1. SUM(): Ця функція повертає суму числового стовпця.
   
2. AVG(): Ця функція повертає середнє значення числового стовпця.
   
3. COUNT(): Ця функція повертає кількість рядків, які відповідають вказаному критерію.
   
4. MIN(): Ця функція повертає найменше значення вибраного стовпця.
   
5. MAX(): Ця функція повертає найбільше значення вибраного стовпця.

Віконні функції ранжування:
Ці функції присвоюють унікальний ранг кожному рядку в межах розділу результатів (або загального набору даних). Приклади це ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE().

1. RANK(): Ця функція присвоює унікальний ранг кожному відмінному рядку в межах розділу результатів. Ранги присвоюються в порядку, визначеному в розділі ORDER BY клавіатури в клавіатурі OVER(). Якщо два чи більше рядки мають однаковий ранг, кожен такий рядок отримує однаковий ранг, і наступні ранги пропускаються.
   
2. DENSE_RANK(): Ця функція працює схоже до RANK(), але, якщо два чи більше рядки мають однаковий ранг, наступний ранг не пропускається. Таким чином, якщо у вас є три елементи з рангом 2, наступний ранг буде 3.
   
3. ROW_NUMBER(): Ця функція присвоює унікальний номер рядка кожному рядку в межах розділу, незалежно від дублікатів. Якщо є однакові значення в упорядкованому наборі, вона все одно присвоює різні номери рядків кожному рядку.
   
4. Функція NTILE() використовується для розділення впорядкованого розділу на вказану кількість груп, або “плиток (tiles)”, і присвоює номер групи кожному рядку в розділі. Це може бути корисно для поділу набору даних на квартилі, децилі, або будь-який інший набір рівномірно розміщених груп.

Подивіться на різні функції ранжування поруч, щоб побачити, як вони можуть виглядати в коді.

Функції віконного значення
Ці функції повертають конкретні значення з кожного розділу. Вони надають можливість отримати доступ до конкретних даних з розділу, дозволяючи порівнювати або обчислювати різницю між значеннями в наборі результатів.

-- RANK() Example
SELECT
  SaleID,
  Salesperson,
  SaleAmount,
  RANK() OVER (ORDER BY SaleAmount DESC) AS RankByAmount
FROM Sales;

-- DENSE_RANK() ExampleSELECT
  SaleID,
  Salesperson,
  SaleAmount,
  DENSE_RANK() OVER (ORDER BY SaleAmount DESC) AS DenseRankByAmount
FROM Sales;

-- ROW_NUMBER() ExampleSELECT
  SaleID,
  Salesperson,
  SaleAmount,
  ROW_NUMBER() OVER (ORDER BY SaleAmount DESC) AS RowNumByAmount
FROM Sales;

-- NTILE() ExampleSELECT
  SaleID,
  Salesperson,
  SaleAmount,
  NTILE(4) OVER (ORDER BY SaleAmount DESC) AS Quartile
FROM Sales;

До прикладів належать FIRST_VALUE(), LAST_VALUE(), LEAD(), LAG().

1. FIRST_VALUE(): Ця функція повертає перше значення в упорядкованому наборі значень з розділу. Наприклад, ви можете використовувати цю функцію, щоб знайти першу здійснену продажу продавцем.
   
2. LAST_VALUE(): Ця функція повертає останнє значення в упорядкованому наборі значень з розділу. Її можна використовувати для знаходження найбільш останньої суми продажу для певного продукту.
   
3. LEAD(): Ця функція дозволяє отримувати доступ до даних з наступних рядків у тому ж самому наборі результатів, надаючи можливість порівнювати поточне значення зі значеннями з наступних рядків. Вона корисна для обчислення різниці в сумах продажів між двома послідовними днями.
   
4. LAG(): Схожа на LEAD(), функція LAG() дозволяє отримувати доступ до даних з попередніх рядків у наборі результатів, без потреби в самостійному з’єднанні. Це може бути корисно для порівняння поточних даних з історичними даними. Ці функції є потужними інструментами для аналізу даних, які дозволяють навігувати через ваші дані та отримувати висновки з конкретних точок даних в порівнянні з іншими.

-- FIRST_VALUE() and LAST_VALUE() Example
SELECT
  SaleID,
  Salesperson,
  SaleAmount,
  FIRST_VALUE(SaleAmount) OVER (ORDER BY SaleDate) AS FirstSaleAmount,
  LAST_VALUE(SaleAmount)  OVER (ORDER BY SaleDate
                               RANGE BETWEEN UNBOUNDED PRECEDING AND
                               UNBOUNDED FOLLOWING
                               ) AS LastSaleAmount
FROM Sales;

-- LEAD() and LAG() ExampleSELECT
  SaleID,
  Salesperson,
  SaleAmount,
  LAG(SaleAmount) OVER (ORDER BY SaleDate) AS PreviousSaleAmount,
  LEAD(SaleAmount) OVER (ORDER BY SaleDate) AS NextSaleAmount
FROM Sales;

Специфікація віконних діапозонів

Цей концепт вказує на підмножину рядків, які використовуються для виконання обчислень для конкретного рядка. Віконний діапозон(frame) може бути визначений за допомогою ROWS або RANGE і може бути необмеженим (враховує всі рядки) або обмеженим конкретним діапазоном.

ROWS: Визначає віконний діапозон(frame) в термінах фізичних рядків. Ви можете вказати фіксовану кількість рядків або використовувати UNBOUNDED PRECEDING та UNBOUNDED FOLLOWING для включення всіх рядків..

RANGE: Визначає віконний діапозон(frame) на основі логічних груп рядків. Схоже на ROWS, ви можете вказати діапазон або використовувати варіанти UNBOUNDED.

-- ROWS Window Frame Specification
SELECT
  SaleID,
  Salesperson,
  SaleAmount,
  AVG(SaleAmount) OVER (ORDER BY SaleDate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS MovingAvg
FROM Sales;

-- RANGE Window Frame SpecificationSELECT
  SaleID,
  Salesperson,
  SaleAmount,
  SUM(SaleAmount) OVER (ORDER BY SaleAmount RANGE BETWEEN 50 PRECEDING AND 50 FOLLOWING) AS CumulativeSum
FROM Sales;

Специфікація віконного диапозону(frame) є ключовою, коли ви хочете виконати обчислення для конкретного набору рядків, пов’язаних з поточним рядком, а не для всього розділу.

Виправлення проблем віконних функцій

Якщо ваша віконна функція не працює так, як очікувалося, розгляньте наступне:

• Перевірте вашу OVER-клаузу: OVER-клауза визначає, як веде себе віконна функція. Переконайтеся, що ви правильно вказали PARTITION BY і ORDER BY.
  
• Перегляньте синтаксис вашої функції: У кожної віконної функції є свій власний синтаксис. Переконайтеся, що ви переглядаєте синтаксис функції, яку ви використовуєте, щоб впевнитися, що він вірний.
  
• Дослідіть типи даних: Переконайтеся, що типи даних, які ви використовуєте в функції, сумісні. Наприклад, ви не можете виконувати операцію SUM для текстового поля (або стовпця із прихованим значенням рядка).

Оптимізація віконних функцій

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

• Зменште кількість рядків: Якщо можна, відфільтруйте свої дані перед застосуванням віконної функції. Чим менше рядків функція повинна обробляти, тим швидше буде виконано ваш запит. Це найкращий спосіб зробити так, щоб ви могли ефективніше відлагоджувати та запускати свій код, перш ніж випустити його на весь обсяг ваших даних.
  
• Використовуйте відповідне індексування: Якщо ви розділяєте або впорядковуєте ваші дані, переконайтеся, що для цих стовпців існують відповідні індекси. Це може значно прискорити виконання вашої віконної функції.
  
• Уникайте складного сортування: Якщо можливо, уникайте використання кількох стовпців у вашій клаузі ORDER BY в межах віконної функції. Кожен додатковий стовпець може збільшити час обчислення.
  
• Обмежте віконний діапозон (frame): За замовчуванням віконні функції враховують всі рядки в розділі. Якщо вам не потрібно враховувати всі рядки, використовуйте клаузу ROWS або RANGE, щоб обмежити віконний кадр.

Із цими розширеними віконними функціями та концепціями ви можете виконувати складні трансформації та обчислення для ваших даних, зробляючи ваші запити SQL більш потужними та пізнавальними. Незалежно від того, чи ранжуєте ви результати, обчислюєте накопичені суми чи отримуєте конкретні значення в межах розділу, віконні функції надають гнучкість та функціональність, необхідні для високорівневого аналізу даних. Ось зручний шпаргалка (джерело: learnsql.com), яким ви можете завжди користуватися для посилання, тепер, коли ми розглянули їх докладно.

Ми пройшли досить складний ландшафт віконних функцій SQL, розкривши їхню здатність перетворювати складний аналіз даних на більш управний завдання. Ці високорівневі функції не лише оптимізують наші запити, але й відкривають світ можливостей для дослідження та звітності з даних. Продовжуючи впроваджувати віконні функції у свій SQL репертуар, пам’ятайте, що ключ до майстерності – це практика і експерименти. Так що занурюйтеся, досліджуйте і нехай віконні функції будуть вашим провідником в світі високорівневих запитань SQL.

Щасливого використання Віконних функцій!

Насолоджуйтеся цією статтею? Натискайте кнопку “Follow”, щоб отримувати мої останні посібники та навчальні матеріали з програмування та науки про дані прямо в своєму стрічці Medium!

Дякую за читання!

🚀Долучайтесь до нашої спільноти Telegram:

• Data Life UA
• Group Data Analysis UA
• Group Data Engineering UA

🚀Долучайтесь до нашої спільноти FaceBook:

• Data Life UA