SQL Подзапросы и общие табличные выражения

 

SELECT *
FROM (SELECT m.rating,
             AVG(m.rental_rate) AS average_rental
        FROM movie AS m
      GROUP BY m.rating
      ORDER BY average_rental DESC
      LIMIT 5) AS best_rating;

Подзапросу во FROM нужно всегда назначать псевдоним, иначе произойдёт ошибка. Теперь можно провести вычисления по подзапросу во FROM:

SELECT AVG(best_rating.average_rental)
FROM (SELECT m.rating,
             AVG(m.rental_rate) AS average_rental
        FROM movie AS m
      GROUP BY m.rating
      ORDER BY average_rental DESC
      LIMIT 5) AS best_rating;

SELECT *
FROM client
WHERE customer_id IN (SELECT customer_id
                      FROM invoice
                      GROUP BY customer_id
                      ORDER BY SUM(total) DESC
                      LIMIT 10);

SELECT AVG(rental_rate)
FROM movie AS m
WHERE film_id IN (SELECT m.film_id
                  FROM movie AS m LEFT JOIN film_actor AS fa ON m.film_id = fa.film_id
                  GROUP BY m.film_id
                  HAVING COUNT(actor_id) > 12)
 AND length <= (SELECT AVG(length) 
                FROM movie AS m);

C таблицами, сформированными с помощью подзапросов, можно работать точно так же, как и с таблицами из базы данных. Часто таблицы объединяют уже после того, как получили срез и сгруппировали данные. Причина проста: объединение больших таблиц занимает много времени. Таблицу лучше предварительно обработать, а уже потом объединять данные. Использовать подзапросы — хорошее решение, но зачастую у них очень громоздкий вид. Что будет, если в одном запросе указать несколько подзапросов: пять, шесть и более? Решить проблему могут общие табличные выражения, или CTE (от англ. common table expressions).

WITH
-- назначение псевдонимов и формирование подзапросов
псевдоним_1 AS (подзапрос_1),
псевдоним_2 AS (подзапрос_2),
псевдоним_3 AS (подзапрос_3),
....
псевдоним_n AS (подзапрос_n)

SELECT -- основной запрос
-- внутри основного запроса работают с псевдонимами, которые назначили в WITH
FROM псевдоним_1 INNER JOIN псевдоним_2 ... 
...
...

К общим табличным выражениям нельзя обращаться после оператора WHERE

WITH 
-- первый подзапрос с псевдонимом i
i AS (SELECT billing_country AS country,
             COUNT(total) AS total_invoice
      FROM invoice
      GROUP BY billing_country
      ORDER BY total_invoice DESC
      LIMIT 5), -- подзапросы разделяют запятыми
-- второй подзапрос с псевдонимом c
 c AS (SELECT country AS country,
              COUNT(customer_id) AS total_clients
              FROM client
              GROUP BY country)

-- основной запрос, в котором указаны псевдонимы для подзапросов
SELECT i.country,
       i.total_invoice,
       c.total_clients
FROM i LEFT OUTER JOIN c ON i.country=c.country
ORDER BY i.total_invoice DESC;

WITH
top40 AS (SELECT *       
         FROM movie
         WHERE rental_rate>2
         ORDER BY length DESC
         LIMIT 40)

-- general query
SELECT top40.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 top40
GROUP BY top40.rating
ORDER BY avg_length ASC;

WITH
const_m AS (SELECT *
            FROM generate_series(1, 12) month),
t AS (SELECT 
            EXTRACT(YEAR FROM CAST(invoice_date AS timestamp)) AS year,
            EXTRACT(MONTH FROM CAST(invoice_date AS timestamp)) AS month,
            COUNT(*)
        FROM invoice
        GROUP BY year, month),
t11 AS (SELECT month, count as year_2011 FROM t WHERE year=2011),
t12 AS (SELECT month, count as year_2012 FROM t WHERE year=2012),
t13 AS (SELECT month, count as year_2013 FROM t WHERE year=2013)

SELECT const_m.month as invoice_month, 
       t11.year_2011, t12.year_2012, t13.year_2013
FROM const_m
LEFT JOIN t11 ON t11.month=const_m.month
LEFT JOIN t12 ON t12.month=const_m.month
LEFT JOIN t13 ON t13.month=const_m.month
ORDER BY invoice_month;