doc/user/layouts/shortcodes/idiomatic-sql/general-example-table.html
| Idiomatic Materialize SQL | |
|---|---|
ANY() Equi-join condition | ***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; ***If duplicates 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 ; |
mz_now() cannot be used with date/time operators | mzsql SELECT * from orders WHERE mz_now() \> order_date + INTERVAL '5min' ; |
mz_now() cannot be used with ORs in materialized/indexed view definitions and SUBSCRIBE statements | **Rewrite as UNION ALL with possible duplicates** mzsql 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** mzsql 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** mzsql 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() ; |