🚫 Ad Blocker Detected

Please disable your AD blocker to continue using this site. Ads help us keep the content free! please press keyboard F5 to refresh page after disabled AD blocker

請關閉廣告攔截器以繼續使用本網站。廣告有助於我們保證內容免費。謝謝! 關閉後請按 F5 刷新頁面

0%

Gaps and Islands problem (SQL) 連續範圍

前言:

SO 發現蠻多人有遇到 Gaps and Islands problem

count of last continuous inserted records based on date 

之前有跟大家分享過 解決連續範圍的思路  [SQL連續範圍] 數字,日期連續範圍

今天針對發問者實際例子來一步步 解決問題…

提問出處:count of last continuous inserted records based on date 


問題說明:

提問者希望可以獲得最近一次連續日期的次數

例如:

    user_id | point |   DateTime
    1       |   10  |   18-08-2018 17:15
    2       |   10  |   01-08-2018 17:15
    1       |   10  |   21-08-2018 17:15
    1       |   10  |   22-08-2018 17:15
    2       |   10  |   26-08-2018 17:15
    1       |   10  |   25-08-2018 17:15
    2       |   10  |   27-08-2018 17:15
    1       |   10  |   26-08-2018 17:15
    1       |   10  |   27-08-2018 17:15

有6筆資料是user_id = 1

希望取得user_id = 1最後一次連續日期數量是3

因為 這三筆是最近連續日期

27-08-2018
26-08-2018
25-08-2018

解決思路整理:

連續資料有個特性就是一組(連續範圍數值) - (基於某個條件順序產稱的數值)  結果是一樣的
  1. user_id 分群 並加上編號
  2. 因為要基於日期找尋連續日期,所以使用一個小技巧 先找尋每個 user_id 最小天數 (最大天數也可) ,之後使用datediff 函數取得差一天數來當數值

我會使用sql-server來解說(因為支援window function) XD


範例說明:

基於某個條件順序產稱的數值  :先在子查詢中取得每個user_id最小日期,以便後面使用datediff函數取得間隔天數(產生編號)

1
MIN(DateTime) over(partition by user_id order by DateTime )

連續範圍數值 : 我使用 Row_number 和 Window function 依照每個使用者給編號.

1
Row_number() over(partition by user_id order by DateTime)

之後使用diffdate函數 並將兩值相減取得

1
2
3
4
5
6
7
8
9
10
11
SELECT DateTime,datediff(day, MIN(DateTime) over(partition by user_id order by DateTime ),DateTime) - Row_number() over(partition by user_id order by DateTime)rn
FROM Table1
Where user_id = 1
| DateTime | rn |
|----------------------|----|
| 2018-08-18T17:15:00Z | -1 |
| 2018-08-21T17:15:00Z | 1 |
| 2018-08-22T17:15:00Z | 1 |
| 2018-08-25T17:15:00Z | 3 |
| 2018-08-26T17:15:00Z | 3 |
| 2018-08-27T17:15:00Z | 3 |

我們可以看到連續日期的分組已經出來了

有了這個連續編號 我們就可以直接取得我們要的結果了

1
2
3
4
5
6
7
8
9
;with cte as (
SELECT DateTime,datediff(day, MIN(DateTime) over(partition by user_id order by DateTime ),DateTime) - Row_number() over(partition by user_id order by DateTime)rn
FROM Table1
Where user_id = 1
)
SELECT TOP 1 count(*) cnt
FROM cte
group by rn
ORDER BY MAX(DateTime) desc

因為只要取得最近一筆連續日期資料 我們可以 ORDER BY MAX(DateTime) 且使用TOP 1來取得最新一筆的連續數量

SQLFiddle


小結:

使用window function後, 語法整個變得很簡單.(有興趣可以進SO連結看沒有使用window function的解法 露露長)

__此文作者__:Daniel Shih(石頭)
__此文地址__: https://isdaniel.github.io/sql-gaps-and-islands-problem/
__版權聲明__:本博客所有文章除特別聲明外,均採用 CC BY-NC-SA 3.0 TW 許可協議。轉載請註明出處!

如果本文對您幫助很大,可街口支付斗內鼓勵石頭^^