使用SqlAlchemy的over函数报错列需出现在GROUP BY子句如何解决

初始查询实现 #

我在开发一款展示数据表内事件总数随时间增长情况的应用，最初使用如下查询按年月分组统计单月事件数量：

query = session.query(
    count(Event.id).label('count'),
    extract('year', Event.date).label('year'),
    extract('month', Event.date).label('month')
).filter(
    Event.date.isnot(None)
).group_by('year', 'month').all()

该查询仅能输出每月新增事件数，返回结果如下：

期望输出效果 #

需要得到累计的事件总量，期望结果如下：

遇到的报错问题 #

查阅资料后尝试用SQLAlchemy的over窗口函数实现需求，但始终触发如下报错：

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.GroupingError) column "event.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT count(event.id) OVER (PARTITION BY event.date ORDER...
                     ^
[SQL: SELECT count(event.id) OVER (PARTITION BY event.date ORDER BY EXTRACT(year FROM event.date), EXTRACT(month FROM event.date)) AS count, EXTRACT(year FROM event.date) AS year, EXTRACT(month FROM event.date) AS month 
FROM event 
WHERE event.date IS NOT NULL GROUP BY year, month]

使用的错误查询代码如下：

session.query(
    count(Event.id).over(
        order_by=(
            extract('year', Event.date),
            extract('month', Event.date)
        ),
        partition_by=Event.date
    ).label('count'),
    extract('year', Event.date).label('year'),
    extract('month', Event.date).label('month')
).filter(
    Event.date.isnot(None)
).group_by('year', 'month').all()

多次尝试后未找到问题根源：如果将event.id加入GROUP BY子句，就会破坏原本按年月分组的逻辑，无法得到按月聚合的结果。

最终可行方案 #

经过调试得到可用的查询代码如下：

query = session.query(
    extract('year', Event.date).label('year'),
    extract('month', Event.date).label('month'),
    func.sum(func.count(Event.id)).over(order_by=(
        extract('year', Event.date),
        extract('month', Event.date)
    )).label('count'),
).filter(
    Event.date.isnot(None)
).group_by('year', 'month')

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