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

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

#MySQL #ROW_NUMBER #Оконные Функции #LAG #LEAD

Аналог рекурсии Производительность