Изгледи
Накратко: Изгледът (VIEW) е заявка, запомнена под име, която се ползва като таблица, но не пази свои данни.
Проблем
Една и съща сметка навсякъде
Колко струва една поръчка? В orders няма сума. Тя се смята от редовете на поръчката:
Тази сметка трябва на отчета за деня, на справката за клиента, на списъка с неплатени поръчки. Всеки път се пише наново. Ако един ден се добави доставка или отстъпка, трябва да се поправи на десет места. И на едно от тях ще се забрави.
Идеята
CREATE VIEW
CREATE VIEW име AS SELECT … запомня заявка под име. Това е изглед (view). После изгледът се чете като таблица:
Сметката е написана веднъж, в изгледа. Отчетите само казват FROM order_totals.
Идеята
Като таблица
В SELECT изгледът се ползва като всяка таблица: с WHERE, ORDER BY, JOIN. В това поле order_totals вече е направен. Ето новите поръчки, с клиента и стойността:
Заявката е по-кратка и по-ясна: „поръчката, клиентът, стойността“. Как се смята стойността е скрито в изгледа.
Пробвай
Големите поръчки
Изгледът order_totals е готов. Кои поръчки са над 100 €? Покажи номера и стойността.
Решение
SELECT order_id, total FROM order_totals WHERE total > 100;SELECT order_id, total FROM order_totals WHERE total > 100;. Три поръчки: 13 (102,10 €), 23 (104 €) и 56 (118,20 €).
Виж го
Изгледът не пази данни
Клиентът на поръчка 1 се обажда: иска 5 сирена, а не 4. Пусни заявката и виж и двата резултата:
Преди е 31,60 €, после е 39,50 €. Никой не е пипал изгледа. Изгледът не пази стойностите, а само заявката. Всяко четене я пуска наново върху таблиците, затова изгледът не може да остарее.
Затова в изглед и не може да се пише:
Данните се променят в таблиците, от които изгледът чете.
По-точно, за любопитните
PostgreSQL има и CREATE MATERIALIZED VIEW: изглед, който пази резултата като таблица. Чете се бързо, но остарява и се опреснява с REFRESH MATERIALIZED VIEW. Ползва се за тежки отчети, които не трябва да са точни до секундата. SQLite няма такъв изглед.
Пробвай
Продажбите по продукт
Направи изглед product_sales: за всеки продукт (product_id) колко броя са поръчани (sold), без редовете от отказаните поръчки.
Решение
CREATE VIEW product_sales AS
SELECT oi.product_id, SUM(oi.quantity) AS sold
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE o.status <> 'cancelled'
GROUP BY oi.product_id;CREATE VIEW product_sales AS SELECT oi.product_id, SUM(oi.quantity) AS sold FROM order_items oi JOIN orders o ON o.id = oi.order_id WHERE o.status <> 'cancelled' GROUP BY oi.product_id;. Без JOIN към orders състоянието на поръчката не се вижда.
Идеята
DROP VIEW
Изгледът се маха с DROP VIEW. DROP TABLE не става:
Таблиците, от които изгледът чете, не се пипат. Махаш само запомнената заявка.
По-точно, за любопитните
За да смениш изглед в SQLite, го махаш с DROP VIEW и го правиш наново с CREATE VIEW. PostgreSQL и MySQL имат CREATE OR REPLACE VIEW, а SQLite не:
Има и обратната връзка: колона, която изглед ползва, не може да се махне с DROP COLUMN (за това стана дума в предния урок).
Упражнение
Два отчета
1. Собственичката иска да изпрати благодарствени картички на тримата клиенти, които са поръчали за най-много пари.
С изгледа order_totals покажи името на клиента и колко е поръчал общо (spent), без отказаните поръчки. Само първите трима, от най-много към по-малко.
Решение
SELECT c.name, ROUND(SUM(t.total), 2) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_totals t ON t.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY c.id, c.name
ORDER BY spent DESC
LIMIT 3;JOIN на customers, orders и order_totals, WHERE o.status <> 'cancelled', GROUP BY клиента, ORDER BY spent DESC LIMIT 3. Мария Иванова (375,70 €), Мартин Костов (216,80 €) и Николай Стоянов (143,50 €).
2. Складът сутрин гледа кои поръчки чакат.
Направи изглед open_orders с номера на поръчката, името на клиента и стойността ѝ, само за новите и платените поръчки (new, paid). Ползвай order_totals.
Решение
CREATE VIEW open_orders AS
SELECT o.id, c.name, t.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_totals t ON t.order_id = o.id
WHERE o.status IN ('new', 'paid');CREATE VIEW open_orders AS SELECT o.id, c.name, t.total FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_totals t ON t.order_id = o.id WHERE o.status IN ('new', 'paid');. 23 поръчки. Изглед може да чете от друг изглед.
Тест