Хабрахабр

[Из песочницы] MySQL — Использование переменных в запросе

Довольно часто спрашивают, есть ли аналоги аналитических (оконных) функций в MySQL. Примечание.На момент написания статьи таких аналогов не было, однако статья и ныне представляет собой академический интерес в плане разбора оригинального для MySQL подхода к использованию переменных.

Большинство таких решений оказываются неэффективными с точки зрения производительности. Для замены аналитических функций часто используют запросы с самосоединением, сложные подзапросы и прочее.

Однако с некоторой частью задач, которые обычно решаются аналитическими функциями или рекурсией, можно справиться и средствами MySQL. Также в MySQL нет рекурсии.

Мы можем объявить переменную внутри запроса, менять ей значение и подставлять в SELECT для вывода. Одним из этих средств является уникальный, нехарактерный для прочих СУБД механизм работы с переменными внутри запроса SQL. Причем порядок обработки строк в запросе и, как следствие, порядок присвоения значений переменным можно задать в пользовательской сортировке!

В статье подразумевается, что обработка выражений в предложении SELECT осуществляется слева направо, однако официального подтверждения такого порядка обработки в документации MySQL нет. Предупреждение. Для гарантии последовательности вычисления можно использовать фиктивный оператор CASE или IF. Это необходимо иметь в виду при смене версии сервера.

Аналог рекурсии

Рассмотрим простой пример, который генерирует последовательность Фибоначчи (в последовательности Фибоначчи каждый член равен сумме двух предыдущих, а первые 2 равны единице):

SELECT IF(X=1, Fn_1, Fn_2) F
FROM( SELECT @I := @I + @J Fn_1, @J := @I + @J Fn_2 FROM (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)a, (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)b, (SELECT @I := 1, @J := 1)IJ
)T, /*Фиктивная таблица, для вывода последовательности в 1 столбец*/ (SELECT 1 X UNION ALL SELECT 2)X;

Данный запрос генерирует 18 чисел Фибоначчи, не считая первых двух:

2,3,5,8,13,21,34,55,89,144,233,377,610,987,1597,2584,4181,6765

Разберём теперь как оно работает.

Тут ничего необычного. В строчках 5) 6) генерируется 9 записей.

В строчке 7) мы объявляем две переменные @I, @J и присваиваем им 1.

Затем то же самое присваиваем переменной @J, причем с учетом того, что значение @I уже поменялось. В строке 3) происходит следующее: сначала переменной @I присваивается сумма двух переменных.

также замечание в начале статьи. Другими словами, вычисления в SELECT выполняются слева направо – см.

при обработке каждой новой строки в переменных @I и @J будут содержаться значения, вычисленные при обработке предыдущей строки. Причем изменение переменных осуществляется в каждой из наших 9 записей, т.е.

Чтобы решить эту же задачу средствами других СУБД, нам пришлось бы писать рекурсивный запрос!

Тип переменной определяется значением, которым она инициализирована. Примечание:
Переменные нужно объявлять в отдельном подзапросе (строка 7), если бы мы объявили переменную в предложении SELECT, она, скорее всего, вычислилась бы только 1 раз (хотя конкретное поведение будет зависеть от версии сервера). Если переменной присвоить NULL, её типом будет BLOB. Этот тип может динамически меняться.

Простой пример нумерации строк в заданном порядке: Порядок обработки строк в SELECT, как было сказано выше, зависит от пользовательской сортировки.


SELECT val, @I:=@I+1 Num
FROM (SELECT 30 val UNION ALL SELECT 20 UNION ALL SELECT 10 UNION ALL SELECT 50)a, (SELECT @I := 0)I
ORDER BY val;

Val Num
10 1
20 2
30 3
50 4

Аналоги аналитических функций

Переменные также можно использовать для замены аналитических функций. Далее несколько примеров. Для простоты будем считать, что все поля NOT NULL, а сортировка и партиционирование (PARTITION BY) происходят по одному полю. Использование NULL значений и более сложных сортировок сделает примеры более громоздкими, но суть не поменяет.

