doc/user/layouts/shortcodes/idiomatic-sql/window-functions-example-table.html
| Idiomatic Materialize SQL | |
|---|---|
| Top-K over partition | |
| (K >= 1) | 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) -- For Top 3 ORDER BY order_id, subtotal DESC; |
| Top-K over partition | |
| (K = 1) | mzsql 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 ... | 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; |
| Last value over partition | |
| order by... | |
| range between unbounded preceding | |
| and unbounded following | 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; |
| Lag over (order by) whose ordering can be represented by some equality condition. | ***If suppressing the first row since it has no previous row*** mzsql 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*** mzsql 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*** mzsql 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*** mzsql 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; |