SQLAlchemy合并同表两个查询 实现门店同期销售客流同行展示

可行实现方案 #

推荐优先使用条件聚合方案，逻辑简单性能更高，无需多表关联或合并查询，一次分组即可得到目标格式结果。 #

方案1：条件聚合（推荐）

from sqlalchemy import func

result = db.session.query(
    Sales.name,
    # 统计本期销售额
    func.sum(func.case(
        (Sales.date.between(start_day, end_day), Sales.sales),
        else_=0
    )).label('total_sales'),
    # 统计去年同期销售额
    func.sum(func.case(
        (Sales.date.between(start_day_ly, end_day_ly), Sales.sales),
        else_=0
    )).label('total_sales_ly'),
    # 统计本期客流
    func.sum(func.case(
        (Sales.date.between(start_day, end_day), Sales.guests),
        else_=0
    )).label('total_guests'),
    # 统计去年同期客流
    func.sum(func.case(
        (Sales.date.between(start_day_ly, end_day_ly), Sales.guests),
        else_=0
    )).label('total_guests_ly')
).filter(
    # 缩小数据扫描范围，仅查询两个目标时间段，提升性能
    Sales.date.between(start_day_ly, end_day) | Sales.date.between(start_day_ly, end_day_ly)
).group_by(Sales.name).all()

查询返回的结果直接匹配你要的单行结构，每个门店对应一条记录，包含全部四个指标。 #

方案2：子查询左连接

适合子查询逻辑更复杂的场景，用外连接保证仅开业一年的门店数据也能正常显示：

# 本期统计子查询
this_year = db.session.query(
    Sales.name,
    func.sum(Sales.sales).label('total_sales'),
    func.sum(Sales.guests).label('total_guests')
).filter(Sales.date.between(start_day, end_day)
).group_by(Sales.name).subquery()

# 去年同期统计子查询
last_year = db.session.query(
    Sales.name,
    func.sum(Sales.sales).label('total_sales_ly'),
    func.sum(Sales.guests).label('total_guests_ly')
).filter(Sales.date.between(start_day_ly, end_day_ly)
).group_by(Sales.name).subquery()

# 关联两个子查询，空值转0避免显示异常
result = db.session.query(
    func.coalesce(this_year.c.name, last_year.c.name).label('name'),
    func.coalesce(this_year.c.total_sales, 0).label('total_sales'),
    func.coalesce(last_year.c.total_sales_ly, 0).label('total_sales_ly'),
    func.coalesce(this_year.c.total_guests, 0).label('total_guests'),
    func.coalesce(last_year.c.total_guests_ly, 0).label('total_guests_ly')
).outerjoin(
    last_year, this_year.c.name == last_year.c.name
).all()

Jinja2 渲染表格示例

直接将查询得到的result传入模板即可：

<table border="1">
  <thead>
    <tr>
      <th>门店名称</th>
      <th>本期销售额</th>
      <th>去年同期销售额</th>
      <th>本期客流</th>
      <th>去年同期客流</th>
    </tr>
  </thead>
  <tbody>
    {% for row in result %}
    <tr>
      <td>{{ row.name }}</td>
      <td>{{ "%.2f"|format(row.total_sales) }}</td>
      <td>{{ "%.2f"|format(row.total_sales_ly) }}</td>
      <td>{{ row.total_guests }}</td>
      <td>{{ row.total_guests_ly }}</td>
    </tr>
    {% endfor %}
  </tbody>
</table>

注意：由于你的date字段为字符串类型，需确保传入的start_day、end_day等参数格式和数据库存储的日期格式完全一致，避免条件匹配失败。

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