Postgres查询用户表月度累计用户数及ActiveRecord转换咨询

原SQL的问题 #

你提供的SQL存在两个核心问题：

• 未做按月聚合：当前语句会返回users表的每一条用户记录，同一个月份会出现多条重复的historic_count值，无法得到按月汇总的结果。
• 月份计算逻辑偏差：date_trunc('month', created_at) + interval '1 month'会把当月日期转为下一月的起始日期，比如2022-12-05会被转为2023-01-01，和你需求中“统计当月累计数”的逻辑不符。

正确的Postgres查询语句 #

要实现按月统计新增用户数及累计用户数，需先按月聚合得到每月新增量，再通过窗口函数计算累计值：

SELECT
  date_trunc('month', created_at) AS month,
  COUNT(*) AS monthly_new_users,
  SUM(COUNT(*)) OVER (ORDER BY date_trunc('month', created_at)) AS historic_count
FROM users
GROUP BY date_trunc('month', created_at)
ORDER BY month;

说明：

• GROUP BY date_trunc('month', created_at)：按月分组，统计每个月的新增用户数
• SUM(COUNT(*)) OVER (...)：基于按月排序的窗口，累加每月新增用户数得到累计值
• 最终结果会返回每个月份、当月新增数、截至该月的累计用户数，完全匹配你的需求示例。

转换为ActiveRecord查询 #

对应Rails的ActiveRecord写法如下：

User.select(
  "date_trunc('month', created_at) AS month",
  "COUNT(*) AS monthly_new_users",
  "SUM(COUNT(*)) OVER (ORDER BY date_trunc('month', created_at)) AS historic_count"
).group("date_trunc('month', created_at)").order("month")

如果需要将month字段转换为更易读的YYYY-MM格式，可调整为：

User.select(
  "to_char(date_trunc('month', created_at), 'YYYY-MM') AS month",
  "COUNT(*) AS monthly_new_users",
  "SUM(COUNT(*)) OVER (ORDER BY date_trunc('month', created_at)) AS historic_count"
).group("date_trunc('month', created_at)").order("date_trunc('month', created_at)")

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