loading..
   English
18:32

Equivalents for analytic functions page 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 can be implemented in the same manner; just the sorting clause has to be changed to ORDER BY group_id, order_id DESC.

Its somewhat more complicated for the functions COUNT, MIN, and MAX, since we cant find out the function value before analyzing all rows in the group (window). For instance, MS SQL Server spools the window (temporary writes its rows to a hidden buffer table for later reference) for this purpose; MySQL has no such functionality. However, we can calculate the function value for the last row (according to the actual sorting order) of the respective group (that is, after processing the whole window), then sort the rows in reverse order and finally replicate the calculated value throughout the whole window.

Thus, we need to do the sorting twice. To ensure the same order as in the examples above, lets first sort the data by group_id ASC, order_id DESC, then by group_id ASC, order_id ASC.

5) COUNT(*) OVER(PARTITION BY group_id)

Within the first sorting, we just enumerate the rows. Within the second one, we copy the maximum value, which represents the number of rows in the respective group, to all rows in the window.

  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 and MIN are calculated similarly. I confine myself to an example for MAX only:

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)

This is an interesting function presently not implemented in MS SQL Server; however, it can be calculated using a subquery by taking MAX of RANK. Lets do the same here. Within the first sorting RANK() OVER(PARTITION BY group_id ORDER BY value DESC) is calculated; then, within the second sorting, its maximum value is copied to all rows of each respective window:

  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
Pages 1 2
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
https://exchangesumo.com/obmen/WMR-WMB/
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100