PostgreSQL中如何按自定义月起始日截断日期统计月活

你要的「以当日为每月起始节点的滚动月MAU统计」完全可以通过日期偏移的方式实现，核心逻辑是先对日期做偏移对齐，再按自然月截断分组，最后还原偏移得到目标周期起始时间。

实现思路 #

假设查询当日为当月D号：

1. 计算偏移天数：D-1天，比如6月7号查询时偏移天数为6天
2. 对齐日期：将所有created_at往前推D-1天，此时原本落在「上月D号 ~ 本月D-1号」这个自定义月周期内的日期，会全部落在同一个自然月区间内
3. 分组统计：用你原本的date_trunc('month', ...)对偏移后的日期做自然月截断，分组统计去重用户数
4. 还原时间：将分组得到的自然月首日往后加D-1天，就能得到和当日同号的自定义周期起始时间，和你预期的输出格式完全匹配

可直接运行的代码（适配PostgreSQL，和你原有语法兼容） #

WITH config AS (
    -- 修改此处的值即可调整要统计的最近X个月范围
    SELECT 6 AS last_x_months
)
SELECT
    date_trunc(
        'month', 
        created_at - (INTERVAL '1 day' * (EXTRACT(DAY FROM CURRENT_DATE) - 1))
    ) + (INTERVAL '1 day' * (EXTRACT(DAY FROM CURRENT_DATE) - 1)) AS time,
    COUNT(DISTINCT user_id) AS mau
FROM "session", config
WHERE
    -- 过滤时间范围，避免全表扫描提升性能
    created_at >= CURRENT_DATE - (INTERVAL '1 month' * last_x_months)
GROUP BY time
ORDER BY time;

特殊情况说明 #

• 月末日期兼容：如果查询当日是某个月的31号，遇到没有31号的月份（比如4月、6月），PostgreSQL会自动将日期计算为对应月份的最后一天，不会出现日期错误，统计逻辑符合滚动月规则
• 固定统计日修改：如果不需要每次以查询当日为月起始点，只需要把代码里的CURRENT_DATE替换为你指定的固定锚点日期即可，比如固定每月7号统计就替换为'2022-06-07'::date
• 性能建议：如果session表数据量较大，建议给created_at字段加B树索引，WHERE条件的范围过滤可以大幅降低计算耗时

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