FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL)I
ORDERBY group_id, order_id
)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.
It’s
somewhat more complicated for the functions COUNT, MIN, and MAX, since
we can’t 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, let’s
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.
FROM TestTable T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I
ORDERBY group_id, order_id DESC
)T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I
ORDERBY group_id, order_id
)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.
Let’s 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:
go
