П'ять основних функцій вікна SQL, які ви повинні знати для інтерв'ю Data Science

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

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

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

Зважаючи на це, давайте зануритися в це!

1. Дельти з LEAD() і LAG()

 
LEAD() і LAG() в основному використовуються під час порівняння одного періоду часу з попереднім періодом часу для даної метрики. Наведу кілька прикладів…

• Ви можете отримати дельту між продажами кожного року та продажами попереднього року
• Ви можете отримати дельту в кількості реєстрацій/конверсій/відвідувань веб-сайту щомісяця
• Ви можете порівнювати відтік користувачів щомісяця

приклад:

Наступний запит показує, як ви можете запитати щомісячну відсоткову зміну витрат

with monthly_costs as ( SELECT date , monthlycosts , LEAD(monthlycosts) OVER (ORDER BY date) as previousCosts FROM costs )SELECT date , (monthlycosts - previousCosts) / previousCosts * 100 AS costPercentChange FROM monthly_costs

2. Кумулятивні суми за допомогою SUM() або COUNT()

 
Розрахунок поточних підсумків можна просто виконати за допомогою функції Windows, яка починається з SUM() або COUNT(). Це потужний інструмент, коли ви хочете показати зростання певного показника з часом. Точніше, це корисно за таких обставин:

• Отримайте загальний дохід і витрати з часом
• Отримайте загальну суму часу, витраченого на додаток для кожного користувача
• Отримайте загальну кількість конверсій за певний час

приклад:

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

SELECT date , monthlycosts , SUM(monthlycosts) OVER (ORDER BY date) as cumCosts FROM cost_table

3. Ковзні середні за допомогою AVG()

 
AVG() дійсно потужний у функціях Windows, оскільки дозволяє обчислювати Ковзаючі середні через деякий час.

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

Більш конкретно…

• Їх можна використовувати, щоб отримати загальну тенденцію тижневих продажів (чи з часом зростає середній показник?). Це вказує на зростання компанії.
• Їх також можна використовувати, щоб отримати загальну тенденцію щотижневих конверсій або відвідувань веб-сайту.

приклад:

Наведений нижче запит є прикладом отримання 10-денного ковзного середнього для конверсій.

SELECT Date , dailyConversions , AVG(dailyConversions) OVER (ORDER BY Date ROWS 10 PRECEDING) AS 10_dayMovingAverage FROM conversions

4. ROW_NUMBER()

 
ROW_NUMBER() особливо корисний, коли ви хочете отримати перший або останній запис. Наприклад, якщо у вас є таблиця, коли члени тренажерного залу прийшли до тренажерного залу, і ви хочете отримати дату першого дня, коли вони прийшли до тренажерного залу, ви можете РОЗДІЛИТИ ЗА клієнтом (ім’я/ідентифікатор) і ЗАМОВИТИ ЗА датою покупки. Потім, щоб отримати перший рядок, ви можете просто відфільтрувати рядки з rowNumber, рівним одиниці.

приклад:

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

with numbered_visits as ( SELECT memberId , visitDate , ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY purchaseDate) as rowNumber FROM gym_visits )SELECT * FROM numbered_visits WHERE rowNumber = 1

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

5. Рекордний рейтинг із DENSE_RANK()

 
DENSE_RANK() схожий на ROW_NUMBER(), за винятком того, що повертає той самий ранг для рівних значень. Щільне рейтингування є досить корисним, коли справа доходить до отримання найкращих рекордів, наприклад:

• Якщо ви хочете вибрати 10 найпопулярніших шоу Netflix цього тижня
• Якщо ви хочете отримати 100 найкращих користувачів на основі витрачених доларів
• Якщо ви хочете побачити поведінку 1000 найменш активних користувачів

приклад:

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

SELECT customerId , totalSales , DENSE_RANK() OVER (ORDER BY totalSales DESC) as rank FROM customers

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

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

Як завжди, бажаю тобі успіхів у навчанні!

 
 
Теренс Шин є ентузіастом даних з 3+-річним досвідом роботи в SQL і 2+-річним досвідом роботи з Python, а також блогером на Towards Data Science і KDnuggets.

Оригінал. Повідомлено з дозволу.

Джерело: https://www.kdnuggets.com/2022/01/top-five-sql-window-functions-know-data-science-interviews.html