Utilizing native JSON variables to emulate window capabilities in MySQL 5.7

One among MySQL 8’s largest enhancements is the help of window capabilities. As I all the time mentioned in conferences, there’s SQL earlier than window capabilities and SQL after window capabilities. When you begin utilizing them, you’ll use them all over the place.

A few of you poor souls are unlucky sufficient to be caught on MySQL 5.7, both of your individual selecting, or since you’re utilizing a clone / fork that’s nonetheless 5.7 suitable. Whereas for most individuals, this weblog publish is simply on your amusement, or nostalgia, for a few of you this publish might be fairly helpful.

Utilizing native variables

Plenty of Stack Overflow questions or weblog posts on the market present the identical outdated trick utilizing native variables. In a procedural context, native variables make excellent sense. For instance, this assertion batch.

SET @c = (SELECT COUNT(*) FROM information_schema.tables);
-- Extra processing
-- Return the end result:
SELECT @c;

A bit hairier is the truth that these native variables will be declared inside a question, and incremented procedurally inside a question:

SELECT
  a, 

  -- Use and increment your variable in SELECT
  @rn := @rn + 1
FROM 
  (
    SELECT 3 AS a UNION ALL
    SELECT 4 AS a    
  ) AS t, 

  -- Declare your variable in FROM
  (SELECT @rn := 0) r
ORDER BY a;

And growth, you’ve a ROW_NUMBER() OVER (ORDER BY a) window perform! The end result being:

|a  |@rn := @rn + 1|
|---|--------------|
|3  |1             |
|4  |2             |

This works fairly by the way, as a result of the expression incrementing the row quantity “occurs to” be evaluated within the desired order, row by row, due to the question’s ORDER BY a clause. Revert it:

SELECT
  a, @rn := @rn + 1
FROM (
  SELECT 3 AS a UNION ALL
  SELECT 4 AS a    
) AS t, (SELECT @rn := 0) r
ORDER BY a DESC;

And you continue to get the specified end result:

|a  |@rn := @rn + 1|
|---|--------------|
|4  |1             |
|3  |2             |

That is actually bushy, as a result of it violates the concept of SQL’s logical order of operations, which most RDBMS agree upon. It assumes ORDER BY “occurs earlier than” SELECT, simply because the optimiser chooses to do issues this fashion. You may tamper with the optimiser and break the “characteristic” simply, e.g. by including DISTINCT:

SELECT DISTINCT
  a, @rn := @rn + 1
FROM (
  SELECT 3 AS a UNION ALL
  SELECT 4 AS a    
) AS t, (SELECT @rn := 0) r
ORDER BY a DESC;

Now the result’s now not what we wished (how may it probably be?):

|a  |@rn := @rn + 1|
|---|--------------|
|4  |2             |
|3  |1             |

The reason being that DISTINCT is often carried out utilizing a form or a hashmap, each is not going to protect any ordering, and in keeping with the aforementioned logical order of operations, that is completely fantastic, as a result of ORDER BY is speculated to “occur after” SELECT and after DISTINCT, at the least logically.

However when you’re cautious, and canopy the whole lot with sufficient assessments, you would nonetheless use this trick. In any case, being caught with MySQL 5.7 is already painful sufficient, so why not deal with your self to an “nearly window perform”.

Observe: Simply to point how a lot of a nasty concept relying on this incidental characteristic is, MySQL 8.x now points a deprecation warning:

Setting person variables inside expressions is deprecated and might be eliminated in a future launch. Think about alternate options: ‘SET variable=expression, …’, or ‘SELECT expression(s) INTO variables(s)’.

The primary motive I’ve seen this syntax getting used on Stack Overflow to this point is to emulate ROW_NUMBER, so, I’d say, good riddance (now that MySQL 8 has window perform help)

PARTITION BY utilizing ORDER BY

What I haven’t seen a lot on Stack Overflow or in blogs, is PARTITION BY help. Most options I’ve seen use ORDER BY to implement partitioning, which is okay. For instance:

