如何在ClickHouse SQL中新增行计算总聚合值减去其他指定行的数值

可以通过公共表表达式（CTE）+ UNION ALL拼接的方式实现，示例代码如下：

WITH source_sum AS (
    SELECT 
        CASE 
            WHEN source = 'all' THEN 'all'
            WHEN source = 'source1' THEN 'source1'
            WHEN source = 'source2' THEN 'source2'
            WHEN source = 'source3' THEN 'source3'
        END AS source,
        SUM(value) AS sum
    FROM `table`
    GROUP BY source
)
-- 输出原有查询结果
SELECT source, sum FROM source_sum
UNION ALL
-- 拼接新增的差值计算行
SELECT 
    'all-source1-source2-source3' AS source,
    (SELECT sum FROM source_sum WHERE source = 'all') 
    - (SELECT sum FROM source_sum WHERE source = 'source1')
    - (SELECT sum FROM source_sum WHERE source = 'source2')
    - (SELECT sum FROM source_sum WHERE source = 'source3') AS sum

如果你使用的数据库支持条件聚合，也可以用更简洁的单步写法，不需要嵌套子查询：

SELECT 
    CASE 
        WHEN source = 'all' THEN 'all'
        WHEN source = 'source1' THEN 'source1'
        WHEN source = 'source2' THEN 'source2'
        WHEN source = 'source3' THEN 'source3'
    END AS source,
    SUM(value) AS sum
FROM `table`
GROUP BY source

UNION ALL

SELECT 
    'all-source1-source2-source3' AS source,
    SUM(CASE WHEN source = 'all' THEN value ELSE -value END) AS sum
FROM `table`
WHERE source IN ('all','source1','source2','source3')

如果需要调整结果的排序规则，可在外层增加ORDER BY子句自定义排序逻辑。

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