loading..
Русский    English
13:57
листать

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

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

  1. SELECT group_id, order_id, value, LAG
  2. FROM(
  3.   SELECT T.*,
  4.     IF(@last_group_id = group_id, @last_value, NULL) LAG,
  5.     @last_group_id := group_id,
  6.     @last_value := value
  7.   FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL)I
  8.   ORDER BY group_id, order_id
  9. )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)

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

  1. SELECT group_id, order_id, value, Cnt
  2. FROM(
  3.   SELECT group_id, order_id, value,
  4.     IF(@last_group_id = group_id, @MaxRowNum, @MaxRowNum := RowNumDesc) Cnt,
  5.     @last_group_id := group_id
  6.   FROM(
  7.     SELECT T.*,
  8.       IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNumDesc,
  9.       @last_group_id := group_id
  10.     FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I
  11.     ORDER BY group_id, order_id DESC /*первая сортировка*/
  12.   )T,(SELECT @last_group_id:=NULL, @MaxRowNum:=NULL)I
  13.   ORDER BY group_id, order_id /*вторая сортировка*/
  14. )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)

  1. SELECT group_id, order_id, value, MaxVal
  2. FROM(
  3.   SELECT group_id, order_id, value,
  4.     IF(@last_group_id = group_id, @MaxVal, @MaxVal := MaxVal) MaxVal,
  5.     @last_group_id := group_id
  6.   FROM(
  7.     SELECT T.*,
  8.       IF(@last_group_id = group_id, GREATEST(@MaxVal, value), @MaxVal:=value) MaxVal,
  9.       @last_group_id := group_id
  10.     FROM TestTable T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I
  11.     ORDER BY group_id, order_id DESC
  12.   )T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I
  13.   ORDER BY group_id, order_id
  14. )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), затем во второй сортировке проставим максимальное значение всем строкам в каждом окне:

  1. SELECT group_id, order_id, value, Cnt
  2. FROM(
  3.   SELECT group_id, order_id, value,
  4.     IF(@last_group_id = group_id, @Rank, @Rank := Rank) Cnt,
  5.     @last_group_id := group_id
  6.   FROM(
  7.     SELECT T.*,
  8.       IF(@last_group_id = group_id,
  9.         IF(@last_value = value, @Rank, @Rank:=@Rank+1)
  10.       , @Rank:=1) Rank,
  11.       @last_group_id := group_id,
  12.       @last_value := value
  13.     FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL, @Rank:=0)I
  14.     ORDER BY group_id, value DESC, order_id DESC
  15.   )T,(SELECT @last_group_id:=NULL, @Rank:=NULL)I
  16.   ORDER BY group_id, value, order_id
  17. )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


Bookmark and Share
Страницы: 1 2
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX Больше тэгов
Учебник обновлялся
несколько дней назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100