通过仅重用一次内部查询来优化查询

Question

请考虑以下查询

SELECT
FlightDate,
FlightNum,
Origin,
Dest,
AirlineID
FROM
ontime1
WHERE 
FlightDate >= (SELECT MIN(FlightDate) FROM ontime2 WHERE Origin='JFK') AND
FlightDate <= (SELECT MAX(FlightDate) FROM ontime2 WHERE Origin='JFK') AND
AirlineID IN (SELECT AirlineID FROM ontime2 WHERE Origin='JFK')
LIMIT 10;

准点1 -> 根据航班日期
编制索引 准时2 -> 根据出发地、目的地编制索引

此外，由于表数据太大
，因此连接表也不是最佳选择

如何在不一次又一次重复相同查询的情况下重用内部查询？

Answer 1

考虑使用 WITH 子句：

WITH (
SELECT (MIN(FlightDate), MAX(FlightDate), groupUniqArray(AirlineID)) /* tuple with required aggregates */
FROM ontime2
WHERE Origin='JFK'
) AS cte
SELECT
FlightDate,
FlightNum,
Origin,
Dest,
AirlineID
FROM
ontime1
WHERE 
FlightDate >= cte.1 AND
FlightDate <= cte.2 AND
has(cte.3, AirlineID)
LIMIT 10;