sql 連續活躍天數

1. 背景

已知數據集為：

目的：
計算每個uid的連續活躍天數，並且每一段活躍期內的開始時間和結束時間

2. 步驟

第一步：處理數據集
處理數據集，使其滿足每個uid每個日期只有一條數據。
第二步：以uid為主鍵，按照日期進行排序，計算row_number.

SELECT  uid
          ,`徵信查詢日期`
          ,ROW_NUMBER() OVER(PARTITION BY uid ORDER BY `徵信查詢日期` ASC) AS `rn`
          ,first_value(`徵信查詢日期`)over(PARTITION BY uid ORDER BY `徵信查詢日期` ASC) `fir`
          ,first_value(`徵信查詢日期`)over(PARTITION BY uid ORDER BY `徵信查詢日期` desc) `las`
FROM   input

兩個關鍵點：

1. 序號rn可以看做一直活躍的情況下，活躍日期最大值和活躍日期最小值之間的天數差。那麼，日期最大值與日期最小值之差如果不等於序號，就表明中間有不連續。
2. 用'徵信查詢日期' - rn 可以計算一列"關鍵列",連續時間段內，它的關鍵列值是一樣的

select *,DATE_SUB(`徵信查詢日期`,`rn`) as `關鍵列` from (
SELECT  uid
          ,`徵信查詢日期`
          ,ROW_NUMBER() OVER(PARTITION BY uid ORDER BY `徵信查詢日期` ASC) AS `rn`
          ,first_value(`徵信查詢日期`)over(PARTITION BY uid ORDER BY `徵信查詢日期` ASC) `fir`
          ,first_value(`徵信查詢日期`)over(PARTITION BY uid ORDER BY `徵信查詢日期` desc) `las`
FROM   input)

第三步：以uid和關鍵列作為主鍵。

select uid, `關鍵列`,count(*) as `連續活躍天數`, min(`徵信查詢日期`) as `活躍開始時間`, max(`徵信查詢日期`) as `活躍結束時間` from (
select *, DATE_SUB(`徵信查詢日期`,`rn`) as `關鍵列` from (
SELECT  uid
          ,`徵信查詢日期`
          ,ROW_NUMBER() OVER(PARTITION BY uid ORDER BY `徵信查詢日期` ASC) AS `rn`
          ,first_value(`徵信查詢日期`)over(PARTITION BY uid ORDER BY `徵信查詢日期` ASC) `fir`
          ,first_value(`徵信查詢日期`)over(PARTITION BY uid ORDER BY `徵信查詢日期` desc) `las`
FROM   input
  ) )group by uid, `關鍵列`