iT邦幫忙

5

[MS SQL] 連續問題 - 連填排序

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

題目:

大家在考高中時應該都有填過志願表,不過以前是用手寫的,現在科技越來越進步,選填志願也網路化。

現在有一張學生志願表如下:

StudentNo VolunteerOrder SchoolNo DepartmentNo
001 1 H01 D01
001 2 H01 D02
001 3 H02 D02
001 4 H02 D03
001 5 H02 D04
001 6 H01 D05
001 7 H01 D06
001 8 H01 D07
002 1 H01 D08
002 2 H02 D09
003 1 H01 D01
003 2 H02 D01
003 3 H02 D02

欄位說明:

  • StudentNo: 學生學號
  • VolunteerOrder: 志願序
  • SchoolNo: 學校代碼
  • DepartmentNo: 科系代碼

志願序為學生希望錄取志願的優先順序,不會重複也不會跳號。

期望的結果:

現在希望依照學生的志願序,排列出選填學校的校序,
校序的規則如下:

  1. 依志願序順序排列學校
  2. 同校連填為相同序位
  3. 同校不連填為不同序位

SchoolOrder 為校序,結果如下表:

StudentNo VolunteerOrder SchoolNo DepartmentNo SchoolOrder
001 1 H01 D01 1
001 2 H01 D02 1
001 3 H02 D02 2
001 4 H02 D03 2
001 5 H02 D04 2
001 6 H01 D05 3
001 7 H01 D06 3
001 8 H01 D07 3
002 1 H01 D08 1
002 2 H02 D09 2
003 1 H01 D01 1
003 2 H02 D01 2
003 3 H02 D02 2

測試資料:

DECLARE @StudentVolunteer TABLE
(
	StudentNo NVARCHAR(10),
	VolunteerOrder INT,
	SchoolNo NVARCHAR(10),
	DepartmentNo NVARCHAR(10)
)
INSERT INTO @StudentVolunteer
	(StudentNo, VolunteerOrder, SchoolNo, DepartmentNo)
VALUES
	('001', 1, 'H01', 'D01'),
	('001', 2, 'H01', 'D02'),
	('001', 3, 'H02', 'D02'),
	('001', 4, 'H02', 'D03'),
	('001', 5, 'H02', 'D04'),
	('001', 6, 'H01', 'D05'),
	('001', 7, 'H01', 'D06'),
	('001', 8, 'H01', 'D07'),
	('002', 1, 'H01', 'D08'),
	('002', 2, 'H02', 'D09'),
	('003', 1, 'H01', 'D01'),
	('003', 2, 'H02', 'D01'),
	('003', 3, 'H02', 'D02')

解法:

這題的解題要點在於,如何去比較上一筆和下一筆的學校有沒有一樣。

我的做法是這樣,首先利用 LAG 函數 比較該筆資料和上一筆的學校有沒有相同,如果相同就將標記 Diff 設為 0,不相同設為 1。

| StudentNo | VolunteerOrder | SchoolNorn | Diff |
|-----------|----------------|------------|------|
|       001 |              1 |        H01 |    1 |
|       001 |              2 |        H01 |    0 |
|       001 |              3 |        H02 |    1 |

接著只需將 Diff 依序累加就是期望的結果。

| StudentNo | VolunteerOrder | SchoolNorn | Diff | Sum |
|-----------|----------------|------------|------|-----|
|       001 |              1 |        H01 |    1 |   1 |
|       001 |              2 |        H01 |    0 |   1 |
|       001 |              3 |        H02 |    1 |   2 |

語法:

SELECT T.*,
(
    SELECT SUM(Diff)
    FROM(
        SELECT *, CASE WHEN (LAG(SchoolNo) OVER(ORDER BY VolunteerOrder))=SchoolNo THEN 0 ELSE 1 END Diff
        FROM @StudentVolunteer AS S
        WHERE S.StudentNo=T.StudentNo 
    ) AS S
    WHERE S.VolunteerOrder <= T.VolunteerOrder
) AS SchoolOrder
FROM @StudentVolunteer AS T
ORDER BY StudentNo, VolunteerOrder

另外提供一個強者同事的解法,效能更好

;WITH CTE AS 
(
    SELECT *, CASE WHEN CONCAT(StudentNo, SchoolNo)=LAG(CONCAT(StudentNo,     SchoolNo)) OVER (ORDER BY StudentNo, VolunteerOrder) THEN 0 ELSE 1 END Diff
    FROM @StudentVolunteer
)

SELECT StudentNo, VolunteerOrder, SchoolNo, DepartmentNo,
       SUM(Diff) OVER (PARTITION BY StudentNo ORDER BY StudentNo, VolunteerOrder) AS SchoolOrder
FROM CTE
ORDER BY StudentNo, VolunteerOrder

結語:

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

SQL1
SQL2


1 則留言

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

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

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

我要留言

立即登入留言