Коррелированный поглощаемый подзапрос
Рассмотрим запрос Q14, являющийся упрощенным вариантом запроса 2 тестового набора TPC-H. Во внешнем запросе имеются дополнительная таблица PARTS и фильтрующий ее предикат. Подзапрос коррелирует с таблицей PARTS и поглощается внешним запросом.
Q14
SELECT s_name, n_name, p_partkey FROM parts P, supplier, partsupp, nation, region WHERE p_partkey = ps_partkey AND s_suppkey = ps_suppkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND p_size=36 AND r_name = 'ASIA' AND ps_supplycost IN (SELECT MIN (ps_supplycost) FROM partsupp, supplier, nation, region WHERE P.p_partkey = ps_partkey AND s_suppkey = ps_suppkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'ASIA');
С использованием метода удаления подзапросов запрос Q14 преобразуется в Q15:
Q15
SELECT s_name, n_name, p_partkey FROM (SELECT ps_supplycost, MIN (ps_supplycost) OVER (PARTITION BY ps_partkey) AS min_ps, s_name, n_name, p_partkey FROM parts, supplier, partsupp, nation, region WHERE p_partkey = ps_partkey AND s_suppkey = ps_suppkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND p_size=36 AND r_name = 'ASIA') V WHERE V.ps_supplycost = V.min_ps;
Дубликаты строк, если они появляются в Q15 после соединения таблиц PARTSUPP и PARTS, на результат не влияют, так как агрегатной функцией является MIN. Если бы агрегатной функцией была бы не MIN/MAX, или если соединение с дополнительной таблицей (в нашем случае PARTS) не являлось бы соединением без потерь, то вычисление оконной функции должно было бы производиться внутри представления, которое затем уже соединялось бы с дополнительной таблицей. Именно так обстоит дело с запросом 17 из TPC-H, для которого удаление подзапроса приводит к Q16.
Q16
SELECT SUM(V.avg_extprice)/7 AS avg_yearly FROM parts, (SELECT (CASE WHEN l_quantity < (1.2 * AVG (l_quantity) OVER (PARTITION BY (l_pertkey)) THEN l_extprice ELSE NULL END) avg_extprice, l_partkey FROM lineitem) V WHERE p_partkey = V.l_partkey AND V.avg_extprice IS NOT NULL AND p_brand = 'Brand#23' AND p_container = 'Med Box';
