Google BigQuery多条件平均值统计与分类查询需求

需求说明 #

针对包含Date、Name、Group、Percent字段的数据集，筛选Group='gr1'的数据后，按月份统计每个月份中各Name的月度Percent平均值所属区间的数量，区间划分如下：

• <=25%
• 25% < 值 <=50%
• 50% < 值 <=75%
• 75% < 值 <=100%
  结果需按月份排序，输出各区间对应的Name数量。

解决方案SQL #

WITH monthly_avg AS (
    -- 计算每个Name每月的Percent平均值，筛选Group='gr1'
    SELECT
        EXTRACT(MONTH FROM Date) AS Month,
        Name,
        AVG(Percent) AS monthly_percent_avg
    FROM `table`
    WHERE Group = 'gr1'
    GROUP BY Month, Name
)
-- 按月份统计各区间的Name数量
SELECT
    Month,
    COUNT(CASE WHEN monthly_percent_avg <= 25 THEN 1 END) AS `<=25%`,
    COUNT(CASE WHEN monthly_percent_avg > 25 AND monthly_percent_avg <= 50 THEN 1 END) AS `25<_<=50%`,
    COUNT(CASE WHEN monthly_percent_avg > 50 AND monthly_percent_avg <= 75 THEN 1 END) AS `50<_<=75%`,
    COUNT(CASE WHEN monthly_percent_avg > 75 AND monthly_percent_avg <= 100 THEN 1 END) AS `75<_<=100%`
FROM monthly_avg
GROUP BY Month
ORDER BY Month;

逻辑解释 #

1. 子查询monthly_avg：通过GROUP BY Month, Name，计算每个用户在每个月份的平均百分比，同时筛选出目标分组gr1的数据，解决了原查询未按月份计算用户平均值的问题。
2. 外层统计：基于子查询得到的月度用户平均值，用CASE语句判断每个平均值的所属区间，再通过COUNT统计每个月份各区间内的用户数量，最后按月份排序输出。

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