Home  /  Overview  /  Idiomatic Materialize SQL  /  Appendix

Idiomatic Materialize SQL chart

Materialize follows the SQL standard (SQL-92) implementation and strives for compatibility with the PostgreSQL dialect. However, for some use cases, Materialize provides its own idiomatic query patterns that can provide better performance.

General

Query Patterns

Idiomatic Materialize SQL Pattern
ANY() Equi-join condition

If no duplicates in the unnested field

WITH my_expanded_values AS
(SELECT UNNEST(array|list|map) AS fieldZ FROM tableB)
SELECT a.fieldA, ...
FROM tableA a
JOIN my_expanded_values t ON a.fieldZ = t.fieldZ
;

If duplicates exist in the unnested field

WITH my_expanded_values AS
(SELECT DISTINCT UNNEST(array|list|map) AS fieldZ FROM tableB)
SELECT a.fieldA, ...
FROM tableA a
JOIN my_expanded_values t ON a.fieldZ = t.fieldZ
;

Examples

Idiomatic Materialize SQL
ANY() Equi-join condition

If no duplicates in the unnested field

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

If duplicates exist in the unnested field

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

Window Functions

Materialize and window functions

For window functions, when an input record in a partition (as determined by the PARTITION BY clause of your window function) is added/removed/changed, Materialize recomputes the results for the entire window partition. This means that when a new batch of input data arrives (that is, every second), the amount of computation performed is proportional to the total size of the touched partitions.

For example, assume that in a given second, 20 input records change, and these records belong to 10 different partitions, where the average size of each partition is 100. Then, amount of work to perform is proportional to computing the window function results for 10*100=1000 rows.

As a rule of thumb, if the total size of all touched window partitions is at most 1000000 rows per second, then the system should be able to keep up with the input data as it arrives. However, if your use case has higher performance requirements, consider rewriting your query to not use window functions. If your query cannot be rewritten without the window functions and the performance of window functions is insufficient for your use case, please contact our team.

Query Patterns

Idiomatic Materialize SQL Pattern
Top-K over partition
(K >= 1)		 
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)		 
SELECT DISTINCT ON(fieldA) fieldA, fieldB, ...
FROM tableA
ORDER BY fieldA, fieldZ ...  -- Top-K where K is 1;
First value over partition
order by ... 
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 following		 
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

SELECT t1.fieldA, t2.fieldB
FROM tableA t1, tableA t2
WHERE t1.fieldA = t2.fieldA +  ...
ORDER BY fieldA;

To include the first row

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

SELECT t1.fieldA, t2.fieldB
FROM tableA t1, tableA t2
WHERE t1.fieldA = t2.fieldA - ...
ORDER BY fieldA;

To include the last row

SELECT t1.fieldA, t2.fieldB
FROM tableA t1
LEFT JOIN tableA t2
ON t1.fieldA = t2.fieldA -  ...
ORDER BY fieldA;

Examples

Idiomatic Materialize SQL
Top-K over partition
(K >= 1)		 
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) -- For Top 3
ORDER BY order_id, subtotal DESC;
Top-K over partition
(K = 1)		 
SELECT DISTINCT ON(order_id) order_id, item, subtotal
FROM orders_view
ORDER BY order_id, subtotal DESC;  -- For Top 1
First value over partition
order by ...		 
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;
Last value over partition
order by...
range between unbounded preceding
and unbounded following		 
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;

Lag over (order by) whose ordering can be represented by some equality condition.

If suppressing the first row since it has no previous row

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;

To include the first row

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;

Lead over (order by) whose ordering can be represented by some equality condition.

To suppress the last row since it has no next row

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;

To include the last row

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;

See also

Back to top ↑