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()
;
```
|