30 питань про віконні функції SQL для інтерв’ю — 47-й день 100 днів виклику з Data Engineering, AI та Azure

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

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

Віконні функції

1. Що таке віконна функція в SQL і чим вона відрізняється від агрегатних функцій?
Відповідь: Віконна функція виконує обчислення по набору рядків, пов'язаних з поточним рядком, без об'єднання результату в один рядок (на відміну від агрегатних функцій). Наприклад, SUM() як агрегатна функція дає загальну суму, а SUM() як віконна функція дає поточну суму.

SELECT employee_id, salary, SUM(salary) OVER () AS total_salary  
FROM employees;

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

2. Поясніть призначення клаузи OVER() у віконній функції.
Відповідь: Клауза OVER() визначає «вікно» рядків, над якими функція працює. Вона може включати PARTITION BY для групування та ORDER BY для сортування.

SELECT employee_id, salary, AVG(salary) OVER (PARTITION BY department_id)   
AS avg_department_salary  
FROM employees;

Клауза OVER() — як прожектор — вона вказує функції, де спрямувати своє світло.

3. Чим віконні функції відрізняються від GROUP BY в SQL?
Відповідь: GROUP BY об'єднує рядки в підсумкові рядки, а віконні функції зберігають індивідуальні рядки та додають обчислені стовпці.

-- GROUP BY  
SELECT department_id, AVG(salary) AS avg_salary  
FROM employees  
GROUP BY department_id;  
-- Віконна функція  
SELECT employee_id, department_id, AVG(salary)   
OVER (PARTITION BY department_id) AS avg_salary  
FROM employees;

GROUP BY — як групове фото — всі тісно об'єднані. Віконні функції — як індивідуальні фото з фільтром групи.

4. Що відбудеться, якщо ви використовуєте віконну функцію без клаузи PARTITION BY?
Відповідь: Без PARTITION BY віконна функція працюватиме над усім результатом запиту.

SELECT employee_id, salary, SUM(salary) OVER () AS total_salary  
FROM employees;

Це як влаштувати вечірку без списку запрошених — всіх запрошено!

5. Чи можна використовувати віконну функцію в клаузі WHERE? Чому чи чому ні?
Відповідь: Ні, тому що віконні функції оцінюються після виконання WHERE. Використовуйте підзапит або CTE замість цього.

WITH ranked_employees AS (  
SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS rank  
FROM employees  
)  
SELECT * FROM ranked_employees WHERE rank = 1;

Спроба використовувати віконну функцію в WHERE — це як намагатися їсти десерт перед основною стравою — це просто не працює.

Практичне використання

6. Напишіть запит для обчислення поточної суми продажів для кожної категорії продуктів.

SELECT product_id, category, sales,   
SUM(sales) OVER (PARTITION BY category ORDER BY product_id) AS running_total  
FROM sales;

Це як вести облік того, скільки ви з'їли на шведському столі.

7.
Як знайти рейтинг співробітників на основі їхніх зарплат в межах кожного відділу?**

SELECT employee_id, department_id, salary,   
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank  
FROM employees;

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

8. Поясніть, чим відрізняються ROW_NUMBER() від RANK() та DENSE_RANK().
Відповідь:
— ROW_NUMBER() присвоює унікальні номери кожному рядку.
— RANK() залишає прогалини для рівних значень.
— DENSE_RANK() не залишає прогалин.

SELECT employee_id, salary,   
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,  
RANK() OVER (ORDER BY salary DESC) AS rank,  
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank  
FROM employees;

ROW_NUMBER() — це як суворий вчитель, RANK() — це поблажливий вчитель, а DENSE_RANK() — це крутий вчитель, якому не важливі прогалини.

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

WITH ranked_employees AS (  
SELECT employee_id, department_id, salary,   
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank  
FROM employees  
)  
SELECT * FROM ranked_employees WHERE rank <= 3;

Це як вибирати топ-3 начинки для піци для кожної піци.

10. Як би ви розрахували рухому середню за останні 5 днів продажів?

