00:06

# 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:

`CREATE TABLE TestTable(  group_id INT NOT NULL,  order_id INT UNIQUE NOT NULL,  value INT NOT NULL);`

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:

`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;`

Some examples of analytic function substitutes.

### 1) ROW_NUMBER() OVER(ORDER BY order_id)

`SELECT T.*, @I:=@I+1 RowNumFROM TestTable T,(SELECT @I:=0)IORDER 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, RowNumFROM(  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, RunningTotalFROM(  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

 Pages 1 2
Tags
The book was updated
several days ago