ФУНКЦІЯ SQL WINDOWS (КОЛИ І ЯК ВИКОРИСТОВУВАТИ)

Posted on by data-life-ua

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

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

Я розповім, зокрема, про такі функції Windows, перелічені нижче

  1. LAG()
  2. LEAD()
  3. ROW_NUMBER()
  4. RANK()
  5. DENSE_RANK()
  6. FIRST_VALUE()
  7. LAST_VALUE()

1.LAG()

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

Ми можемо використовувати функцію LAG, щоб отримати всі записи з попереднього рядка, просто використовуючи цей синтаксис LAG:

LAG(НАЗВА_СТОВПЦЯ, N)

N вказує на те, на скільки рядків назад потрібно відступити

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

LAG(REVENUE, 1) OVER (ORDER BY MONTH_ID) AS PREVIOUS_MONTH_REVENUE

У нашому випадку ми впорядковуємо за MONTH_ID за зростанням, оскільки MONTH_ID ідеально впорядковується за зростанням.

ОСЬ ПОВНИЙ СИНТАКСИС


    SELECT 
    MONTH_ID, MONTH_NAME, 
    LAG(REVENUE, 1) OVER (ORDER BY MONTH_ID) AS PREVIOUS_MONTH_REVENUE,
    REVENUE AS CURRENT_MONTH_REVENUE
    FROM MONTHLY_REVENUE_DETAIL

Ось результат

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

  FORMULA FOR CALCULATING REVENUE CHANGE

MONTHLY_REVENUE_CHANGE = CURRENT_MONTH_REVENUE - PREVIOUS_MONTH_REVENUE

MONTHLY_PERCENTAGE_REVENUE_CHANGE = 
(CURRENT_MONTH_REVENUE - PREVIOUS_MONTH_REVENUE)/PREVIOUS_MONTH_REVENUE * 100
 SELECT 
    MONTH_ID, MONTH_NAME, 
    LAG(REVENUE, 1) OVER (ORDER BY MONTH_ID) AS PREVIOUS_MONTH_REVENUE,
    REVENUE,
       FORMAT((REVENUE - LAG(REVENUE, 1) OVER (ORDER BY MONTH_ID))/
    LAG(REVENUE, 1) OVER (ORDER BY MONTH_ID), 'P')
    FROM MONTHLY_REVENUE_DETAILS

ОСЬ РЕЗУЛЬТАТ

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

ДЛЯ КОРИСТУВАЧІВ MYSQL

 CREATE DATABASE WINDOWS_FUNCTION_PRACTICE_MEDIUM_ARTICLE;

   USE WINDOWS_FUNCTION_PRACTICE_MEDIUM_ARTICLE;

   CREATE TABLE MONTHLY_REVENUE_DETAILS
    (MONTH_ID INT,
    MONTH_NAME TEXT,
    REVENUE NUMERIC(10,2));


    INSERT INTO MONTHLY_REVENUE_DETAILS
    VALUES (1, 'JANUARY', 50500),
           (2, 'FEBRUARY', 42500),
           (3, 'MARCH', 65000),
           (4, 'APRIL', 71000),
           (5, 'MAY', 68000),
           (6, 'JUNE', 59000),
           (7, 'JULY', 81000),
           (8, 'AUGUST', 71500),
           (9, 'SPETEMBER', 64000),
           (10, 'OCTOBER', 87000),
           (11, 'NOVEMBER', 89000),
           (12, 'DECEMBER', 125000);
                
    CREATE TABLE TESLA_YEARLY_STOCK_PRICE_HISTORY(
    YEAR INT,
    REVENUE FLOAT);


    INSERT INTO TESLA_YEARLY_STOCK_PRICE_HISTORY
    VALUES (2009, 112000000),
           (2010, 117000000),
           (2011, 204000000),
           (2012, 413000000),
           (2013, 2013000000),
           (2014, 3198000000),
           (2015, 4046000000),
           (2016, 7000000000),
           (2017, 11759000000),
           (2018, 21461000000),
           (2019, 24578000000),
           (2020, 31536000000),
           (2021, 53823000000),
           (2022, 81462000000),
           (2023, 96773000000);

