10個SQL技巧之四：找到連續的沒有間隙的最大系列的日期

banq發表於2019-01-03
SQL

現在我們正在進行宣告性程式設計，我們不關心維護任何狀態和記憶體中的計數器。我們希望以線上分析SQL的形式表達這一點。即考慮這些資料：

| LOGIN_TIME | 
| --------------------- | 
| 2014-03-18 05:37:13 | 
| 2014-03-16 08:31:47 | 
| 2014-03-16 06:11:17 | 
| 2014-03-16 05:59:33 | 
| 2014-03-15 11:17:28 | 
| 2014-03-15 10:00:11 | 
| 2014-03-15 07:45:27 | 
| 2014-03-15 07:42:19 | 
| 2014-03-14 09:38:12 |


這沒有多大幫助。讓我們從時間戳中刪除小時。這很簡單：

SELECT DISTINCT
  cast(login_time AS DATE) AS login_date
FROM logins
WHERE user_id = :user_id


輸出：

| LOGIN_DATE | 
| ------------ | 
| 2014-03-18 | 
| 2014-03-16 | 
| 2014-03-15 | 
| 2014-03-14 |


現在，我們已經瞭解了視窗函式，讓我們只為每個日期新增一個簡單的行號：

SELECT
  login_date,
  row_number() OVER (ORDER BY login_date)
FROM login_dates


輸出：

| LOGIN_DATE | RN | 
| ------------ | ---- | 
| 2014-03-18 | 4 | 
| 2014-03-16 | 3 | 
| 2014-03-15 | 2 | 
| 2014-03-14 | 1 |


還很容易。現在，如果不是單獨選擇這些值，我們會減去它們會發生什麼？

SELECT
  login_date -
  row_number() OVER (ORDER BY login_date)
FROM login_dates

我們得到這樣的結果：

| LOGIN_DATE | RN | GRP | 
| ------------ | ---- | ------------ | 
| 2014-03-18 | 4 | 2014-03-14 | 
| 2014-03-16 | 3 | 2014-03-13 | 
| 2014-03-15 | 2 | 2014-03-13 | 
| 2014-03-14 | 1 | 2014-03-13 |


哇。有趣。所以，14 - 1 = 13,15 - 2 = 13,16 - 3 = 13，但是18 - 4 = 14.

這個動作代表一個簡單的判斷：
  1. ROW_NUMBER（）沒有間隙。這就是它的定義方式
  2. 但是，我們的資料確實如此

因此，當我們從“連續”系列的非連續日期中減去“無間隙”系列連續整數時，我們將為連續日期的每個“無間隙”子系列獲得相同的日期，我們將再次獲得新的有間隙的日期系列。

這意味著我們現在只需GROUP BY這個任意日期值：

SELECT
  min(login_date), max(login_date),
  max(login_date) - 
  min(login_date) + 1 AS length
FROM login_date_groups
GROUP BY grp
ORDER BY length DESC


我們已經完成了。已找到最大系列的連續日期，沒有間隙：

| MIN        | MAX        | LENGTH |
|------------|------------|--------|
| 2014-03-14 | 2014-03-16 |      3 |
| 2014-03-18 | 2014-03-18 |      1 |


完整查詢為：

WITH
  login_dates AS (
    SELECT DISTINCT cast(login_time AS DATE) login_date 
    FROM logins WHERE user_id = :user_id
  ),
  login_date_groups AS (
    SELECT
      login_date,
      login_date - row_number() OVER (ORDER BY login_date) AS grp
    FROM login_dates
  )
SELECT
  min(login_date), max(login_date), 
  max(login_date) - min(login_date) + 1 AS length
FROM login_date_groups
GROUP BY grp
ORDER BY length DESC


查詢本身確實非常簡單和優雅。你決不能以比這更精簡的方式實現一些命令式演算法。

相關文章