如何在Snowflake中合并Output A、B、C并转换为指定结果格式？

输入结果集 #

Output A #

+------+-------+--------+
| year | group | amount |
+------+-------+--------+
| 2018 | a     | 5      |
+------+-------+--------+
| 2019 | a     | 6      |
+------+-------+--------+
| 2021 | a     | 1239   |
+------+-------+--------+

Output B #

+------+-------+--------+
| year | group | amount |
+------+-------+--------+
| 2021 | b     | 12     |
+------+-------+--------+
| 2022 | b     | 34     |
+------+-------+--------+
| 2023 | b     | 5691   |
+------+-------+--------+

Output C #

+------+-------+--------+
| year | group | amount |
+------+-------+--------+
| 2018 | total | 12     |
+------+-------+--------+
| 2019 | total | 34     |
+------+-------+--------+
| 2020 | total | 5691   |
+------+-------+--------+
| 2021 | total | 12341  |
+------+-------+--------+
| 2022 | total | 96812  |
+------+-------+--------+
| 2023 | total | 100123 |
+------+-------+--------+ 

目标格式 #

+------+--------+----------+----------+
| year | total_ | a_amount | b_amount |
|      | amount |          |          |
+------+--------+----------+----------+
| 2018 | 12     | 5        | 0        |
+------+--------+----------+----------+
| 2019 | 34     | 6        | 0        |
+------+--------+----------+----------+
| 2020 | 5691   | 0        | 0        |
+------+--------+----------+----------+
| 2021 | 12341  | 1239     | 12       |
+------+--------+----------+----------+
| 2022 | 96812  | 0        | 34       |
+------+--------+----------+----------+
| 2023 | 100123 | 0        | 5691     |
+------+--------+----------+----------+

实现方案（Snowflake SQL） #

可以通过公共表表达式（CTE）结合左连接实现，用COALESCE将空值替换为0，确保所有年份都能匹配到数据：

WITH output_a AS (
    SELECT year, amount AS a_amount
    FROM (
        VALUES
            (2018, 'a', 5),
            (2019, 'a', 6),
            (2021, 'a', 1239)
    ) AS t(year, group_col, amount)
),
output_b AS (
    SELECT year, amount AS b_amount
    FROM (
        VALUES
            (2021, 'b', 12),
            (2022, 'b', 34),
            (2023, 'b', 5691)
    ) AS t(year, group_col, amount)
),
output_c AS (
    SELECT year, amount AS total_amount
    FROM (
        VALUES
            (2018, 'total', 12),
            (2019, 'total', 34),
            (2020, 'total', 5691),
            (2021, 'total', 12341),
            (2022, 'total', 96812),
            (2023, 'total', 100123)
    ) AS t(year, group_col, amount)
)
SELECT 
    c.year,
    c.total_amount,
    COALESCE(a.a_amount, 0) AS a_amount,
    COALESCE(b.b_amount, 0) AS b_amount
FROM output_c c
LEFT JOIN output_a a ON c.year = a.year
LEFT JOIN output_b b ON c.year = b.year
ORDER BY c.year;

说明 #

1. 用CTE分别定义三个输入数据集，如果这三个结果集是实际表的查询结果，直接替换CTE里的VALUES部分为对应的查询语句即可
2. 以output_c作为主表，因为它包含了所有需要展示的年份
3. 通过左连接关联output_a和output_b，确保即使某年份没有对应分组数据也能保留该行
4. 使用COALESCE将左连接产生的空值替换为0，符合目标格式要求
5. 最后按年份排序，保证结果顺序正确

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