SELECT
  a, b,
  ROW_NUMBER() OVER (PARTITION BY a ORDER BY b DESC) AS rn1,
  IF (
    @prev = a, 
    @rn := @rn + 1, 
    CASE WHEN (@prev := a) IS NOT NULL OR TRUE THEN @rn := 1 END
  ) AS rn2
FROM (
  SELECT 1 AS a, 3 AS b UNION ALL
  SELECT 2 AS a, 4 AS b UNION ALL
  SELECT 1 AS a, 5 AS b UNION ALL
  SELECT 2 AS a, 6 AS b
) AS t, (SELECT @rn := 0, @prev := NULL) r
ORDER BY a, b DESC;

Producing:

|a  |b  |rn1|rn2|
|---|---|---|---|
|1  |5  |1  |1  |
|1  |3  |2  |2  |
|2  |6  |1  |1  |
|2  |4  |2  |2  |

A couple of notes:

• The specified PARTITION BY and ORDER BY clauses each need to be mirrored within the high degree question. Should you solely wished to ORDER BY b DESC, not ORDER BY a as nicely, robust luck. (If you wish to mess around with this, strive eradicating the ROW_NUMBER() perform, which additionally orders stuff by a, implicitly)
• I’ve tried to place all of the variable task logic right into a single expression with a view to keep away from any additional columns being generated. This makes the expression a bit extra ugly than it wanted to be.

PARTITION BY utilizing JSON

A extra sturdy, however maybe slower strategy to emulating PARTITION BY can be to keep up a JSON object that retains monitor of every partition key’s ROW_NUMBER(), as a result of why not?

Behold this magnificence:

SELECT
  a, b,
  ROW_NUMBER() OVER (PARTITION BY a ORDER BY b DESC) AS rn1,
  json_extract(
    @rn := json_set(
      @rn, @path := concat('$."', a, '"'), 
      (coalesce(json_extract(@rn, @path), 0) + 1)
    ), 
    @path
  ) AS rn2,
  @rn AS debug -- Added for debugging functions solely
FROM (
  SELECT 1 AS a, 3 AS b UNION ALL
  SELECT 2 AS a, 4 AS b UNION ALL
  SELECT 1 AS a, 5 AS b UNION ALL
  SELECT 2 AS a, 6 AS b
) AS t, (SELECT @rn := '{}') r
ORDER BY b DESC;

Try the outcomes:

|a  |b  |rn1|rn2|debug               |
|---|---|---|---|--------------------|
|2  |6  |1  |1.0|{"1": 2.0, "2": 1.0}|
|1  |5  |1  |1.0|{"1": 1.0}          |
|2  |4  |2  |2.0|{"1": 2.0, "2": 2.0}|
|1  |3  |2  |2.0|{"1": 2.0}          |

You may do this on MySQL 5.7 (eradicating the ROW_NUMBER(), in fact), and also you’ll see this works completely fantastic! How does it work?

• We begin with an empty object {} within the FROM clause.
• On each row that’s by the way ordered by the ORDER BY b DESC clause, we’ll extract the row quantity worth for the partition key PARTITION BY a. That is finished with a dynamically created JSON path expression concat('$."', a, '"'). For instance: $."1" or $."2".
• At first, this worth is NULL, in fact, so we flip it to zero with COALESCE(<expr>, 0).
• We add 1 to it
• Then we JSON_SET the worth again into the thing, assigning the end result again to @rn.
• Then, we re-extract the worth we’ve simply calculated

This might be simplified a bit if it wasn’t only a single expression, however since I’m pondering of implementing this emulation in jOOQ, I wished to do the train of protecting the projection unchanged (think about, the jOOQ person writes ROW_NUMBER() with jOOQ, and needs this to “simply work”).

Caveats:

