如何在AWS Athena/Presto中统计人生事件前后的ATM存款次数？

可以用条件聚合+用户内关联的方式实现，这是Athena/Presto里简便高效的写法，完整代码如下：

WITH life_events(person_name, life_event, event_time) AS (
    values 
    ('john',   'ten_year_anniversary',          date('2016-03-02')),
    ('john',   'got_fired_from_ibm',            date('2019-01-17')),
    ('john',   'purchased_first_lamborghini',   date('2023-02-10')),
    ('jane',   'got_flight_license',            date('2017-04-04')),
    ('jane',   'first_child_born',              date('2020-10-10')),
    ('daniel', 'college_graduation',            date('2010-07-09')),
    ('daniel', 'first_job',                     date('2020-10-30'))
),

log_of_deposits(person_name, time_of_deposit) AS (
    values
    ('john',   date('2015-01-07')),
    ('john',   date('2016-01-30')),
    ('john',   date('2017-10-10')),
    ('john',   date('2018-04-10')),
    ('john',   date('2020-03-23')),
    ('john',   date('2023-05-12')),
    ('jane',   date('2018-09-15')),
    ('jane',   date('2018-11-12')),
    ('jane',   date('2019-05-23')),
    ('jane',   date('2021-09-09')),
    ('jane',   date('2022-08-10')),
    ('daniel', date('2009-01-15')),
    ('daniel', date('2010-03-20')),
    ('daniel', date('2015-10-12')),
    ('daniel', date('2016-09-09')),
    ('daniel', date('2018-02-03')),
    ('daniel', date('2019-12-01'))
)

SELECT 
    le.person_name,
    le.life_event,
    COUNT(CASE WHEN lod.time_of_deposit < le.event_time THEN 1 END) AS n_deposits_before,
    COUNT(CASE WHEN lod.time_of_deposit > le.event_time THEN 1 END) AS n_deposits_after
FROM life_events le
CROSS JOIN log_of_deposits lod
WHERE le.person_name = lod.person_name
GROUP BY le.person_name, le.life_event, le.event_time
ORDER BY le.person_name, le.event_time;

核心逻辑说明 #

• 用户内关联：通过CROSS JOIN+WHERE条件，把同一个用户的所有人生事件和存款日志一一配对，确保每个事件都能拿到该用户的全部存款记录。
• 条件聚合统计：用COUNT(CASE...)分别筛选出事件发生前后的存款记录并计数，CASE里不满足条件的会返回NULL，不会被COUNT统计。
• 分组排序：按用户、事件和事件时间分组，保证每个事件的统计结果独立，排序后输出和期望格式一致。

执行这段查询后，就能得到你想要的统计结果。

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