在前面一篇里面，演算法-一個經典sql 題和一個Java演算法題大資料開發-Hive-常用日期函式&&日期連續題sql套路有一道經典sql題目，解決連續問題，本文繼續總結關于連續性的套路，來自于實際生產專案的問題，本文略去其他不重要資訊欄位，來從更深地層次解決問題，由于在生產中，常常是理解需求，轉換需求，讓需求拆解為更通用的處理方式，同時從解決難題中提高自己，解決這些問題，也是快樂的源泉之一??，

1.生產問題原始描述

關于給用戶打標簽的問題，對于標簽的資料源在實際環境下幾乎是覆寫全部門甚至全公司的，有的資料源本身是明細方式，有的是原始ods日志方式，有的是維度表，有的是名單表，對于需求方來說要的是快速迭代上線標簽，看需求或者資料倉庫劃分或者處理麻煩程度，往往會采用不同的處理方式，另外對于標簽的應用方來說一定會有人查值，值查人，資產分析等，本文的例子就一個名單表，什么是名單表呢，就是圈客的結果，比如滿足某條件某條件的一堆id，標簽天然就是為滿足圈客用的，但是原始資料它是圈客的結果，所以同時為了使得標簽在業務方使用起來更方便，所以把名單表加工成標簽，那么需求就確定了，從名單表里面加工出標簽，

原始表如下

解釋，為了說明更簡單，假設日期型別都用數字來表示，每天會有一群去重id，下一天的id更這一天的id必然是兩個交叉圓的形式，即既有多出來的，又有少出來的，又有重疊的，

現在需求就是，

我要篩選任意時間段的用戶，滿足是這段時間首次有效，或者無效，或者存量有效，分別對應描述里面的多出來，少了重合的每天

至于有效的定義就是，前一天名單沒有這個id，下一天有這個id，這就是首次有效，但是對于名單這種資料來說，一定會有有效，無效，有效，無效，這種,所以標簽的每天分片里面會有昨天的id加今天的id.

2.生產問題分析拆解

針對上面分析到的問題，有效無效這種狀態不能是最近一次，或者首次的有效，因為會有區段的概念，比如某歷史一段時間，只要首次出現在這段時間的，而且多天重合的，所以針對這個問題，我這邊處理成每天相對于前一天的有效狀態，分別為0，1，2，也即對應無效，首次有效，存量有效，另外對應每一段的資訊，

所以這樣問題就化解為，先求有效狀態，然后再在有效狀態的基礎上，加工出最早時間和最晚時間，為了篩選方便，我另外加工出兩個冗余子屬性欄位，是否首次有效和是否當前有效,對于有效無效，如果用視窗函式，找到同一個id的前一個日期，如果是日期的前一天，即表示2，存量有效，如果不為前一天則為增量有效，但是這種方式沒法把無效的取出來，所以我后面想到的是用full join來實作，這樣即可以把前一天后一天的資料都拿到，

3.問題解決

-- 對于有效無效的加工
with t3 as (
select id,dt,CASE
                  WHEN a.did IS NOT NULL
                          AND b.did IS NULL THEN 1
                       WHEN a.did IS NULL
                           AND b.did IS NOT NULL THEN 0
                     WHEN a.did IS NOT NULL
                           AND b.did IS NOT NULL THEN 2
                           end as is_valid
 from t1 a full join b on a.id = b.id
 )

-- 最早時間12，最晚時間14，是否首次有效，是否當前有效
select id,min(dt) as min_dt,max(dt) as max_dt, gid from (
select id,dt,is_valid, (dt - row_number() over (partition by id order by dt)) gid from t3 where is_valid != 0
) tmp group by id,gid

4.總結套路

4.1.首先思考可以用到的日期函式

datediff, date_sub/date_add

4.2.連續日期

連續問題都會用到一個排名函式，但是排名函式的值是數值，要與日期的連續性做到映射，才方便分組，比如可以把日期映射到連續數字，或者數字映射到連續日期，實作這兩個的操作就是通過前面的datedff 和 date_sub組合，原理就是日期與日期相減即可得到連續整數，整數隨便與某個日期做相減即可得到連續的日期,其中date_sub可以是反向排序得到連續日期，