• If the PARTITION BY clause has a number of expressions, then the composite worth must be used as a key, e.g. utilizing some “not possible” concatenation token (a token that may’t seem within the information set), or a hash worth (risking collisions, in fact), or an extra lookup, making issues fairly sophisticated.
• The concat('$."', a, '"') expression doesn’t correctly quote a but, in case it incorporates double quotes.
• If a number of distinct window perform calculations with distinct ORDER BY clauses are required, then this strategy received’t work as simply. It may be potential to calculate issues with one derived desk nest degree per window perform (?). Nonetheless, a number of distinct PARTITION BY clauses are fantastic. Simply generate a separate @rn variable per distinct PARTITOIN BY clause.
• The JSON doc would possibly lose information sort data. For instance, in JSON, numbers could also be represented as floats, so when you require decimal precision, maybe you must work with JSON strings as an alternative, and forged issues backwards and forwards, all the time valuing correctness over efficiency.

Do you suppose you’ve seen the whole lot? Let’s do one thing much more bushy:

DENSE_RANK with PARTITION BY

We received’t cease right here, as a result of as soon as we’ve chosen this loopy path, we’d as nicely see it to the top. Let’s emulate DENSE_RANK(), which is a bit more durable, making the SQL extra “lovely”:

SELECT
  a, b,
  DENSE_RANK() OVER (PARTITION BY a ORDER BY b DESC) AS rn1,
  json_extract(
    @rn := json_set(@rn, 
      @rnpath := concat('$."rn-', a, '"'), 
      (coalesce(json_extract(@rn, @rnpath), 0) + IF (
        json_extract(@rn, @prepath := concat('$."pre-v-', a, '"')) = b, 
        0, 1
      )),
      @prepath,
      b
    ), 
    @rnpath
  ) AS rn2,
  @rn AS debug
FROM (
  SELECT 1 AS a, 3 AS b UNION ALL
  SELECT 1 AS a, 5 AS b UNION ALL
  SELECT 1 AS a, 5 AS b UNION ALL
  SELECT 2 AS a, 6 AS b
) AS t, (SELECT @rn := '{}') r
ORDER BY b DESC;

Right here’s the end result:

|a  |b  |rn1|rn2|debug                                                 |
|---|---|---|---|------------------------------------------------------|
|2  |6  |1  |1.0|{"rn-1": 2.0, "rn-2": 1.0, "pre-v-1": 3, "pre-v-2": 6}|
|1  |5  |1  |1.0|{"rn-1": 1.0, "pre-v-1": 5}                           |
|1  |5  |1  |1.0|{"rn-1": 1.0, "pre-v-1": 5}                           |
|1  |3  |2  |2.0|{"rn-1": 2.0, "pre-v-1": 3}                           |

How does it differ?

• We now have to recollect not simply the earlier row quantity worth per partition ("rn-1", "rn-2"), but in addition the earlier worth of b (the ORDER BY standards) per partition ("pre-v-1", "pre-v-2").
• Then, we increment the row quantity per partition provided that the earlier worth is totally different from the present worth

Caveats:

• There can nonetheless be a number of PARTITION BY expressions, in addition to path escaping issues, see caveats of ROW_NUMBER for particulars.
• If there are a number of ORDER BY columns, the "pre-v-n" values must keep in mind their composite worth, e.g. by nesting a JSON object. It is a bit less complicated to have in mind than a number of PARTITION BY expressions

Furry sufficient? Let’s go deeper

RANK with PARTITION BY

Who would have thought that RANK is more durable than DENSE_RANK (see this text for a direct comparability of the capabilities). Now, along with remembering the earlier ordering worth per partition, we additionally want to recollect the earlier rank per partition (all of the whereas persevering with to rely up the row quantity).

Observe, you possibly can refactor this to one thing extra readable when you take away the jOOQ imposed single expression restriction, however the place’s the problem in that, proper? Right here it’s, bow earlier than it in awe (or terror):

