Освоєння SQL «Віконні Функції»: Повний посібник

Posted on by data-life-ua

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

Цей практичний посібник присвячений вивченню реальних застосувань віконних функцій, пристосований як для початківців, так і для досвідчених професіоналів у галузі науки про дані(Data Science).

Прочитавши цю статтю, ви не лише зрозумієте основи віконних функцій, але й навчитеся застосовувати їх у практичних сценаріях, таких як обчислення проміжних підсумків, змінна середня (moving avarage) або порівняння рядів у наборі даних(dataset).

1. Вступ до віконних функцій

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

На відміну від агрегатних функцій, віконні функції не згортають рядки в один вихідний рядок; вони зберігають ідентичність окремих рядків.
Огляд синтаксису:

SELECT 
    [Column1], 
    [Window Function] OVER (PARTITION BY [Column2] ORDER BY [Column3]) 
FROM [Table];

2. Підсумкові показники (Running Total) – перший крок

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

SELECT 
    Date,
    SaleAmount,
    SUM(SaleAmount) OVER (ORDER BY Date) AS RunningTotal
FROM Sales;
>>
|    Date    | SaleAmount | RunningTotal |
|------------|------------|--------------|
| 2023-01-01 |     100    |      100     |
| 2023-01-02 |     150    |      250     |
| 2023-01-03 |     200    |      450     |
| 2023-01-04 |     120    |      570     |
| 2023-01-05 |     180    |      750     |

Цей запит обчислює проміжний підсумок продажів, накопичуючи суми продажів у міру проходження по рядках, впорядкованих за датою.

3. Середня змінна – згладжування коливань

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

Функції вікна спрощують обчислення ковзних середніх.

