Несколько подзапросов в WITH

Посмотрим на решение задания про сотрудников, которые продали меньше, чем среднестатистический сотрудник.

В запросе

WITH employee_result AS (
  SELECT p.employee_id,
         sum (pi.price * pi.count) AS sum_purchases
    FROM purchase p,
         purchase_item pi
   WHERE pi.purchase_id = p.purchase_id
     AND p.employee_id IS NOT NULL
   GROUP BY p.employee_id
)
SELECT er.employee_id,
       e.last_name,
       e.first_name,
       er.sum_purchases
  FROM employee_result er,
       employee e
 WHERE e.employee_id = er.employee_id
   AND er.sum_purchases < (SELECT avg (ern.sum_purchases)
                             FROM employee_result ern)
 ORDER BY er.sum_purchases,
          er.employee_id

мы ссылаемся на таблицу employee_result дважды: во фразе FROM и в подзапросе

(SELECT avg (ern.sum_purchases)
   FROM employee_result ern)

Это очень удобно. Один раз написал и используешь сколько нужно.

Несколько подзапросов

Пойдем дальше в анализе продаж сотрудниками. Добавим в результат сумму продаж по каждому магазину.

WITH employee_result AS (
  SELECT p.employee_id,
         p.store_id,
         sum (pi.price * pi.count) AS sum_purchases
    FROM purchase p,
         purchase_item pi
   WHERE pi.purchase_id = p.purchase_id
     AND p.employee_id IS NOT NULL
   GROUP BY p.employee_id,
            p.store_id
),
store_result AS (
  SELECT er.store_id,
         sum (er.sum_purchases) AS sum_purchases
    FROM employee_result er
   GROUP BY er.store_id
)
SELECT s.name AS store_name,
       e.last_name,
       e.first_name,
       er.sum_purchases AS sum_purchases_employee,
       sr.sum_purchases AS sum_purchases_store
  FROM employee_result er,
       store_result sr,
       employee e,
       store s
 WHERE sr.store_id = er.store_id
   AND e.employee_id = er.employee_id
   AND s.store_id = er.store_id
 ORDER BY s.name,
          er.sum_purchases

После подзапроса employee_result в WITH появился еще один подзапрос

store_result AS (
  SELECT er.store_id,
         sum (er.sum_purchases) AS sum_purchases
    FROM employee_result er
   GROUP BY er.store_id
)

Стоит отметить, что в WITH:

1. Можно писать несколько подзапросов.
2. Использовать выше определенные подзапросы вместо таблиц.

В основном запросе

SELECT s.name AS store_name,
       e.last_name,
       e.first_name,
       er.sum_purchases AS sum_purchases_employee,
       sr.sum_purchases AS sum_purchases_store
  FROM employee_result er,
       store_result sr,
       employee e,
       store s
 WHERE sr.store_id = er.store_id
   AND e.employee_id = er.employee_id
   AND s.store_id = er.store_id
 ORDER BY s.name,
          er.sum_purchases

Используется и employee_result, и store_result.

Разработка запроса

С использованием WITH очень удобно разрабатывать запрос с несколькими подзапросами.

Например, в запросе

WITH employee_result AS (
  SELECT p.employee_id,
         p.store_id,
         sum (pi.price * pi.count) AS sum_purchases
    FROM purchase p,
         purchase_item pi
   WHERE pi.purchase_id = p.purchase_id
     AND p.employee_id IS NOT NULL
   GROUP BY p.employee_id,
            p.store_id
),
store_result AS (
  SELECT er.store_id,
         sum (er.sum_purchases) AS sum_purchases
    FROM employee_result er
   GROUP BY er.store_id
)
SELECT s.name AS store_name,
       e.last_name,
       e.first_name,
       er.sum_purchases AS sum_purchases_employee,
       sr.sum_purchases AS sum_purchases_store
  FROM employee_result er,
       store_result sr,
       employee e,
       store s
 WHERE sr.store_id = er.store_id
   AND e.employee_id = er.employee_id
   AND s.store_id = er.store_id
 ORDER BY s.name,
          er.sum_purchases

посмотрим, что возвращает каждый подзапрос. Для этого комментируем (или удаляем, мы для краткости удалим) основной запрос и оставляем SELECT * из интересующего подзапроса.

Посморим, что в employee_result:

WITH employee_result AS (
  SELECT p.employee_id,
         p.store_id,
         sum (pi.price * pi.count) AS sum_purchases
    FROM purchase p,
         purchase_item pi
   WHERE pi.purchase_id = p.purchase_id
     AND p.employee_id IS NOT NULL
   GROUP BY p.employee_id,
            p.store_id
),
store_result AS (
  SELECT er.store_id,
         sum (er.sum_purchases) AS sum_purchases
    FROM employee_result er
   GROUP BY er.store_id
)
SELECT *
  FROM employee_result

Что в store_result:

WITH employee_result AS (
  SELECT p.employee_id,
         p.store_id,
         sum (pi.price * pi.count) AS sum_purchases
    FROM purchase p,
         purchase_item pi
   WHERE pi.purchase_id = p.purchase_id
     AND p.employee_id IS NOT NULL
   GROUP BY p.employee_id,
            p.store_id
),
store_result AS (
  SELECT er.store_id,
         sum (er.sum_purchases) AS sum_purchases
    FROM employee_result er
   GROUP BY er.store_id
)
SELECT *
  FROM store_result