Віконними називають функції, які обробляють виділені набори рядків (вікна чи партиції) та записують результати обчислень в окремому стовпці.
Одна з головних переваг віконних функцій полягає в тому, що вони повертають ту саму кількість записів, яку отримали на вхід.
Уявіть, що ви хочете розрахувати деяке значення для групи рядків, об’єднаних загальною ознакою (наприклад, ID користувача). Якби ви скористалися оператором GROUP BY, то на виході замість вхідної кількості рядків у групі отримали один рядок з результатом.
При групуванні так відбувається завжди - число рядків у результуючій таблиці завжди дорівнює кількості груп у вхідній таблиці. В той же час віконна функція дозволяє проводити ті ж розрахунки з агрегацією по групах, але при цьому зберігає структуру вихідної таблиці - для кожного запису, що належить певній групі, в окремому стовпчику просто вказується результат агрегації.
20.1OVER - визначення вікна
Визначаються вікна за допомогою оператора OVER – у загальному вигляді його синтаксис виглядає так:
OVER (PARTITIONBY column_1, column_2, ... - визначаються партиції усередині вікна (аналог GROUPBY) ORDERBY column_3, ... - вказується сортування записів у партиціяхROWS/RANGEBETWEEN... - задаються межі вікна)
Для проведення обчислень за заданим в OVER вікну використовуються різні функції. Наприклад, з агрегуючою функцією SUM запис може виглядати так:
SELECTSUM(column) OVER (PARTITIONBY... ORDERBY... ROWS/RANGEBETWEEN...) ASsumFROMtable
Тепер кілька слів про інструкції, які можна вказувати під час створення вікна. Усього їх три:
PARTITION BY
ORDER BY ASC/DESC
ROWS/RANGE BETWEEN
При цьому всі вони є необов’язковими.
20.2PARTITION BY
Інструкція PARTITION BY визначає стовпець, яким дані ділитися на групи, які називаються партіціями. Наприклад, так як буде виглядати групування за user_id:
SELECT user_id, date, price, SUM(price) OVER (PARTITIONBY user_id) ASsumFROMtable
В результаті такого запиту для кожного запису в таблиці буде обчислено загальну суму всіх покупок даного користувача, а результат обчислень буде вписаний в стовпець sum:
user_id
date
price
sum
Alex
09.01
500
3950
Alex
13.03
3000
3950
Alex
02.08
450
3950
Kate
25.07
100
900
Kate
17.09
800
900
20.3ORDER BY
Інструкція ORDER BY визначає стовпець, яким значення всередині вікна будуть сортуватися при обробці. Наприклад, сортування по date всередині вікна задається так:
SELECT user_id, date, price, SUM(price) OVER (PARTITIONBY user_id ORDERBYdate) ASsumFROMtable
У цьому випадку для кожного запису в таблиці буде обчислено суму поточної та всіх попередніх покупок користувача. Результат обчислень буде вписаний у стовпець sum:
user_id
date
price
sum
Alex
09.01
500
500
Alex
13.03
3000
3500
Alex
02.08
450
3950
Kate
25.07
100
100
Kate
17.09
800
900
Чому ж рахується сума саме поточної та всіх попередніх, а не взагалі всіх покупок користувача?
Справа в тому, що при використанні у парі віконних та агрегатних функцій для кожного рядка визначається так звана рамка вікна - набір рядків у її партиції. Якщо в OVER вказати ORDER BY, то за замовчуванням рамка складатиметься з усіх рядків від початку партиції до поточного рядка (також у рамку будуть включені рядки, що дорівнюють поточному рядку за значенням вказаним у ORDER BY).
Саме тому в нашому прикладі сума вважається за кожним користувачем наростаючим підсумком.
Якщо ж ORDER BY не вказувати, то стандартна рамка буде складатися з усіх рядків партиції, тобто буде пораховано суму всіх покупок кожного користувача. Також можна не вказувати і PARTITION BY – тоді рамкою вікна стане вся таблиця, і ми просто порахуємо суму покупок усіх користувачів:
SELECT user_id, date, price, SUM(price) OVER () ASsumFROMtable
20.4ROWS/RANGE BETWEEN
Інструкції ROWS та RANGE можуть додатково задавати межі рамки вікна та обмежувати діапазон роботи функцій усередині партиції. Першим аргументом вказується початок рамки, другим - кінець рамки:
SELECT user_id, date, price, SUM(price) OVER (PARTITIONBY user_id ORDERBYdateROWSBETWEEN1PRECEDINGANDCURRENTROW) ASsumFROMtable
В результаті для кожного запису в таблиці буде обчислено суму поточної та попередньої покупок користувача, а результат буде знову вписаний в стовпець sum:
user_id
date
price
sum
Alex
09.01
500
500
Alex
13.03
3000
3500
Alex
02.08
450
3450
Kate
25.07
100
100
Kate
17.09
800
900
Рамку можна встановити в двох режимах:
ROWS — початок та кінець рамки визначаються рядками щодо поточного рядка.
RANGE — початок та кінець рамки задаються різницею значень у стовпці з ORDER BY.
Початок і кінець рамки задаються одним із наступних способів:
UNBOUNDED PRECEDING: вказує, що рамка починається з першого рядка партиції.
UNBOUNDED FOLLOWING: вказує, що рамка закінчується на останньому рядку партиції.
PRECEDING та FOLLOWING: вказують, що рамка починається або закінчується зі зсувом на задану кількість рядків щодо поточного рядка.
CURRENT ROW: вказує, що рамка починається або закінчується на поточному рядку.
Рамка завжди починається з початку рамки та закінчується кінцем рамки. Якщо кінець рамки не вказаний, мається на увазі CURRENT ROW.
За замовчуванням рамка визначається так:
RANGEUNBOUNDEDPRECEDING
Це рівносильно розширеному визначенню рамки:
RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW
Варіанти значення PRECEDING та значення FOLLOWING допускаються лише у режимі ROWS.
Наприклад, наступний запис означає створення рамки, що включає 3 рядки до поточної і 3 рядки після поточної (зрозуміло, поточний рядок також включається до рамки):
ROWSBETWEEN3PRECEDINGAND3FOLLOWING
Якщо в інструкції ORDER BY знаходиться стовпець date з типом даних DATE, то рамку вікна можна задати так:
RANGEBETWEEN'3 days'PRECEDINGAND'3 days'FOLLOWING
Це означатиме рамку, що включає 3 дні перед та 3 дні після поточної дати (включаючи поточну дату).
При вказівці рамки через RANGE обов’язковою умовою є лише один стовпчик в інструкції ORDER BY.
Як і решта інструкцій, інструкція ROWS/RANGE BETWEEN є необов’язковою.
20.5 Де та як можна використовувати віконні функції?
Також важливо знати, що віконні функції дозволяється використовувати у запиті лише у SELECT та ORDER BY. В інших операторах, включаючи WHERE, HAVING і GROUP BY, вони заборонені, оскільки логічно виконуються після звичайних агрегатних функцій.
Якщо потрібно відфільтрувати або згрупувати рядки після обчислення віконних функцій, можна використати вкладений запит:
Над результатом віконних функцій можна виконувати різні арифметичні операції. Також результат віконних функцій може виступати як аргумент інших функцій:
SELECT user_id, date, price, 1.15*SUM(price) OVER (PARTITIONBY user_id ORDERBYdate) ASsumFROMtableSELECT user_id, date, price, ROUND(AVG(price) OVER (PARTITIONBY user_id ORDERBYdate), 2) ASsumFROMtable
Також для визначення інструкцій усередині вікна можна використовувати розрахункові поля:
SELECT user_id, date, price, SUM(price) OVER (PARTITIONBY DATE_TRUNC('month', date)) AS monthly_sumFROMtable
Самі вікна також можна визначати через оператор WINDOW, а потім викликати по аліасу в операторі SELECT:
SELECTSUM(column) OVER w ASsumFROMtableWHERE...GROUPBY...HAVING...WINDOW w AS (PARTITIONBY... ORDERBY...ROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW )ORDERBY...LIMIT...
20.6 Використання віконних функцій з іншими функціями
У парі з віконними функціями можна використовувати функції різних класів:
Агрегатні функції SUM, AVG, MAX, MIN, COUNT
Всередині вікна до таких функцій можна застосовувати ORDER BY. Так, сортування дозволить отримати замість загальної суми наростаючу, а замість абсолютного максимуму — максимум серед значень до поточного.
Ранжируючі функції:
ROW_NUMBER: проста нумерація (1, 2, 3, 4, 5).
RANK: нумерація з урахуванням повторюваних значень з пропуском рангів (1, 2, 2, 4, 5).
DENSE_RANK: нумерація з урахуванням повторюваних значень без пропуску рангів (1, 2, 2, 3, 4).
Зрозуміло, для функцій ранжирування завжди потрібно вказувати ORDER BY, інакше вони працюватимуть некоректно.
Функції зміщення:
LAG, LEAD: значення попереднього чи наступного рядка.
FIRST_VALUE, LAST_VALUE: перше чи останнє значення у вікні.
Для функцій зміщення визначення правил сортування теж необхідне.
Почнемо знайомство з віконними функціями з найпростіших завдань. Для початку попрацюємо з ранжуючими функціями:
SELECTROW_NUMBER() OVER (PARTITIONBY... ORDERBY... ROWS/RANGEBETWEEN...) ASrow_numberFROMtableSELECTRANK() OVER (PARTITIONBY... ORDERBY... ROWS/RANGEBETWEEN...) ASrankFROMtableSELECTDENSE_RANK() OVER (PARTITIONBY... ORDERBY... ROWS/RANGEBETWEEN...) ASdense_rankFROMtable
Завдання 20.1 Застосуйте віконні функції до таблиці products і за допомогою ранжирующих функцій упорядкуйте всі товари за ціною від найдорожчих до найдешевших. Додайте до таблиці наступні колонки:
Колонку product_number із порядковим номером товару (функція ROW_NUMBER).
Колонку product_rank із рангом товару з пропусками рангів (функція RANK).
Колонку product_dense_rank з рангом товару без перепусток рангів (функція DENSE_RANK).
Не забувайте вказувати у вікні сортування записів — без неї ранжуючі функції можуть давати некоректний результат, якщо таблиця заздалегідь не відсортована. Поділ на партиції всередині вікна зараз не потрібний. Сортувати записи в результуючій таблиці також не потрібно.
Поля в результуючій таблиці: product_id, name, price, product_number, product_rank, product_dense_rank
Рішення
%%sqlSELECT product_id, name, price, row_number() OVER (ORDER BY price desc) as product_number, rank() OVER (ORDER BY price desc) as product_rank, dense_rank() OVER (ORDER BY price desc) as product_dense_rankFROM products
product_id
name
price
product_number
product_rank
product_dense_rank
0
13
caviar
800.0
1
1
1
1
37
mutton
559.0
2
2
2
2
15
olive oil
450.0
3
3
3
3
57
pork
450.0
4
3
3
4
43
decaffeinated coffee
400.0
5
5
4
...
...
...
...
...
...
...
82
6
crackers
25.0
83
83
49
83
5
coffee 3 in 1
15.0
84
84
50
84
73
cake
15.0
85
84
50
85
10
seeds
12.0
86
86
51
86
54
paper bag
1.0
87
87
52
87 rows × 6 columns
20.8 Агрегатні функції
З ранжуючими функціями розібралися, тепер давайте навчимося в парі з віконними і агрегуючі функції:
SELECTSUM(column) OVER (PARTITIONBY... ORDERBY... ROWS/RANGEBETWEEN...) ASsumFROMtable
Завдання 20.2 Застосуйте віконну функцію до таблиці products і за допомогою агрегатної функції в окремій колонці для кожного запису проставте ціну найдорожчого товару. Колонку із цим значенням назвіть max_price. Потім для кожного товару порахуйте частку його ціни у вартості найдорожчого товару - просто поділіть одну колонку на іншу. Отримані частки округліть до двох знаків після коми. Колонку із частками назвіть share_of_max.
Виведіть всю інформацію про товари, включаючи значення у нових колонках. Результат відсортуйте спочатку за спаданням ціни товару, потім за зростанням id товару.
Поля в результуючій таблиці: product_id, name, price, max_price, share_of_max
Пояснення:
У цьому вся задачі вікном виступає вся таблиця. Сортувати всередині вікна вказувати не потрібно.
З результатом агрегації з вікном можна проводити арифметичні та логічні операції. Також до нього можна застосовувати й інші функції - наприклад, округлення, як у цій задачі.
Рішення
%%sqlSELECT product_id, name, price,max(price) OVER () as max_price,round(price /max(price) OVER (), 2) as share_of_maxFROM productsORDER BY price desc, product_id
product_id
name
price
max_price
share_of_max
0
13
caviar
800.0
800.0
1.00
1
37
mutton
559.0
800.0
0.70
2
15
olive oil
450.0
800.0
0.56
3
57
pork
450.0
800.0
0.56
4
43
decaffeinated coffee
400.0
800.0
0.50
...
...
...
...
...
...
82
6
crackers
25.0
800.0
0.03
83
5
coffee 3 in 1
15.0
800.0
0.02
84
73
cake
15.0
800.0
0.02
85
10
seeds
12.0
800.0
0.02
86
54
paper bag
1.0
800.0
0.00
87 rows × 5 columns
20.9 Віконні функції та ORDER BY
А тепер давайте доповнимо наш попередній запит і вкажемо інструкцію ORDER BY для вікна, що працює в парі агрегатною функцією.
Завдання 20.3 Застосуйте дві віконні функції до таблиці products — одна з функцією MAX, а інша MIN — для обчислення максимальної та мінімальної ціни. Для двох вікон задайте інструкцію ORDER BY щодо зменшення ціни. Помістіть результат обчислень у дві колонки max_price та min_price.
Виведіть всю інформацію про товари, включаючи значення у нових колонках. Результат відсортуйте спочатку за спаданням ціни товару, потім за зростанням id товару.
Поля в результуючій таблиці: product_id, name, price, max_price, min_price
Після того, як вирішите завдання, проаналізуйте отриманий результат і подумайте, чому вийшли саме такі розрахунки. За потреби поверніться до першого кроку і ще раз уважно ознайомтеся з тим, як працює рамка вікна під час сортування.
Рішення
%%sqlSELECT product_id, name, price,max(price) OVER (ORDER BY price desc) as max_price,min(price) OVER (ORDER BY price desc) as min_priceFROM productsORDER BY price desc, product_id
product_id
name
price
max_price
min_price
0
13
caviar
800.0
800.0
800.0
1
37
mutton
559.0
800.0
559.0
2
15
olive oil
450.0
800.0
450.0
3
57
pork
450.0
800.0
450.0
4
43
decaffeinated coffee
400.0
800.0
400.0
...
...
...
...
...
...
82
6
crackers
25.0
800.0
25.0
83
5
coffee 3 in 1
15.0
800.0
15.0
84
73
cake
15.0
800.0
15.0
85
10
seeds
12.0
800.0
12.0
86
54
paper bag
1.0
800.0
1.0
87 rows × 5 columns
Тепер застосуємо віконну функцію з інструкцією ORDER BY для вирішення практичного завдання.
Як ми пам’ятаємо з першого кроку, вказівка сортування визначає рамку вікна від початку таблиці або партиції до поточного рядка. Давайте використовуємо цю особливість розрахунку кумулятивної суми, тобто зробимо так, щоб для кожного запису повертався результат додавання її значення зі значеннями всіх попередніх записів.
Завдання 20.4 Спочатку на основі таблиці orders сформуйте нову таблицю із загальною кількістю замовлень по дням. Підраховуючи кількість замовлень, не враховуйте скасовані замовлення (їх можна визначити за таблицею user_actions). Колонку з днями назвіть date, а колонку з числом замовлень orders_count.
Потім помістіть отриману таблицю в підзапит і застосуйте до неї віконну функцію в парі з функцією SUM для розрахунку кумулятивної суми числа замовлень. Не забудьте для вікна вказати інструкцію ORDER BY за датою.
Назвіть колонку з накопичувальною сумою orders_cum_count. В результаті такої операції значення кумулятивної суми для останнього дня має вийти рівним загальній кількості замовлень за весь період.
Сортувати результуючу таблицю робити не потрібно.
Поля в результуючій таблиці: date, orders_count, orders_cum_count
Пояснення:
Зверніть увагу, що віконні функції як результат повертають значення типу DECIMAL, незважаючи на те, що вхідне значення знаходиться у форматі INTEGER. Тому не забудьте отримане значення кумулятивної суми додатково привести до цілісного формату.
Рішення
%%sqlwith t1 as (SELECT creation_time::date as date, COUNT(*) as orders_countFROM ordersWHERE order_id NOT IN (SELECT order_id from user_actions WHERE action ='cancel_order')GROUP BY date)select date, orders_count, SUM(orders_count) OVER (ORDER BY date)::integer as orders_cum_countFROM t1
20.10 Віконні функції та PARTITION BY
У попередніх завданнях як вікно виступала вся таблиця. Тепер давайте навчимося додавати у параметри вікна поділ на партиції та попрацюємо з інструкцією PARTITION BY.
Завдання 20.5 Для кожного користувача у таблиці user_actions порахуйте порядковий номер кожного замовлення. Для цього застосовуйте віконну функцію ROW_NUMBER до колонки з часом замовлення. Не забудьте вказати поділ на партиції за користувачами та сортування усередині партицій. Скасовані замовлення не враховуйте. Нову колонку із порядковим номером замовлення назвіть order_number. Результат відсортуйте спочатку за зростанням ID користувача, потім за зростанням order_number. Додати LIMIT 1000.
Поля в результуючій таблиці: user_id, order_id, time, order_number
Рішення
%%sqlSELECT user_id, order_id, time, row_number() OVER (PARTITION BY user_id ORDER BY time) as order_numberFROM user_actionsWHERE order_id notin (SELECT order_id FROM user_actions WHERE action ='cancel_order')ORDER BY user_id, order_number limit 1000
user_id
order_id
time
order_number
0
1
1
2022-08-24 01:52:00
1
1
1
4683
2022-08-27 20:56:00
2
2
1
22901
2022-09-02 00:58:00
3
3
1
23149
2022-09-02 02:36:00
4
4
2
2
2022-08-24 06:37:00
1
...
...
...
...
...
995
248
13935
2022-08-30 17:13:00
8
996
248
15518
2022-08-31 02:25:00
9
997
249
287
2022-08-25 03:54:00
1
998
249
758
2022-08-25 15:19:00
2
999
249
7347
2022-08-28 19:27:00
3
1000 rows × 4 columns
20.11 Віконні функції та зміщення
Тепер давайте попрацюємо з функціями зміщення – у цьому теж немає нічого складного:
SELECTLAG(column, 1) OVER (PARTITIONBY... ORDERBY... ROWS/RANGEBETWEEN...) AS lag_valueFROMtableSELECTLEAD(column, 1) OVER (PARTITIONBY... ORDERBY... ROWS/RANGEBETWEEN...) AS lead_valueFROMtable
В якості першого аргументу функцій LAG і LEAD вказується колонка зі значеннями, в якості другого — те, скільки рядків проводити зміщення (назад і вперед відповідно). Другий аргумент можна не вказувати, за умовчанням його значення дорівнює 1.
Завдання 20.6 Доповніть запит із попереднього завдання та за допомогою віконної функції для кожного замовлення кожного користувача розрахуйте, скільки часу пройшло з попереднього замовлення.
Для цього спочатку в окремому стовпці за допомогою LAG зробіть зміщення по стовпцю часу на одне значення назад. Стовпець зі зміщеними значеннями назвіть time_lag. Потім відніміть від кожного значення колонці time нове значення зі зміщенням (або можете використовувати вже знайому функцію AGE). Назвіть колонку з отриманим інтервалом time_diff. Змінювати формат відображення значень не потрібно, вони повинні мати приблизно такий вигляд:
3 days, 12:18:22
Як і раніше, не враховуйте скасовані замовлення. Також залиште у запиті порядковий номер кожного замовлення, розрахований на минулому етапі. Результат відсортуйте спочатку за зростанням ID користувача, потім за зростанням порядкового номера замовлення. Додати LIMIT 1000.
Поля в результуючій таблиці: user_id, order_id, time, order_number, time_lag, time_diff
Пояснення:
Не забувайте про поділ на партиції та сортування усередині вікна.
Також зверніть увагу, що в результаті зміщення перших замовлень кожного користувача в колонці time_lag вийшли пропущені значення. Для таких записів функція не знайшла попередніх значень та повернула NULL. Те саме сталося в записах користувачів з одним замовленням — усередині партиції з одним записом просто нема куди зміщатися.
Пропущені значення, що утворилися, прибирати з результату не потрібно.
Рішення
%%sqlSELECT user_id, order_id, time, row_number() OVER (PARTITION BY user_id ORDER BY time) as order_number, lag(time, 1) OVER (PARTITION BY user_id ORDER BY time) as time_lag, time - lag(time, 1) OVER (PARTITION BY user_id ORDER BY time) as time_diffFROM user_actionsWHERE order_id notin (SELECT order_id FROM user_actions WHERE action ='cancel_order')ORDER BY user_id, order_number limit 1000
user_id
order_id
time
order_number
time_lag
time_diff
0
1
1
2022-08-24 01:52:00
1
NaT
NaT
1
1
4683
2022-08-27 20:56:00
2
2022-08-24 01:52:00
3 days 19:04:00
2
1
22901
2022-09-02 00:58:00
3
2022-08-27 20:56:00
5 days 04:02:00
3
1
23149
2022-09-02 02:36:00
4
2022-09-02 00:58:00
0 days 01:38:00
4
2
2
2022-08-24 06:37:00
1
NaT
NaT
...
...
...
...
...
...
...
995
248
13935
2022-08-30 17:13:00
8
2022-08-30 11:04:00
0 days 06:09:00
996
248
15518
2022-08-31 02:25:00
9
2022-08-30 17:13:00
0 days 09:12:00
997
249
287
2022-08-25 03:54:00
1
NaT
NaT
998
249
758
2022-08-25 15:19:00
2
2022-08-25 03:54:00
0 days 11:25:00
999
249
7347
2022-08-28 19:27:00
3
2022-08-25 15:19:00
3 days 04:08:00
1000 rows × 6 columns
Давайте для повноти картини порахуємо, скільки в середньому проходить між замовленнями кожного користувача.
Завдання 20.7 На основі запиту попереднього завдання для кожного користувача розрахуйте, скільки в середньому часу проходить між його замовленнями. Порахуйте цей показник лише для користувачів, які за весь час оформили більше одного замовлення.
Середній час між замовленнями виразіть у годинах, округливши значення до цілого числа. Колонку із середнім значенням часу назвіть hours_between_orders. Результат відсортуйте за зростанням id користувача.
Додайте в запит оператор LIMIT і включіть у результат лише перші 1000 записів.
Поля у результуючій таблиці: user_id, hours_between_orders
Пояснення:
Щоб перевести значення інтервалу в години, необхідно витягти з нього кількість секунд, а потім поділити на кількість секунд в одній годині. Для отримання кількості секунд з інтервалу можна скористатися такою конструкцією:
Функція EXTRACT працює аналогічно до функції DATE_PART, яку ми розглядали раніше, але має дещо інший синтаксис. Спробуйте скористатися функцією EXTRACT у цій задачі.
В результаті всіх розрахунків для кожного користувача з більш ніж одним замовленням, у вас має вийти ціле число годин, яке в середньому проходить між його замовленнями. Подумайте, як отримати з даних користувачів з одним замовленням. За потреби додатково перетворіть середнє значення годин на цілий тип даних.
Повторювати всі попередні віконні функції з попереднього запиту не обов’язково — залиште найнеобхідніше.
Рішення
%%sqlSELECT user_id, avg(time_diff)::integer as hours_between_ordersFROM (SELECT user_id, order_id, time, extract(epoch FROM (time - lag(time, 1) OVER ( PARTITION BY user_id ORDER BY time)))/3600as time_diff FROM user_actions WHERE order_id notin (SELECT order_id FROM user_actions WHERE action ='cancel_order')) tWHERE time_diff isnot nullGROUP BY user_idORDER BY user_id limit 1000
user_id
hours_between_orders
0
1
72
1
2
108
2
3
64
3
4
77
4
6
11
...
...
...
995
1125
35
996
1126
49
997
1127
37
998
1129
33
999
1130
20
1000 rows × 2 columns
20.12 Задачі з ROWS BETWEEN
Настав час трохи попрацювати з інструкцією ROWS BETWEEN, яку докладно розглядали раніше. Нагадаємо, що початок і кінець рамки задаються такими способами:
SELECTSUM(column_3) OVER (PARTITIONBY column_1 ORDERBY column_2 ROWSBETWEENUNBOUNDEDPRECEDINGAND3FOLLOWING) ASsumFROMtable
Але в яких завданнях корисно вказувати рамку для розрахунків? Перше, що спадає на думку будь-якому аналітику, — ковзна середня.
Ковзне середнє - це показник, який обчислюється в кожній точці часового ряду як середнє значення за N попередніх періодів (днів, тижнів, місяців тощо в залежності від рівня агрегації даних). Ковзне середнє переміщається по часовому ряду і щоразу враховує фіксовану кількість значень - для проведення таких розрахунків якраз і потрібна рамка вікна, яка задається інструкцією ROWS BETWEEN.
Спробуймо провести такі розрахунки на наших даних.
Завдання 20.8 Спочатку на основі таблиці orders сформуйте нову таблицю із загальною кількістю замовлень щодня. Ви вже робили у завданні 20.4. Підраховуючи кількість замовлень, не враховуйте скасовані замовлення (їх можна визначити за таблицею user_actions). Назвіть колонку з кількістю замовлень orders_count.
Потім помістіть отриману таблицю в підзапит і застосуйте до неї віконну функцію в парі з функцією AVG для розрахунку ковзного середнього числа замовлень. Ковзне середнє для кожного запису рахуйте за трьома попередніми днями. Подумайте, як правильно встановити межі рамки, щоб отримати коректні розрахунки.
Отримані значення ковзного середнього округліть до двох знаків після коми. Колонку із розрахованим показником назвіть moving_avg. Сортувати результуючу таблицю робити не потрібно.
Поля у результуючій таблиці: date, orders_count, moving_avg
Пояснення:
При вирішенні завдання можете пробувати різні межі рамки та перевіряти себе вручну. Важливо для кожної дати врахувати у розрахунках саме 3 попередні дні.
Заповнювати пропущені значення у цій задачі не потрібно. Подумайте чому вони могли з’явитися.
Рішення
%%sqlSELECT date, orders_count,round(avg(orders_count) OVER (ORDER BY date rows between 3 preceding and1 preceding),2) as moving_avgFROM (SELECT creation_time :: date as date, count(order_id) as orders_count,sum(count(order_id)) OVER (ORDER BY creation_time :: date) as orders_cum_count FROM orders WHERE order_id notin (SELECT order_id FROM user_actions WHERE action ='cancel_order') GROUP BY date) as t1
date
orders_count
moving_avg
0
2022-08-24
138
NaN
1
2022-08-25
1059
138.00
2
2022-08-26
1447
598.50
3
2022-08-27
2141
881.33
4
2022-08-28
2998
1549.00
5
2022-08-29
3267
2195.33
6
2022-08-30
3371
2802.00
7
2022-08-31
3410
3212.00
8
2022-09-01
3688
3349.33
9
2022-09-02
5001
3489.67
10
2022-09-03
5709
4033.00
11
2022-09-04
6010
4799.33
12
2022-09-05
4675
5573.33
13
2022-09-06
3451
5464.67
14
2022-09-07
4777
4712.00
15
2022-09-08
5474
4301.00
20.13 Задача з CASE
Тепер навчимося разом із віконними функціями застосовувати конструкцію CASE. Приклад такого запису:
SELECTCASEWHENSUM(column) OVER (...) >100THEN'above 100'WHENSUM(column) OVER (...) <100THEN'below 100'ELSE'equal 100'ENDAS sum_caseFROMtable
Завдання 20.9 Позначте в окремій таблиці тих кур’єрів, які доставили у вересні 2022 замовлень більше, ніж у середньому всі кур’єри.
Спочатку для кожного кур’єра у таблиці courier_actions розрахуйте загальну кількість доставлених у вересні замовлень. Потім в окремому стовпці за допомогою віконної функції вкажіть, скільки в середньому замовлень доставили цього місяця всі кур’єри. Після цього порівняйте кількість замовлень, доставлених кожним кур’єром, із середнім значенням у новому стовпці. Якщо кур’єр доставив більше замовлень, ніж у середньому всі кур’єри, то окремому стовпці за допомогою CASE вкажіть число 1, інакше вкажіть 0.
Колонку з результатом порівняння назвіть is_above_avg, колонку з кількістю доставлених замовлень кожним кур’єром – delivered_orders, а колонку із середнім значенням – avg_delivered_orders. При розрахунку середнього значення округліть його до двох знаків після коми. Результат відсортуйте за зростанням id кур’єра.
Поля в результуючій таблиці: courier_id, delivered_orders, avg_delivered_orders, is_above_avg
Пояснення:
Таблицю з кур’єрами та числом доставлених замовлень сформуйте на основі таблиці courier_actions та перед застосуванням віконних функцій помістіть її у підзапит.
З цим завданням можна впоратися без конструкції CASE, якщо сконвертувати результат логічної операції (TRUE чи FALSE) у тип даних INT. Можете спробувати розв’язати завдання різними способами.
Рішення
%%sqlSELECT courier_id, delivered_orders,round(avg(delivered_orders) OVER (), 2) as avg_delivered_orders,case when delivered_orders >round(avg(delivered_orders) OVER (), 2) then 1else0 end as is_above_avgFROM (SELECT courier_id, count(order_id) as delivered_orders FROM courier_actions WHERE action ='deliver_order'and date_part('month', time) =9and date_part('year', time) =2022 GROUP BY courier_id) tORDER BY courier_id-- або-- SELECT *,--round(avg(delivered_orders) OVER (), 2) as avg_delivered_orders,-- (delivered_orders > avg(delivered_orders) OVER ())::integer as is_above_avg-- FROM (SELECT courier_id,-- count(*) as delivered_orders-- FROM courier_actions-- WHERE date_part('month', time) =09--and date_part('year', time) =2022--and action ='deliver_order'-- GROUP BY courier_id) t1
courier_id
delivered_orders
avg_delivered_orders
is_above_avg
0
1
20
13.76
1
1
2
14
13.76
1
2
3
23
13.76
1
3
4
9
13.76
0
4
5
23
13.76
1
...
...
...
...
...
2817
3163
4
13.76
0
2818
3164
3
13.76
0
2819
3165
2
13.76
0
2820
3166
2
13.76
0
2821
3167
3
13.76
0
2822 rows × 4 columns
20.14 Задача з FILTER
У цьому завданні розглянемо ще більш сучасний функціонал і навчимося разом з віконними функціями застосовувати оператор FILTER, з яким ми вже працювали у завданні 17.16.
Якщо до визначення віконної функції додати пропозицію FILTER, то у вікно потраплять лише ті вхідні рядки, для яких умова фільтра буде обчислена як дійсна.
При цьому пропозиція FILTER допускається тільки для агрегатних віконних функцій.
У загальному вигляді вся конструкція виглядає так:
SELECT agg_function(column) FILTER (WHERE [condition]) OVER (...)FROMtable
Завдання 20.10 Застосуйте віконну функцію до таблиці products та за допомогою агрегуючої функції в окремій колонці для кожного запису проставте середню ціну всіх товарів. Колонку із цим значенням назвіть avg_price. Потім за допомогою віконної функції та оператора FILTER в окремій колонці розрахуйте середню ціну товарів без урахування найдорожчого. Колонку із цим середнім значенням назвіть avg_price_filtered. Отримані середні значення в колонках avg_price та avg_price_filtered округліть до двох знаків після коми.
Виведіть всю інформацію про товари, включаючи значення у нових колонках. Результат відсортуйте спочатку за спаданням ціни товару, потім за зростанням id товару.
Поля в результуючій таблиці: product_id, name, price, avg_price, avg_price_filtered
Пояснення:
У цьому завданні вікном знову виступає вся таблиця. Сортувати всередині вікна не потрібно.
Рішення
%%sqlSELECT product_id, name, price,round(avg(price) OVER (), 2) as avg_price,round(avg(price) filter (WHERE price != (SELECT max(price) FROM products))OVER (), 2) as avg_price_filteredFROM productsORDER BY price desc, product_id
product_id
name
price
avg_price
avg_price_filtered
0
13
caviar
800.0
133.54
125.79
1
37
mutton
559.0
133.54
125.79
2
15
olive oil
450.0
133.54
125.79
3
57
pork
450.0
133.54
125.79
4
43
decaffeinated coffee
400.0
133.54
125.79
...
...
...
...
...
...
82
6
crackers
25.0
133.54
125.79
83
5
coffee 3 in 1
15.0
133.54
125.79
84
73
cake
15.0
133.54
125.79
85
10
seeds
12.0
133.54
125.79
86
54
paper bag
1.0
133.54
125.79
87 rows × 5 columns
А тепер ще одне завдання на фільтрацію по вікну — цього разу складніше.
Завдання 20.11 Для кожного запису в таблиці user_actions за допомогою віконних функцій та FILTER порахуйте скільки замовлень зробив і скільки скасував кожен користувач на момент здійснення нової дії.
Іншими словами, для кожного користувача в кожний момент часу порахуйте дві накопичувальні суми - числа оформлених та відмінених замовлень. Якщо користувач оформляє замовлення, кількість оформлених ним замовлень збільшуйте на 1, якщо скасовує — збільшуйте на 1 кількість скасуваних.
Колонки з накопичувальними сумами числа оформлених та скасованих замовлень назвіть відповідно created_orders та canceled_orders. На основі цих двох колонок для кожного запису користувача порахуйте показник cancel_rate, тобто частку скасованих замовлень у загальній кількості оформлених замовлень. Значення показника округліть до двох знаків після коми. Назвіть колонку з ним cancel_rate.
В результаті у вас повинні вийти три нові колонки з динамічними показниками, які змінюються в часі з кожною новою дією користувача.
У результуючій таблиці відобразіть усі колонки з вихідної таблиці разом із новими колонками. Відсортуйте результат за колонками user_id, order_id, time – за зростанням значень у кожній.
Додайте в запит оператор LIMIT та виведіть лише перші 1000 рядків результуючої таблиці.
Поля в результуючій таблиці: user_id, order_id, action, time, created_orders, canceled_orders, cance`l_rate
Пояснення:
Подумайте, як правильно встановити вікна і які фільтри в них потрібно вказати.
Не забудьте змінити тип даних під час поділу двох цілих значень.
Рішення
%%sqlSELECT user_id, order_id, action, time, created_orders, canceled_orders,round(canceled_orders::decimal / created_orders, 2) as cancel_rateFROM (SELECT user_id, order_id, action, time, count(order_id) filter (WHERE action !='cancel_order') OVER (PARTITION BY user_id ORDER BY time) as created_orders, count(order_id) filter (WHERE action ='cancel_order') OVER (PARTITION BY user_id ORDER BY time) as canceled_orders FROM user_actions) tORDER BY user_id, order_id, time limit 1000
user_id
order_id
action
time
created_orders
canceled_orders
cancel_rate
0
1
1
create_order
2022-08-24 01:52:00
1
0
0.0
1
1
4683
create_order
2022-08-27 20:56:00
2
0
0.0
2
1
22901
create_order
2022-09-02 00:58:00
3
0
0.0
3
1
23149
create_order
2022-09-02 02:36:00
4
0
0.0
4
2
2
create_order
2022-08-24 06:37:00
1
0
0.0
...
...
...
...
...
...
...
...
995
227
12572
create_order
2022-08-30 08:36:00
4
0
0.0
996
227
12578
create_order
2022-08-30 08:38:00
5
0
0.0
997
227
23268
create_order
2022-09-02 04:03:00
6
0
0.0
998
227
25873
create_order
2022-09-02 17:52:00
7
0
0.0
999
227
25885
create_order
2022-09-02 17:54:00
8
0
0.0
1000 rows × 7 columns
Завдання 20.12 З таблиці courier_actions відберіть топ 10% кур’єрів за кількістю доставлених за весь час замовлень. Виведіть id кур’єрів, кількість доставлених замовлень та порядковий номер кур’єра відповідно до кількості доставлених замовлень.
У кур’єра, який доставив найбільше замовлень, порядковий номер повинен дорівнювати 1, а кур’єра з найменшою кількістю замовлень — числу, що дорівнює десяти відсоткам від кількості кур’єрів у таблиці courier_actions.
Під час розрахунку номера останнього кур’єра округляйте значення до цілого числа.
Колонки з кількістю доставлених замовлень та порядковим номером назвіть відповідно orders_count та courier_rank. Результат відсортуйте за зростанням порядкового номера кур’єра.
Поля у результуючій таблиці: courier_id, orders_count, courier_rank
Пояснення:
Якщо у двох кур’єрів виявилося однакове число доставлених замовлень, вищий ранг ми надамо кур’єру з меншим id. Наприклад, якщо у кур’єрів з id 10 і 80 виявилася максимальна кількість замовлень, то перший ранг ми надамо кур’єру з id 10.
Рішення
%%sqlwith courier_count as (SELECT count(distinct courier_id) FROM courier_actions)SELECT courier_id, orders_count, courier_rankFROM (SELECT courier_id, count(distinct order_id) as orders_count, row_number() OVER (ORDER BY count(distinct order_id) desc, courier_id) as courier_rank FROM courier_actions WHERE action ='deliver_order' GROUP BY courier_id ORDER BY orders_count desc, courier_id) as t1WHERE courier_rank <=round((SELECT * FROM courier_count)*0.1)
Завдання 20.13 За допомогою віконної функції відберіть із таблиці courier_actions всіх кур’єрів, які працюють у нашій компанії 10 і більше днів. Також розрахуйте скільки замовлень вони вже встигли доставити за весь час роботи.
Вважатимемо, що наш сервіс пропонує найвигідніші умови праці і тому за весь аналізований період жоден кур’єр не звільнився з компанії. Можливих перерв між змінами не враховуйте — для нас важлива лише різниця в часі між першою дією кур’єра і поточною позначкою часу.
Поточною відміткою часу, щодо якої необхідно розраховувати тривалість роботи кур’єра, вважайте час останньої дії таблиці courier_actions. Враховуйте лише цілі дні, що пройшли з моменту першого виходу кур’єра на роботу (години та хвилини не враховуйте).
У результат включіть три колонки: id кур’єра, тривалість роботи в днях та кількість доставлених замовлень. Дві нові колонки назвіть відповідно days_employed та delivered_orders. Результат відсортуйте спочатку за зменшенням кількості відпрацьованих днів, потім за зростанням id кур’єра.
Поля в результуючій таблиці: courier_id, days_employed, delivered_orders
Пояснення:
Для вирішення задачі крім віконної функції вам можуть стати у нагоді функція DATE_PART та оператор FILTER.
Число днів, які відпрацював кур’єр - це кількість днів, що пройшли з першого прийнятого замовлення до часу останнього запису в таблиці courier_actions. Простежте, щоб кількість днів у результуючій таблиці було виражено цілим числом.
Рішення
%%sqlSELECT courier_id, days_employed, delivered_ordersFROM (SELECT courier_id, delivered_orders, date_part('days', max(max_time) OVER() - min_time)::integer as days_employed FROM (SELECT courier_id, count(distinct order_id) filter (WHERE action ='deliver_order') as delivered_orders,min(time) as min_time,max(time) as max_time FROM courier_actions GROUP BY courier_id) t1) t2WHERE days_employed >=10ORDER BY days_employed desc, courier_id
courier_id
days_employed
delivered_orders
0
1
15
45
1
2
15
35
2
3
15
45
3
5
15
43
4
7
15
40
...
...
...
...
1059
1194
10
21
1060
1198
10
35
1061
1203
10
20
1062
1204
10
26
1063
1205
10
18
1064 rows × 3 columns
Завдання 20.14 За допомогою інформації в таблицях orders і products розрахуйте вартість кожного замовлення, щоденний виторг сервісу та частку вартості кожного замовлення в щоденній виручці, виражену у відсотках. У результаті включіть наступні колонки: id замовлення, час створення замовлення, вартість замовлення, виручку за день, в який було здійснено замовлення, а також частку вартості замовлення у виручці за день, що виражається у відсотках.
При розрахунку часток округляйте їх до трьох знаків після коми.
Результат відсортуйте спочатку за спаданням дати здійснення замовлення (саме дати, а не часу), потім за зменшенням частки замовлення у виручці за день, потім за зростанням id замовлення.
Під час проведення розрахунків скасовані замовлення не враховуйте.
Поля в результуючій таблиці: order_id, creation_time, order_price, daily_revenue, percentage_of_daily_revenue
Рішення
%%sqlSELECT order_id, creation_time, order_price,sum(order_price) OVER(PARTITION BY creation_time::date) as daily_revenue,round(100* order_price :: decimal /sum(order_price) OVER(PARTITION BY creation_time::date),3 ) as percentage_of_daily_revenueFROM ( SELECT order_id, creation_time,sum(price) as order_price FROM ( SELECT order_id, creation_time, product_ids, unnest(product_ids) as product_id FROM orders WHERE order_id notin ( SELECT order_id FROM user_actions WHERE action ='cancel_order' ) ) t3 LEFT JOIN products using(product_id) GROUP BY order_id, creation_time ) tORDER BY creation_time::date desc, percentage_of_daily_revenue desc, order_id
Завдання 20.15 За допомогою інформації в таблицях orders та products розрахуйте щоденну виручку сервісу та відобразіть її в колонці daily_revenue. Потім за допомогою віконних функцій та функцій зміщення порахуйте щоденний приріст виручки. Приріст виручки відобразіть як у абсолютних значеннях, і у % щодо попереднього дня. Колонку з абсолютним приростом назвіть revenue_growth_abs, а колонку з відносним – revenue_growth_percentage.
Для першого дня вкажіть приріст рівним 0 в обох колонках. Під час проведення розрахунків скасовані замовлення не враховуйте. Результат відсортуйте по колонці з датами зростання.
Метрики daily_revenue, revenue_growth_abs, revenue_growth_percentage округліть до одного знака за допомогою ROUND().
Поля в результуючій таблиці: date, daily_revenue, revenue_growth_abs, revenue_growth_percentage
Рішення
%%sqlwith t1 as ( SELECT order_id, creation_time, product_ids, unnest(product_ids) as product_id FROM orders WHERE order_id notin (SELECT order_id FROM user_actions WHERE action ='cancel_order') ), t2 as ( SELECT creation_time::date as date,sum(price) as daily_revenue FROM t1 LEFT JOIN products using(product_id) GROUP BY date )SELECT date,round(daily_revenue, 1) as daily_revenue,round(coalesce(daily_revenue - lag(daily_revenue, 1) OVER (ORDER BY date), 0), 1) as revenue_growth_abs,round(coalesce(round((daily_revenue - lag(daily_revenue, 1) OVER (ORDER BY date))::decimal / lag(daily_revenue, 1) OVER (ORDER BY date) *100, 2), 0), 1) as revenue_growth_percentageFROM t2ORDER BY date
І наприкінці давайте спробуємо застосувати віконні функції на вирішення нетривіальної задачі.
Насправді при розрахунку середніх значень аналітикам часто доводиться мати справу з екстремально малими чи екстремально великими значеннями у вибірках. Тому замість звичайного середнього іноді доводиться рахувати медіану.
Медіана - це таке значення в деякому впорядкованому за зростанням наборі чисел, що одна половина елементів набору не менше за нього, а інша половина не більше. Тобто це таке число, що знаходиться десь у середині цього набору.
Наведемо приклад. Допустимо, у нас є набір наступних чисел:
1, 5, 2, 12, 17, 8, 50, 12, 9, 1, 3
Якщо ми впорядкуємо цей набір за зростанням, то отримаємо наступний числовий ряд:
1, 1, 2, 3, 5, 8, 9, 12, 12, 17, 50
Давайте пронумеруємо елементи впорядкованого набору:
Тепер добре видно, що центральним елементом ряду є число 8 (елемент під номером 6), оскільки одна половина значень (елементи під номерами 1-5) не більша за 8, а друга половина (елементи під номерами 7-11) не менше 8. Таким чином, медіана нашого ряду дорівнює 8.
Але це працює, коли ряд складається з непарної кількості елементів (у нас їх 11). Якщо ж у вибірці парне число елементів, тоді як медіану вважають середнє значення двох елементів, що у середині ряду.
Давайте додамо до нашого ряду число 0 і подивимося, що в результаті вийде:
Тепер центральними є числа 5 та 8 (елементи під номерами 6 та 7). Медіана у цьому випадку дорівнює: \[(5+8)/2=6.5\]
Знову половина значень (елементи під номерами 1-5) не більше 6.5, а друга половина (елементи під номерами 7-11) не менше 6.5.
Трапляються випадки, коли в ряді, що складається з парного числа елементів, граничними є два однакові значення. Давайте замінимо у нашому ряді число 8 на число 5:
Тепер медіаною буде число 5, оскільки: \[(5+5)/2=5\]
На цьому наш невеликий лікнеп добігає кінця, і ми повертаємося до практики.
Завдання 20.16 За допомогою віконної функції розрахуйте медіанну вартість всіх замовлень із таблиці orders, оформлених у нашому сервісі. Як результат виведіть одне число. Назвіть колонку з ним median_price. Скасовані замовлення не враховуйте.
Поле у результуючій таблиці: median_price
Пояснення:
Запит повинен враховувати два можливі сценарії: для парного та непарного числа замовлень. Вбудовані функції для розрахунку квантилей застосовувати не можна.
Підказка на випадок, якщо зовсім не виходить
Для розрахунку медіани необхідно впорядкувати та пронумерувати вартість замовлень, а потім вибрати необхідний елемент/елементи послідовності.
Рішення
%%sqlWITH orders_prods AS (SELECT *, unnest(product_ids) AS product_id FROM orders),total AS (SELECT order_id, SUM(p.price) AS order_price, ROW_NUMBER() OVER(ORDER BY SUM(p.price)) AS number FROM orders_prods AS o JOIN products AS p ON o.product_id = p.product_id WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action ='cancel_order') GROUP BY order_id),median_numbers AS (SELECT CASE MAX(number) %2 WHEN 0 THEN MAX(number) /2 ELSE (MAX(number) +1) /2 END AS number_1, CASE MAX(number) %2 WHEN 0 THEN MAX(number) /2+1 END AS number_2 FROM total),number_one AS (SELECT number_1 FROM median_numbers),number_two AS (SELECT number_2 FROM median_numbers)SELECT AVG(order_price) AS median_priceFROM totalWHERE number IN (SELECT * FROM number_one UNION SELECT * FROM number_two)--- або-- WITH main_table AS (-- SELECT-- order_price,-- ROW_NUMBER() OVER (-- ORDER BY-- order_price-- ) AS row_number,-- COUNT(*) OVER() AS total_rows-- FROM-- (-- SELECT-- SUM(price) AS order_price-- FROM-- (-- SELECT-- order_id,-- product_ids,-- UNNEST(product_ids) AS product_id-- FROM-- orders-- WHERE-- order_id NOT IN (-- SELECT-- order_id-- FROM-- user_actions-- WHERE-- action ='cancel_order'-- )-- ) t3-- LEFT JOIN products USING(product_id)-- GROUP BY-- order_id-- ) t1-- )-- SELECT-- AVG(order_price) AS median_price-- FROM-- main_table-- WHERE-- row_number BETWEEN total_rows /2.0-- AND total_rows /2.0+1
