能否用AWS Athena基于列数据生成复杂行数据？附场景需求

我有一张结构如下的表：

+----+------+-------------------+-------+
| id | code | size              | count |
+----+------+-------------------+-------+
| 1  | CA   | TwentyToFortyNine | 65    |
+----+------+-------------------+-------+
| 1  | CA   | FiveToNinteen     | 385   |
+----+------+-------------------+-------+
| 1  | CA   | OneToFour         | 492   |
+----+------+-------------------+-------+
| 1  | DK   | OneToFour         | 38    |
+----+------+-------------------+-------+
| 1  | DK   | TwentyToFortyNine | 1     |
+----+------+-------------------+-------+
| 2  | CA   | FiveToNinteen     | 389   |
+----+------+-------------------+-------+
| 2  | CA   | OneToFour         | 494   |
+----+------+-------------------+-------+
| 2  | DK   | FiveToNinteen     | 10    |
+----+------+-------------------+-------+
| 2  | DK   | OneToFour         | 38    |
+----+------+-------------------+-------+

size列的唯一值固定为：OneToFour、FiveToNinteen和TwentyToFortyNine，但并非所有code条目都包含全部这些值，比如id为2的条目没有size为TwentyToFortyNine的数据。

目标 #

为每个id生成标记为code: TOTAL的新行，展示size列的所有唯一值及其对应的总count（没有数据的size对应count为0）。

示例输出 #

+----+-------+-------------------+-------+
| id | code  | size              | count |
+----+-------+-------------------+-------+
| 1  | TOTAL | OneToFour         | 530   |
+----+-------+-------------------+-------+
| 1  | TOTAL | FiveToNinteen     | 385   |
+----+-------+-------------------+-------+
| 1  | TOTAL | TwentyToFortyNine | 66    |
+----+-------+-------------------+-------+
| 1  | CA    | TwentyToFortyNine | 65    |
+----+-------+-------------------+-------+
| 1  | CA    | FiveToNinteen     | 385   |
+----+-------+-------------------+-------+
| 1  | CA    | OneToFour         | 492   |
+----+-------+-------------------+-------+
| 1  | DK    | OneToFour         | 38    |
+----+-------+-------------------+-------+
| 1  | DK    | TwentyToFortyNine | 1     |
+----+-------+-------------------+-------+
| 2  | TOTAL | OneToFour         | 532   |
+----+-------+-------------------+-------+
| 2  | TOTAL | FiveToNinteen     | 399   |
+----+-------+-------------------+-------+
| 2  | TOTAL | TwentyToFortyNine | 0     |
+----+-------+-------------------+-------+
| 2  | CA    | FiveToNinteen     | 389   |
+----+-------+-------------------+-------+
| 2  | CA    | OneToFour         | 494   |
+----+-------+-------------------+-------+
| 2  | DK    | FiveToNinteen     | 10    |
+----+-------+-------------------+-------+
| 2  | DK    | OneToFour         | 38    |
+----+-------+-------------------+-------+

请问能否通过AWS Athena实现该需求？如果可以，请说明实现方法；是否需要使用Pandas来完成？

完全可以通过AWS Athena实现该需求，不需要使用Pandas。核心思路是先构建每个id与所有size值的全量组合，再关联原表计算总和，最后将结果与原表合并。

具体实现步骤 #

1. 生成固定size基准列表：直接用VALUES子句生成已知的三个size值集合。
2. 构建id与size的全量组合：将原表中的唯一id列表和基准size列表做笛卡尔积，确保每个id都覆盖所有size值。
3. 计算TOTAL行数据：将全量组合与原表按id和size关联，用COALESCE把无数据的情况转为0，再按id和size求和得到TOTAL行的count。
4. 合并并排序结果：用UNION ALL将TOTAL行和原表数据合并，最后按id、code（让TOTAL排在前面）、size排序。

完整SQL代码 #

WITH all_sizes AS (
    -- 生成所有固定的size值
    SELECT size FROM (VALUES 
        ('OneToFour'), 
        ('FiveToNinteen'), 
        ('TwentyToFortyNine')
    ) AS t(size)
),
id_size_combinations AS (
    -- 生成每个id与所有size的全量组合
    SELECT DISTINCT t.id, s.size
    FROM your_table_name t
    CROSS JOIN all_sizes s
),
total_rows AS (
    -- 计算每个id+size的总count，无数据则为0
    SELECT 
        isc.id,
        'TOTAL' AS code,
        isc.size,
        COALESCE(SUM(t.count), 0) AS count
    FROM id_size_combinations isc
    LEFT JOIN your_table_name t ON isc.id = t.id AND isc.size = t.size
    GROUP BY isc.id, isc.size
)
-- 合并TOTAL行与原表数据，并排序
SELECT * FROM total_rows
UNION ALL
SELECT * FROM your_table_name
ORDER BY id, code DESC, size;

代码说明 #

• 替换your_table_name为你实际的表名。
• CROSS JOIN保证每个id都能匹配到所有size值，避免遗漏。
• COALESCE(SUM(t.count), 0)确保无数据的size对应的count显示为0，符合示例要求。
• ORDER BY id, code DESC让每个id下的TOTAL行排在最前面，和示例输出顺序一致。

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