Home  /  Overview  /  Idiomatic Materialize SQL  /  Appendix

Window function to idiomatic Materialize

View as Markdown

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 not use the window functions and can provide better performance.

Materialize and window functions

For indexed views and materialized views that contain window functions (including aggregate functions used with an OVER clause), when an input record in a partition is added/removed/changed, Materialize recomputes the results from scratch for that partition (instead of using incremental computation).

The PARTITION BY clause of your window function determines your partitions. If PARTITION BY is omitted, all records belong to a single partition (i.e., any record change results in a recomputation from scratch over the whole input).

To avoid performance issues that may arise as the number of records grows, consider rewriting your indexed views and materialized views to use idiomatic Materialize SQL instead of window functions. If your view definitions cannot be rewritten without the window functions and the performance of window functions is insufficient for your use case, please contact our team.

Windows function anti-pattern Materialize idiomatic SQL

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

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

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 ... ;

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

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

-- 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;

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

 
-- 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, ...;

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 ... ;

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

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

-- 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;

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

 
-- 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 ...;

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 ... ;
Back to top ↑