如何让SQLAlchemy+PostgreSQL查询返回0或None计数结果？

原问题描述 #

想要创建一个查询，按站点按周统计Redirect的数量。编写的查询结果几乎正确，但当没有Redirect时，不会返回计数结果（既无0也无None，完全没有结果）。尝试将coalesce放在count外部，但结果相同。

原查询代码：

date_series = db.func.generate_series(min_date , todays_date, timedelta(weeks=1))
trunc_date = db.func.date_trunc('week', date_series)

subquery = db.session.query(trunc_date.label('week')).subquery()

query = db.session.query(subquery.c.week, Site, db.func.count(db.func.coalesce(Redirect.id, 0)))
    .outerjoin(Site, subquery.c.week == db.func.date_trunc('week', Redirect.timestamp))
    .outerjoin(Site, Redirect.site_id == Site.id)
    .group_by(subquery.c.week, Site.id)

counts = query.all()

编辑后的补充信息 #

进一步简化了查询，移除了Site分组，将‘week’改为‘day’，尝试仅统计单个站点的结果。但仍然无效，结果表现得像是普通join而非outerjoin，无法理解为何不返回0计数结果。

更新后的查询：

query = db.session.query(subquery.c.day, db.func.count(Redirect.id))
    .outerjoin(Redirect, subquery.c.day == db.func.date(Redirect.timestamp))
    .filter(Redirect.site_id == 82)
    .group_by(subquery.c.day)

返回结果（仅含有数据的日期）：

0:(datetime.datetime(2022, 11, 23, 0, 0), 1)
1:(datetime.datetime(2023, 12, 7, 0, 0), 1)

生成的SQL：

SELECT anon_1.day AS anon_1_day, count(redirect.id) AS count_1 
FROM (SELECT date_trunc(%(date_trunc_1)s, generate_series(%(generate_series_1)s, %(generate_series_2)s, %(generate_series_3)s)) AS day) AS anon_1 LEFT OUTER JOIN redirect ON anon_1.day = date(redirect.timestamp) 
WHERE redirect.site_id = %(site_id_1)s GROUP BY anon_1.day

核心问题分析 #

问题出在**WHERE子句过滤左连接后的表字段**：左连接后，没有Redirect数据的日期对应的redirect.site_id是NULL，而WHERE redirect.site_id = 82会直接排除这些NULL行，导致结果和普通内连接一致，只保留有匹配数据的日期。

针对简化后的单日单站点查询的修复 #

把站点过滤条件移到左连接的ON条件中，而非WHERE子句：

query = db.session.query(subquery.c.day, db.func.coalesce(db.func.count(Redirect.id), 0))
    .outerjoin(Redirect, 
               db.and_(subquery.c.day == db.func.date(Redirect.timestamp),
                       Redirect.site_id == 82))  # 将站点过滤放在连接条件里
    .group_by(subquery.c.day)

这样左连接时只会匹配指定站点的Redirect数据，无数据的日期会保留NULL，count(Redirect.id)会自动统计为0（NULL不会被count计数），用coalesce可以确保返回明确的0而非空值。

针对最初的按站点按周统计的修复 #

原查询的表连接逻辑有误，需调整连接顺序，若要覆盖所有站点的所有周，需先生成日期与站点的笛卡尔积，再左连接Redirect数据：

date_series = db.func.generate_series(min_date , todays_date, timedelta(weeks=1))
trunc_date = db.func.date_trunc('week', date_series)

# 生成日期序列子查询
date_subquery = db.session.query(trunc_date.label('week')).subquery()
# 生成所有站点的子查询
site_subquery = db.session.query(Site.id, Site.name).subquery()
# 生成日期与所有站点的笛卡尔积，确保每个站点的每个周都有基础记录
date_site_subquery = db.session.query(
    date_subquery.c.week,
    site_subquery.c.id.label('site_id'),
    site_subquery.c.name.label('site_name')
).cross_join(site_subquery).subquery()

# 左连接Redirect数据并统计数量
query = db.session.query(
    date_site_subquery.c.week,
    date_site_subquery.c.site_id,
    date_site_subquery.c.site_name,
    db.func.coalesce(db.func.count(Redirect.id), 0).label('redirect_count')
).outerjoin(
    Redirect,
    db.and_(
        date_site_subquery.c.week == db.func.date_trunc('week', Redirect.timestamp),
        date_site_subquery.c.site_id == Redirect.site_id
    )
).group_by(
    date_site_subquery.c.week,
    date_site_subquery.c.site_id,
    date_site_subquery.c.site_name
)

counts = query.all()

该方案会确保每个站点的每个周都返回统计结果，无Redirect数据时计数为0。

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