如何计算社交平台每月帖子发布量及环比增长率？

核心思路 #

先按月聚合帖子数量并生成当月首日，再通过窗口函数获取上月数据计算增长率，最后格式化百分比输出。以下分两种主流数据库给出实现代码：

PostgreSQL 实现 #

WITH monthly_posts AS (
  -- 按月统计帖子数量，生成当月首日
  SELECT
    DATE_TRUNC('month', created_at)::DATE AS dt,
    COUNT(id) AS count
  FROM posts
  GROUP BY DATE_TRUNC('month', created_at)
)
SELECT
  dt,
  count,
  -- 计算增长率，首月返回null
  CASE
    WHEN LAG(count) OVER (ORDER BY dt) IS NULL THEN NULL
    ELSE ROUND(
      (count::FLOAT - LAG(count) OVER (ORDER BY dt)) / LAG(count) OVER (ORDER BY dt) * 100,
      1
    ) || '%'
  END AS prent_growth
FROM monthly_posts
ORDER BY dt;

MySQL 实现 #

WITH monthly_posts AS (
  -- 按月统计帖子数量，生成当月首日
  SELECT
    DATE_FORMAT(created_at, '%Y-%m-01') AS dt,
    COUNT(id) AS count
  FROM posts
  GROUP BY DATE_FORMAT(created_at, '%Y-%m-01')
)
SELECT
  dt,
  count,
  -- 计算增长率，首月返回null
  CASE
    WHEN LAG(count) OVER (ORDER BY dt) IS NULL THEN NULL
    ELSE CONCAT(
      ROUND(
        (count - LAG(count) OVER (ORDER BY dt)) / LAG(count) OVER (ORDER BY dt) * 100,
        1
      ),
      '%'
    )
  END AS prent_growth
FROM monthly_posts
ORDER BY dt;

关键细节说明 #

• 当月首日生成：PostgreSQL 用 DATE_TRUNC('month', created_at) 截断到月份后转成日期；MySQL 直接用 DATE_FORMAT 格式化到当月1号。
• 增长率计算：用 LAG(count) OVER (ORDER BY dt) 获取上月的帖子数量，通过公式 (当月数-上月数)/上月数*100 计算增长率，ROUND 保留1位小数后拼接%符号。
• 首月处理：通过 CASE 判断，首月没有上月数据，增长率返回null。
• 空值忽略：COUNT(id) 会自动忽略created_at为null的记录（因为id是主键非空），确保统计准确。

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