如何用SQLAlchemy的select()按月份统计用户创建数量？

数据表模型 #

使用SQLAlchemy定义的person表结构：

from sqlalchemy import Table, Column, BIGINT, VARCHAR, TIMESTAMP

Table(
   'person',
   metadata,
   Column('id', BIGINT, nullable=False, primary_key=True),
   Column('name', VARCHAR(300)),
   Column('user_created', TIMESTAMP),
   Column('user_deleted', TIMESTAMP)
)

现有查询及问题 #

为统计每月创建的用户数量，编写了如下查询函数：

def count_users_by_month(user_table: Table):
   query = select(
      user_table.c.id,
      func.count(user_table.c.user_created).label('count'),
   ).where(and_(table.c.user_deleted.is_(None)))
      .group_by(
         user_table.c.id,
         user_table.c.created_at,
         func.date_trunc('month', table.c.user_created)
      )
   return query

执行该查询后得到的结果为：

(74, datetime.datetime(2020, 3, 2, 10, 19, 39), 1)
(75, datetime.datetime(2020, 3, 2, 10, 21, 24), 1)
(102, datetime.datetime(2020, 3, 4, 18, 46, 49), 1)
(141, datetime.datetime(2020, 3, 6, 16, 12, 6), 1)
(443, datetime.datetime(2020, 4, 1, 11, 37, 29), 1)
(450, datetime.datetime(2020, 4, 1, 14, 16, 53), 1)
(487, datetime.datetime(2020, 4, 6, 10, 42, 23), 1)
(509, datetime.datetime(2020, 4, 8, 10, 51, 55), 1)

问题：结果按每个用户单独统计，数量均为1，无法得到每月创建用户的总数。期望结果格式为：

(datetime.datetime(2020, 3), 4),
(datetime.datetime(2020, 4), 4),
...
(datetime.datetime(2022, 12), 10),

另外，若从group_by中移除user_table.c.id或user_table.c.created_at，会触发报错：column "person.id" must appear in the GROUP BY clause or be used in an aggregate function。

正确实现方案 #

调整查询逻辑，只按截断后的月份分组，并仅选择月份和统计数量：

from sqlalchemy import select, func, and_

def count_users_by_month(user_table: Table):
    # 定义截断后的月份字段，方便复用
    month_created = func.date_trunc('month', user_table.c.user_created).label('month_created')
    query = select(
        month_created,
        func.count(user_table.c.id).label('user_count')  # 统计用户ID的数量，确保每个用户只被计数一次
    ).where(
        and_(user_table.c.user_deleted.is_(None))  # 修正原代码中table变量的错误，统一使用user_table
    ).group_by(
        month_created  # 仅按截断后的月份分组
    ).order_by(
        month_created  # 按月份排序，结果更直观
    )
    return query

关键修正点说明 #

1. 查询字段调整：不再选择用户ID和原始创建时间，只保留截断后的月份和聚合统计数，避免了非聚合字段必须出现在GROUP BY中的问题。
2. GROUP BY优化：仅按截断后的月份分组，确保同一月份的所有用户被归为一组，统计结果为当月总用户数。
3. 变量修正：原代码中where和group_by里误用了table变量，统一替换为入参user_table；同时修正了笔误user_table.c.created_at（表结构中字段名为user_created）。
4. 统计逻辑优化：使用func.count(user_table.c.id)统计用户数，利用主键非空的特性，确保计数准确。

执行修正后的查询，即可得到期望的每月用户总数结果。

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