20:20

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Optimizing

MySQL. Usage of query variables

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 RowNum
FROM TestTable T,(SELECT @I:=0)I
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)

SELECT group_id, order_id, value, RowNum
FROM(
  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, RunningTotal
FROM(
  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;