ДЛЯ КОРИСТУВАЧІВ SQL-СЕРВЕРІВ

 CREATE DATABASE WINDOWS_FUNCTION_PRACTICE_MEDIUM_ARTICLE;
   GO

   USE WINDOWS_FUNCTION_PRACTICE_MEDIUM_ARTICLE;
   GO

   CREATE TABLE MONTHLY_REVENUE_DETAILS
    (MONTH_ID INT,
    MONTH_NAME TEXT,
    REVENUE NUMERIC(10,2));
 GO

    INSERT INTO MONTHLY_REVENUE_DETAILS
    VALUES (1, 'JANUARY', 50500),
           (2, 'FEBRUARY', 42500),
           (3, 'MARCH', 65000),
           (4, 'APRIL', 71000),
           (5, 'MAY', 68000),
           (6, 'JUNE', 59000),
           (7, 'JULY', 81000),
           (8, 'AUGUST', 71500),
           (9, 'SPETEMBER', 64000),
           (10, 'OCTOBER', 87000),
           (11, 'NOVEMBER', 89000),
           (12, 'DECEMBER', 125000);
 GO
                
    CREATE TABLE TESLA_YEARLY_STOCK_PRICE_HISTORY(
    YEAR INT,
    REVENUE FLOAT);
 GO

    INSERT INTO TESLA_YEARLY_STOCK_PRICE_HISTORY
    VALUES (2009, 112000000),
           (2010, 117000000),
           (2011, 204000000),
           (2012, 413000000),
           (2013, 2013000000),
           (2014, 3198000000),
           (2015, 4046000000),
           (2016, 7000000000),
           (2017, 11759000000),
           (2018, 21461000000),
           (2019, 24578000000),
           (2020, 31536000000),
           (2021, 53823000000),
           (2022, 81462000000),
           (2023, 96773000000);
 GO

Перш ніж ми продовжимо, пам’ятайте, що ви можете зв’язатися зі мною в соціальній мережі, натиснувши на це посилання:www.linkedin.com/in/ifeanyi-okoye-a857911ba

2. LEAD() FUNCTION

LEAD() – це просто протилежність LAG(), оскільки вона повертає значення у новому рядку, а не значення у попередньому рядку.

LEAD() СИНТАКСИС

LEAD(НАЗВА_СТОВПЧИКА, N)

LEAD(REVENUE, 1) OVER (ORDER BY MONTH_ID) AS NEXT_MONTH_REVENUE
 SELECT 
    MONTH_ID, MONTH_NAME, REVENUE,
    LEAD(REVENUE, 1) OVER (ORDER BY MONTH_ID) AS NEXT_MONTH_REVENUE
    FROM MONTHLY_REVENUE_DETAILS
LEAD() FUNCTION

ЯК І ФУНКЦІЮ LAG(), МИ ТАКОЖ МОЖЕМО ВИКОРИСТОВУВАТИ ЦЮ ФУНКЦІЮ ДЛЯ ОБЧИСЛЕННЯ ВІДСОТКОВОГО ЗРОСТАННЯ ДОХОДУ, ВИКОРИСТОВУЮЧИ НАВЕДЕНИЙ НИЖЧЕ СИНТАКСИС.

  SELECT 
    MONTH_ID, MONTH_NAME,
    REVENUE,
    LEAD(REVENUE, 1) OVER (ORDER BY MONTH_ID) AS NEXT_MONTH_REVENUE,
    FORMAT((LEAD(REVENUE, 1) OVER (ORDER BY MONTH_ID) - REVENUE)
    /REVENUE, 'P')
    FROM MONTHLY_REVENUE_DETAILS

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

3. ROW_NUMBER(), RANK(), DENSE_RANK()

Ці 3 функції дуже схожі, але мають невеликі відмінності.

Розглянемо цю таблицю нижче.

Зверніть увагу на повторення 500 (500 зустрічається в цій таблиці 3 рази),

і (600 зустрічається в цій таблиці двічі).

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

  SELECT *,
    ROW_NUMBER() OVER (ORDER BY NUMBER) AS ROW_NUMBER,
    RANK() OVER (ORDER BY NUMBER) AS RANK,
    DENSE_RANK() OVER (ORDER BY NUMBER) AS DENSE_RANK
    FROM LIST_OF_NUMBERS

І ось результат.

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

