如何在单条SQL查询中获取多时间维度的客户交易数据

直接用条件聚合就能在单条查询里同时计算所有需要的统计值，不用拆分多个查询再关联，具体SQL如下：

SELECT 
    customer_id,
    -- 终身消费/折扣总额
    SUM(net_amount) AS lifetime_spent,
    SUM(net_discount) AS lifetime_discount,
    -- 最近10天消费/折扣总额
    SUM(CASE WHEN order_date >= DATEADD(dd, -10, GETDATE()) THEN net_amount ELSE 0 END) AS last10d_spent,
    SUM(CASE WHEN order_date >= DATEADD(dd, -10, GETDATE()) THEN net_discount ELSE 0 END) AS last10d_discount,
    -- 最近30天消费/折扣总额
    SUM(CASE WHEN order_date >= DATEADD(dd, -30, GETDATE()) THEN net_amount ELSE 0 END) AS last30d_spent,
    SUM(CASE WHEN order_date >= DATEADD(dd, -30, GETDATE()) THEN net_discount ELSE 0 END) AS last30d_discount
FROM 
    [Order]  -- Order是SQL关键字，加方括号避免语法报错
GROUP BY 
    customer_id;

补充说明 #

1. 条件聚合的逻辑：通过CASE判断订单日期是否在目标范围内，符合条件的才累加对应金额，否则用0填充（也可以用NULL，SUM会自动忽略NULL），这样就能在同一行输出不同时间维度的统计结果。
2. 纠正原查询的问题：
   • 原最近10天查询的BETWEEN日期顺序错误，应该是早日期在前、晚日期在后，而且直接用日期函数比较比转成字符串更可靠——转字符串会丢失时间精度，还可能导致order_date的索引失效。
3. 如果需要严格按自然日统计（比如截止到今天0点），可以截断GETDATE()的时间部分：

   SELECT 
       customer_id,
       SUM(net_amount) AS lifetime_spent,
       SUM(net_discount) AS lifetime_discount,
       SUM(CASE WHEN order_date >= DATEADD(dd, -10, CAST(GETDATE() AS DATE)) THEN net_amount ELSE 0 END) AS last10d_spent,
       SUM(CASE WHEN order_date >= DATEADD(dd, -10, CAST(GETDATE() AS DATE)) THEN net_discount ELSE 0 END) AS last10d_discount,
       SUM(CASE WHEN order_date >= DATEADD(dd, -30, CAST(GETDATE() AS DATE)) THEN net_amount ELSE 0 END) AS last30d_spent,
       SUM(CASE WHEN order_date >= DATEADD(dd, -30, CAST(GETDATE() AS DATE)) THEN net_discount ELSE 0 END) AS last30d_discount
   FROM 
       [Order]
   GROUP BY 
       customer_id;
   

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