只重用一次内部查询的sql查询优化

rqcrx0a6 于 2021-07-15 发布在 ClickHouse

关注(0)|答案(1)|浏览(358)

考虑下面的查询

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;

ontime1->基于flightdate索引
ontime2->基于原点、目标建立索引
此外，由于表数据太大，因此连接表也不是最佳的
如何在不重复同一查询的情况下重用内部查询？

sql clickhouse Database

来源：https://stackoverflow.com/questions/62402102/query-optimization-by-reusing-the-inner-query-only-once

1条答案

按热度按时间

dzjeubhm1#

考虑使用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;

赞(0）回复(0）举报 2021-07-15

我来回答

只重用一次内部查询的sql查询优化

1条答案

相关问题

热门标签

最新问答