ContextQMD
Back to Materialize

Window Functions Syntax Table

doc/user/layouts/shortcodes/idiomatic-sql/window-functions-syntax-table.html

1231.9 KB
Original Source
Idiomatic Materialize SQL Pattern
Top-K over partition
(K >= 1)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) -- K is a number \>= 1 ORDER BY fieldA, fieldZ ... ;
Top-K over partition
(K = 1)mzsql SELECT DISTINCT ON(fieldA) fieldA, fieldB, ... FROM tableA ORDER BY fieldA, fieldZ ... -- Top-K where K is 1;
First value over partition
order by ...mzsql SELECT tableA.fieldA, tableA.fieldB, minmax.Z FROM tableA, (SELECT fieldA, MIN(fieldZ) -- Or MAX() FROM tableA GROUP BY fieldA) minmax WHERE tableA.fieldA = minmax.fieldA ORDER BY fieldA ... ;
Last value over partition
order by ...
range between unbounded preceding
and unbounded followingmzsql SELECT tableA.fieldA, tableA.fieldB, minmax.Z FROM tableA, (SELECT fieldA, MAX(fieldZ) -- Or MIN() FROM tableA GROUP BY fieldA) minmax WHERE tableA.fieldA = minmax.fieldA ORDER BY fieldA ... ;
Lag over (order by) whose ordering can be represented by some equality condition.***To exclude the first row since it has no previous row*** mzsql SELECT t1.fieldA, t2.fieldB FROM tableA t1, tableA t2 WHERE t1.fieldA = t2.fieldA + ... ORDER BY fieldA; ***To include the first row*** mzsql SELECT t1.fieldA, t2.fieldB FROM tableA t1 LEFT JOIN tableA t2 ON t1.fieldA = t2.fieldA + ... ORDER BY fieldA;
Lead over (order by) whose ordering can be represented by some equality condition.***To exclude the last row since it has no next row*** mzsql SELECT t1.fieldA, t2.fieldB FROM tableA t1, tableA t2 WHERE t1.fieldA = t2.fieldA - ... ORDER BY fieldA; ***To include the last row*** mzsql SELECT t1.fieldA, t2.fieldB FROM tableA t1 LEFT JOIN tableA t2 ON t1.fieldA = t2.fieldA - ... ORDER BY fieldA;