在SQL Server中统计连续两日的客户交易数量

嘿，我来帮你搞定这个月度内连续两天客户交易数的统计需求！下面是针对你的场景的具体实现步骤和SQL代码：

实现思路 #

我们需要先按客户和日期汇总每日交易数据，再关联下一天的交易记录，最后计算连续两天的交易总数，同时保留最后一天到次日的统计（即使次日没有交易）。

完整SQL代码 #

-- 先创建临时表并插入示例数据
CREATE TABLE #Sample (ID int, CustomerID char(4), TransactionDate Date);
GO
INSERT INTO #Sample VALUES
(1,'C001','20170701'),
(2,'C001','20170701'),
(3,'C001','20170701'),
(4,'C001','20170702'),
(5,'C002','20170702'),
(6,'C001','20170715'),
(7,'C001','20170715'),
(8,'C001','20170716'),
(9,'C001','20170717');
GO

-- 核心统计逻辑
WITH DailyTransactions AS (
    -- 第一步：统计每个客户每天的交易笔数
    SELECT 
        CustomerID,
        TransactionDate,
        COUNT(*) AS daily_count
    FROM #Sample
    GROUP BY CustomerID, TransactionDate
)
SELECT 
    -- 计算连续两天的总交易数，次日无数据则按0计算
    (dt1.daily_count + ISNULL(dt2.daily_count, 0)) AS count,
    dt1.CustomerID,
    -- 转换日期格式为dd/mm/yyyy
    CONVERT(VARCHAR, dt1.TransactionDate, 103) AS fromDate,
    CONVERT(VARCHAR, DATEADD(day, 1, dt1.TransactionDate), 103) AS toDate
FROM DailyTransactions dt1
-- 左连接匹配下一天的交易数据，确保即使次日无交易也能保留当前日期记录
LEFT JOIN DailyTransactions dt2 
    ON dt1.CustomerID = dt2.CustomerID 
    AND dt2.TransactionDate = DATEADD(day, 1, dt1.TransactionDate)
-- 过滤2017年7月的数据
WHERE DATEPART(month, dt1.TransactionDate) = 7 
    AND DATEPART(year, dt1.TransactionDate) = 2017
-- 按客户和日期排序
ORDER BY dt1.CustomerID, dt1.TransactionDate;

-- 清理临时表
DROP TABLE #Sample;
GO

结果说明 #

运行上述代码后，你会得到如下结果（以C001为例）：

如果只需要展示C001的记录，只需在WHERE子句中添加AND dt1.CustomerID = 'C001'即可。你提供的期望结果中缺少了02/07/2017到03/07/2017的行，这是因为C001在02号有1笔交易，按逻辑应该被统计进去；如果你不需要这类仅单日有交易的连续日期对，可以将LEFT JOIN改为INNER JOIN，但这样会同时去掉17/07/2017到18/07/2017的行，需要单独处理最后一天的统计。

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