SELECT sale_date, sales,   
AVG(sales) OVER (ORDER BY sale_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg  
FROM daily_sales;

Це як обчислювати середній настрій за останні 5 днів — сподіваюся, що він був переважно хорошим!

Ключові віконні функції

11. У чому різниця між функціями LAG() та LEAD()?
Відповідь: LAG() дивиться на попередні рядки, а LEAD() — на майбутні.

SELECT employee_id, salary,   
LAG(salary, 1) OVER (ORDER BY employee_id) AS prev_salary,  
LEAD(salary, 1) OVER (ORDER BY employee_id) AS next_salary  
FROM employees;

LAG() — це як дивитися в дзеркало заднього виду, а LEAD() — як дивитися через лобове скло.

12. Напишіть запит за допомогою LAG(), щоб знайти різницю в продажах між двома послідовними днями для кожного продукту.

SELECT product_id, sale_date, sales,   
sales - LAG(sales, 1) OVER (PARTITION BY product_id ORDER BY sale_date) AS sales_diff  
FROM daily_sales;

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

13. Як працюють функції FIRST_VALUE() і LAST_VALUE() у віконній функції?
Відповідь: FIRST_VALUE() повертає перше значення у вікні, а LAST_VALUE() — останнє значення.

SELECT employee_id, salary,   
FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS highest_salary,  
LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_salary  
FROM employees;

FIRST_VALUE() — це як перший шматок піци, а LAST_VALUE() — це сумний, холодний шматок, який ніхто не хоче.

14. Коли ви б використовували функцію NTILE() в сценарії аналізу даних?
Відповідь: NTILE() розподіляє рядки по відрах. Це корисно для створення процентилів або квартилів.

SELECT employee_id, salary,   
NTILE(4) OVER (ORDER BY salary DESC) AS quartile  
FROM employees;

Це як поділяти своїх друзів на групи для гри — хтось завжди опиняється в останній групі.

15. Поясніть призначення функції PERCENT_RANK(). Як вона відрізняється від CUME_DIST()?
Відповідь: PERCENT_RANK() обчислює відносний ранг рядка, а CUME_DIST() обчислює кумулятивний розподіл.

SELECT employee_id, salary,   
PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank,  
CUME_DIST() OVER (ORDER BY salary) AS cumulative_dist  
FROM employees;

PERCENT_RANK() — це як питання: «Наскільки я хороший порівняно з іншими?», а CUME_DIST() — це як: «Який відсоток людей гірші за мене?»

Розбиття на частини та сортування

16.
Яка роль клаузи PARTITION BY у віконних функціях?**
Відповідь: Вона розділяє дані на групи (партиції), в межах яких функція виконується.

SELECT employee_id, department_id, salary,   
AVG(salary) OVER (PARTITION BY department_id) AS avg_department_salary  
FROM employees;

Це як розділення піци на частини — кожна частина має свої топінги.

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

SELECT region, month, revenue,   
SUM(revenue) OVER (PARTITION BY region ORDER BY month) AS cumulative_revenue  
FROM sales;

Це як відстежувати, скільки грошей ви витратили на каву кожного місяця.

18. Як клаузи ORDER BY всередині віконної функції впливають на результат?
Відповідь: Вона визначає порядок обробки рядків у вікні.

SELECT employee_id, salary,   
SUM(salary) OVER (ORDER BY employee_id) AS running_total  
FROM employees;

Це як стояти в черзі на американській гірці — порядок важливий!

19. Чи можна використовувати кілька стовпців в клаузі PARTITION BY? Наведіть приклад.
Відповідь: Так, можна розділяти за кількома стовпцями.

SELECT employee_id, department_id, job_id, salary,   
AVG(salary) OVER (PARTITION BY department_id, job_id) AS avg_salary  
FROM employees;

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

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

SELECT region, sale_date, sales,   
sales - LAG(sales, 1) OVER (PARTITION BY region ORDER BY sale_date) AS sales_diff  
FROM daily_sales;

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

Розширені концепції

21. Що відбувається, якщо ви використовуєте ROWS BETWEEN або RANGE BETWEEN у віконній функції?
Відповідь: Вони визначають віконний фрейм для обчислень. ROWS — фізичний (рядки), а RANGE — логічний (значення).

SELECT sale_date, sales,   
AVG(sales) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg  
FROM daily_sales;

Це як вибір кількості скибочок піци для вашого середнього значення.

22. Поясніть різницю між ROWS і RANGE у віконному фреймі.
Відповідь: ROWS рахує рядки, а RANGE рахує значення.

SELECT sale_date, sales,   
SUM(sales) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total_rows,  
SUM(sales) OVER (ORDER BY sale_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total_range  
FROM daily_sales;

ROWS — це як рахувати скибочки, а RANGE — це як рахувати топінги.

23. Напишіть запит для обчислення 7-денного рухомого середнього продажів для кожного магазину.

SELECT store_id, sale_date, sales,   
AVG(sales) OVER   
(PARTITION BY store_id ORDER BY sale_date   
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  
) AS rolling_avg  
FROM daily_sales;

Це як обчислювати середнє споживання кави за останні 7 днів — не забудьте пити воду!

24. Як віконні функції взаємодіють з DISTINCT у запиті?
Відповідь: DISTINCT застосовується після віконних функцій, тому це не впливає на їх обчислення.

SELECT DISTINCT department_id,   
AVG(salary) OVER (PARTITION BY department_id) AS avg_salary  
FROM employees;

DISTINCT — це як охоронець на вході — він пропускає тільки унікальні рядки на вечірку.

25.
Як написати запит для ранжування продуктів на основі їхніх загальних продажів, але з перезапуском рангу для кожної категорії?**

SELECT   
 CategoryID,   
 ProductID,   
 SUM(SalesAmount) AS TotalSales,  
 RANK() OVER (PARTITION BY CategoryID ORDER BY SUM(SalesAmount) DESC) AS ProductRank  
FROM Sales  
GROUP BY CategoryID, ProductID  
ORDER BY CategoryID, ProductRank;

SUM(SalesAmount):

• Агреґує загальний обсяг продажів для кожного продукту.
• Це необхідно, оскільки ранжування базується на загальному обсязі продажів для кожного продукту.

PARTITION BY CategoryID:

• Розділяє набір даних на групи, базуючись на CategoryID.
• Функція RANK() перезапускає ранжування для кожної категорії.

ORDER BY SUM(SalesAmount) DESC:

• Ранжує продукти за спаданням загальних продажів у кожній категорії.
• Продукти з найбільшими продажами в категорії отримують найвищий ранг (1).

GROUP BY CategoryID, ProductID:

• Групує дані за CategoryID і ProductID, щоб обчислити SUM(SalesAmount) для кожного продукту в кожній категорії.

RANK():

• Присвоює унікальний ранг кожному продукту в межах категорії на основі його загальних продажів.
• Якщо два продукти мають однакову кількість продажів, вони отримують однаковий ранг, а наступний ранг пропускається (наприклад, 1, 1, 3).

ORDER BY CategoryID, ProductRank:

• Сортує фінальний результат, щоб відображати ранги, згруповані за категоріями і відсортовані за рангом.

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

Налагодження та оптимізація

26. Як би ви усунули проблеми з продуктивністю в запитах з віконними функціями?
Відповідь: Перевірте на наявність непотрібних розділів (partitions), великих віконних фреймів або відсутніх індексів. Використовуйте EXPLAIN для аналізу плану запиту.
Це як з'ясувати, чому ваша доставка піци запізнюється — перевірте маршрут, трафік і піч!

27. Який вплив мають великі набори даних на продуктивність віконних функцій і як їх оптимізувати?
Відповідь: Великі набори даних можуть сповільнити роботу віконних функцій. Оптимізуйте їх, зменшивши кількість розділів, обмеживши віконні фрейми або використовуючи індексацію.
Це як намагатися з'їсти величезну піцу — розріжте її, щоб зробити її більш керованою! Віконні функції потужні, але вони можуть стати "вузьким місцем" для продуктивності при великих наборах даних. Ось як їх оптимізувати ефективно: Розуміння планів виконання

• Пояснення: Використовуйте інструменти, як EXPLAIN (PostgreSQL, MySQL) або EXPLAIN PLAN (Oracle), для аналізу того, як виконується запит. Ці плани покажуть, де ваш запит проводить найбільше часу (наприклад, сортування, розділення або сканування).

EXPLAIN ANALYZE   
SELECT   
 EmployeeID,   
 ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC)   
FROM Employees;

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

Зменшення вхідного набору даних

• Пояснення: Фільтрування даних до застосування віконних функцій мінімізує кількість оброблюваних рядків. Використовуйте клаузи WHERE або загальні вирази таблиць (CTE) для фільтрації непотрібних даних.

WITH FilteredData AS (  
 SELECT *   
 FROM Sales   
 WHERE SaleDate > '2023-01-01'  
)  
SELECT   
 ProductID,   
 ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY SaleAmount DESC) AS Rank  
