loading..
Русский    English
18:16

Equivalents for analytic functions page 1

Variables can also be used to emulate analytic functions. A few examples follow. For simplicity, let’s assume all data fields NOT NULL, and sorting and partitioning (PARTITION BY) use one column only. Including NULLs and multi-column sorting/partitioning would make the examples more cumbersome and difficult to read without changing their basic idea.

Let’s create a table named TestTable for our examples:

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

Here, group_id is the group identifier (the equivalent of an analytic function window), order_id contains unique values used for sorting, and value is just a numeric value.

Let’s fill our table with test data:

  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;

Some examples of analytic function substitutes.

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
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 CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100