SELECT
  a, b,
  RANK() OVER (PARTITION BY a ORDER BY b DESC) AS rn1,
  coalesce(
    json_extract(
      @rn := json_set(@rn, 
        @rnpath := concat('$."rn-', a, '"'), 
        @currn := coalesce(json_extract(@rn, @rnpath), 0) + 1,
        @prevpath := concat('$."pre-v-', a, '"'),
        b,
        @prernpath := concat('$."pre-rn-', a, '"'),
        IF (json_extract(@rn, @prevpath) = b, 
          coalesce(json_extract(@rn, @prernpath), @currn) div 1,
          @currn
        )
      ), 
      @prernpath
    ), 
    @currn
  ) AS rn2,
  @rn AS debug
FROM (
  SELECT 1 AS a, 3 AS b UNION ALL
  SELECT 1 AS a, 5 AS b UNION ALL
  SELECT 1 AS a, 5 AS b UNION ALL
  SELECT 2 AS a, 6 AS b
) AS t, (SELECT @rn := '{}') r
ORDER BY b DESC;

It produces:

|a  |b  |rn1|rn2|debug                                                                                   |
|---|---|---|---|----------------------------------------------------------------------------------------|
|2  |6  |1  |1.0|{"rn-1": 3.0, "rn-2": 1.0, "pre-v-1": 3, "pre-v-2": 6, "pre-rn-1": 3.0, "pre-rn-2": 1.0}|
|1  |5  |1  |1.0|{"rn-1": 1.0, "pre-v-1": 5, "pre-rn-1": 1.0}                                            |
|1  |5  |1  |1.0|{"rn-1": 2.0, "pre-v-1": 5, "pre-rn-1": 1.0}                                            |
|1  |3  |3  |3.0|{"rn-1": 3.0, "pre-v-1": 3, "pre-rn-1": 3.0}                                            |

How does it work? “Merely”:

Caveats:

PERCENT_RANK and CUME_DIST

I’m not satisfied that these will be emulated with the native variable based mostly strategy. In precept:

• PERCENT_RANK() OVER w is simply (RANK() OVER w - 1) / (COUNT(*) OVER () - 1)
• CUME_DIST() OVER w is simply (RANK() OVER w) / (COUNT(*) OVER ())

However as we’ll see under, it’s not potential (I believe?) to emulate COUNT(*) OVER () utilizing this native variable based mostly strategy. You possibly can, perhaps, do one other spherical of calculations when wrapping issues in a derived desk, although.

LEAD, LAG, and so forth.

A few of these may also be emulated with the above method, particularly those which might be “backward wanting”.

• LAG: For instance, with LAG, we simply have to recollect once more the "pre-v-x" for every partition, and produce it once more on the present row. Relying on the LAG‘s OFFSET, we’d must preserve round a JSON array of values, all the time appending the present worth to the array, and eradicating the primary worth, like in a FIFO queue.
• LEAD: The ahead wanting capabilities simply need to reverse the ORDER BY clause. For instance, all LEAD capabilities will be carried out with LAG as nicely.
• FIRST_VALUE: It is a bit less complicated than LAG, as we don’t need to preserve a whole JSON array of values. We simply keep in mind the primary one, after which preserve reproducing this.
• LAST_VALUE is once more simply the inverse of FIRST_VALUE with reversed ORDER BY clause.
• NTH_VALUE wants a counter per partition, to make sure we catch the Nth worth. Alternatively, we are able to once more retailer the whole lot in a JSON array till it reaches dimension N.
• IGNORE NULLS will be carried out by skipping all of the NULL values from being entered into the aforementioned FIFO queue
• Issues get a bit trickier when there’s a RANGE or ROWS clause, in case of which the JSON array / FIFO queue needs to be shifted. This impacts FIRST_VALUE greater than LEAD, I’d say.

The precise implementation is left as an train to the person. (In all probability about time to think about upgrading to MySQL 8, by now!)

Mixture capabilities

All SQL mixture capabilities will be was window capabilities by appending OVER (). For instance:

• SUM(x) is an mixture perform, aggregating information per group generated by the GROUP BY clause, shared by your complete question.
• SUM(x) OVER () is the corresponding window perform, aggregating information per partition generated by the PARTITION BY clause per window perform (or relatively, per specific or implicit window specification)

