PostgreSQL 11.x 编写SQL生成近7天origin的type占比统计报表

实现思路 #

• 先过滤近7天的所有有效记录
• 按origin维度聚合，标记每个origin是否存在type A、type B的记录
• 统计总origin数量、各类型对应origin数量，计算百分比

核心SQL代码 #

WITH origin_type_flag AS (
    SELECT 
        origin,
        BOOL_OR(type = 'A') AS has_a,
        BOOL_OR(type = 'B') AS has_b
    FROM your_table_name -- 替换为实际表名
    WHERE date >= CURRENT_TIMESTAMP - INTERVAL '7 days'
    GROUP BY origin
),
total_calc AS (
    SELECT
        COUNT(*) AS total_origin_cnt,
        COUNT(*) FILTER (WHERE has_a) AS a_origin_cnt,
        COUNT(*) FILTER (WHERE has_b) AS b_origin_cnt,
        COUNT(*) FILTER (WHERE has_a AND has_b) AS both_origin_cnt
    FROM origin_type_flag
)
SELECT
    ROUND(a_origin_cnt::NUMERIC / total_origin_cnt * 100, 2) AS type_a_percentage,
    ROUND(b_origin_cnt::NUMERIC / total_origin_cnt * 100, 2) AS type_b_percentage,
    ROUND(both_origin_cnt::NUMERIC / total_origin_cnt * 100, 2) AS both_type_percentage
FROM total_calc;

注意事项 #

• 需将代码中的your_table_name替换为你实际使用的数据表名
• 若type字段的取值为小写a/b或其他格式，自行调整type = 'A'、type = 'B'中的匹配值
• 若需按自然日而非7*24小时范围统计，可将时间过滤条件修改为date >= CURRENT_DATE - INTERVAL '7 days' AND date < CURRENT_DATE
• 代码中使用PostgreSQL原生的BOOL_OR聚合函数，相比CASE WHEN写法更简洁高效，完全兼容11.x版本
• 计数转NUMERIC类型是为了避免整数除法导致小数位被截断的问题，默认保留2位小数，可自行调整ROUND函数的第二个参数修改精度

可选调整 #

如果统计的分母需要是全表所有origin（不管近7天是否有记录），只需修改total_calc部分的total_origin_cnt取值即可：

total_calc AS (
    SELECT
        (SELECT COUNT(DISTINCT origin) FROM your_table_name) AS total_origin_cnt,
        COUNT(*) FILTER (WHERE has_a) AS a_origin_cnt,
        COUNT(*) FILTER (WHERE has_b) AS b_origin_cnt,
        COUNT(*) FILTER (WHERE has_a AND has_b) AS both_origin_cnt
    FROM origin_type_flag
)

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