Для примеров создадим таблицу TestTable:

CREATE TABLE TestTable( group_id INT NOT NULL, order_id INT UNIQUE NOT NULL, value INT NOT NULL
);

где
group_id – идентификатор группы (аналог окна аналитической функции);
order_id – уникальное поле, по которому будет производиться сортировка;
value – некоторое числовое значение.

Заполним нашу таблицу тестовыми данными:

INSERT TestTable(order_id, group_id, value) SELECT *
FROM( SELECT 1 order_id, 1 group_id, 1 value UNION ALL SELECT 2, 1, 2 UNION ALL SELECT 3, 1, 2 UNION ALL SELECT 4, 2, 1 UNION ALL SELECT 5, 2, 2 UNION ALL SELECT 6, 2, 3 UNION ALL SELECT 7, 3, 1 UNION ALL SELECT 8, 3, 2 UNION ALL SELECT 9, 4, 1 UNION ALL SELECT 11, 3, 2
)T;

Примеры замены некоторых аналитических функций.

1) ROW_NUMBER() OVER(ORDER BY order_id)

SELECT T.*, @I:=@I+1 RowNum
FROM TestTable T,(SELECT @I:=0)I
ORDER BY order_id;

group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 4
2 5 2 5
2 6 3 6
3 7 1 7
3 8 2 8
4 9 1 9
3 11 2 10

2) ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY order_id)

SELECT group_id, order_id, value, RowNum
FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNum, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id
)T;

group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 1
2 5 2 2
2 6 3 3
3 7 1 1
3 8 2 2
3 11 2 3
4 9 1 1

3) SUM(value) OVER(PARTITION BY group_id ORDER BY order_id)

SELECT group_id, order_id, value, RunningTotal
FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+value, @I:=value) RunningTotal, @last_group_id := group_id FROM TestTable T, (SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id
)T;

group_id order_id value RunningTotal
1 1 1 1
1 2 2 3
1 3 2 5
2 4 1 1
2 5 2 3
2 6 3 6
3 7 1 1
3 8 2 3
3 11 2 5
4 9 1 1

4) LAG(value) OVER(PARTITION BY group_id ORDER BY order_id)

SELECT group_id, order_id, value, LAG
FROM( SELECT T.*, IF(@last_group_id = group_id, @last_value, NULL) LAG, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL)I ORDER BY group_id, order_id
)T;

group_id order_id value LAG
1 1 1 NULL
1 2 2 1
1 3 2 2
2 4 1 NULL
2 5 2 1
2 6 3 2
3 7 1 NULL
3 8 2 1
3 11 2 2
4 9 1 NULL

Для LEAD всё то же самое, только нужно сменить сортировку на ORDER BY group_id, order_id DESC

MS SQL, например, для этих целей «спулит» окно (временно помещает строки окна в скрытую буферную таблицу для повторного к ним обращения), в MySQL такой возможности нет. Для функций COUNT, MIN, MAX всё несколько сложнее, поскольку, пока мы не проанализируем все строчки в группе(окне), мы не сможем узнать значение функции. после анализа всего окна), а затем, отсортировав строки в окне в обратном порядке, проставить вычисленное значение по всему окну. Но мы можем для каждого окна вычислить значение функции в последней строке при заданной сортировке (т.е.

Чтобы итоговая сортировка осталась той же, что и в примерах выше, отсортируем сначала по полям group_id ASC, order_id DESC, затем по полям group_id ASC, order_id ASC. Таким образом, нам понадобится две сортировки.

5) COUNT(*) OVER(PARTITION BY group_id)

В первой сортировке мы просто нумеруем записи. Во второй всем строкам окна присваиваем максимальный номер, который и будет соответствовать количеству строк в окне.

SELECT group_id, order_id, value, Cnt
FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxRowNum, @MaxRowNum := RowNumDesc) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNumDesc, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id DESC /*первая сортировка*/ )T,(SELECT @last_group_id:=NULL, @MaxRowNum:=NULL)I ORDER BY group_id, order_id /*вторая сортировка*/
)T;

