SQL Server 2008 R2每月新老客户订单统计实现求助

嘿，我来帮你搞定这个新老客户订单的统计需求！针对你的场景，我们可以先确定每个客户的首次下单月份，再判断每笔订单属于新客户还是重复客户，最后按月聚合统计结果。

核心思路 #

• 新客户订单：该订单所在月份是客户的首次下单月份
• 重复客户订单：该订单所在月份晚于客户的首次下单月份

适配SQL Server 2008 R2的解决方案 #

因为SQL Server 2008 R2支持CTE（公共表表达式），我们可以用它先计算每个客户的首次下单月份，再关联原表做统计：

WITH CustomerFirstOrder AS (
    -- 计算每个客户的首次下单月份（截断到当月第一天）
    SELECT 
        Customer,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, MIN(OrderDate)), 0) AS FirstOrderMonth
    FROM Orders
    GROUP BY Customer
)
SELECT 
    -- 将订单日期截断到当月第一天，作为统计月份
    DATEADD(MONTH, DATEDIFF(MONTH, 0, o.OrderDate), 0) AS OrderMonth,
    -- 统计新客户订单数
    COUNT(CASE WHEN DATEADD(MONTH, DATEDIFF(MONTH, 0, o.OrderDate), 0) = cfo.FirstOrderMonth THEN 1 END) AS NewCustomerOrderCount,
    -- 统计新客户订单总金额
    SUM(CASE WHEN DATEADD(MONTH, DATEDIFF(MONTH, 0, o.OrderDate), 0) = cfo.FirstOrderMonth THEN o.OrderAmount ELSE 0 END) AS NewCustomerOrderTotal,
    -- 统计重复客户订单数
    COUNT(CASE WHEN DATEADD(MONTH, DATEDIFF(MONTH, 0, o.OrderDate), 0) > cfo.FirstOrderMonth THEN 1 END) AS RepeatCustomerOrderCount,
    -- 统计重复客户订单总金额
    SUM(CASE WHEN DATEADD(MONTH, DATEDIFF(MONTH, 0, o.OrderDate), 0) > cfo.FirstOrderMonth THEN o.OrderAmount ELSE 0 END) AS RepeatCustomerOrderTotal
FROM Orders o
JOIN CustomerFirstOrder cfo ON o.Customer = cfo.Customer
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, o.OrderDate), 0)
ORDER BY OrderMonth;

如果不习惯用CTE，也可以用子查询替代，效果完全一样：

SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, o.OrderDate), 0) AS OrderMonth,
    COUNT(CASE WHEN DATEADD(MONTH, DATEDIFF(MONTH, 0, o.OrderDate), 0) = cfo.FirstOrderMonth THEN 1 END) AS NewCustomerOrderCount,
    SUM(CASE WHEN DATEADD(MONTH, DATEDIFF(MONTH, 0, o.OrderDate), 0) = cfo.FirstOrderMonth THEN o.OrderAmount ELSE 0 END) AS NewCustomerOrderTotal,
    COUNT(CASE WHEN DATEADD(MONTH, DATEDIFF(MONTH, 0, o.OrderDate), 0) > cfo.FirstOrderMonth THEN 1 END) AS RepeatCustomerOrderCount,
    SUM(CASE WHEN DATEADD(MONTH, DATEDIFF(MONTH, 0, o.OrderDate), 0) > cfo.FirstOrderMonth THEN o.OrderAmount ELSE 0 END) AS RepeatCustomerOrderTotal
FROM Orders o
JOIN (
    SELECT 
        Customer,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, MIN(OrderDate)), 0) AS FirstOrderMonth
    FROM Orders
    GROUP BY Customer
) cfo ON o.Customer = cfo.Customer
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, o.OrderDate), 0)
ORDER BY OrderMonth;

针对你提供的样例数据的输出结果 #

运行上述代码后，会得到如下统计结果：

这个结果完全符合你的需求：

• 2017年1月：A、B、C都是首次下单，3笔新订单，总金额60
• 2017年4月：D首次下单（2笔订单），C是重复客户（1笔订单）
• 2018年1月：B是重复客户（1笔订单）

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