Обзор функций окон
Введение
Функции окон позволяют выполнять вычисления над набором строк, связанных с текущей строкой.
Синтаксис
function (expression) OVER (
[ PARTITION BY expression_list ]
[ ORDER BY order_list [ frame_clause ] ] )
function:
A valid window function
expression_list:
expression | column_name [, expr_list ]
order_list:
expression | column_name [ ASC | DESC ]
[, ... ]
frame_clause:
{ROWS | RANGE} {frame_border | BETWEEN frame_border AND frame_border}
frame_border:
| UNBOUNDED PRECEDING
| UNBOUNDED FOLLOWING
| CURRENT ROW
| expr PRECEDING
| expr FOLLOWING
Описание
В некотором смысле, функции окон похожи на функции агрегирования, поскольку они выполняют вычисления над набором строк. Однако, в отличие от функций агрегирования, вывод не группируется в одну строку.
Неагрегирующие функции окон включают
- CUME_DIST
- DENSE_RANK
- FIRST_VALUE
- LAG
- LAST_VALUE
- LEAD
- MEDIAN
- NTH_VALUE
- NTILE
- PERCENT_RANK
- PERCENTILE_CONT
- PERCENTILE_DISC
- RANK, ROW_NUMBER
Функции агрегирования, которые также могут использоваться как функции окон, включают
- AVG
- BIT_AND
- BIT_OR
- BIT_XOR
- COUNT
- MAX
- MIN
- STD
- STDDEV
- STDDEV_POP
- STDDEV_SAMP
- SUM
- VAR_POP
- VAR_SAMP
- VARIANCE
Запросы с функциями окон характеризуются ключевым словом OVER, после которого указывается набор строк, используемых для вычисления. По умолчанию набор строк, используемых для вычисления (окно), — это весь набор данных, который можно упорядочить с помощью предложения ORDER BY. Предложение PARTITION BY используется для ограничения окна до конкретной группы в наборе данных.
Например, при следующих данных:
CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
следующие два запроса возвращают среднее значение, разделенное по тесту и по имени соответственно:
SELECT name, test, score, AVG(score) OVER (PARTITION BY test) AS average_by_test FROM student; +---------+--------+-------+-----------------+ | name | test | score | average_by_test | +---------+--------+-------+-----------------+ | Chun | SQL | 75 | 65.2500 | | Chun | Tuning | 73 | 68.7500 | | Esben | SQL | 43 | 65.2500 | | Esben | Tuning | 31 | 68.7500 | | Kaolin | SQL | 56 | 65.2500 | | Kaolin | Tuning | 88 | 68.7500 | | Tatiana | SQL | 87 | 65.2500 | | Tatiana | Tuning | 83 | 68.7500 | +---------+--------+-------+-----------------+ SELECT name, test, score, AVG(score) OVER (PARTITION BY name) AS average_by_name FROM student; +---------+--------+-------+-----------------+ | name | test | score | average_by_name | +---------+--------+-------+-----------------+ | Chun | SQL | 75 | 74.0000 | | Chun | Tuning | 73 | 74.0000 | | Esben | SQL | 43 | 37.0000 | | Esben | Tuning | 31 | 37.0000 | | Kaolin | SQL | 56 | 72.0000 | | Kaolin | Tuning | 88 | 72.0000 | | Tatiana | SQL | 87 | 85.0000 | | Tatiana | Tuning | 83 | 85.0000 | +---------+--------+-------+-----------------+
Также можно указать, какие строки следует включить в функцию окна (например, текущую строку и все предыдущие строки). Дополнительные сведения см. в разделе Фреймы окон.
Область применения
Функции окон были введены в SQL:2003, а их определение было расширено в последующих версиях стандарта. Последнее расширение было сделано в последней версии стандарта SQL:2011.
Большинство баз данных поддерживают подмножество стандарта, они реализуют некоторые функции, определенные в SQL:2011, и в то же время не реализуют некоторые части SQL:2008.
MariaDB:
- Поддерживает фреймы типа ROWS и RANGE
- Поддерживаются все типы границ фреймов, включая
RANGE PRECEDING|FOLLOWING nграницы фреймов (в отличие от PostgreSQL или MS SQL Server) - Пока не поддерживается тип данных DATE[TIME] и арифметика для фреймов типа RANGE (MDEV-9727)
- Поддерживаются все типы границ фреймов, включая
- Не поддерживает фреймы типа GROUPS (кажется, ни одна популярная база данных не поддерживает их)
- Не поддерживает исключение фреймов (похоже, ни одна база данных не поддерживает его) (MDEV-9724)
- Не поддерживает явное
NULLS FIRSTилиNULLS LAST. - Не поддерживает вложенную навигацию в функциях окон (это
VALUE_OF(expr AT row_marker [, default_value)синтаксис)
- Поддерживаются следующие функции окон:
- «Потоковые» функции окон: ROW_NUMBER, RANK, DENSE_RANK
- Функции окон, которые могут быть переданы один раз, после того как известно число строк в разделе: PERCENT_RANK, CUME_DIST, NTILE
- Функции агрегирования, которые в настоящее время поддерживаются в качестве функций окон: COUNT, SUM, AVG, BIT_OR, BIT_AND, BIT_XOR
- Функции агрегирования с модификатором
DISTINCT(например,COUNT( DISTINCT x)) не поддерживаются в качестве функций окон.
Ссылки
- MDEV-6115 — основная задача jira для разработки функций окон. Другие задачи прикреплены как подзадачи
- bb-10.2-mdev9543 — ветвь функций окон. Разработка продолжается, и в этой ветви есть новейшие изменения
- Тесты находятся в
mysql-test/t/win*.test
Примеры
При следующих образцовых данных:
CREATE TABLE users (
email VARCHAR(30),
first_name VARCHAR(30),
last_name VARCHAR(30),
account_type VARCHAR(30)
);
INSERT INTO users VALUES
('admin@boss.org', 'Admin', 'Boss', 'admin'),
('bob.carlsen@foo.bar', 'Bob', 'Carlsen', 'regular'),
('eddie.stevens@data.org', 'Eddie', 'Stevens', 'regular'),
('john.smith@xyz.org', 'John', 'Smith', 'regular'),
('root@boss.org', 'Root', 'Chief', 'admin')
Сначала давайте упорядочим записи по электронным адресам в алфавитном порядке, присвоив каждой из них возрастающее значение rnum, начиная с 1. Это позволит использовать функцию окна ROW_NUMBER:
SELECT row_number() OVER (ORDER BY email) AS rnum,
email, first_name, last_name, account_type
FROM users ORDER BY email;
+------+------------------------+------------+-----------+--------------+
| rnum | email | first_name | last_name | account_type |
+------+------------------------+------------+-----------+--------------+
| 1 | admin@boss.org | Admin | Boss | admin |
| 2 | bob.carlsen@foo.bar | Bob | Carlsen | regular |
| 3 | eddie.stevens@data.org | Eddie | Stevens | regular |
| 4 | john.smith@xyz.org | John | Smith | regular |
| 5 | root@boss.org | Root | Chief | admin |
+------+------------------------+------------+-----------+--------------
Мы можем сгенерировать отдельные последовательности, основанные на типе счета, используя предложение PARTITION BY:
SELECT row_number() OVER (PARTITION BY account_type ORDER BY email) AS rnum, email, first_name, last_name, account_type FROM users ORDER BY account_type,email; +------+------------------------+------------+-----------+--------------+ | rnum | email | first_name | last_name | account_type | +------+------------------------+------------+-----------+--------------+ | 1 | admin@boss.org | Admin | Boss | admin | | 2 | root@boss.org | Root | Chief | admin | | 1 | bob.carlsen@foo.bar | Bob | Carlsen | regular | | 2 | eddie.stevens@data.org | Eddie | Stevens | regular | | 3 | john.smith@xyz.org | John | Smith | regular | +------+------------------------+------------+-----------+--------------+
Учитывая следующую структуру и данные, мы хотим найти 5 самых высоких зарплат в каждом отделе.
CREATE TABLE employee_salaries (dept VARCHAR(20), name VARCHAR(20), salary INT(11));
INSERT INTO employee_salaries VALUES
('Engineering', 'Dharma', 3500),
('Engineering', 'Binh', 3000),
('Engineering', 'Adalynn', 2800),
('Engineering', 'Samuel', 2500),
('Engineering', 'Cveta', 2200),
('Engineering', 'Ebele', 1800),
('Sales', 'Carbry', 500),
('Sales', 'Clytemnestra', 400),
('Sales', 'Juraj', 300),
('Sales', 'Kalpana', 300),
('Sales', 'Svantepolk', 250),
('Sales', 'Angelo', 200);
Это можно сделать без использования функций окон следующим образом:
select dept, name, salary
from employee_salaries as t1
where (select count(t2.salary)
from employee_salaries as t2
where t1.name != t2.name and
t1.dept = t2.dept and
t2.salary > t1.salary) < 5
order by dept, salary desc;
+-------------+--------------+--------+
| dept | name | salary |
+-------------+--------------+--------+
| Engineering | Dharma | 3500 |
| Engineering | Binh | 3000 |
| Engineering | Adalynn | 2800 |
| Engineering | Samuel | 2500 |
| Engineering | Cveta | 2200 |
| Sales | Carbry | 500 |
| Sales | Clytemnestra | 400 |
| Sales | Juraj | 300 |
| Sales | Kalpana | 300 |
| Sales | Svantepolk | 250 |
+-------------+--------------+--------+
Это имеет ряд недостатков:
- если индекса нет, запрос может занимать много времени, если таблица employee_salary_table большая
- Добавление и поддержание индексов увеличивает нагрузку, и даже с индексами на dept и salary каждое выполнение подзапроса увеличивает нагрузку, выполняя поиск по индексу.
Давайте попробуем достичь того же с помощью функций окон. Сначала сгенерируем рейтинг для всех сотрудников, используя функцию RANK.
select rank() over (partition by dept order by salary desc) as ranking,
dept, name, salary
from employee_salaries
order by dept, ranking;
+---------+-------------+--------------+--------+
| ranking | dept | name | salary |
+---------+-------------+--------------+--------+
| 1 | Engineering | Dharma | 3500 |
| 2 | Engineering | Binh | 3000 |
| 3 | Engineering | Adalynn | 2800 |
| 4 | Engineering | Samuel | 2500 |
| 5 | Engineering | Cveta | 2200 |
| 6 | Engineering | Ebele | 1800 |
| 1 | Sales | Carbry | 500 |
| 2 | Sales | Clytemnestra | 400 |
| 3 | Sales | Juraj | 300 |
| 3 | Sales | Kalpana | 300 |
| 5 | Sales | Svantepolk | 250 |
| 6 | Sales | Angelo | 200 |
+---------+-------------+--------------+--------+
Каждый отдел имеет отдельную последовательность рангов из-за предложения PARTITION BY. Эта конкретная последовательность значений для rank() задается предложением ORDER BY внутри предложения OVER функции окна. Наконец, чтобы получить результаты в читаемом формате, мы упорядочим данные по dept и новому столбцу ranking.
Теперь нам нужно сократить результаты, чтобы найти только 5 лучших по каждому отделу. Вот распространенная ошибка:
select rank() over (partition by dept order by salary desc) as ranking, dept, name, salary from employee_salaries where ranking <= 5 order by dept, ranking; ERROR 1054 (42S22): Unknown column 'ranking' in 'where clause'
Попытка отфильтровать только первые 5 значений по каждому отделу, поместив в предложение where, не работает из-за того, как вычисляются функции окон. Вычисление функций окон происходит после того, как выполнены все предложения WHERE, GROUP BY и HAVING, непосредственно перед ORDER BY, поэтому предложение WHERE не знает о том, что столбец рейтинга существует. Он появляется только после того, как мы отфильтровали и сгруппировали все строки.
Чтобы решить эту проблему, нам нужно обернуть запрос в производную таблицу. Затем мы можем добавить в него предложение where:
select *from (select rank() over (partition by dept order by salary desc) as ranking, dept, name, salary from employee_salaries) as salary_ranks where (salary_ranks.ranking <= 5) order by dept, ranking; +---------+-------------+--------------+--------+ | ranking | dept | name | salary | +---------+-------------+--------------+--------+ | 1 | Engineering | Dharma | 3500 | | 2 | Engineering | Binh | 3000 | | 3 | Engineering | Adalynn | 2800 | | 4 | Engineering | Samuel | 2500 | | 5 | Engineering | Cveta | 2200 | | 1 | Sales | Carbry | 500 | | 2 | Sales | Clytemnestra | 400 | | 3 | Sales | Juraj | 300 | | 3 | Sales | Kalpana | 300 | | 5 | Sales | Svantepolk | 250 | +---------+-------------+--------------+--------+
См. также
© 2023 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/window-functions-overview/
