Каждый программист базы данных MySQL должен изучить и применить недавно добавленные оконные функции MariaDB и MySQL и выражения общих таблиц (CTE) в своей повседневной работе. И CTE, и оконные функции позволяют легко решать многие проблемы запросов, которые в предыдущих выпусках было сложно, а иногда невозможно преодолеть. Освоение этих функций открывает двери для поиска решений, которые более надежны, выполняются быстрее и их легче обслуживать с течением времени, чем предыдущие решения, использующие более старые методы.
Сегодня мы сравним функции окон и общие табличные выражения в обеих базах данных.
Оконные функции
Хотя все администраторы баз данных знакомы с агрегатными функциями, такими как COUNT(), SUM() и AVG(), гораздо меньше людей используют оконные функции в своих запросах. В отличие от агрегатных функций, которые работают со всей таблицей, оконные функции работают с набором строк и возвращают одно агрегированное значение для каждой строки.
Основное преимущество использования оконных функций над обычными агрегатными функциями заключается в том, что оконные функции не приводят к тому, что строки объединяются в одну выходную строку. Вместо этого строки сохраняют свои отдельные идентификаторы, и к каждой строке добавляется агрегированное значение.
Оконные функции в MariaDB
Функции управления окнами были добавлены в стандарт ANSI/ISO SQL: 2003, а затем расширены в стандарте ANSI/ISO SQL: 2008. DB2, Oracle, Sybase, PostgreSQL и другие продукты имеют полные реализации в течение многих лет. Другие производители добавили поддержку оконных функций позже. Например, когда Microsoft представила оконные функции в SQL Server 2005, она включала лишь несколько функций, а именно ROW_NUMBER, RANK, NTILE и DENSE_RANK. Только в SQL Server 2012 был реализован полный спектр оконных функций.
После многочисленных желаний и запросов на функции для оконных функций они были наконец представлены в MariaDB 10.2.0. Теперь MariaDB включает в себя оконные функции, такие как ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, COUNT, SUM, AVG, BIT_OR, BIT_AND и BIT_XOR.
Синтаксис
Запросы оконной функции характеризуются ключевым словом OVER, после которого указывается набор строк, используемых для расчета. По умолчанию набор строк, используемых для расчета («окно»), представляет собой весь набор данных, который можно упорядочить с помощью предложения ORDER BY. Предложение PARTITION BY затем используется для сокращения окна до определенной группы в наборе данных.
Вот пример:
приведена следующая таблица результатов тестов учащихся:
+------------+---------+--------+ | name | test | score | +------------+---------+--------+ | Denis | SQL | 75 | +------------+---------+--------+ | Andrey | SQL | 43 | +------------+---------+--------+ | Boris | SQL | 56 | +------------+---------+--------+ | Tatiana | SQL | 87 | +------------+---------+--------+ | Denis | Tuning | 73 | +------------+---------+--------+ | Andrey | Tuning | 31 | +------------+---------+--------+ | Boris | Tuning | 88 | +------------+---------+--------+ | Tatiana | Tuning | 83 | +------------+---------+--------+
Следующие два запроса возвращают средние результаты тестов, разделенные по тестам и по именам соответственно — другими словами, агрегированные по тестам и по имени:
SELECT name, test, score, AVG(score) OVER (PARTITION BY test) AS average_by_test FROM test_scores; +----------+--------+-------+-----------------+ | name | test | score | average_by_test | +----------+--------+-------+-----------------+ | Denis | SQL | 75 | 65.2500 | | Denis | Tuning | 73 | 68.7500 | | Andrey | SQL | 43 | 65.2500 | | Andrey | Tuning | 31 | 68.7500 | | Boris | SQL | 56 | 65.2500 | | Boris | 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 | +---------+--------+-------+-----------------+ | Denis | SQL | 75 | 74.0000 | | Denis | Tuning | 73 | 74.0000 | | Andrey | SQL | 43 | 37.0000 | | Andrey | Tuning | 31 | 37.0000 | | Boris | SQL | 56 | 72.0000 | | Boris | Tuning | 88 | 72.0000 | | Tatiana | SQL | 87 | 85.0000 | | Tatiana | Tuning | 83 | 85.0000 | +---------+--------+-------+-----------------+
В обоих случаях обратите внимание, что исходные результаты по-прежнему доступны для каждой строки.
Оконные функции в MySQL 8
MySQL даже позже принял стандарт Window Functions, поскольку он является частью версии 8.0, выпуск которой ожидается в этом году.
В MySQL используется тот же стандарт ANSI/ISO, что и в других СУБД, в соответствии с чем запросы оконных функций характеризуются ключевым словом OVER, а условие PARTITION BY используется для сокращения окна до определенной группы в наборе результатов.
В настоящее время поддерживаются следующие функции:
| название | Описание |
| CUME_DIST() | Совокупное значение распределения |
| DENSE_RANK() | Ранг текущей строки внутри ее раздела, без пробелов |
| FIRST_VALUE() | Значение аргумента из первого ряда оконной рамы |
| LAG() | Значение аргумента из строки, отстающей от текущей строки в разделе |
| LAST_VALUE() | Значение аргумента из последнего ряда оконной рамы |
| LEAD() | Значение аргумента из строки, ведущей текущей строки в разделе |
| NTH_VALUE() | Значение аргумента из N-го ряда оконной рамы |
| NTILE() | Номер блока текущей строки в ее разделе. |
| PERCENT_RANK() | Процентное значение ранга |
| RANK() | Ранг текущей строки внутри ее раздела с пробелами |
| ROW_NUMBER() | Номер текущей строки в ее разделе |
В качестве примера мы рассмотрим функцию CUME_DIST().
Возвращает кумулятивное распределение значения в группе значений; то есть процент значений разделов, меньших или равных значению в текущей строке. Это представляет количество строк, предшествующих или равных текущей строке в порядке расположения окон раздела окна, деленное на общее количество строк в разделе окна. Возвращаемые значения варьируются от 0 до 1.
Эта функция обычно используется с ORDER BY для сортировки строк в нужном порядке. Без ORDER BY все строки равноправны и имеют значение 1.
Следующий запрос показывает для набора значений в столбце val значение CUME_DIST() для каждой строки, а также значение процентного ранга, возвращаемое аналогичной функцией PERCENT_RANK(). Для справки, запрос также отображает номера строк, используя ROW_NUMBER():
SELECT val, ROW_NUMBER() OVER w AS 'row_number', CUME_DIST() OVER w AS 'cume_dist', PERCENT_RANK() OVER w AS 'percent_rank' FROM numbers WINDOW w AS (ORDER BY val); +------+------------+--------------------+--------------+ | val | row_number | cume_dist | percent_rank | +------+------------+--------------------+--------------+ | 1 | 1 | 0.2222222222222222 | 0 | | 1 | 2 | 0.2222222222222222 | 0 | | 2 | 3 | 0.3333333333333333 | 0.25 | | 3 | 4 | 0.6666666666666666 | 0.375 | | 3 | 5 | 0.6666666666666666 | 0.375 | | 3 | 6 | 0.6666666666666666 | 0.375 | | 4 | 7 | 0.8888888888888888 | 0.75 | | 4 | 8 | 0.8888888888888888 | 0.75 | | 5 | 9 | 1 | 1 | +------+------------+--------------------+--------------+
Предложение OVER разрешено для многих агрегатных функций, включая:
- AVG()
- COUNT()
- MAX()
- MIN()
- STDDEV_POP(), STDDEV(), STD()
- STDDEV_SAMP()
- SUM()
- VAR_POP(), VARIANCE()
- VAR_SAMP()
Они могут использоваться как оконные или неоконные функции, в зависимости от того, присутствует или отсутствует предложение OVER:
MySQL также поддерживает неагрегированные функции, которые используются только в качестве оконных функций. Для них обязательным является предложение OVER:
- CUME_DIST()
- DENSE_RANK()
- FIRST_VALUE()
- LAG()
- LAST_VALUE()
- LEAD()
- NTH_VALUE()
- NTILE()
- PERCENT_RANK()
- RANK()
- ROW_NUMBER()
В качестве примера неагрегированных оконных функций этот запрос использует ROW_NUMBER(), который создает номер строки для каждой строки в своем разделе. В этом случае строки нумеруются для каждой страны. По умолчанию строки разделов неупорядочены, а нумерация строк не определена. Чтобы отсортировать строки раздела, включите предложение ORDER BY в определение окна. Запрос использует неупорядоченные и упорядоченные разделы (столбцы row_num1 и row_num2), чтобы проиллюстрировать разницу, которую пропускает и включая ORDER BY:
SELECT year, country, product, profit, ROW_NUMBER() OVER(PARTITION BY country) AS row_num1, ROW_NUMBER() OVER(PARTITION BY country ORDER BY year, product) AS row_num2 FROM sales; +------+---------+------------+--------+----------+----------+ | year | country | product | profit | row_num1 | row_num2 | +------+---------+------------+--------+----------+----------+ | 2009 | Sweden | Computer | 2310 | 2 | 1 | | 2009 | Sweden | Phone | 100 | 1 | 2 | | 2013 | Sweden | Phone | 10 | 3 | 3 | | 2009 | Russia | Calculator | 75 | 2 | 1 | | 2009 | Russia | Calculator | 75 | 3 | 2 | | 2009 | Russia | Computer | 1200 | 1 | 3 | | 2009 | USA | Calculator | 75 | 5 | 1 | | 2009 | USA | Computer | 2310 | 4 | 2 | | 2013 | USA | Calculator | 50 | 2 | 3 | | 2013 | USA | Computer | 2310 | 3 | 4 | | 2013 | USA | Computer | 1200 | 7 | 5 | | 2013 | USA | TV | 150 | 1 | 6 | | 2013 | USA | TV | 100 | 6 | 7 | +------+---------+------------+--------+----------+----------+
Общие табличные выражения (CTE)
Распространенное табличное выражение (CTE) можно рассматривать как временный набор результатов, определенный в области выполнения одного оператора SELECT, INSERT, UPDATE, DELETE или CREATE VIEW. CTE похож на производную таблицу в том смысле, что он не сохраняется как объект и действует только в течение запроса. Однако, в отличие от производной таблицы, CTE может иметь самоссылку и ссылаться на нее несколько раз в одном запросе.
CTE может использоваться для:
- Создать рекурсивный запрос.
- Замена представления, когда общее использование представления не требуется; то есть вам не нужно хранить определение в метаданных.
- Включите группировку по столбцу, который получен из скалярного подвыбора, или функции, которая не является детерминированной или имеет внешний доступ.
- Ссылаться на полученную таблицу несколько раз в одном выражении.
Использование CTE предлагает преимущества улучшенной читаемости и простоты обслуживания сложных запросов. Запрос может быть разделен на отдельные, простые, логические строительные блоки. Эти простые блоки могут затем использоваться для построения более сложных временных CTE до тех пор, пока не будет создан окончательный набор результатов.
Рекурсивные CTE
Рекурсивные общие табличные выражения (CTE) были реализацией стандарта SQL: 1999 для иерархических запросов. Первые реализации Рекурсивных CTE начали появляться в 2007 году. Рекурсивные CTE из стандарта были относительно близки к существующей реализации в IBM DB2 версии 2. Рекурсивные CTE в конечном итоге поддерживались Microsoft SQL Server (начиная с SQL Server 2008 R2), Firebird 2.1, PostgreSQL 8.4+, SQLite 3.8.3+, Oracle 11g Release 2 и IBM Informix версии 11.50+.
Без выражений Common-table или выражения connected-by все еще возможно выполнять иерархические запросы с помощью пользовательских рекурсивных функций, но они, как правило, приводят к очень сложному SQL.
CTE в MariaDB
В MariaDB нерекурсивный CTE в основном считается локальным представлением запроса, синтаксис которого более читабелен, чем вложенный FROM (SELECT…). CTE может ссылаться на другое и на него можно ссылаться из нескольких мест.
Таким образом, CTE аналогичны производным таблицам. Например:
SQL с производной таблицей:
SELECT * FROM
( SELECT * FROM employees
WHERE dept = 'Engineering' ) AS engineers
WHERE
...
SQL с CTE:
WITH engineers AS
( SELECT * FROM employees
WHERE dept = 'Engineering' )
SELECT * FROM engineers
WHERE ...
SQL обычно плох в рекурсии. Одним из преимуществ CTE является то, что они позволяют запросу ссылаться на себя, а следовательно, на рекурсивный SQL. Рекурсивный CTE будет многократно выполнять подмножества данных, пока не получит полный набор результатов. Это делает его особенно полезным для обработки иерархических или древовидных данных.
С помощью рекурсивных CTE вы можете добиться того, что было бы очень трудно сделать со стандартным SQL, и с более высокой скоростью выполнения. Они могут помочь решить многие типы бизнес-задач и даже упростить некоторую сложную логику SQL/приложения вплоть до простого рекурсивного вызова базы данных.
Некоторые примеры использования для рекурсивного CTE: найти пробелы в данных, создать организационные диаграммы и создать тестовые данные.
WITH RECURSIVE означает рекурсивный CTE. Ему присваивается имя, за которым следует тело (основной запрос):
Ниже приведен рекурсивный CTE, который насчитывает от 1 до 50.
WITH cte
AS (SELECT 1 AS n -- anchor member
UNION ALL
SELECT n + 1 -- recursive member
FROM cte
WHERE n < 50 -- terminator
)
SELECT n
FROM cte;
Вышеприведенное утверждение печатает ряд чисел от 1 до 49.
CTE в MySQL
MySQL 8.0 добавляет CTE через стандартное ключевое слово WITH, почти так же, как это реализовано в конкурирующих продуктах.
Чтобы указать общие табличные выражения, используйте предложение WITH, содержащее один или несколько разделенных запятыми подпунктов. Каждый подпункт обеспечивает подзапрос, который создает набор результатов и связывает имя с подзапросом. В следующем примере определяются CTE с именами cte1 и cte2 в предложении WITH и ссылки на них в SELECT верхнего уровня, который следует за предложением WITH:
WITH cte1 AS (SELECT a, b FROM table1), cte2 AS (SELECT c, d FROM table2) SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c;
Сразу перед SELECT для операторов, которые включают оператор SELECT:
- INSERT … WITH … SELECT …
- REPLACE … WITH … SELECT …
- CREATE TABLE … WITH … SELECT …
- CREATE VIEW … WITH … SELECT …
- DECLARE CURSOR … WITH … SELECT …
- EXPLAIN … WITH … SELECT …
Рекурсивное общее табличное выражение — это выражение, имеющее подзапрос, который ссылается на собственное имя. Например:
WITH RECURSIVE cte (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 5 ) SELECT * FROM cte; +------+ | n | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+
Заключительные
Оконные функции и общие табличные выражения (CTE) уже давно стали основой многих популярных продуктов баз данных. С выпуском MySQL версии 8 и MariaDB 10.2.0 оба поставщика догнали конкурирующие СУБД, такие как SQL Server и Oracle.