石頭的coding之路

提問出處: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函數取得間隔天數(產生編號)

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

 

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

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

 

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

    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 |

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

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

;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的解法 露露長)

 

---

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