Equivalents for analytic functions page 2
4) LAG(value) OVER(PARTITION BY group_id ORDER BY order_id)
LEAD can be implemented in the same manner; just the sorting clause has to be changed to ORDER BY group_id, order_id DESC.
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)
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.
MAX and MIN are calculated similarly. I confine myself to an example for MAX only:
6) MAX(value) OVER(PARTITION BY group_id)
7) COUNT(DISTINCT value) OVER(PARTITION BY group_id)
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