Views and materialized views

Reusable queries

The same JOIN-heavy query appears in five places. A view gives it a name you can select from like a table. When it's slow and the data changes rarely, a materialized view stores the result and you refresh it when needed.

CREATE VIEW, CREATE MATERIALIZED VIEW

CREATE VIEW served_lines AS
SELECT o.id AS order_id, c.name AS customer, d.name AS dish, oi.qty, d.price * oi.qty AS line_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN dishes d ON d.id = oi.dish_id
WHERE o.status = 'served';

SELECT customer, sum(line_total) AS spent FROM served_lines GROUP BY customer ORDER BY spent DESC;

CREATE MATERIALIZED VIEW dish_sales AS
SELECT dish, sum(qty) AS sold FROM served_lines GROUP BY dish;

SELECT * FROM dish_sales ORDER BY sold DESC, dish;
REFRESH MATERIALIZED VIEW dish_sales;

Reusable queries

CREATE VIEW, CREATE MATERIALIZED VIEW

The rest of this lesson