SQL（含Presto）处理缺失值：按Group/月/Party计算平均时长

原始表结构及数据 #

计算规则 #

按Group_id、month、party维度计算平均时长，公式为：

该维度下总时长 / 对应Group_id、month下的总事件数

注意：如果某个Party在某事件中没有time_interval数据，要按0时长计算，分母必须用Group_id+month维度的总事件数，不能用该Party自己的事件数。

原SQL的问题 #

原代码错把每个Party自身的事件数当分母，还没处理缺失值，结果肯定不对：

SELECT Group_id , month, party, total/num_cases FROM(
 SELECT Group_id , month, party, SUM(time_interval) AS total, COUNT(DISTINCT(event_id)) AS num_cases
 FROM table
 GROUP BY Group_id , month, party
 )

正确实现方案 #

核心是先算出Group_id+month的总事件数，再和每个Party的总时长关联计算，同时用COALESCE把缺失的时长转成0。

完整SQL代码 #

WITH group_month_total AS (
    -- 先算每个Group+月份的总事件数，这个是统一的分母
    SELECT Group_id, month, COUNT(DISTINCT event_id) AS total_events
    FROM your_table
    GROUP BY Group_id, month
),
party_total_hours AS (
    -- 算每个Group+月份+Party的总时长，空值按0算
    SELECT 
        Group_id, 
        month, 
        party,
        SUM(COALESCE(CAST(SPLIT_PART(time_interval, ' ', 1) AS INT), 0)) AS total_hours
    FROM your_table
    GROUP BY Group_id, month, party
)
-- 关联计算平均时长，最后格式化输出成要求的字符串格式
SELECT 
    gmt.Group_id,
    gmt.month,
    pth.party,
    CONCAT(
        pth.total_hours / gmt.total_events,
        ' hour',
        CASE WHEN pth.total_hours / gmt.total_events != 1 THEN 's' ELSE '' END
    ) AS avg_time_interval
FROM group_month_total gmt
JOIN party_total_hours pth 
    ON gmt.Group_id = pth.Group_id 
    AND gmt.month = pth.month
ORDER BY gmt.Group_id, gmt.month, pth.party;

执行结果 #

跑出来就是预期的结果：

关键细节 #

• COALESCE：把time_interval为空的情况转成0，保证总时长计算准确
• 单独计算group_month_total：确保所有Party用同一个分母，不会因为某个Party没参与某些事件就变小
• SPLIT_PART+CAST：把X hour/hours的字符串转成数字，才能做算术运算
• CONCAT+CASE：把计算结果转回要求的字符串格式，单数是hour，复数是hours

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