FROM FilteredData;

• Чому: Чим менше рядків обробляється, тим швидше працює запит.

Це як прибирання кімнати перед тим, як запросити друзів — не потрібно, щоб вони оцінювали вашу купу білизни.

Індексування

• Пояснення: Індексуйте стовпці, що використовуються в клаузах PARTITION BY і ORDER BY. Індекси дозволяють базі даних швидше сортувати і групувати рядки.

CREATE INDEX idx_department_salary ON Employees (Department, Salary DESC);

• Чому: Сортування — одна з найбільш ресурсозатратних операцій.
  Індекс значно прискорює сортування.

Індекси — це як короткі шляхи, щоб уникнути трафіку — ви все одно їдете, але приїдете швидше.

Уникайте повних сканувань розділів

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

SELECT   
 EmployeeID,   
 AVG(Salary) OVER (  
 PARTITION BY Department   
 ORDER BY HireDate   
 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW  
 ) AS MovingAverage  
FROM Employees;

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

Подумайте про це, як про обмеження пошуку лише вашим районом, а не всім містом.

Матеріалізація проміжних результатів

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

CREATE TEMP TABLE TempSales AS  
SELECT   
 ProductID,   
 SUM(SaleAmount) AS TotalSales  
FROM Sales  
GROUP BY ProductID;  

