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