Postgres无需持久化子查询与重复计算获取value列min/max/分位数

问题原因 #

你之前的写法中MIN/MAX聚合函数是在GROUP BY value的分组内计算的，每个分组仅对应一行value，所以统计结果和当前行value完全一致，无法得到全局统计值。

可行解决方案 #

以下方案完全满足你提出的「不持久化首次查询结果、不重复编写计算逻辑、无冗余min/max值」的要求：

方案1：CTE封装逻辑，分两次查询（推荐）

适用于支持公用表表达式（CTE）的数据库（MySQL 8.0+、PostgreSQL、SQL Server、Spark SQL等），核心计算逻辑仅写一次，分两次查询分别获取明细和统计结果：

-- 把value计算逻辑统一封装在CTE中，无需持久化，查询结束自动销毁
WITH calculated_values AS (
    SELECT 
        name, 
        SUM(table1.tons_of_data * table2.tons_of_data / 1000) AS value
    FROM table1
    INNER JOIN table2 ON table1.name = table2.name
    WHERE table1.tons_of_data IS NOT NULL
      AND table2.tons_of_data IS NOT NULL
    GROUP BY name, value -- 补充原查询缺失的name分组，避免严格SQL模式下语法报错
)
-- 第一次查询：获取全量name+value明细
SELECT name, value FROM calculated_values;

-- 第二次查询：获取全局统计值，可按需添加分位数计算
SELECT 
    MIN(value) AS min, 
    MAX(value) AS max,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) AS median -- 中位数示例，可修改为其他分位数
FROM calculated_values;

方案2：单次查询返回两类结果，标记区分

如果你的场景要求必须一次查询返回所有结果，可通过UNION ALL加结果类型标记实现，后续按标记过滤即可：

WITH calculated_values AS (
    SELECT 
        name, 
        SUM(table1.tons_of_data * table2.tons_of_data / 1000) AS value
    FROM table1
    INNER JOIN table2 ON table1.name = table2.name
    WHERE table1.tons_of_data IS NOT NULL
      AND table2.tons_of_data IS NOT NULL
    GROUP BY name, value
)
-- 明细数据
SELECT 'detail' AS result_type, name, value FROM calculated_values
UNION ALL
-- 全局最小值
SELECT 'stats' AS result_type, 'global_min' AS name, MIN(value) AS value FROM calculated_values
UNION ALL
-- 全局最大值
SELECT 'stats' AS result_type, 'global_max' AS name, MAX(value) AS value FROM calculated_values
-- 按需添加其他分位数统计行

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