Window function to idiomatic Materialize - Materialize

Materialize offers a wide range of window functions. However, for some LAG(), LEAD(), ROW_NUMBER(), FIRST_VALUE(), and LAST_VALUE() use cases, Materialize provides its own idiomatic query patterns that do <red>not</red> use the window functions and can provide better performance.

Materialize and window functions

<table> <thead> <tr> <th> Windows function anti-pattern </th> <th> Materialize idiomatic SQL </th> </tr> </thead> <tbody> <tr> <td colspan=2>

First value within groups. For more information and examples, see Idiomatic Materialize SQL: First value.

</td> </tr> <tr> <td> <div style="background-color: var(--code-block)">

nofmt

-- Anti-pattern. Avoid. --
SELECT fieldA, fieldB,
 FIRST_VALUE(fieldZ)
   OVER (PARTITION BY fieldA ORDER BY ...)
FROM tableA
ORDER BY fieldA, ...;

</div> </td> <td class="copyableCode">

mzsql

SELECT tableA.fieldA, tableA.fieldB, minmax.Z
FROM tableA,
     (SELECT fieldA,
        MIN(fieldZ)
      FROM tableA
      GROUP BY fieldA) minmax
WHERE tableA.fieldA = minmax.fieldA
ORDER BY fieldA ... ;

</td> </tr> <tr> <td colspan=2>

Lag over whose order by field advances in a regular pattern. For more information and examples, see Idiomatic Materialize SQL: Lag over.

</td> </tr> <tr> <td> <div style="background-color: var(--code-block)">

nofmt

-- Anti-pattern. Avoid --
SELECT fieldA, ...
  LAG(fieldZ)
    OVER (ORDER BY fieldA) as previous_row_value
FROM tableA;

</div> </td> <td class="copyableCode">

mzsql

-- Excludes the first row in the results --
SELECT t1.fieldA, t2.fieldB as previous_row_value
FROM tableA t1, tableA t2
WHERE t1.fieldA = t2.fieldA + ...
ORDER BY fieldA;

</td> </tr> <tr> <td colspan=2>

Last value within groups. For more information and examples, see Idiomatic Materialize SQL: Last value in group.

</td> </tr> <tr> <td> <div style="background-color: var(--code-block)">

nofmt

-- Anti-pattern. Unsupported range. --
SELECT fieldA, fieldB,
  LAST_VALUE(fieldZ)
    OVER (PARTITION BY fieldA ORDER BY fieldZ
          RANGE BETWEEN
            UNBOUNDED PRECEDING AND
            UNBOUNDED FOLLOWING)
FROM tableA
ORDER BY fieldA, ...;

</div> </td> <td class="copyableCode">

mzsql

SELECT tableA.fieldA, tableA.fieldB, minmax.Z
 FROM tableA,
      (SELECT fieldA,
         MAX(fieldZ)
       FROM tableA
       GROUP BY fieldA) minmax
WHERE tableA.fieldA = minmax.fieldA
ORDER BY fieldA ... ;

</td> </tr> <tr> <td colspan=2>

Lead over whose order by field advances in a regular pattern. For more information and examples, see Idiomatic Materialize SQL: Lead over.

</td> </tr> <tr> <td> <div style="background-color: var(--code-block)">

nofmt

-- Anti-pattern. Avoid. --
SELECT fieldA, ...
    LEAD(fieldZ)
      OVER (ORDER BY fieldA) as next_row_value
FROM tableA;

</div> </td> <td class="copyableCode">

mzsql

-- Excludes the last row in the results --
SELECT t1.fieldA, t2.fieldB as next_row_value
FROM tableA t1, tableA t2
WHERE t1.fieldA = t2.fieldA - ...
ORDER BY fieldA;

</td> </tr> <tr> <td colspan=2>

Top-K queries. For more information and examples, see Idiomatic Materialize SQL: Top-K in group.

</td> </tr> <tr> <td> <div style="background-color: var(--code-block)">

nofmt

-- Anti-pattern. Avoid. --
SELECT fieldA, fieldB, ...
FROM (
  SELECT fieldA, fieldB, ... , fieldZ,
     ROW_NUMBER() OVER (PARTITION BY fieldA
     ORDER BY fieldZ ... ) as rn
  FROM tableA)
WHERE rn <= K
ORDER BY fieldA, fieldZ ...;

</div> </td> <td class="copyableCode">

mzsql

SELECT fieldA, fieldB, ...
FROM (SELECT DISTINCT fieldA FROM tableA) grp,
  LATERAL (SELECT fieldB, ... , fieldZ FROM tableA
           WHERE fieldA = grp.fieldA
           ORDER BY fieldZ ... LIMIT K)
ORDER BY fieldA, fieldZ ... ;

</td> </tr> </tbody> </table>