RANK(): Rank присвоює однаковий номер, якщо номер однаковий, але пропускає номери залежно від кількості рядків, яким він присвоїв однаковий номер. У наведеному вище прикладі 500 зустрічається 3 рази, тому у 4-му рядку, де вперше зустрічається 600, 600 буде присвоєно номер 4

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

СЦЕНАРІЙ

В якому сценарії ми можемо використовувати ці функції.

Розглянемо таблицю нижче

Зверніть увагу на перші два стовпчики, кожна назва додатку має категорію, а кожна категорія має декілька назв додатків. Як бачимо вище, у категорії ПОСЛУГИ є додатки «нявкання», «метамаска» та «неторговий».

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

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

  SELECT Category,
           Module as App_Name,
        FORMAT(ROUND(SUM(Daily_Revenue), -3), 'C', 'US-US') 
        AS REVENUE_PER_MODULE,
        ROW_NUMBER() OVER (PARTITION BY Category
                                 ORDER BY SUM(Daily_Revenue) DESC) AS RN
    FROM [dbo].[DW_DEFI_REVENUE_TABLE]
    GROUP BY Category, Module

Результат

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

Синтаксис нижче.

  SELECT *
    FROM (
    SELECT Category,
           Module as App_Name,
        FORMAT(ROUND(SUM(Daily_Revenue), -3), 'C', 'US-US') AS REVENUE_PER_MODULE,
        ROW_NUMBER() OVER (PARTITION BY Category
                                 ORDER BY SUM(Daily_Revenue) DESC) AS RN
    FROM [dbo].[DW_DEFI_REVENUE_TABLE]
    GROUP BY Category, Module
    ) SUB_SUB    
    WHERE RN <= 2

Результат

Ми отримали лише 2 найкращі програми в кожній категорії за критерієм «Дохід».

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

Розглянемо таблицю нижче

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

  1. Використовуючи Partition з ROW_NUMBER(), ми можемо виявити повторювані дані

СИНТАКСИС НИЖЧЕ


   SELECT SALES_ID, ORDER_SUM,
   ROW_NUMBER() OVER 
   (PARTITION BY SALES_ID, ORDER_SUM ORDER BY SALES_ID, ORDER_SUM)
   FROM SALES_ORDERS

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

Повторювані дані можна ідентифікувати за допомогою запиту, наведеного нижче.

WITH DIRTY_TABLE AS 
   (
   SELECT SALES_ID, ORDER_SUM,
   ROW_NUMBER() OVER 
   (PARTITION BY SALES_ID, ORDER_SUM ORDER BY SALES_ID, ORDER_SUM)
   AS RN
   FROM SALES_ORDERS
   )
   SELECT *
   FROM DIRTY_TABLE
   WHERE RN > 1

РЕЗУЛЬТАТ

Нарешті, ми можемо видалити ці дублікати даних за допомогою речення DELETE І WHERE.

SYNTAX

   WITH DIRTY_TABLE AS 
   (
   SELECT SALES_ID, ORDER_SUM,
   ROW_NUMBER() OVER 
   (PARTITION BY SALES_ID, ORDER_SUM ORDER BY SALES_ID, ORDER_SUM)
   AS RN
   FROM SALES_ORDERS
   )
   DELETE
   FROM DIRTY_TABLE
   WHERE RN > 1

4. FIRST_VALUE() AND LAST_VALUE()

СЦЕНАРІЙ:

Ось таблиця, що показує загальний річний дохід Tesla з 2009 по 2023 рік

Вам потрібно обчислити зростання доходу Tesla з 2009 по 2023 рік і показати його у відсотках.

Ви можете легко отримати саме ПЕРШЕ ЗНАЧЕННЯ і ОСТАННЄ ЗНАЧЕННЯ на основі ієрархії з заданого набору даних за допомогою функцій FIRST_VALUE() і LAST_VALUE().

СИНТАКСИС ФУНКЦІЇ FIRST_VALUE()

    FIRST_VALUE(COLUMN_NAME) OVER
        (ORDER BY HIERARCHY_COLUMN) AS FIRST_VALUE

СИНТАКСИС LAST_VALUE()

  LAST_VALUE(COLUMN_NAME) OVER
        (ORDER BY HIERARCHY_COLUMN) AS LAST_VALUE

Спочатку давайте порахуємо зміну доходів між 2009 та 2023 роками.

