如何避免SQLAlchemy查询结果丢失零销量日期？

问题背景 #

运营多供应商销售站点，需要统计每日各卖家的销量。先通过子查询生成过去365天的日期序列：

date_series = func.generate_series(min_date , todays_date, timedelta(days=1))
trunc_date = func.date_trunc('day', date_series)
subquery = session.query(trunc_date.label('day')).subquery()

再编写主查询统计销量：

query = session.query(subquery.c.day, func.count(Sale.id), Vendor.name)
    
query = query.outerjoin(Sale, subquery.c.day == func.date_trunc('day', Sale.timestamp))
query = query.outerjoin(Product, Sale.product_id == Product.id)
query = query.outerjoin(Vendor, Product.vendor_id == Vendor.id)

query = query.group_by(subquery.c.day, Vendor)
query = query.order_by(subquery.c.day.desc())

counts = query.all()

遇到的问题：将Vendor加入group_by后，查询不再返回各供应商销量为0的日期，仅出现一条NULL供应商的记录；移除Vendor相关逻辑后，能返回零销量日期但无法按供应商拆分数据。

补充信息 #

生成的SQL语句：

SELECT anon_1.day AS anon_1_day, vendor.name AS vendor_name, count(sales.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 sales ON anon_1.day = date_trunc(%(date_trunc_2)s, sales.timestamp) LEFT OUTER JOIN product ON sales.product_id = product.id LEFT OUTER JOIN vendor ON product.vendor_id = vendor.id GROUP BY anon_1.day, vendor.name ORDER BY anon_1.day DESC

表关系：

Sale -> Product -> Vendor

当前查询结果：

(datetime.datetime(2023, 6, 11, 0, 0), None, None)
(datetime.datetime(2023, 6, 10, 0, 0), None, None)
(datetime.datetime(2023, 6, 9, 0, 0), None, None)
(datetime.datetime(2023, 6, 8, 0, 0), Vendor_1, 10)
(datetime.datetime(2023, 6, 8, 0, 0), Vendor_2, 3)
(datetime.datetime(2023, 6, 7, 0, 0), Vendor_1, 9)
(datetime.datetime(2023, 6, 7, 0, 0), Vendor_2, 11)

期望查询结果：

(datetime.datetime(2023, 6, 11, 0, 0), Vendor_1, 0)
(datetime.datetime(2023, 6, 11, 0, 0), Vendor_2, 0)
(datetime.datetime(2023, 6, 10, 0, 0), Vendor_1, 0)
(datetime.datetime(2023, 6, 10, 0, 0), Vendor_2, 0)
(datetime.datetime(2023, 6, 9, 0, 0), Vendor_1, 0)
(datetime.datetime(2023, 6, 9, 0, 0), Vendor_2, 0)
(datetime.datetime(2023, 6, 8, 0, 0), Vendor_1, 10)
(datetime.datetime(2023, 6, 8, 0, 0), Vendor_2, 3)
(datetime.datetime(2023, 6, 7, 0, 0), Vendor_1, 9)
(datetime.datetime(2023, 6, 7, 0, 0), Vendor_2, 11)

解决方案 #

核心逻辑是先生成日期序列与所有供应商的笛卡尔积，确保每个日期都对应所有供应商的组合，再关联销量数据进行统计。

步骤1：获取所有供应商的子查询 #

vendor_subquery = session.query(Vendor.id, Vendor.name).subquery()

步骤2：生成日期-供应商的全量组合（交叉连接） #

# 日期序列子查询保持不变
date_series = func.generate_series(min_date, todays_date, timedelta(days=1))
trunc_date = func.date_trunc('day', date_series)
date_subquery = session.query(trunc_date.label('day')).subquery()

# 交叉连接日期和供应商，得到每个日期+每个供应商的完整组合
cross_subquery = session.query(
    date_subquery.c.day,
    vendor_subquery.c.id.label('vendor_id'),
    vendor_subquery.c.name.label('vendor_name')
).select_from(date_subquery).cross_join(vendor_subquery).subquery()

步骤3：关联销量数据并统计 #

query = session.query(
    cross_subquery.c.day,
    cross_subquery.c.vendor_name,
    func.count(Sale.id).label('sales_count')
).select_from(cross_subquery)

# 左连产品表，关联供应商ID
query = query.outerjoin(Product, Product.vendor_id == cross_subquery.c.vendor_id)
# 左连订单表，关联日期和产品ID
query = query.outerjoin(
    Sale,
    and_(
        func.date_trunc('day', Sale.timestamp) == cross_subquery.c.day,
        Sale.product_id == Product.id
    )
)

# 按日期和供应商分组（用vendor_id避免名称重复问题）
query = query.group_by(cross_subquery.c.day, cross_subquery.c.vendor_id, cross_subquery.c.vendor_name)
# 按日期倒序、供应商名称排序
query = query.order_by(cross_subquery.c.day.desc(), cross_subquery.c.vendor_name)

counts = query.all()

原理说明 #

原查询中，当某日期无订单时，Vendor字段会变为NULL，GROUP BY后只会生成一条NULL供应商的记录，而非每个供应商对应0销量。通过交叉连接生成日期与供应商的全量组合，再左连销量数据，就能保证每个组合都存在，无销量时count(Sale.id)会返回0，完美匹配期望结果。

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