如何优化SQL查询：将日期设为首列，高效统计近31天订单数据

需求回顾 #

• 输出近31天（含当日）的日期列表（格式yyyy-mm-dd）
• 每日统计两个指标：
  1. 当日创建的有效订单数（基于CreatedAt字段，排除OrderStatus=7的记录）
  2. 当日完成的有效订单数（基于CompletedAt字段，排除OrderStatus=7的记录）
• 限制：无法创建临时表，仅允许读操作

问题分析 #

原方案通过UNION手动拼接31天的查询，操作繁琐且维护性差；直接GROUP BY会因同一订单的创建/完成日期不同，导致同一日期对应多行记录。核心解决思路是先生成连续日期序列，再关联订单表做聚合统计。

最优SQL实现（SQL Server） #

WITH DateRange AS (
    -- 起始日期：今日
    SELECT CAST(GETDATE() AS DATE) AS [Date]
    UNION ALL
    -- 递归生成往前30天的日期
    SELECT DATEADD(DAY, -1, [Date])
    FROM DateRange
    WHERE [Date] > DATEADD(DAY, -30, CAST(GETDATE() AS DATE))
)
SELECT
    dr.[Date],
    -- 统计当日创建的有效订单数，无数据则显示0
    ISNULL(created.Counts, 0) AS [Booked Orders],
    -- 统计当日完成的有效订单数，无数据则显示0
    ISNULL(completed.Counts, 0) AS [Completed Orders]
FROM DateRange dr
-- 左连接统计创建订单数的子查询
LEFT JOIN (
    SELECT
        CAST(CreatedAt AS DATE) AS [Date],
        COUNT(OrderId) AS Counts
    FROM [tt].[order]
    WHERE OrderStatus NOT IN (7)
    GROUP BY CAST(CreatedAt AS DATE)
) created ON dr.[Date] = created.[Date]
-- 左连接统计完成订单数的子查询
LEFT JOIN (
    SELECT
        CAST(CompletedAt AS DATE) AS [Date],
        COUNT(OrderId) AS Counts
    FROM [tt].[order]
    WHERE OrderStatus NOT IN (7)
    GROUP BY CAST(CompletedAt AS DATE)
) completed ON dr.[Date] = completed.[Date]
ORDER BY dr.[Date] DESC;

代码说明 #

1. 递归CTE DateRange：自动生成从今日往前30天的连续日期序列，无需手动逐个声明日期变量。
2. 两个统计子查询：分别按创建日期、完成日期聚合有效订单数，避免同一订单跨日期导致的重复行问题。
3. 左连接关联：确保即使某一天没有创建/完成订单，也会显示日期并填充0，保证日期序列的完整性。
4. ISNULL处理：避免无数据时出现NULL，统一显示为0，符合报表展示需求。

样本数据验证 #

针对提供的样本数据，执行上述SQL后：

• 2025-02-28的Completed Orders为4（样本中前4条订单的CompletedAt均为该日期），Booked Orders为0（样本中无订单在当日创建）
• 2025-02-27的Completed Orders为1（第5条订单的CompletedAt为该日期），Booked Orders为0（样本中无订单在当日创建）

（注：期望输出中的数值为示例，实际以真实数据统计为准）

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