SELECT 
    Date,
    SaleAmount,
    AVG(SaleAmount) OVER (ORDER BY Date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS MovingAverage
FROM Sales;
>>
|    Date    | SaleAmount | MovingAverage |
|------------|------------|---------------|
| 2023-01-01 |     100    |      125      |
| 2023-01-02 |     150    |      150      |
| 2023-01-03 |     200    |      156.67   |
| 2023-01-04 |     120    |      166.67   |
| 2023-01-05 |     180    |      150      |

Цей запит обчислює ковзне середнє продажів, усереднюючи кожну суму продажу з сумою до і після неї.

  • Для першого запису ковзне середнє обчислюється на основі 100 (самого запису) і 150 (наступного рядка).
  • Для другого запису середнє розраховується на основі попереднього, поточного та наступного рядків: 100, 150, 200.
  • І так далі для кожного запису, використовуючи два сусідніх рядки (попередній і наступний).

4. Порівняння рядків – розуміння через відмінності

Порівняння рядків – ще одне корисне застосування віконних функцій.

Наприклад, ви можете порівняти значення поточного рядка зі значенням попереднього.

SELECT 
    Date,
    SaleAmount,
    LAG(SaleAmount, 1) OVER (ORDER BY Date) AS PreviousSaleAmount,
    SaleAmount - LAG(SaleAmount, 1) OVER (ORDER BY Date) AS DifferenceFromPreviousDay
FROM Sales;
>>
|    Date    | SaleAmount | PreviousSaleAmount | DifferenceFromPreviousDay |
|------------|------------|--------------------|---------------------------|
| 2023-01-01 |     100    |        NULL        |            NULL           |
| 2023-01-02 |     150    |        100         |            50             |
| 2023-01-03 |     200    |        150         |            50             |
| 2023-01-04 |     120    |        200         |           -80             |
| 2023-01-05 |     180    |        120         |            60             |

Цей запит демонструє використання функції LAG для порівняння сум продажів за різні дні.

  • У першому рядку значення PreviousSaleAmount дорівнює NULL, оскільки немає попереднього запису.
  • У наступних рядках для кожного запису відображається попередня сума продажу та різниця між поточним і попереднім днями (SaleAmount - PreviousSaleAmount).

5. Кумулятивний розподіл – розуміння розкиду даних

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

SELECT 
    TestScore,
    PERCENT_RANK() OVER (ORDER BY TestScore) AS CumulativeDistribution
FROM TestResults;
>>
| TestScore | CumulativeDistribution |
|-----------|------------------------|
|    60     |         0.000          |
|    65     |         0.250          |
|    70     |         0.500          |
|    80     |         0.750          |
|    90     |         1.000          |

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

Значення PERCENT_RANK() обчислює відсотковий ранг кожного балу у відсортованому списку, де:

  • Найнижчий бал має ранг 0.
  • Найвищий бал має ранг 1.
  • Проміжні значення розподіляються пропорційно між мінімальним і максимальним балами.

6. Розбиття даних на частини – сегментування для кращого аналізу

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

SELECT 
    Department,
    EmployeeID,
    Salary,
    AVG(Salary) OVER (PARTITION BY Department) AS AverageDepartmentSalary
FROM Employees;
>>
| Department | EmployeeID | Salary | AverageDepartmentSalary |
|------------|------------|--------|-------------------------|
| HR         |     1      |  5000  |          5500           |
| HR         |     2      |  6000  |          5500           |
| IT         |     3      |  7000  |          7250           |
| IT         |     4      |  7500  |          7250           |
| Sales      |     5      |  4500  |          4750           |
| Sales      |     6      |  5000  |          4750           |

Тут запит обчислює середню зарплату в кожному відділі, сегментуючи дані за відділами.

  • Для кожного співробітника відображається середня зарплата в його відділі (AverageDepartmentSalary), обчислена на основі зарплат всіх співробітників у цьому відділі.
  • Функція PARTITION BY Department дозволяє обчислювати середню зарплату окремо для кожного відділу.

7. Ранжування даних – встановлення порядку та ієрархії

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

SELECT 
    EmployeeID,
    Salary,
    RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;
>>
| EmployeeID | Salary | SalaryRank |
|------------|--------|------------|
|     4      |  7500  |     1      |
|     3      |  7000  |     2      |
|     2      |  6000  |     3      |
|     1      |  5000  |     4      |
|     6      |  5000  |     4      |
|     5      |  4500  |     6      |

Цей запит ранжує працівників на основі їхньої заробітної плати, причому найвища заробітна плата отримує найвищий ранг.

  • Функція RANK() присвоює ранг кожному співробітнику на основі їхньої зарплати в порядку спадання.
  • Якщо кілька співробітників мають однакову зарплату, вони отримують однаковий ранг (наприклад, EmployeeID 1 і 6 мають однакову зарплату 5000 і ділять 4-е місце), після чого наступний ранг пропускається (тобто немає рангу 5).

8. Аналіз першого та останнього значення

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

SELECT 
    Date,
    SaleAmount,
    FIRST_VALUE(SaleAmount) OVER (ORDER BY Date) AS FirstSaleAmount,
    LAST_VALUE(SaleAmount) OVER (ORDER BY Date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastSaleAmount
FROM Sales;
>>
|    Date    | SaleAmount | FirstSaleAmount | LastSaleAmount |
|------------|------------|-----------------|----------------|
| 2023-01-01 |     100    |       100       |      180       |
| 2023-01-02 |     150    |       100       |      180       |
| 2023-01-03 |     200    |       100       |      180       |
| 2023-01-04 |     120    |       100       |      180       |
| 2023-01-05 |     180    |       100       |      180       |

Цей приклад показує, як визначити першу та останню суми продажу в послідовності даних про продажі.

  • FIRST_VALUE(SaleAmount) OVER (ORDER BY Date) обчислює перше значення суми продажу з першого запису (тобто для 2023-01-01).
  • LAST_VALUE(SaleAmount) OVER (ORDER BY Date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) повертає останню суму продажу з усього діапазону (тобто для 2023-01-05).
  • Кожен рядок відображає як перше, так і останнє значення суми продажу в межах усього набору даних.

9. Перцентилі – оцінка розподілу

Процентилі – це ще один аспект, де віконні функції “розривають”, допомагаючи зрозуміти розподіл даних у заданих інтервалах.

SELECT 
    TestScore,
    NTILE(4) OVER (ORDER BY TestScore) AS Quartile
FROM TestResults;
>>
| TestScore | Quartile |
|-----------|----------|
|    60     |    1     |
|    65     |    1     |
|    70     |    2     |
|    75     |    2     |
|    80     |    3     |
|    85     |    3     |
|    90     |    4     |
|    95     |    4     |

Цей запит ділить результати тесту на квартилі, показуючи, в який квартиль потрапляє кожен результат.

  • Функція NTILE(4) ділить результати на 4 рівні групи (квартилі) на основі значення TestScore.
  • Бали з 60 до 65 потрапляють у перший квартиль, 70 до 75 у другий, і так далі.

10. Розширені сценарії – просуваємось далі

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

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

Ця функція дозволяє проводити дуже тонкий аналіз даних.

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

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

SELECT 
    Category,
    SaleDate,
    SaleAmount,
    SUM(SaleAmount) OVER (
        PARTITION BY Category 
        ORDER BY SaleDate 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS RunningTotal
FROM Sales
WHERE SaleDate >= '2023-01-01'
ORDER BY Category, SaleDate;
>>
| Category |   SaleDate   | SaleAmount | RunningTotal |
|----------|--------------|------------|--------------|
|   A      |  2023-01-01  |    100     |     100      |
|   A      |  2023-01-03  |    150     |     250      |
|   A      |  2023-01-05  |    200     |     450      |
|   B      |  2023-01-02  |    120     |     120      |
|   B      |  2023-01-04  |    180     |     300      |
|   B      |  2023-01-06  |    140     |     440      |

Пояснення:

  • PARTITION BY Category: Цей параметр розділяє дані за кожною категорією товарів, гарантуючи, що проміжний підсумок розраховується окремо для кожної категорії.
  • ORDER BY SaleDate: Впорядковує продажі в кожній групі за датою продажу.
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Визначає рамку вікна, яка у цьому випадку включає всі рядки від початку розділу до поточного рядка у визначеному порядку.
  • WHERE SaleDate >= ‘2023-01-01’: Ця умова фільтрує дані, щоб включити тільки продажі за останній квартал.

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

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

Цей приклад демонструє універсальність і потужність віконних функцій у вирішенні складних завдань аналізу даних.

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

11. Найкращі практики та поради щодо ефективності

  • Використовуйте віконні функції розумно, особливо у великих наборах даних, оскільки вони можуть впливати на продуктивність.
  • Розуміти відмінності в реалізації віконних функцій у різних базах даних SQL.
  • Тестуйте та оптимізуйте свої запити, щоб переконатися, що вони працюють ефективно.

12. Висновок – вікно до розширених методів SQL

Віконні функції в SQL – це потужний інструмент, що пропонує широкий спектр можливостей для аналізу даних.

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

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

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

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

⭐️ Мій магазин Gumroad: https://codewarepam.gumroad.com/

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

https://ai-codehub.beehiiv.com/?source=post_page—–030cd263b224——————————–

Електронна книга-бестселер:

50+ найкращих ChatGPT-персонажів для індивідуальних інструкцій

Безкоштовні електронні книги:

  1. Опановуємо мистецтво швидкого інжинірингу
  2. Топ 50+ ефективних підказок ChatGPT для аналітиків даних
  3. Оволодіння ідеальними підказками з мистецтва ШІ: 50+ найкращих підказок
  4. 200+ найкращих авторських підказок
  5. Доменна майстерність для аспірантів даних

ОРИГІНАЛ СТАТТІ:Mastering SQL “WINDOW FUNCTIONS”: The Ultimate Guide

АВТОР СТАТІ:Richard Warepam

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

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

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

Posted in DBTagged

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts