loading..
Русский    English
21:41
листать

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

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

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

  1. CREATE TABLE TestTable(
  2.   group_id INT NOT NULL,
  3.   order_id INT UNIQUE NOT NULL,
  4.   value INT NOT NULL
  5. );

где

group_id – идентификатор группы (аналог окна аналитической функции);

order_id – уникальное поле, по которому будет производиться сортировка;

value – некоторое числовое значение.

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

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

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

1) ROW_NUMBER() OVER(ORDER BY order_id)

  1. SELECT T.*, @I:=@I+1 RowNum
  2. FROM TestTable T,(SELECT @I:=0)I
  3. 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)

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

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


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