AWS Athena SQL聚合时取字段首尾值及统计unique_id出现次数方案

完全可以直接在AWS Athena SQL中实现两个需求，无需后续在业务代码中额外处理，修改后的查询语句如下：

SELECT item, 
    sum(cost) as sum_cost, 
    avg(cost) as avg_cost,
    -- 需求1：取分组内首条vietnamese，如需取末条将索引1改为-1即可
    element_at(array_agg(vietnamese ORDER BY cost), 1) as vietnamese,
    array_agg(cost) as costs,
    -- 需求2：直接统计unique_id出现频次，返回键值对结构
    histogram(unique_id) as unique_id_count
FROM foodtable
GROUP BY item
ORDER BY avg_cost

细节说明 #

• 需求1实现逻辑：
  • 如果需要严格按照指定排序取分组内的首/末条vietnamese值，可以在array_agg中通过ORDER BY自定义排序规则，再用element_at取对应位置的元素即可
  • 如果同个item对应的vietnamese值固定相同，也可以直接替换为any_value(vietnamese)，性能更优；如果需要去重后的所有翻译值，使用array_distinct(array_agg(vietnamese))即可
• 需求2实现逻辑：
  • 直接调用Athena内置的histogram聚合函数，传入要统计的unique_id字段，返回结果为MAP类型，键为去重后的id值，值为对应出现次数，效果和collections.Counter完全一致

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