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

有個需求要做找出連續出勤超過五天的人

下圖是Log表 

期望結果找出 如下表連續出勤超過五天的人

這是一個很經典的 [SQL連續範圍] 問題

 


解決連續範圍問題,運用到一個小小技巧

連續資料有個特性是一組連續範圍數值 - 有順序的值  結果是一樣的

如下圖  我們可發現有三組連續數字


DECLARE  @t  TABLE( a int );

INSERT into @t VALUES(1);
INSERT into @t VALUES(2);
INSERT into @t VALUES(3);
INSERT into @t VALUES(100);
INSERT into @t VALUES(101);
INSERT into @t VALUES(103);
INSERT into @t VALUES(104);
INSERT into @t VALUES(105);

+-----+
| a   |
+-----+ 
|   1 |
|   2 |
|   3 |
| 100 |
| 101 |
| 103 |
| 104 |
| 105 |
+-----+ 

分別是

  1. 1~3
  2. 100~101
  3. 103~105

第一步

多新增一組序號(RK)

  1. 將資料按照順序排巡(RK)

就可得到如下表


+-----+------+
| num | RK   |
+-----+------+
|   1 |    1 |
|   2 |    2 |
|   3 |    3 |
| 100 |    4 |
| 101 |    5 |
| 103 |    6 |
| 104 |    7 |
| 105 |    8 |
+-----+------+

 

有了上表我們就可以變魔術摟~~請看

利用(num - RK)  可以得到分組的資料(真是太神奇啦XD)

 



+-----+------+-----------+
| num | RK   | num - RK  |
+-----+------+-----------+
|   1 |    1 |    0      |
|   2 |    2 |    0      |
|   3 |    3 |    0      |
| 100 |    4 |   96      |
| 101 |    5 |   96      |
| 103 |    6 |   97      |
| 104 |    7 |   97      |
| 105 |    8 |   97      |
+-----+------+-----------+

 

有了上面概念後就可運用此腳本來取得分組資訊


SELECT MIN(T.num) fromNum,MAX( T.num) TONum
FROM
(

	SELECT  *,num - ROW_NUMBER() OVER(ORDER BY num) RKG
	FROM @t
) AS T 
GROUP BY RKG



Result:

fromNum     TONum
----------- -----------
1           3
100         101
103         105

 

 

回歸正題

題目中的連續資料是日期,所以我們無法像數字一樣直接相減排序。

假如可以按照日期來排出序號該有多好...

好在SQL_Server有提供一個關鍵字是 OVER 視窗函式

我們就可運用 ROW_NUMBER() + OVER  來排序

 

話不多說貼上程式碼XD

DECLARE @T TABLE 
(
	Name VARCHAR(50),
    Dates DATE
);

INSERT INTO @T VALUES
('Tom','2017/10/10'),
('Tom','2017/10/11'),
('Tom','2017/10/12'),
('Tom','2017/10/13'),
('Tom','2017/10/14'),
('AMY','2017/10/10'),
('AMY','2017/10/11'),
('AMY','2017/10/12');


SELECT Name,MIN(DATES) AS [StartDate],MAX(DATES) AS [EndDate]
FROM 
(
	SELECT *, ROW_NUMBER() OVER(ORDER BY [Name]) - ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY DATES) as rn
	FROM @T
)AS T
GROUP BY NAME
HAVING COUNT(RN) > 4


 

小結 :

查找連續範圍問題重點在如何把資料分群

只要分群後就只要Group by 搂^^