如何不使用窗口函数计算P50、P90值？SQL查询优化咨询

有如下结构的表：

id    category    value1    value2    value3
1     1           100       324       940
1     1           222       404       1000
1     1           333       304       293
1     2           490       490       400
1     2           140       400       499
1     3           400       400       103
1     3           300       123       124

需要按(id, category)分组计算各value字段的P50和P90值，原查询语句如下（注：原查询中所有PERCENTILE_CONT都误用了value1，已修正为对应字段）：

SELECT DISTINCT
    id
  , category
  , PERCENTILE_CONT(value1, 0.5) OVER (PARTITION BY id, category) AS p50_value1
  , PERCENTILE_CONT(value2, 0.5) OVER (PARTITION BY id, category) AS p50_value2
  , PERCENTILE_CONT(value3, 0.5) OVER (PARTITION BY id, category) AS p50_value3
  , PERCENTILE_CONT(value1, 0.9) OVER (PARTITION BY id, category) AS p90_value1
  , PERCENTILE_CONT(value2, 0.9) OVER (PARTITION BY id, category) AS p90_value2
  , PERCENTILE_CONT(value3, 0.9) OVER (PARTITION BY id, category) AS p90_value3
FROM my_table;

该语句嵌入长查询时触发内存超限错误：

Resources exceeded during query execution. The query could not be executed in the allotted memory. Peak usage: 133% of limit. Top memory consumer: OVER() clauses: 100%

咨询两个问题：

1. 是否可以改用GROUP BY id, category实现需求？
2. 使用窗口别名的写法是否能提升性能，还是仅为代码简化？

SELECT DISTINCT
    id
  , category
  , PERCENTILE_CONT(value1, 0.5) OVER w AS p50_value1
  , PERCENTILE_CONT(value2, 0.5) OVER w AS p50_value2
  , PERCENTILE_CONT(value3, 0.5) OVER w AS p50_value3
  , PERCENTILE_CONT(value1, 0.9) OVER w AS p90_value1
  , PERCENTILE_CONT(value2, 0.9) OVER w AS p90_value2
  , PERCENTILE_CONT(value3, 0.9) OVER w AS p90_value3
FROM my_table
WINDOW w AS (PARTITION BY id, category);

问题1：能用GROUP BY实现需求吗？ #

当然可以，而且这是解决内存超限问题的最佳方案。

原查询用窗口函数+DISTINCT的逻辑，会先给每一行计算窗口百分位值，再去重生成分组结果——数据量一大，中间会产生大量冗余行，直接把内存撑爆。而用GROUP BY直接对分组做聚合计算，从根源上避免了冗余中间行的生成，内存占用会大幅降低。

以BigQuery（从错误信息判断大概率是这个环境）为例，正确的GROUP BY写法如下：

SELECT
    id
  , category
  , PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value1) AS p50_value1
  , PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value2) AS p50_value2
  , PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value3) AS p50_value3
  , PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY value1) AS p90_value1
  , PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY value2) AS p90_value2
  , PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY value3) AS p90_value3
FROM my_table
GROUP BY id, category;

如果数据集特别大，还可以用近似百分位函数APPROX_PERCENTILE_CONT，牺牲极小的精度换更低的内存占用和更快的执行速度：

SELECT
    id
  , category
  , APPROX_PERCENTILE_CONT(value1, 0.5) AS p50_value1
  , APPROX_PERCENTILE_CONT(value2, 0.5) AS p50_value2
  , APPROX_PERCENTILE_CONT(value3, 0.5) AS p50_value3
  , APPROX_PERCENTILE_CONT(value1, 0.9) AS p90_value1
  , APPROX_PERCENTILE_CONT(value2, 0.9) AS p90_value2
  , APPROX_PERCENTILE_CONT(value3, 0.9) AS p90_value3
FROM my_table
GROUP BY id, category;

问题2：窗口别名写法的作用？ #

窗口别名写法主要是简化代码，几乎没有性能提升。

SQL优化器会自动识别重复的窗口定义，不管你写多少次PARTITION BY id, category，都会复用同一个窗口的计算结果。所以两种写法的执行计划、内存占用完全一致，窗口别名只是让代码更整洁，少写重复内容，方便维护，但解决不了原查询的内存问题——因为本质还是窗口函数+DISTINCT的逻辑，依然会生成大量中间行。

内容的提问来源于stack exchange，提问作者Ricardo Francois