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

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

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

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

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

Предупреждение. В статье подразумевается, что обработка выражений в предложении 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.

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

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

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

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

Примечание:
Переменные нужно объявлять в отдельном подзапросе (строка 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

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

Таким образом, нам понадобится две сортировки. Чтобы итоговая сортировка осталась той же, что и в примерах выше, отсортируем сначала по полям 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 выдаёт:

Duration / Fetch
16.084 sec / 0.016 sec

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

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

Выдаёт:

Duration / Fetch
0.016 sec / 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. Т.е. чтобы выбрать 40 строк, MySQL будет вычислять значение RowNum для миллиона строк! Красивого решения этой задачи одним запросом в MySQL нет. Но можно сначала получить список уникальных значений 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 для миллиона строк.