SQLAlchemy按分钟分组聚合PostgreSQL的scores表points总和报错如何解决

报错原因梳理 #

• 第一次报错：你的timestamp字段存储的是Unix时间戳整数类型，PostgreSQL内置的day()/hour()/minute()函数仅支持处理timestamp/timestamptz时间类型，无法直接作用于整数，因此报函数不存在错误。
• 第二次报错：你的查询语句仅返回了聚合后的points字段，没有将分组用的分钟级时间戳加入查询结果，因此访问a.timestamp时找不到对应字段。另外你误用了func.max，需求是求和应该用func.sum。

正确实现代码 #

两种写法都可以，按需选择即可：

写法1：直接用Unix时间戳计算分组（无需转时间类型） #

# 把分组后的分钟级时间戳加入查询，同时用sum聚合points
agg = (
    postgres_session.query(
        (func.floor(Score.timestamp / 60) * 60).label('minute_timestamp'),
        func.sum(Score.points).label('total_points')
    )
    .group_by('minute_timestamp')
    .order_by('minute_timestamp')
    .all()
)

# 遍历打印结果
for item in agg:
    print(item.minute_timestamp, item.total_points)

写法2：先把整数时间戳转成PostgreSQL的时间类型再分组 #

如果后续需要调整为按小时/天/周等更灵活的时间周期聚合，推荐转成时间类型处理：

agg = (
    postgres_session.query(
        func.date_trunc('minute', func.to_timestamp(Score.timestamp)).label('minute_time'),
        func.sum(Score.points).label('total_points')
    )
    .group_by('minute_time')
    .order_by('minute_time')
    .all()
)

# 遍历打印，minute_time是datetime类型，可自行转成时间戳或格式化字符串
for item in agg:
    print(int(item.minute_time.timestamp()), item.total_points)

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