Создание временных таблиц напоминает назначение переменных: псевдониму назначается таблица, сформированная подзапросом. Затем пишут общий запрос, в котором работают с временной таблицей.
WITH
-- назначение псевдонимов и формирование подзапросов
псевдоним_1 AS (подзапрос_1),
псевдоним_2 AS (подзапрос_2),
псевдоним_3 AS (подзапрос_3),
....
псевдоним_n AS (подзапрос_n)
SELECT -- основной запрос
-- внутри основного запроса работают с псевдонимами, которые назначили в WITH
FROM псевдоним_1 INNER JOIN псевдоним_2 ...
...
...
После ключевого слова WITH указывают подзапросы. Порядок здесь другой, чем в обычном запросе: сначала пишут псевдоним, затем ключевое слово AS, а затем в скобках указывают подзапрос.
Все конструкции перечисляют через запятую. После последнего подзапроса запятую не ставят — это означает начало основного запроса.
Задача 1
Перепишите один из своих прошлых запросов с использованием оператора WITH.
Выведите топ-40 самых длинных фильмов, аренда которых составляет больше 2 долларов. Проанализируйте данные о возрастных рейтингах отобранных фильмов. Выгрузите в итоговую таблицу следующие поля:
- возрастной рейтинг (поле rating);
- минимальное и максимальное значения длительности (поле length), назовите поля min_length и max_length соответственно;
- среднее значение длительности (поле length), назовите поле avg_length;
- минимум, максимум и среднее для цены просмотра (поле rental_rate), назовите поля min_rental_rate, max_rental_rate, avg_rental_rate соответственно.
Отсортируйте среднюю длительность фильма по возрастанию.
WITH
top40 AS (
SELECT film_id
FROM movie
WHERE rental_rate >2
ORDER BY length DESC
LIMIT 40
)
SELECT rating,
MIN(length) AS min_length,
MAX(length) AS max_length,
AVG(length) AS avg_length,
MIN(rental_rate) AS min_rental_rate,
MAX(rental_rate) AS max_rental_rate,
AVG(rental_rate) AS avg_rental_rate
FROM
movie INNER JOIN top40 ON
movie.film_id = top40.film_id
GROUP BY rating
ORDER BY AVG(length)
Задача 2
Перепишите один из своих прошлых запросов, используя оператор WITH.
Составьте сводную таблицу. Посчитайте заказы, оформленные за каждый месяц в течение нескольких лет: с 2011 по 2013 год. Итоговая таблица должна включать четыре поля: invoice_month, year_2011, year_2012, year_2013. Поле month должно хранить месяц в виде числа от 1 до 12.
Если в какой-либо месяц заказы не оформляли, номер такого месяца всё равно должен попасть в таблицу.
SELECT iM.invoice_month, i2011.year_2011, i2012.year_2012, i2013.year_2013
FROM
(SELECT EXTRACT(MONTH FROM CAST(invoice_date AS date)) AS invoice_month
FROM invoice
GROUP BY invoice_month
ORDER BY invoice_month) AS iM
LEFT JOIN
(SELECT
EXTRACT(MONTH FROM CAST(invoice_date AS date)) AS invoice_month, COUNT(invoice_id) AS year_2011
FROM invoice
WHERE EXTRACT(YEAR FROM CAST(invoice_date AS date)) = 2011
GROUP BY invoice_month
) AS i2011
ON iM.invoice_month = i2011.invoice_month
LEFT JOIN
(SELECT
EXTRACT(MONTH FROM CAST(invoice_date AS date)) AS invoice_month, COUNT(invoice_id) AS year_2012
FROM invoice
WHERE EXTRACT(YEAR FROM CAST(invoice_date AS date)) = 2012
GROUP BY invoice_month
) AS i2012
ON iM.invoice_month = i2012.invoice_month
LEFT JOIN
(SELECT
EXTRACT(MONTH FROM CAST(invoice_date AS date)) AS invoice_month, COUNT(invoice_id) AS year_2013
FROM invoice
WHERE EXTRACT(YEAR FROM CAST(invoice_date AS date)) = 2013
GROUP BY invoice_month
) AS i2013
ON iM.invoice_month = i2013.invoice_month
Задача 3
Проанализируйте данные из таблицы invoice за 2012 и 2013 годы. В итоговую таблицу должны войти поля:
- month — номер месяца;
- sum_total_2012 — выручка за этот месяц в 2012 году;
- sum_total_2013 — выручка за этот месяц в 2013 году;
- perc — процент, который отображает, насколько изменилась месячная выручка в 2013 году по сравнению с 2012 годом.
Округлите значение в поле perc до ближайшего целого числа. Отсортируйте таблицу по значению в поле month от меньшего к большему.
WITH a AS
(SELECT EXTRACT(MONTH
FROM CAST(invoice_date AS DATE)) AS MONTH,
SUM(total) AS sum_total_2012
FROM invoice
WHERE EXTRACT(YEAR
FROM CAST(invoice_date AS DATE)) = 2012
GROUP BY MONTH),
b AS
(SELECT EXTRACT(MONTH
FROM CAST(invoice_date AS DATE)) AS MONTH,
SUM(total) AS sum_total_2013
FROM invoice
WHERE EXTRACT(YEAR
FROM CAST(invoice_date AS DATE)) = 2013
GROUP BY MONTH)
SELECT a.month,
a.sum_total_2012,
b.sum_total_2013,
ROUND((b.sum_total_2013 - a.sum_total_2012) / a.sum_total_2012 * 100) AS perc
FROM a
LEFT JOIN b ON a.month = b.month
ORDER BY MONTH;