Рекурсивные подзапросы

Подзапрос во фразе FROM

В первой части по подзапросам мы упоминали, что подзапросы можно использовать во фразе FROM. Но так ни разу этим не воспользовались.

Когда это может быть полезно?

Пример из жизни. Покупать товар по самой дешевой цене всегда приятно. Однако, не всегда удается купить именно по самой дешевой цене. То магазин закрыт, то добираться неудобно...

Допустим, что нас устроит покупка по цене от минимальной до средней на этот товар по всем магазинам.

Мы уже владеем коррелированными подзапросами. Воспользуемся ими для решения задачи:

SELECT *
  FROM product_price pp
 WHERE pp.price BETWEEN
           (SELECT min (ppi.price)
              FROM product_price ppi
             WHERE ppi.product_id = pp.product_id)
       AND (SELECT avg (ppi.price)
              FROM product_price ppi
             WHERE ppi.product_id = pp.product_id)
 ORDER BY pp.product_id,
          pp.price
#product_idstore_idprice
1130010500.00
2250026100.00
3350022000.00
4380022100.00
5440020000.00
............

Не ошиблись ли мы? Не знаю, давай проверим на товаре с product_id = 3:

SELECT pp.product_id,
       pp.price
  FROM product_price pp
 WHERE pp.product_id = 3
 ORDER BY pp.price
#product_idprice
1322000.00
2322100.00
3322900.00

Вроде бы не ошиблись. А давай в исходном запросе выведем минимальную и среднюю стоимость. Мы же умеем писать коррелированные подзапросы:

SELECT pp.product_id,
       pp.store_id,
       pp.price,
       (SELECT round (min (ppi.price), 2)
          FROM product_price ppi
         WHERE ppi.product_id = pp.product_id
       ) AS price_min,
       (SELECT round (avg (ppi.price), 2)
          FROM product_price ppi
         WHERE ppi.product_id = pp.product_id
       ) AS price_avg
  FROM product_price pp
 WHERE pp.price BETWEEN
           (SELECT min (ppi.price)
              FROM product_price ppi
             WHERE ppi.product_id = pp.product_id)
       AND (SELECT avg (ppi.price)
              FROM product_price ppi
             WHERE ppi.product_id = pp.product_id)
 ORDER BY pp.product_id,
          pp.price
          
#product_idstore_idpriceprice_minprice_avg
1130010500.0010500.0011666.67
2250026100.0026100.0026800.00
3350022000.0022000.0022333.33
4380022100.0022000.0022333.33
5440020000.0020000.0021125.00
..................

Работает, но читать такое сложновато.

Есть решение лучше. Давай сначала по каждому товару найдем минимальную и среднюю стоимость, а затем найдем товары с ценой в диапазоне от минимальной до средней.

  1. Определим минимальную и среднюю стоимость по товару:
SELECT pp.product_id,
       min (pp.price) AS price_min,
       avg (pp.price) AS price_avg
  FROM product_price pp
 GROUP BY pp.product_id
#product_idprice_minprice_avg
1110500.0011666.6666666666666667
2226100.0026800.000000000000
3322000.0022333.333333333333
4420000.0021125.000000000000
............
  1. Зная минимальную и среднюю стоимость по товару, возьмем товары с нужными ценами.

Можно использовать подзапросы во фразе FROM. В таком случае к результатам подзапроса можно обращаться как к обычной таблице:

SELECT *
  FROM (SELECT pp.product_id,
               min (pp.price) AS price_min,
               avg (pp.price) AS price_avg
          FROM product_price pp
         GROUP BY pp.product_id
       ) as ppma
#product_idprice_minprice_avg
1110500.0011666.6666666666666667
2226100.0026800.000000000000
3322000.0022333.333333333333
4420000.0021125.000000000000
............

Правда есть одно условие: результату подзапроса нужно назначить псевдоним. Если не задать псевдоним, то возникнет ошибка:

SELECT *
  FROM (SELECT pp.product_id,
               min (pp.price) AS price_min,
               avg (pp.price) AS price_avg
          FROM product_price pp
         GROUP BY pp.product_id
       ) 
error: subquery in FROM must have an alias

Воспользуемся новыми знаниями для поиска товаров в магазинах с ценой от минимальной до средней:

SELECT pp.product_id,
       pp.store_id,
       pp.price,
       round (ppma.price_min, 2) AS price_min,
       round (ppma.price_avg, 2) AS price_avg
  FROM (SELECT pp.product_id,
               min (pp.price) AS price_min,
               avg (pp.price) AS price_avg
          FROM product_price pp
         GROUP BY pp.product_id
       ) ppma,
       product_price pp
 WHERE pp.product_id = ppma.product_id
   AND pp.price BETWEEN ppma.price_min AND ppma.price_avg
 ORDER BY pp.product_id,
          pp.price
#product_idstore_idpriceprice_minprice_avg
1130010500.0010500.0011666.67
2250026100.0026100.0026800.00
3350022000.0022000.0022333.33
4380022100.0022000.0022333.33
5440020000.0020000.0021125.00
..................
ПредыдущаяСледующая