group_id order_id value Cnt
1 1 1 3
1 2 2 3
1 3 2 3
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 3
3 8 2 3
3 11 2 3
4 9 1 1

Функции MAX и MIN вычисляются по аналогии. Приведу только пример для MAX:

6) MAX(value) OVER(PARTITION BY group_id)

SELECT group_id, order_id, value, MaxVal
FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxVal, @MaxVal := MaxVal) MaxVal, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, GREATEST(@MaxVal, value), @MaxVal:=value) MaxVal, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id DESC )T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id
)T;

group_id order_id value MaxVal
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1

7) COUNT(DISTINCT value) OVER(PARTITION BY group_id)

Интересная вещь, которая отсутствует в MS SQL Server, но её можно вычислить с подзапросом, взяв MAX от RANK. Так же поступим и здесь. В первой сортировке вычислим RANK() OVER(PARTITION BY group_id ORDER BY value DESC), затем во второй сортировке проставим максимальное значение всем строкам в каждом окне:

SELECT group_id, order_id, value, Cnt
FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @Rank, @Rank := Rank) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, IF(@last_value = value, @Rank, @Rank:=@Rank+1) , @Rank:=1) Rank, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL, @Rank:=0)I ORDER BY group_id, value DESC, order_id DESC )T,(SELECT @last_group_id:=NULL, @Rank:=NULL)I ORDER BY group_id, value, order_id
)T;

group_id order_id value Cnt
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1

Производительность

Для начала сравним по производительности нумерацию строк в запросе с помощью самосоединения и с помощью переменных.

1) Классический способ с самомоединением

SELECT COUNT(*)N, T1.*
FROM TestTable T1 JOIN TestTable T2 ON T1.order_id >= T2.order_id
GROUP BY T1.order_id;

Что на 10000 записей в таблице TestTable выдаёт:

084 sec / 0. Duration / Fetch
16. 016 sec

2) С использованием переменных:

SELECT @N:=@N+1 N, T1.*
FROM TestTable T1, (SELECT @N := 0)M
ORDER BY T1.order_id;

Выдаёт:

016 sec / 0. Duration / Fetch
0. 015 sec

Однако надо понимать, что вычисленные с помощью переменных значения не оптимально использовать в условиях фильтрации. Результат говорит сам за себя. Сортировка и вычисление будут происходить для ВСЕХ строк, несмотря на то, что в итоге нам нужна только малая их часть.

Рассмотрим более подробно на примере такой задачи:

Вывести по 2 первые строки из таблицы TestTable для каждого значения group_id, отсортированных по order_id.

Вот как эта задача решалась бы в СУБД с поддержкой аналитических функций:

SELECT group_id, order_id, value
FROM( SELECT *, ROW_NUMBER()OVER(PARTITION BY group_id ORDER BY order_id) RowNum FROM TestTable
)T
WHERE RowNum <= 2;

Однако оптимизатор MySQL ничего не знает о том, по каким правилам мы вычисляем поле RowNum. Ему придётся пронумеровать ВСЕ строки, и только потом отобрать нужные.

Т.е. Теперь представьте, что у нас 1 миллион записей и 20 уникальных значений group_id. Красивого решения этой задачи одним запросом в MySQL нет. чтобы выбрать 40 строк, MySQL будет вычислять значение RowNum для миллиона строк! Но можно сначала получить список уникальных значений group_id, например, так:

SELECT DISTINCT group_id FROM TestTable;

Затем средствами любого другого языка программирования сгенерировать запрос вида:

SELECT * FROM TestTable WHERE group_id=1 ORDER BY order_id LIMIT 2
UNION ALL
SELECT * FROM TestTable WHERE group_id=2 ORDER BY order_id LIMIT 2
UNION ALL

SELECT * FROM TestTable WHERE group_id=20 ORDER BY order_id LIMIT 2;

20 лёгких запросов отработают намного быстрее, чем вычисление RowNum для миллиона строк.

Теги
Показать больше

Похожие статьи

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Кнопка «Наверх»
Закрыть