EXPLAIN EXTENDED

In the previous articles I wrote about emulating some of the analytic functions in MySQL.

Today, I’ll write about four more userful functions: FIRST_VALUE, LAST_VALUE, LEAD and LAG.

These functions also do not have aggregate analogs.

FIRST VALUE(column) returns the value of column from the first row of the grouping set.

LAST_VALUE(column) returns the value of column from the last row of the grouping set.

This can be illustrated by the following query:

SELECT  m.*,
            FIRST_ROW(month) OVER (PARTITION BY season ORDER BY id),
            LAST_ROW(month) OVER (PARTITION BY season ORDER BY id)
FROM    t_month m

This query selects the first and the last month of each row’s season.

In this example above, January is the first month and December is the last month of winter, as January, 2009 comes earlier than December, 2009.

In MySQL, we can emulate FIRST_ROW() with the following construction:

SELECT  *,
        @r AS `FIRST_VALUE(month) OVER (PARTITION BY season ORDER BY id)`
FROM    (
        SELECT  m.*
        FROM    (
                SELECT  @_season = NULL
                ) vars,
                t_month m
        ORDER BY
                season, id
        ) mo
WHERE   (CASE WHEN @_season IS NULL OR @_season <> season THEN @r := month ELSE month END IS NOT NULL)
        AND (@_season := season) IS NOT NULL

In this case, we just assign the value of month to @r whenever the season changes.

What about LAST_ROW()?

By analogy, we’ll just assign a…

Wait. When we enter the new set, we don’t yet have something to assign! We’ll have it only when the set ends, but we already need to output the intermediate results.

We’d better change the ordering in the ORDER BY clause so that id‘s get selected in the descending order. In this case, FIRST_ROW() will become LAST_ROW() and vise versa:

SELECT  *,
        @r AS `LAST_VALUE(month) OVER (PARTITION BY season ORDER BY id)`
FROM    (
        SELECT  m.*
        FROM    (
                SELECT  @_season = NULL
                ) vars,
                t_month m
        ORDER BY
                season, id DESC
        ) mo
WHERE   (CASE WHEN @_season IS NULL OR @_season <> season THEN @r := month ELSE month END IS NOT NULL)
        AND (@_season := season) IS NOT NULL

Using the same principle, we can also emulate LEAD and LAG.

LAG(column) returns the value of column from the previous row in the grouping set, or NULL if there is no previous row.
LEAD(column) returns the value of column from the next row in the grouping set, or NULL if there is no next row.

SELECT  mo.id, mo.season,
        @r AS `LAG(month) OVER (PARTITION BY season ORDER BY id)`,
        (@r := month) AS month
FROM    (
        SELECT  m.*
        FROM    (
                SELECT  @_season = NULL,
                        @s := NULL
                ) vars,
                t_month m
        ORDER BY
                season, id
        ) mo
WHERE  (CASE WHEN @_season IS NULL OR @_season <> season THEN @r := NULL ELSE NULL END IS NULL)
        AND (@_season := season) IS NOT NULL

SELECT  mo.id, mo.season,
        @r AS `LEAD(month) OVER (PARTITION BY season ORDER BY id)`,
        (@r := month) AS month
FROM    (
        SELECT  m.*
        FROM    (
                SELECT  @_season = NULL,
                        @s := NULL
                ) vars,
                t_month m
        ORDER BY
                season, id DESC
        ) mo
WHERE  (CASE WHEN @_season IS NULL OR @_season <> season THEN @r := NULL ELSE NULL END IS NULL)
        AND (@_season := season) IS NOT NULL

In this query, we assign a new value of month to @r in the SELECT clause, and we need to change the order or columns a little so that the new value is assigned only after the old value is selected.

If it is not acceptable for some reason, we’ll need to wrap this query into a subquery, but I won’t do it in this example for the sake of brevity.

To be continued.

If this article was useful to you, please put some effort into sharing it with the others.
I would really appreciate it.