MIN/MAX ... GROUP BY subquery. |
| Lag over a regularly increasing field | Use self join or a self LEFT JOIN/LEFT OUTER JOIN by an equality match on the regularly increasing field. |
| Last value within groups | Use MIN/MAX ... GROUP BY subquery |
| Lead over a regularly increasing field | Use self join or a self LEFT JOIN/LEFT OUTER JOIN by an equality match on the regularly increasing field. |
| Top-K | Use an ORDER BY ... LIMIT subquery with a LATERAL JOIN on a DISTINCT subquery (or, for K=1, a SELECT DISTINCT ON ... ORDER BY ... LIMIT query) |
## General query patterns
| Query Pattern | Idiomatic Materialize |
| --- | --- |
| ANY() Equi-join condition | Use UNNEST() or DISTINCT UNNEST() to expand the values and join. |
| mz_now() with date/time operators | Move the operation to the other side of the comparison: |
| mz_now() with disjunctions (OR) in materialized/indexed view definitions and SUBSCRIBE statements: | Rewrite using UNION ALL or UNION (deduplicating as necessary) expression |
---
## `ANY()` equi-join condition
## Overview
The "`field = ANY(...)`" equality condition returns true if the equality
comparison is true for any of the values in the `ANY()` expression.
For equi-join whose `ON` expression includes an [`ANY` operator
expression](/sql/functions/#expression-bool_op-any),
Materialize provides an idiomatic SQL as an alternative to the `ANY()`
expression.
> ### Materialize and equi-join `ON fieldX = ANY(|
**If no duplicates exist in the unnested field:** Use a Common Table
Expression (CTE) to [`UNNEST()`](/sql/functions/#unnest) the array of values and
perform the equi-join on the unnested values.
```mzsql
-- array_field contains no duplicates.--
WITH my_expanded_values AS
(SELECT UNNEST(array_field) AS fieldZ FROM tableB)
SELECT a.fieldA, ...
FROM tableA a
JOIN my_expanded_values t ON a.fieldZ = t.fieldZ
;
```
|
|
|
**Duplicates may exist in the unnested field:** Use a Common Table
Expression (CTE) to [`DISTINCT`](/sql/select/#select-distinct)
[`UNNEST()`](/sql/functions/#unnest) the array of values and perform the
equi-join on the unnested values.
```mzsql
-- array_field may contain duplicates.--
WITH my_expanded_values AS
(SELECT DISTINCT UNNEST(array_field) AS fieldZ FROM tableB)
SELECT a.fieldA, ...
FROM tableA a
JOIN my_expanded_values t ON a.fieldZ = t.fieldZ
;
```
|
|
|
```nofmt
-- Anti-pattern. Avoid. --
SELECT a.fieldA, ...
FROM tableA a, tableB b
WHERE a.fieldZ = ANY(b.array_field) -- Anti-pattern. Avoid.
;
```
|
| ***If no duplicates in the unnested field*** ```mzsql -- sales_items.items contains no duplicates. -- WITH individual_sales_items AS (SELECT unnest(items) as item, week_of FROM sales_items) SELECT s.week_of, o.order_id, o.item, o.quantity FROM orders o JOIN individual_sales_items s ON o.item = s.item WHERE date_trunc('week', o.order_date) = s.week_of ORDER BY s.week_of, o.order_id, o.item, o.quantity ; ``` ***To omit duplicates that may exist in the unnested field*** ```mzsql -- sales_items.items may contains duplicates -- WITH individual_sales_items AS (SELECT DISTINCT unnest(items) as item, week_of FROM sales_items) SELECT s.week_of, o.order_id, o.item, o.quantity FROM orders o JOIN individual_sales_items s ON o.item = s.item WHERE date_trunc('week', o.order_date) = s.week_of ORDER BY s.week_of, o.order_id, o.item, o.quantity ; ``` | |
|
```nofmt
-- Anti-pattern. Avoid. --
SELECT s.week_of, o.order_id, o.item, o.quantity
FROM orders o
JOIN sales_items s ON o.item = ANY(s.items)
WHERE date_trunc('week', o.order_date) = s.week_of
ORDER BY s.week_of, o.order_id, o.item, o.quantity
;
```
|
|
Use a subquery that uses the [MIN()](/sql/functions/#min) or
[MAX()](/sql/functions/#max) aggregate function.
```mzsql
SELECT tableA.fieldA, tableA.fieldB, minmax.Z
FROM tableA,
(SELECT fieldA,
MIN(fieldZ),
MAX(fieldZ)
FROM tableA
GROUP BY fieldA) minmax
WHERE tableA.fieldA = minmax.fieldA
ORDER BY fieldA ... ;
```
|
|
|
```nofmt
-- Anti-pattern. Avoid. --
SELECT fieldA, fieldB,
FIRST_VALUE(fieldZ) OVER (PARTITION BY fieldA ORDER BY ...),
FIRST_VALUE(fieldZ) OVER (PARTITION BY fieldA ORDER BY ... DESC)
FROM tableA
ORDER BY fieldA, ...;
```
|
| ```mzsql SELECT o.order_id, minmax.lowest_price, o.item, o.price, o.price - minmax.lowest_price AS diff_lowest_price FROM orders_view o, (SELECT order_id, MIN(price) AS lowest_price FROM orders_view GROUP BY order_id) minmax WHERE o.order_id = minmax.order_id ORDER BY o.order_id, o.item; ``` | |
|
```nofmt
-- Anti-pattern --
SELECT order_id,
FIRST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price) AS lowest_price,
item,
price,
price - FIRST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price) AS diff_lowest_price
FROM orders_view
ORDER BY order_id, item;
```
|
| ```mzsql SELECT o.order_id, minmax.highest_price, o.item, o.price, o.price - minmax.highest_price AS diff_highest_price FROM orders_view o, (SELECT order_id, MAX(price) AS highest_price FROM orders_view GROUP BY order_id) minmax WHERE o.order_id = minmax.order_id ORDER BY o.order_id, o.item; ``` | |
|
```nofmt
-- Anti-pattern --
SELECT order_id,
FIRST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price DESC) AS highest_price,
item,
price,
price - FIRST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price DESC) AS diff_highest_price
FROM orders_view
ORDER BY order_id, item;
```
|
| ```mzsql SELECT o.order_id, minmax.lowest_price, minmax.highest_price, o.item, o.price, o.price - minmax.lowest_price AS diff_lowest_price, o.price - minmax.highest_price AS diff_highest_price FROM orders_view o, (SELECT order_id, MIN(price) AS lowest_price, MAX(price) AS highest_price FROM orders_view GROUP BY order_id) minmax WHERE o.order_id = minmax.order_id ORDER BY o.order_id, o.item; ``` | |
|
```nofmt
-- Anti-pattern --
SELECT order_id,
FIRST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price) AS lowest_price,
FIRST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price DESC) AS highest_price,
item,
price,
price - FIRST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price) AS diff_lowest_price,
price - FIRST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price DESC) AS diff_highest_price
FROM orders_view
ORDER BY order_id, item;
```
|
|
Use a self join that specifies an **equality match** on the lag's order by field
(e.g., `fieldA`). The order by field must increment in a regular pattern in
order to be represented by an equality condition (e.g., `WHERE t1.fieldA =
t2.fieldA + ...`). The
query *excludes* the first row in the results since it does not have a previous
row.
> **Important:** The idiomatic Materialize SQL applies only to those "lag over" queries whose
> ordering can be represented by some **equality condition**.
```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 + ... -- or some other operand ORDER BY fieldA; ``` |
|
|
```nofmt
-- Anti-pattern. Avoid. --
SELECT fieldA, ...
LAG(fieldZ) OVER (ORDER BY fieldA) as previous_row_value
FROM tableA;
```
|
|
Use a self [`LEFT JOIN/LEFT OUTER JOIN`](/sql/select/join/#left-outer-join)
(e.g., `FROM tableA t1 LEFT JOIN tableA t2`) that specifies an **equality
match** on the lag's order by field (e.g., `fieldA`). The order by field must
increment in a regular pattern in order to be represented by an equality
condition (e.g., `ON t1.fieldA = t2.fieldA + ...`). The
query *includes* the first row, returning `null` as its lag value.
> **Important:** The idiomatic Materialize SQL applies only to those "lag over" queries whose
> ordering can be represented by some **equality condition**.
```mzsql -- Includes the first row in the results -- SELECT t1.fieldA, t2.fieldB as previous_row_value FROM tableA t1 LEFT JOIN tableA t2 ON t1.fieldA = t2.fieldA + ... -- or some other operand ORDER BY fieldA; ``` |
|
|
```nofmt
SELECT fieldA, ...
LAG(fieldZ) OVER (ORDER BY fieldA) as previous_row_value
FROM tableA;
```
|
| ```mzsql -- Excludes the first row in results -- SELECT o1.order_date, o1.daily_total, o2.daily_total as previous_daily_total FROM orders_daily_totals o1, orders_daily_totals o2 WHERE o1.order_date = o2.order_date + INTERVAL '1' DAY ORDER BY order_date; ``` > **Important:** The idiomatic Materialize SQL applies only to those "lag over" queries whose > ordering can be represented by some **equality condition**. | |
|
```nofmt
-- Anti-pattern. Includes the first row's value. --
SELECT order_date, daily_total,
LAG(daily_total) OVER (ORDER BY order_date) as previous_daily_total
FROM orders_daily_totals;
```
|
| ```mzsql -- Include the first row in results -- SELECT o1.order_date, o1.daily_total, o2.daily_total as previous_daily_total FROM orders_daily_totals o1 LEFT JOIN orders_daily_totals o2 ON o1.order_date = o2.order_date + INTERVAL '1' DAY ORDER BY order_date; ``` > **Important:** The idiomatic Materialize SQL applies only to those "lag over" queries whose > ordering can be represented by some **equality condition**. | |
|
```nofmt
-- Anti-pattern. Includes the first row's value. --
SELECT order_date, daily_total,
LAG(daily_total) OVER (ORDER BY order_date) as previous_daily_total
FROM orders_daily_totals;
```
|
|
Use a subquery that uses the [MIN()](/sql/functions/#min) or
[MAX()](/sql/functions/#max) aggregate function.
```mzsql
SELECT tableA.fieldA, tableA.fieldB, minmax.Z
FROM tableA,
(SELECT fieldA,
MAX(fieldZ),
MIN(fieldZ)
FROM tableA
GROUP BY fieldA) minmax
WHERE tableA.fieldA = minmax.fieldA
ORDER BY fieldA ... ;
```
|
|
|
```nofmt
-- Unsupported --
SELECT fieldA, fieldB,
LAST_VALUE(fieldZ)
OVER (PARTITION BY fieldA ORDER BY fieldZ
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING),
LAST_VALUE(fieldZ)
OVER (PARTITION BY fieldA ORDER BY fieldZ DESC
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING)
FROM tableA
ORDER BY fieldA, ...;
```
|
| ```mzsql SELECT o.order_id, minmax.highest_price, o.item, o.price, o.price - minmax.highest_price AS diff_highest_price FROM orders_view o, (SELECT order_id, MAX(price) AS highest_price FROM orders_view GROUP BY order_id) minmax WHERE o.order_id = minmax.order_id ORDER BY o.order_id, o.item; ``` | |
|
```nofmt
-- Unsupported --
SELECT order_id,
LAST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS highest_price,
item,
price,
price - LAST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS diff_highest_price
FROM orders_view
ORDER BY order_id, item;
```
|
| ```mzsql SELECT o.order_id, minmax.lowest_price, o.item, o.price, o.price - minmax.lowest_price AS diff_lowest_price FROM orders_view o, (SELECT order_id, MIN(price) AS lowest_price FROM orders_view GROUP BY order_id) minmax WHERE o.order_id = minmax.order_id ORDER BY o.order_id, o.item; ``` | |
|
```nofmt
-- Unsupported --
SELECT order_id,
LAST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price DESC
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS lowest_price,
item,
price,
price - LAST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price DESC
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS diff_lowest_price
FROM orders_view
ORDER BY order_id, item;
```
|
| ```mzsql SELECT o.order_id, minmax.lowest_price, minmax.highest_price, o.item, o.price, o.price - minmax.lowest_price AS diff_lowest_price, o.price - minmax.highest_price AS diff_highest_price FROM orders_view o, (SELECT order_id, MIN(price) AS lowest_price, MAX(price) AS highest_price FROM orders_view GROUP BY order_id) minmax WHERE o.order_id = minmax.order_id ORDER BY o.order_id, o.item; ``` |
```nofmt
-- Unsupported --
SELECT order_id,
LAST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price DESC
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS lowest_price,
LAST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS highest_price,
item,
price,
price - LAST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price DESC
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS diff_lowest_price,
price - LAST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS diff_highest_price
FROM orders_view
ORDER BY order_id, item;
```
|
|
Use a self join that specifies an **equality match** on the lead's order by
field (e.g., `fieldA`). The order by field must increment in a regular pattern
in order to be represented by an equality condition (e.g., `WHERE t1.fieldA =
t2.fieldA - ...`). The query *excludes* the last row in the results since it
does not have a next row.
> **Important:** The idiomatic Materialize SQL applies only to those "lead over" queries whose
> ordering can be represented by some **equality condition**.
```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 - ... -- or some other operand ORDER BY fieldA; ``` |
|
|
```nofmt
-- Anti-pattern. Avoid. --
SELECT fieldA, ...
LEAD(fieldZ) OVER (ORDER BY fieldA) as next_row_value
FROM tableA;
```
|
| Use a self [`LEFT JOIN/LEFT OUTER JOIN`](/sql/select/join/#left-outer-join) (e.g., `FROM tableA t1 LEFT JOIN tableA t2`) that specifies an **equality match** on the lag's order by field (e.g., `fieldA`). The order by field must increment in a regular pattern in order to be represented by an equality condition (e.g., `ON t1.fieldA = t2.fieldA - ...`). The query *includes* the last row, returning `null` as its lead value. > **Important:** The idiomatic Materialize SQL applies only to those "lead over" queries whose > ordering can be represented by some **equality condition**. ```mzsql -- Includes the last row in the response -- SELECT t1.fieldA, t2.fieldB as next_row_value FROM tableA t1 LEFT JOIN tableA t2 ON t1.fieldA = t2.fieldA - ... -- or some other operand ORDER BY fieldA; ``` | |
|
```nofmt
SELECT fieldA, ...
LEAD(fieldZ) OVER (ORDER BY fieldA) as next_row_value
FROM tableA;
```
|
| ```mzsql -- Excludes the last row in results -- SELECT o1.order_date, o1.daily_total, o2.daily_total as next_daily_total FROM orders_daily_totals o1, orders_daily_totals o2 WHERE o1.order_date = o2.order_date - INTERVAL '1' DAY ORDER BY order_date; ``` > **Important:** The idiomatic Materialize SQL applies only to those "lead over" queries whose > ordering can be represented by some **equality condition**. | |
|
```nofmt
-- Anti-pattern. Includes the last row's value. --
SELECT order_date, daily_total,
LEAD(daily_total) OVER (ORDER BY order_date) as next_daily_total
FROM orders_daily_totals;
```
|
| ```mzsql -- Include the last row in the results -- SELECT o1.order_date, o1.daily_total, o2.daily_total as next_daily_total FROM orders_daily_totals o1 LEFT JOIN orders_daily_totals o2 ON o1.order_date = o2.order_date - INTERVAL '1' DAY ORDER BY order_date; ``` > **Important:** The idiomatic Materialize SQL applies only to those "lead over" queries whose > ordering can be represented by some **equality condition**. | |
|
```nofmt
-- Anti-pattern. Includes the last row in results. --
SELECT order_date, daily_total,
LEAD(daily_total) OVER (ORDER BY order_date) as next_daily_total
FROM orders_daily_totals;
```
|
mz_now() must be used with one of the following comparison operators: =,
<, <=, >, >=, or an operator that desugars to them or to a conjunction
(AND) of them (for example, BETWEEN...AND...). That is, you cannot use
date/time operations directly on mz_now() to calculate a timestamp in the
past or future. Instead, rewrite the query expression to move the operation to
the other side of the comparison.
#### Examples
| WHERE mz_now() > order_date + INTERVAL '5min'; WHERE mz_now() - INTERVAL '5min' > order_date; When used in a materialized view definition, a view definition that is being
indexed (i.e., although you can create the view and perform ad-hoc query on
the view, you cannot create an index on that view), or a SUBSCRIBE
statement:
mz_now() clauses can only be combined using an AND, and
All top-level WHERE or HAVING conditions must be combined using an AND,
even if the mz_now() clause is nested.
SELECT * FROM orders WHERE status = 'Shipped' OR order_date + interval '1' days <= mz_now() ; ✅ Valid
Ad-hoc queries do not have the same restrictions.
| |CREATE MATERIALIZED VIEW forecast_completed_orders AS SELECT * FROM orders WHERE order_date + interval '3' days > mz_now() AND order_date + interval '1' days < mz_now() ; CREATE MATERIALIZED VIEW forecast_completed_orders AS SELECT * FROM orders WHERE (status = 'Complete' OR status = 'Shipped') AND order_date + interval '1' days <= mz_now() ; CREATE MATERIALIZED VIEW forecast_completed_orders AS SELECT * FROM orders WHERE status = 'Shipped' OR order_date + interval '1' days <= mz_now() ; ❌ Invalid
In materialized view definitions, mz_now() clause can only be combined using an AND.
CREATE MATERIALIZED VIEW forecast_completed_orders AS SELECT * FROM orders WHERE status = 'Complete' OR (status = 'Shipped' AND order_date + interval '1' days <= mz_now()) ❌ Invalid
In materialized view definitions with mz_now() clauses, top-level conditions must be combined using an AND.
CREATE VIEW forecast_completed_orders AS SELECT * FROM orders WHERE status = 'Complete' OR (status = 'Shipped' AND order_date + interval '1' days <= mz_now()) ; CREATE INDEX idx_forecast_completed_orders ON forecast_completed_orders (order_date); -- Unsupported because of the `mz_now()` clause ❌ Invalid
To index a view whose definitions includes mz_now() clauses, top-level conditions must be combined using an AND in the view definition.
Rewrite as UNION ALL with possible duplicates
CREATE MATERIALIZED VIEW forecast_completed_orders_duplicates_possible AS SELECT item, quantity, status from orders WHERE status = 'Shipped' UNION ALL SELECT item, quantity, status from orders WHERE order_date + interval '30' minutes >= mz_now() ; Rewrite as UNION ALL that avoids duplicates across queries
CREATE MATERIALIZED VIEW forecast_completed_orders_deduplicated_union_all AS SELECT item, quantity, status from orders WHERE status = 'Shipped' UNION ALL SELECT item, quantity, status from orders WHERE order_date + interval '30' minutes >= mz_now() AND status != 'Shipped' -- Deduplicate by excluding those with status 'Shipped' ; Rewrite as UNION to deduplicate any and all duplicated results
CREATE MATERIALIZED VIEW forecast_completed_orders_deduplicated_results AS SELECT item, quantity, status from orders WHERE status = 'Shipped' UNION SELECT item, quantity, status from orders WHERE order_date + interval '30' minutes >= mz_now() ; -- Unsupported CREATE MATERIALIZED VIEW forecast_completed_orders_unsupported AS SELECT item, quantity, status from orders WHERE status = 'Shipped' OR order_date + interval '30' minutes >= mz_now(); | Use a subquery to [SELECT DISTINCT](/sql/select/#select-distinct) on the grouping key (e.g., `fieldA`), and perform a [LATERAL](/sql/select/join/#lateral-subqueries) join (by the grouping key `fieldA`) with another subquery that specifies the ordering (e.g., `fieldZ [ASC|DESC]`) and the limit K. ```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 ... ; ``` | |
|
```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 -- K is a number >= 1
ORDER BY fieldA, fieldZ ...;
```
|
| ```mzsql SELECT DISTINCT ON(fieldA) fieldA, fieldB, ... FROM tableA ORDER BY fieldA, fieldZ ... ; ``` | |
|
```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 = 1
ORDER BY fieldA, fieldZ ...;
```
|
| ```mzsql SELECT order_id, item, subtotal FROM (SELECT DISTINCT order_id FROM orders_view) grp, LATERAL (SELECT item, subtotal FROM orders_view WHERE order_id = grp.order_id ORDER BY subtotal DESC LIMIT 3) ORDER BY order_id, subtotal DESC; ``` | |
|
```nofmt
-- Anti-pattern --
SELECT order_id, item, subtotal
FROM (
SELECT order_id, item, subtotal,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY subtotal DESC) as rn
FROM orders_view)
WHERE rn <= 3
ORDER BY order_id, subtotal DESC;
```
|
| ```mzsql SELECT DISTINCT ON(order_id) order_id, item, subtotal FROM orders_view ORDER BY order_id, subtotal DESC; ``` | |
|
```nofmt
-- Anti-pattern --
SELECT order_id, item, subtotal
FROM (
SELECT order_id, item, subtotal,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY subtotal DESC) as rn
FROM orders_view)
WHERE rn = 1
ORDER BY order_id, subtotal DESC;
```
|