SELECT   
 ProductID,   
 ROW_NUMBER() OVER (ORDER BY TotalSales DESC) AS Rank  
FROM TempSales;

• Чому: Попереднє обчислення результатів зменшує зайву обробку та може значно покращити продуктивність.

Навіщо готувати ту саму страву тричі, коли можна приготувати один раз і залишити собі залишки?

Використання паралелізму

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

--PostgreSQL  
SET max_parallel_workers_per_gather = 4;

• Чому: Паралелізм розподіляє навантаження між кількома ядрами процесора, що знижує загальний час виконання.

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

Попереднє сортування даних

• Пояснення: Попереднє сортування даних може зменшити витрати на сортування під час виконання запиту.

CREATE INDEX idx_sales_date ON Sales (SaleDate);  
SELECT   
 ProductID,   
 RANK() OVER (PARTITION BY Region ORDER BY SaleDate DESC) AS Rank  
FROM Sales;

• Чому: Попередньо відсортовані дані мінімізують навантаження на базу даних під час виконання.

Попереднє сортування — це як прибирання перед приходом прибиральників — це прискорює процес і робить вас виглядати добре.

Уникайте вкладених віконних функцій

• Пояснення: Уникайте вкладених віконних функцій. Замість цього використовуйте CTE або проміжні таблиці для ясності та ефективності.
• Поганий запит:

SELECT   
 EmployeeID,   
 RANK() OVER (ORDER BY SUM(Salary) OVER (PARTITION BY Department)) AS FinalRank  
FROM Employees;

• Ідеальний запит:

WITH DeptSalaries AS (  
 SELECT   
 Department,   
 EmployeeID,   
 SUM(Salary) AS TotalSalary  
 FROM Employees  
 GROUP BY Department, EmployeeID  
)  
SELECT   
 EmployeeID,   
 RANK() OVER (ORDER BY TotalSalary DESC) AS FinalRank  
FROM DeptSalaries;

• Чому: Це зменшує зайве обчислення.

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

Моніторинг продуктивності запитів

• Пояснення: Регулярно моніторьте продуктивність запитів за допомогою специфічних для бази даних інструментів, таких як:
• PostgreSQL: pg_stat_activity
• SQL Server: sys.dm_exec_requests
• MySQL: SHOW PROCESSLIST
• Чому: Ідентифікація повільних запитів допомагає виявити вузькі місця.

Це як спостерігати за вашою дитиною з тарілкою спагеті — ви знаєте, звідки йде безлад.

Оцінка альтернативних підходів

• Пояснення: В деяких випадках віконні функції можуть бути не найкращим рішенням.
  Використовуйте з'єднання (joins), підзапити або групування, якщо це спрощує задачу.
• Самоз'єднання (Self-Join):

SELECT   
 a.EmployeeID,   
 COUNT(b.EmployeeID) AS Rank  
FROM Employees a  
LEFT JOIN Employees b   
 ON a.Department = b.Department AND a.Salary < b.Salary  
GROUP BY a.EmployeeID;

• Чому: Альтернативи можуть зменшити обчислювальні витрати.

Навіщо підніматися на гору, коли є ліфт поруч?

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

28. Чи можна використовувати віконні функції разом із з'єднаннями (joins)? Наведіть приклад запиту.
Відповідь: Так, їх можна використовувати разом із з'єднаннями.

SELECT e.employee_id, e.salary, d.department_name,   
RANK() OVER (PARTITION BY d.department_name ORDER BY e.salary DESC) AS rank  
FROM employees e  
JOIN departments d ON e.department_id = d.department_id;

Це як об'єднати дві піци в одну мегапіцу — смачно і ефективно!

29. Чи можливо комбінувати кілька віконних функцій в одному запиті? Як?
Відповідь: Так, можна використовувати кілька віконних функцій в одному запиті.

SELECT employee_id, salary,   
RANK() OVER (ORDER BY salary DESC) AS rank,  
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank  
FROM employees;

Це як додати кілька начинок до піци — навіщо обмежуватися лише однією?

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

Відповідь:

WITH ranked_employees AS (  
 SELECT employee_id, department_id, salary,   
 RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank   
 FROM employees  
)  
SELECT * FROM ranked_employees WHERE rank = 2;

Це як знайти срібного призера у зарплатній гонці! — все одно досить чудово!

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

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

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

Перекладено з: 30 SQL Window Functionion Questions for Interviews- Day 47 of 100 Days of Data Engineering, AI and Azure Challenge