СИНТАКСИС НИЖЧЕ

SELECT TOP 1
           YEAR, 
           REVENUE,
           FIRST_VALUE(REVENUE) OVER
           (ORDER BY YEAR) AS FIRST_YEAR_REVENUE,
           LAST_VALUE(REVENUE) OVER
           (ORDER BY YEAR DESC) AS MOST_RECENT_YEAR_REVENUE
    FROM TESLA_YEARLY_STOCK_PRICE_HISTORY

Зверніть увагу, що я використовую TOP 1, тому що мені потрібні тільки верхні значення в цьому запиті, для користувачів SQL SERVER ви можете використовувати TOP 1, для користувачів MYSQL ви можете використовувати LIMIT 1, як показано нижче.

SELECT     YEAR, 
           REVENUE,
           FIRST_VALUE(REVENUE) OVER
           (ORDER BY YEAR) AS FIRST_YEAR_REVENUE,
           LAST_VALUE(REVENUE) OVER
           (ORDER BY YEAR DESC) AS MOST_RECENT_YEAR_REVENUE
FROM TESLA_YEARLY_STOCK_PRICE_HISTORY
LIMIT 1

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

HISTORICAL REVENUE CHANGE FORMULA

REVENUE CHANGE = ((MOST_REVENT_REVENUE - FIRST_YEAR_REVENUE)
                  /FIRST_YEAR_REVENUE) * 100

ДЛЯ КОРИСТУВАЧІВ СЕРВЕРА SQL (ВИКОРИСТОВУЙТЕ АБО СКОПІЮЙТЕ ЗАПИТ НИЖЧЕ)

 SELECT TOP 1
           YEAR, 
        FORMAT(FIRST_VALUE(REVENUE) OVER
        (ORDER BY YEAR), 'C', 'US-US') AS FIRST_YEAR_REVENUE,
         FORMAT(LAST_VALUE(REVENUE) OVER
        (ORDER BY YEAR DESC), 'C', 'US-US') AS MOST_RECENT_YEAR_REVENUE,
        FORMAT((LAST_VALUE(REVENUE) OVER
        (ORDER BY YEAR DESC) - FIRST_VALUE(REVENUE) OVER
        (ORDER BY YEAR))/FIRST_VALUE(REVENUE) OVER
        (ORDER BY YEAR), 'P') AS PRICE_CHANGE_SINCE_FIRST_YEAR
    FROM TESLA_YEARLY_STOCK_PRICE_HISTORY

РЕЗУЛЬТАТ

Виручка Tesla зросла на 86 304% з 2009 по 2023 рік.

ДЛЯ КОРИСТУВАЧІВ MYSQL (ВИКОРИСТОВУЙТЕ АБО СКОПІЮЙТЕ ЗАПИТ НИЖЧЕ), ЩОБ ОТРИМАТИ ТАКИЙ САМИЙ РЕЗУЛЬТАТ

  SELECT 
         YEAR, 
        FORMAT(FIRST_VALUE(REVENUE) OVER
        (ORDER BY YEAR), 'C', 'US-US') AS FIRST_YEAR_REVENUE,
         FORMAT(LAST_VALUE(REVENUE) OVER
        (ORDER BY YEAR DESC), 'C', 'US-US') AS MOST_RECENT_YEAR_REVENUE,
        ((LAST_VALUE(REVENUE) OVER
        (ORDER BY YEAR DESC) - FIRST_VALUE(REVENUE) OVER
        (ORDER BY YEAR))/FIRST_VALUE(REVENUE) OVER
        (ORDER BY YEAR)) * 100 AS PRICE_CHANGE_SINCE_FIRST_YEAR
    FROM TESLA_YEARLY_STOCK_PRICE_HISTORY
    LIMIT 1

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

Сподіваємось, ця стаття допомогла вам у цьому.

Не соромтеся додавати свої коментарі в розділі коментарів, дякую за прочитання.

Пам’ятайте, що ви можете зв’язатися зі мною через LINKED IN за цим посиланням:

www.linkedin.com/in/ifeanyi-okoye-a857911ba

ОРИГІНАЛ СТАТТІ:SQL WINDOWS FUNCTION (WHEN AND HOW TO USE)

АВТОР СТАТІ:Kingsley Okoye

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

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

Leave a Reply

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

Related Posts