The post has been translated automatically. Original language: Russian
Window functions in SQl are useful for performing calculations based on table rows. They allow you to make queries simpler and more readable. You can often get the same result without using window functions, but with them the query will be shorter and more optimally executed. These functions help you prepare analytical reports, calculate rolling row values, and calculate different attribution models. The essence of window functions in SQL is to divide a query into parts, or so-called partitions, according to certain conditions. In this case, each part is processed separately from the others. The function outputs a separate result for each row, saving it. This is how window functions differ from GROUP BY, where rows are grouped.
Also, some window functions may use strings that are not included in the selection. For example, the offset function in SQL allows you to take data from the previous or next row of the window.
Window functions allow you to analyze the available information and get additional information without changing the basic data. They can be used in SQL for various purposes.:
Writing window functions involves specifying the function name, its conditions, and specification. The SQL specification limits the set of rows that will be involved in performing calculations.
Let's figure out what each SQL parameter means.:
- <function_name> is the name of a function of one of the classes, always written in angle brackets.
- (arguments) — arguments, for example, the name of the column for calculation, are enclosed in parentheses.
- OVER is a parameter that declares that it is the SQL window function that is being used.
- PARTITION BY (column names) — indicates the partitions by columns, written in square brackets.
- ORDER BY (column names) — forms the calculation order of the function, enclosed in square brackets.
- [frame clause] — specifies the frame for the partition, enclosed in square brackets.
The SQL window function may also include an optional condition.:
- [FILTER (WHERE filter_clause)] is a filtering expression that uses square and round brackets.
SQL window functions are divided into four types:
- Aggregated data. Perform calculations on the rows and give the only result for the group. These include SUM(), AVG(), MIN(), MAX().
- The ranking ones. The ranks of the rows are determined based on a given condition. This type includes RANK(), DENSE_RANK(), ROW_NUMBER().
- Analytical ones. The values of all rows are calculated based on other rows in the same group. These include CUME_DIST(), PERCENT_RANK(), PERCENTLE_CONT(), and PERCENTLE_DISC().
- Displacement functions. The values are determined based on the moving row window. These include LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE().
SELECT
department,
employee_name,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY hire_date) AS employee_rank
FROM employees;
Here ROW_NUMBER() numbers employees within each department (PARTITION BY department) in the order of their hiring (ORDER BY hire_date).
SELECT
customer_id,
sale_date,
amount,
amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY sale_date) AS diff_from_prev
FROM sales;
The LAG() function takes the value from the previous line within the client and allows you to compare the current sale with the previous one.
Оконные функции в SQl полезны для проведения вычислений на основе строк таблицы. Они позволяют сделать запросы более простыми и читаемыми. Часто тот же результат можно получить и без использования оконных функций, но с ними запрос будет короче и будет оптимальнее выполняться. Такие функции помогают готовить аналитические отчёты, рассчитывать скользящие значения строк и вычислять разные модели атрибуции. Суть оконных функций в SQL заключается в разделении запроса на части, или так называемые партиции, по определённым условиям. При этом каждая часть обрабатывается отдельно от других. Функция выдаёт отдельный результат для каждой строки, сохраняя его. Этим оконные функции отличаются от GROUP BY, где строки группируются.
Также некоторые оконные функции могут использовать строки, которые не участвуют в выборке. К примеру, функция смещения в SQL позволяет брать данные из предыдущей или следующей строки окна.
Оконные функции позволяют анализировать имеющуюся информацию и получать дополнительную без изменения базовых данных. Их можно использовать в SQL для различных целей:
Написание оконных функций включает указание имени функции, её условий и спецификации. Спецификация в SQL ограничивает набор строк, которые будут участвовать в выполнении вычислений.
Разбираемся, что означает каждый параметр SQL:
- <function_name> — название функции одного из классов, всегда пишется в угловых скобках.
- (arguments) — аргументы, допустим название столбца для расчёта, заключаются в круглые скобки.
- OVER — параметр, объявляющий, что используется именно оконная функция SQL.
- PARTITION BY (column names) — указывает партиции по колонкам, пишется в квадратных скобках.
- ORDER BY (column names) — формирует порядок вычисления функции, заключается в квадратные скобки.
- [frame clause] — указание фрейма для партиции, заключается в квадратные скобки.
Также оконная функция SQL может включать необязательное условие:
- [FILTER (WHERE filter_clause)] — выражение фильтрации, в котором используются квадратные и круглые скобки.
Оконные функции в SQL делятся на четыре вида:
- Агрегатные. Проводят вычисления над строками и выдают единственный результат для группы. К ним относятся SUM(), AVG(), MIN(), MAX().
- Ранжирующие. Определяют ранги строк, основываясь на заданном условии. В этот вид входят RANK(), DENSE_RANK(), ROW_NUMBER().
- Аналитические. Рассчитывают значения всех строк, основываясь на других строках этой же группы. Сюда относятся CUME_DIST(), PERCENT_RANK(), PERCENTILE_CONT() и PERCENTILE_DISC().
- Функции смещения. Определяют значения на основе движущегося окна строк. К ним относятся LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE().
SELECT
department,
employee_name,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY hire_date) AS employee_rank
FROM employees;
Здесь ROW_NUMBER() нумерует сотрудников внутри каждого отдела (PARTITION BY department) в порядке их приёма на работу (ORDER BY hire_date).
SELECT
customer_id,
sale_date,
amount,
amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY sale_date) AS diff_from_prev
FROM sales;
Функция LAG() берёт значение из предыдущей строки в рамках клиента и позволяет сравнить текущую продажу с предыдущей.
Share
