SQL统计需求：需按指定维度分组的平均值对比时间实例数

问题分析 #

你当前代码里，arrival_cluster_final中的子查询(select AVG(m_per_imei_cluster::TIME) from arrival_cluster_raw)计算的是全表平均到达时间，没法满足按uc_id、cluster_id、cluster_centroid、campaign_date组合计算组内平均，并基于组内平均统计实例数的需求。

修改方案 #

以下提供两种可行修改方式，核心是先获取每个分组的平均到达时间，再和组内每条数据对比统计：

方法1：使用窗口函数（单次表扫描，效率更高）

arrival_cluster_raw as (
    SELECT 
        routes.uc_id ,
        cg.cluster_id ,
        cg.cluster_centroid ,
        routes.imei ,
        routes.time_created::date as campaign_date,
        min(routes.time_created) as m_per_imei_cluster 
    FROM cluster_groups as cg
    -- 注：原代码未展示routes与cluster_groups的关联条件，实际使用需补充JOIN逻辑
    group by 1,2,3,4,5
),
arrival_cluster_final as (
    SELECT 
        uc_id, 
        campaign_date, 
        cluster_id, 
        cluster_centroid,
        -- 窗口函数计算当前分组的平均到达时间
        date_trunc('second', AVG(m_per_imei_cluster::TIME) OVER (PARTITION BY uc_id, cluster_id, cluster_centroid, campaign_date)) as avg_arrival_time,
        -- 统计组内早于平均到达时间的实例数
        COUNT(CASE WHEN m_per_imei_cluster::TIME < AVG(m_per_imei_cluster::TIME) OVER (PARTITION BY uc_id, cluster_id, cluster_centroid, campaign_date) THEN 1 END) as num_of_arrival_teams_before_avg_time,
        -- 统计组内晚于平均到达时间的实例数
        COUNT(CASE WHEN m_per_imei_cluster::TIME > AVG(m_per_imei_cluster::TIME) OVER (PARTITION BY uc_id, cluster_id, cluster_centroid, campaign_date) THEN 1 END) as num_of_arrival_teams_after_avg_time
    FROM arrival_cluster_raw
    GROUP BY uc_id, cluster_id, cluster_centroid, campaign_date, m_per_imei_cluster
)
SELECT * FROM arrival_cluster_final;

方法2：预计算分组平均再关联（逻辑更直观）

arrival_cluster_raw as (
    SELECT 
        routes.uc_id ,
        cg.cluster_id ,
        cg.cluster_centroid ,
        routes.imei ,
        routes.time_created::date as campaign_date,
        min(routes.time_created) as m_per_imei_cluster 
    FROM cluster_groups as cg
    -- 注：原代码未展示routes与cluster_groups的关联条件，实际使用需补充JOIN逻辑
    group by 1,2,3,4,5
),
cluster_avg as (
    -- 先计算每个分组的平均到达时间
    SELECT 
        uc_id, 
        cluster_id, 
        cluster_centroid, 
        campaign_date,
        date_trunc('second', AVG(m_per_imei_cluster::TIME)) as avg_arrival_time
    FROM arrival_cluster_raw
    GROUP BY uc_id, cluster_id, cluster_centroid, campaign_date
)
-- 关联原表与分组平均表，统计符合条件的实例数
SELECT 
    car.uc_id,
    car.campaign_date,
    car.cluster_id,
    car.cluster_centroid,
    ca.avg_arrival_time,
    COUNT(CASE WHEN car.m_per_imei_cluster::TIME < ca.avg_arrival_time THEN 1 END) as num_of_arrival_teams_before_avg_time,
    COUNT(CASE WHEN car.m_per_imei_cluster::TIME > ca.avg_arrival_time THEN 1 END) as num_of_arrival_teams_after_avg_time
FROM arrival_cluster_raw car
JOIN cluster_avg ca 
    ON car.uc_id = ca.uc_id
    AND car.cluster_id = ca.cluster_id
    AND car.cluster_centroid = ca.cluster_centroid
    AND car.campaign_date = ca.campaign_date
GROUP BY car.uc_id, car.cluster_id, car.cluster_centroid, car.campaign_date, ca.avg_arrival_time;

关键说明 #

• 两种方法都替换了原代码的全表平均逻辑，改为按指定分组维度计算组内平均。
• 原代码未显示routes表与cluster_groups表的关联条件，实际运行时需要补充对应的JOIN语句（比如JOIN routes ON cg.xxx = routes.xxx），否则会出现笛卡尔积或字段不存在的错误。

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