自动调整bucket大小，10个bucket覆盖全范围

Question

下面的查询可以完美地工作：

select
  floor(duration / 100) * 100 as bucket,
  count(*) as count
from flights
group by floor(duration / 100) * 100
order by bucket asc

但是，我尝试使存储桶大小自动调整为值的范围，以便在整个范围内总共有10个存储桶：

select
  floor(duration / (max(duration)/10)) * (max(duration)/10) as bucket,
  count(*) as count
from flights
group by floor(duration / (max(duration)/10)) * (max(duration)/10)
order by bucket asc

-- SYNTAX_ERROR: GROUP BY clause cannot contain aggregations or window functions: ["max"("duration"), "max"("duration")]

这个失败的查询中的想法是使用最大值(假设最小值为0)来派生存储桶大小。有没有办法让它工作，或者完全使用更好的方法？

Answer 1

您需要使用单独的子查询显式计算max(duration)。用(SELECT max(duration) FROM flights)替换max(duration)。

作为一个副作用，flights表将被读取两次。它应该只读两次，而不是更多。可以通过运行EXPLAIN <your query>并在输出中搜索Scan来验证这一点。如果flights使用扫描仪的次数超过两次，则可以进一步改进。实际上，改进后的版本可能更易于阅读(我还通过引用first select item使用了速记GROUP BY和ORDER BY子句)：

SELECT
  floor(duration / (max_duration/10)) * (max_duration/10) as bucket,
  count(*) as count
FROM flights, (SELECT max(duration) max_duration FROM flights)
GROUP BY 1
ORDER BY 1