[MS SQL] 连续範围问题 - 依照购买顺序排序店家

前阵子版上大大分享的题目 【SQL分享】 统计玩家游戏连胜连败的资料，觉得还蛮有趣的，看了下面的留言才知道，原来这种题型叫做 连续範围问题，刚好最近也有遇到类似的问题，分享给大家玩看看。

题目:

下方为使用者的购买清单。

栏位说明:

UserNo: 使用者代码StoreNo: 店家代码ProductNo: 商品代码Order: 购买顺序UserNoStoreNoProductNoOrder001S01P011001S01P022001S02P023001S02P034001S02P045001S01P056001S01P067001S01P078002S01P081002S02P092003S01P011003S02P012003S02P023

现在希望依照使用者的购买顺序排序店家，相同店家为同一序位，不过如果相同店家中间穿插其他店家则为不同序位。

期望的结果:

UserNoStoreNoProductNoOrderStoreOrder001S01P0111001S01P0221001S02P0232001S02P0342001S02P0452001S01P0563001S01P0673001S01P0783002S01P0811002S02P0922003S01P0111003S02P0122003S02P0232

测试资料:

DECLARE @BuyList TABLE(UserNo NVARCHAR(10),StoreNo NVARCHAR(10),ProductNo NVARCHAR(10),[Order] INT)INSERT INTO @BuyList(UserNo, StoreNo, ProductNo, [Order])VALUES('001', 'S01', 'P01', 1),('001', 'S01', 'P02', 2),('001', 'S02', 'P02', 3),('001', 'S02', 'P03', 4),('001', 'S02', 'P04', 5),('001', 'S01', 'P05', 6),('001', 'S01', 'P06', 7),('001', 'S01', 'P07', 8),('002', 'S01', 'P08', 1),('002', 'S02', 'P09', 2),('003', 'S01', 'P01', 1),('003', 'S02', 'P01', 2),('003', 'S02', 'P02', 3)

---

解法:

这题的重点在于如何去比较上一笔和下一笔的店家是否相同，发现 LAG 函数 可以达到此需求。

首先利用 LAG 函数比较上一笔的店家，如果相同就将标记 Diff 为 0，不相同为 1。

| UserNo | StoreNo | Order | Diff ||--------|---------|-------|------||   001  |   S01   |   1   |   1  ||   001  |   S01   |   2   |   0  ||   001  |   S02   |   3   |   1  |

接着只需将 Diff 依序累加就是期望的结果。

| UserNo | StoreNo | Order | Diff | Sum ||--------|---------|-------|------|-----||   001  |   S01   |   1   |   1  |  1  ||   001  |   S01   |   2   |   0  |  1  ||   001  |   S02   |   3   |   1  |  2  |

语法1

SELECT T.*,(    SELECT SUM(Diff)    FROM(        SELECT *, CASE WHEN (LAG(StoreNo) OVER(ORDER BY [Order]))=StoreNo THEN 0 ELSE 1 END Diff        FROM @BuyList AS S        WHERE S.UserNo=T.UserNo     ) AS S    WHERE S.[Order] <= T.[Order]) AS StoreOrderFROM @BuyList AS TORDER BY UserNo, [Order]

语法2，效能更好

;WITH CTE AS (    SELECT *, CASE WHEN LAG((UserNo + StoreNo)) OVER (ORDER BY UserNo, [Order])=(UserNo + StoreNo) THEN 0 ELSE 1 END Diff    FROM @BuyList)SELECT UserNo, StoreNo, ProductNo, [Order],    SUM(Diff)         OVER (PARTITION BY UserNo ORDER BY UserNo, [Order]) AS StoreOrderFROM CTEORDER BY UserNo, [Order]

结语:

最后附上线上测试的连结，感谢大家观看。

SQL1
SQL2

参考文章:

[SQL连续範围] 数字，日期连续範围