PostgreSQL中如何计算年龄占比与环比？

Question

在PostgreSQL中，如何使用窗口函数与聚合函数结合，准确计算不同年龄段用户在总体用户中的占比以及环比变化情况？

Answer 1

一、背景与问题描述

在数据分析场景中，统计不同年龄段用户的占比以及环比变化情况是常见的需求。特别是在用户增长、用户画像、产品优化等业务场景中，了解不同年龄段用户的变化趋势，有助于制定更精准的运营策略。

PostgreSQL 提供了强大的窗口函数和聚合函数能力，可以高效地完成此类分析任务。

二、核心概念解析

• 窗口函数（Window Function）：用于在一组数据上进行计算，而不像普通聚合函数那样将多行合并为一行。
• 聚合函数（Aggregate Function）：如 COUNT、SUM 等，用于对数据进行汇总计算。
• 环比变化：通常指相邻时间段的变化率，如本月与上月的比较。
• 年龄段划分：通常使用 CASE WHEN 或 WIDTH_BUCKET 进行分段。

三、数据准备与结构设计

假设我们有一个用户表 users，包含如下字段：

字段名	类型	说明
id	integer	用户ID
age	integer	用户年龄
signup_date	date	注册日期

四、年龄段划分与基础统计

首先，我们需要对用户进行年龄段划分。例如：0-18、19-30、31-45、46-60、60+。

SELECT
    CASE
        WHEN age BETWEEN 0 AND 18 THEN '0-18'
        WHEN age BETWEEN 19 AND 30 THEN '19-30'
        WHEN age BETWEEN 31 AND 45 THEN '31-45'
        WHEN age BETWEEN 46 AND 60 THEN '46-60'
        ELSE '60+'
    END AS age_group,
    COUNT(*) AS user_count
FROM users
GROUP BY age_group;

五、计算年龄段用户占比

使用窗口函数 SUM() OVER() 来计算总用户数，从而得出各年龄段的占比：

SELECT
    age_group,
    user_count,
    ROUND((user_count * 100.0 / SUM(user_count) OVER()), 2) AS percentage
FROM (
    SELECT
        CASE
            WHEN age BETWEEN 0 AND 18 THEN '0-18'
            WHEN age BETWEEN 19 AND 30 THEN '19-30'
            WHEN age BETWEEN 31 AND 45 THEN '31-45'
            WHEN age BETWEEN 46 AND 60 THEN '46-60'
            ELSE '60+'
        END AS age_group,
        COUNT(*) AS user_count
    FROM users
    GROUP BY age_group
) t;

六、环比变化分析：按月份统计

我们按月份统计每个年龄段的新增用户数，并使用 LAG() 窗口函数计算环比变化：

WITH monthly_users AS (
    SELECT
        DATE_TRUNC('month', signup_date) AS month,
        CASE
            WHEN age BETWEEN 0 AND 18 THEN '0-18'
            WHEN age BETWEEN 19 AND 30 THEN '19-30'
            WHEN age BETWEEN 31 AND 45 THEN '31-45'
            WHEN age BETWEEN 46 AND 60 THEN '46-60'
            ELSE '60+'
        END AS age_group,
        COUNT(*) AS new_users
    FROM users
    GROUP BY month, age_group
)
SELECT
    month,
    age_group,
    new_users,
    LAG(new_users, 1) OVER (PARTITION BY age_group ORDER BY month) AS prev_users,
    ROUND(
        ((new_users - LAG(new_users, 1) OVER (PARTITION BY age_group ORDER BY month)) * 100.0 /
         NULLIF(LAG(new_users, 1) OVER (PARTITION BY age_group ORDER BY month), 0)), 2
    ) AS growth_rate
FROM monthly_users
ORDER BY month, age_group;

七、性能优化与注意事项

• 分区字段：在使用窗口函数时，注意合理使用 PARTITION BY 和 ORDER BY。
• 索引优化：为 signup_date 和 age 建立合适的索引，提升查询效率。
• NULL值处理：使用 COALESCE() 或 NULLIF() 避免除以零的情况。
• 数据量控制：对于大数据表，可使用分区表或物化视图来提升性能。

八、完整分析流程图示

graph TD
      A[用户数据表] --> B[年龄段划分]
      B --> C[按年龄段统计用户数]
      C --> D[计算总用户数]
      D --> E[计算年龄段占比]
      E --> F[按月份分组]
      F --> G[使用LAG计算环比]
      G --> H[输出结果]