iT邦幫忙

5

[MS SQL] 連續問題 - 依照購買順序排序店家

前陣子版上大大分享的題目 【SQL分享】 統計玩家遊戲連勝連敗的資料,覺得還蠻有趣的,看了下面的留言才知道,原來這種題型叫做 連續問題,剛好最近也有遇到類似的問題,分享給大家玩看看。

題目:

下方為使用者的購買清單。

欄位說明:

  • UserNo: 使用者代碼
  • StoreNo: 店家代碼
  • ProductNo: 商品代碼
  • Order: 購買順序
UserNo StoreNo ProductNo Order
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

現在希望依照使用者的購買順序排序店家,相同店家為同一序位,不過如果相同店家中間穿插其他店家則為不同序位。

期望的結果:

UserNo StoreNo ProductNo Order StoreOrder
001 S01 P01 1 1
001 S01 P02 2 1
001 S02 P02 3 2
001 S02 P03 4 2
001 S02 P04 5 2
001 S01 P05 6 3
001 S01 P06 7 3
001 S01 P07 8 3
002 S01 P08 1 1
002 S02 P09 2 2
003 S01 P01 1 1
003 S02 P01 2 2
003 S02 P02 3 2

測試資料:

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 StoreOrder
FROM @BuyList AS T
ORDER 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 StoreOrder
FROM CTE
ORDER BY UserNo, [Order]

結語:

最後附上線上測試的連結,感謝大家觀看。

SQL1
SQL2

參考文章:

[SQL連續範圍] 數字,日期連續範圍


1 則留言

1
神Q超人
iT邦新手 2 級 ‧ 2018-06-24 16:13:16

把diff欄位給SUM加總起來的方法也太聰明了!!
我剛剛還一直用子查詢去查上一筆VolunteerOrder-1比較和這筆的學校有沒有一樣,
想不到能直接用LAG這個函數/images/emoticon/emoticon16.gif
感謝大大分享了!

fysh711426 iT邦研究生 5 級‧ 2018-06-26 00:06:56 檢舉

哈哈哈,我也是偶然間發現有這個函數,
他還有一個兄弟 LEAD 可以找下一筆,
不過這兩個函數 SQL SERVER 2012 以上才有支援,
低版本的資料庫就要用大大說的子查詢或 JOIN 的方式去處理。

我要留言

立即登入留言