window-function-to-materialize.md 4.6 KB
title: "Window function to idiomatic Materialize" description: "Cheatsheet for window functions to idiomatic Materialize SQL." disable_toc: true menu: main:
parent: idiomatic-materialize-appendix
weight: 20
identifier: idiomatic-materialize-appendix-window-functions-to-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 not use the window functions and
can provide better performance.
{{< callout >}}
Materialize and window functions
{{< idiomatic-sql/materialize-window-functions >}}
{{</ callout >}}
| Windows function anti-pattern | Materialize idiomatic SQL |
|---|---|
| **First value within groups.** For more information and examples, see [Idiomatic Materialize SQL: First value](/transform-data/idiomatic-materialize-sql/first-value/). | |
|
```nofmt
-- Anti-pattern. Avoid. --
SELECT fieldA, fieldB,
FIRST_VALUE(fieldZ)
OVER (PARTITION BY fieldA ORDER BY ...)
FROM tableA
ORDER BY fieldA, ...;
```
|
```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 ... ; ``` |
| **Lag over whose order by field advances in a regular pattern.** For more information and examples, see [Idiomatic Materialize SQL: Lag over](/transform-data/idiomatic-materialize-sql/lag/). | |
|
```nofmt
-- Anti-pattern. Avoid --
SELECT fieldA, ...
LAG(fieldZ)
OVER (ORDER BY fieldA) as previous_row_value
FROM tableA;
```
|
```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; ``` |
| **Last value within groups.** For more information and examples, see [Idiomatic Materialize SQL: Last value in group](/transform-data/idiomatic-materialize-sql/last-value/). | |
|
```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, ...;
```
|
```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 ... ; ``` |
| **Lead over whose order by field advances in a regular pattern.** For more information and examples, see [Idiomatic Materialize SQL: Lead over](/transform-data/idiomatic-materialize-sql/lead/). | |
|
```nofmt
-- Anti-pattern. Avoid. --
SELECT fieldA, ...
LEAD(fieldZ)
OVER (ORDER BY fieldA) as next_row_value
FROM tableA;
```
|
```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; ``` |
| **Top-K queries.** For more information and examples, see [Idiomatic Materialize SQL: Top-K in group](/transform-data/idiomatic-materialize-sql/top-k/). | |
|
```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 ...;
```
|
```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 ... ; ``` |