如何在同一张表统计近7天与近1个月营收？SQL查询排查求助

现有语句存在的问题 #

• 语法错误：两个计算营收的子查询WHERE子句后多余了and关键字，执行时会直接报语法错误
• 维度关联缺失：两个子查询没有和外层的product_therapy维度做关联，也没有加is_active='1'的有效订单过滤，查出来的T7、T30数值是全平台总营收，不是对应产品线的营收，结果完全错误
• 表名引用不统一：JOIN时使用的表名是"d2c"."fact_order_item"，但SELECT、GROUP BY阶段引用的表名是"Fact Order Item"，数据库无法识别未定义的表标识，会报表/对象不存在错误
• 性能问题：用嵌套子查询分两次查同一张订单表做聚合，数据量大时查询效率极低
• 时间统计不严谨：直接用GETDATE()做时间偏移没有截断时分秒，比如当前时间是15日16:42，近7天统计会从8日16:42开始算，不符合大多数业务按自然天统计的需求，容易出现数值偏差

正确实现SQL #

SELECT 
    foi.product_therapy AS Product_therapy,
    SUM(
        CASE WHEN fo.date_created >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 7, 0)
        THEN fo.total_order_value_inc_tax_uc
        ELSE 0 END
    ) AS [REVENUE - T7],
    SUM(
        CASE WHEN fo.date_created >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 30, 0)
        THEN fo.total_order_value_inc_tax_uc
        ELSE 0 END
    ) AS [REVENUE - T30]
FROM d2c.fact_order fo
LEFT JOIN d2c.fact_order_item foi
    ON fo.order_id = foi.order_id
WHERE fo.is_active = '1'
  -- 提前过滤30天外的数据，减少计算量
  AND fo.date_created >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 30, 0)
GROUP BY foi.product_therapy

实现说明：

• 给表设置了简短别名，统一引用规则，避免表名不匹配的报错
• 用条件聚合替代嵌套子查询，仅扫描一次订单表即可完成两个时段的营收计算，查询性能提升明显
• 对时间字段做了天级截断，统计的是完整自然日维度的近7天、近30天营收，不会因查询时点的时分秒产生统计偏差
• 营收聚合自动和产品线维度绑定，结果是每个产品线对应时段的有效订单营收，不会出现全平台数值重复的问题
• 提前在WHERE阶段过滤超出30天范围的历史数据，进一步降低计算数据量

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