Англійська для функцій вікна SQL

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

Ключовий словник

** Window function ** — функція, яка обчислює значення у наборі пов’ язаних рядків без згортання їх у один рядок на групу, на відміну від агрегаційної функції, яку використовують з GROUP BY. “Ми хотіли, щоб кожен рядок замовлення показував свою власну загальну суму, тому функція вікна була правильним інструментом — GROUP BY згорнула б рядки, які нам потрібно було зберегти.”

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

• “Розділення за ІД клієнта означає виконання загальних скидань для кожного клієнта, замість накопичення по всьому набору результатів.” *

** Frame ** — певна підмножина рядків у межах розділу, яку функція вікна розглядає для обчислення кожного рядка, визначена ROWS BETWEEN або RANGE BETWEEN, що керує такими речами, як кінцева сума за 7 днів. “Фрейм є ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, що дає нам 7- рядкове вікно для рухомого середнього.”

** Функція ранжування ** — категорія віконних функцій ( ROW_NUMBER, RANK, DENSE_RANK ), які призначають порядкову позицію кожному рядку у його розділі, відрізняються за тим, як вони обробляють зв’ язки. ” RANK залишає прогалини після рівності — два рядки рівні за першим отримують ранг 1, а наступний рядок отримує ранг 3 — в той час як DENSE_RANK дасть йому ранг 2 замість цього.”

** ORDER BY (в межах OVER) ** — клаузула впорядкування всередині клаузули OVER функції вікна, яка визначає рядки послідовності, які обробляються для ранжування, підсумків або обчислень кадрів, відмінні від зовнішнього ORDER BY запиту. “ORDER BY всередині клаузули OVER визначає порядок, в якому накопичується загальна сума — це незалежно від того, як фінальний набір результатів буде сортований.”

Звичайні фрази

• Чи потрібно нам це розділене за клієнтом, або воно повинно працювати по всьому набору результатів?»
• «Що тут за рамка — це вікно, що йде вниз, чи вся перегородка?»
• Чи потрібно нам використовувати RANK або DENSE_RANK — чи потрібні тут проміжки після тисків чи ні?
• Чи це ORDER BY всередині функції вікна, чи фінальний сорт запитів?»
• «Це повинна бути функція вікна, а не самоз’єднання — ми не хочемо згортати рядки.»

Приклади висловлювань

Пояснення вибору запиту у перегляді проекту: “Ми використовуємо ROW_NUMBER() розділений за ідентифікатором користувача і впорядкований за часом у спадному порядку, тому ми можемо отримати лише останню подію для кожного користувача без окремого підзапиту для кожного користувача.”

Зневадження неправильного результату ранжування: “Ранкінги виглядали неправильно, тому що ми забувають про клаузулу розділення — це ранжування кожного рядка проти всієї таблиці, а не в межах кожної області окремо.”

Опис обчислення рухомого середнього:

• “Розмір рамки буде встановлено на поточний рядок і шість попередніх рядків, що дасть нам правильну середню 7- дніву оцінку замість середньої оцінки за весь набір даних.” *

Професійні поради

• Скажіть ** window function **, а не “aggregate”, коли метою є вивід по рядках разом з обчисленням - об’єднання двох в обговоренні зазвичай означає, що хтось досягне GROUP BY і втратить рядки, які їм потрібні.
• Завжди вказуйте клаузулу ** partition ** явно, коли описуєте запит вікна — « ranked » або « running total » є неоднозначними, поки ви не скажете, що це за об’ єкт.
• Вкажіть точно ** рамку ** (кінцеві N рядків, цілий розділ тощо) під час обговорення рухомих середніх або рухомих підсумків — типова рамка не завжди відповідає тому, що люди вважають за потрібне.
• Виберіть правильне ранкування функції навмисно і назвемо причину — RANK, DENSE_RANK, і ROW_NUMBER обробляють зв’язки по-різному, і вибір неправильного безмовно змінює результати.

Практичні вправи

1. Напишіть речення, яке пояснює різницю між GROUP BY і функцією вікна.
2. Пояснити, що блок керує у функції вікна.
3. Описати різницю між RANK і DENSE_RANK на рівних рядках.

На практиці: Навігація та співпраця

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

Розглянемо цей сценарій: Вам було доручено створити запит, який обчислює сьому за величиною зарплату у кожному відділі. Після надсилання вашого запиту на збирання ви отримаєте коментар від Сари, старшого розробника. У коментарі йдеться: « Ця функція вікна трохи заплутана. Чи можете ви пояснити, як клаузула OVER визначає рамку? Здається, що ви використовуєте щільну рамку, тоді як розріджена рамка була б ефективнішою і простішою для розуміння. Якщо ви просто відповісте « Я використовував щільну рамку », Сара може не повністю зрозуміти вашу думку, особливо якщо вона не знайома з нюансами типів рамок. Краще пояснити, * чому * ви обираєте щільну структуру — можливо, з міркувань продуктивності, або тому, що вона відповідає конкретним бізнес- вимогам. Ви можете сказати щось на зразок: « Дякую за відгук, Сара! Я використовував щільну рамку, тому що хотів переконатися, що ми послідовно класифікуємо зарплати в кожному відділі, незалежно від будь-яких відхилень. Розріджена рамка ввела б деяку складність у роботі з цими екстремальними значеннями»

Інша поширена ситуація виникає під час обговорення дизайну запиту. При описі мети вашого запиту у описі PR, ключовим є використання точної мови. Замість того, щоб сказати « Цей запит отримує 5 найбільших продажів за регіоном », що є неясним, ви можете сказати: « Цей запит SQL використовує функцію вікна для визначення п’ яти найбільш успішних регіонів на основі загальних доходів від продажів, розділених за географічною територією. Функція RANK() у клаузулі OVER присвоює ранги у межах кожного розділу, що дозволяє нам визначити ті регіони, які найбільше сприяють загальним продажам. » Цей рівень деталізації демонструє ваше розуміння і дозволяє рецензентам швидко оцінити логіку запиту.

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

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

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