如何编写Athena查询按分组合并稀疏列存数据的非NULL值

解决方案 #

你要的按指定列分组折叠稀疏NULL值的需求，Athena完全支持，不需要额外用pandas处理，可直接通过SQL实现。

Athena底层基于Presto引擎，MAX/MIN/arbitrary这类聚合函数都会自动忽略NULL值，刚好匹配你“忽略每行NULL列合并取值”的要求，示例查询如下：

SELECT
  foo,
  -- 取分组内bar列的非NULL值，仅有一个非NULL值时会直接返回
  arbitrary(bar) AS bar,
  arbitrary(baz) AS baz
FROM table1
-- 过滤掉所有非分组列全为NULL的无效行，减少扫描计算量
WHERE NOT (bar IS NULL AND x1 IS NULL AND x2 IS NULL AND x3 IS NULL AND baz IS NULL)
GROUP BY foo
-- 仅需要foo=1的结果时添加下方条件
HAVING foo = 1

说明：arbitrary是Presto特有的函数，会返回分组内的任意一个非NULL值，不需要做大小排序，比MAX/MIN性能更高，适合你这种每个分组内某列只有一个非NULL值的场景。如果业务需要取最大/最小值，替换成MAX/MIN即可。

拓展建议 #

1. 如果同一个分组下某列存在多个非NULL值，可根据业务需求调整聚合逻辑：比如用array_agg(列名)把所有非NULL值拼成数组，或搭配max_by函数结合时间列取最新写入的非NULL值。
2. 如果需要把折叠后的规整数据固化下来，可使用CTAS（CREATE TABLE 新表名 AS SELECT ...）语句直接生成新的Parquet表存储在S3，后续查询性能更高、成本更低。
3. 如果你写入的Parquet是分区表，记得在WHERE中添加分区裁剪条件，可大幅减少扫描的数据量，提升查询效率。

你的实现思路没有问题，Athena非常适合这类轻量的分布式聚合计算场景，相比拉取全量数据到本地用pandas处理，可节省大量本地资源和数据传输成本。

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