Since these beforehand mentioned native variable based mostly approaches are row-by-row based mostly calculations, I don’t suppose it’s potential to emulate partition large mixture window capabilities, as a result of these require with the ability to have a look at your complete partition, together with rows that haven’t but been projected.

Nonetheless (by no means quit!), some window frames will be emulated additionally for mixture capabilities, particularly the backward wanting ones. For simplicity, I’ll simply strive emulating this:

SUM(b) OVER (
  PARTITION BY a
  ORDER BY b DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Observe: with out specific window body, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is implicit, and that signifies that with a view to embrace tied rows within the sum, we’d need to once more be ahead wanting, which I don’t suppose is feasible with the native variable row-by-row based mostly strategy.

Nonetheless, it may be potential to emulate various backward wanting ROWS frames. That train is once more left to the reader.

So, let’s do that:

SELECT
  a, b,
  SUM(b) OVER w AS sum1,
  json_extract(
    @w := json_set(@w, 
      @spath := concat('$."s-', a, '"'), 
      (coalesce(json_extract(@w, @spath), 0) + b),
      @cpath := concat('$."c-', a, '"'), 
      (coalesce(json_extract(@w, @cpath), 0) + 1)
    ), 
    @spath
  ) AS sum2,
  COUNT(*) OVER w AS cnt1,
  json_extract(@w, @cpath) AS cnt2,
  AVG(b) OVER w AS avg1,
  json_extract(@w, @spath) / json_extract(@w, @cpath) AS avg2,
  @w AS debug
FROM (
  SELECT 1 AS a, 3 AS b UNION ALL
  SELECT 1 AS a, 5 AS b UNION ALL
  SELECT 1 AS a, 5 AS b UNION ALL
  SELECT 2 AS a, 6 AS b
) AS t, (SELECT @w := '{}') r
WINDOW w AS (
  PARTITION BY a 
  ORDER BY b DESC 
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
ORDER BY b DESC;

The output appears appropriate:

|a  |b  |sum1|sum2|cnt1|cnt2|avg1  |avg2        |debug                                            |
|---|---|----|----|----|----|------|------------|-------------------------------------------------|
|2  |6  |6   |6.0 |1   |1.0 |6     |6           |{"c-1": 3.0, "c-2": 1.0, "s-1": 13.0, "s-2": 6.0}|
|1  |5  |5   |5.0 |1   |1.0 |5     |5           |{"c-1": 1.0, "s-1": 5.0}                         |
|1  |5  |10  |10.0|2   |2.0 |5     |5           |{"c-1": 2.0, "s-1": 10.0}                        |
|1  |3  |13  |13.0|3   |3.0 |4.3333|4.3333333333|{"c-1": 3.0, "s-1": 13.0}                        |

Notes:

• I’ve saved all of the window calculations in the identical JSON object, assuming all of them share the identical window specification, to allow them to reuse their values (e.g. AVG(x) = SUM(x) / COUNT(x), and thus AVG(x) OVER w = SUM(x) OVER w / COUNT(x) OVER w)
• Aside from that, issues work just about similar to for ROW_NUMBER()

Conclusion

This has been a enjoyable weblog publish to write down. I hope it was useful to you both as an train to consider what window capabilities actually do, or within the worst case, that can assist you poor soul truly implement issues this fashion on MySQL 5.7.

There have been numerous caveats. This emulation strategy doesn’t all the time work and makes (heavy) assumptions about your question. For instance:

• You may’t use DISTINCT
• You may’t use arbitrary ORDER BY clauses that don’t match the window perform’s
• You may’t use a number of window capabilities with totally different window specs
• You may’t use ahead wanting window frames (together with frameless mixture window capabilities that mixture your complete partition)

There are in all probability extra caveats that haven’t been mentioned right here. Should you’re diligent, and check issues closely, nonetheless, you would possibly have the ability to pull off utilizing these approaches. Good luck (and don’t blame me 😅)