問題描述：

1） 連續(xù)問題：找出連續(xù)三天（或者連續(xù)幾天的啥啥啥）。

2） 間斷連續(xù)：統(tǒng)計各用戶連續(xù)登錄最長天數(shù)，間斷一天也算連續(xù)，比如1、3、4、6也算登陸了6天

問題分析：

這兩類問題，都是涉及到連續(xù)，只不過連續(xù)類問題只要判斷出來相鄰的時間是和當(dāng)前行的時間挨著即可，而間斷連續(xù)，是還可能在期間有中斷現(xiàn)象，并且即使中斷了，也要算在連續(xù)的時間內(nèi)，比如1、3、4、6號登錄，但是算是連續(xù)登陸了6天。所以只要中斷不超過兩天，也能判斷出來是連續(xù)的，并且在計算連續(xù)天數(shù)的時候還要算上這個時間。

解決思路：

1）針對連續(xù)問題：

可以通過對每一個用戶id進(jìn)行開窗，然后排序里面的時間，加上row_number()，這樣對每一個日期都會有一個排序，之后將日期減去排序序號，只要這個值是一樣的，就說明這幾天是連續(xù)的。之后只要根據(jù)用戶id，以及這個減去過排序序號的差值diff進(jìn)行一起group by分組，然后通過having count(*) >= 3，這個篩選條件過濾出的結(jié)果就是最終連續(xù)登錄了三天的用戶

2）針對間斷連續(xù)問題：

? 也是對每一個用戶id進(jìn)行開窗，只不過這次是針對當(dāng)前行，用lag（）取上一行數(shù)據(jù)?！颈仨氁瓷弦恍?，而不是看下一行，因為如果當(dāng)前行開始變化，當(dāng)前行需要記錄，如果看下一行，那看的是下一行有沒有變化，是不是不連續(xù)了之類的，下一行的情況無法記錄】。之后用當(dāng)天日期對這個用開窗函數(shù)得到的上一天日期進(jìn)行相減，如果大于2，就說明相鄰兩天的日期是超過了2天，按照定義不算是“連續(xù)”，所以增加個標(biāo)記，記錄為1，這樣每一行都會有一個標(biāo)記，如果是連續(xù)的，那這一行后面就標(biāo)記的0。

? 接下來就是關(guān)鍵的步驟，根據(jù)**用戶id進(jìn)行開窗求sum聚合group by，然后從最開始的一行，到當(dāng)前行，進(jìn)行求sum。**這樣的話，原本是1、0、0、1、0、1、0，比如是這樣的標(biāo)記序列，每到一個1就說明從這天開始，就不是間斷一天連續(xù)了，然后從最開始一行到當(dāng)前行求sum的結(jié)果就是，1、1、1、2、2、3、3。這就很好的劃分出來三段“間斷連續(xù)時間”。

? 然后根據(jù)用戶id，以及這個標(biāo)記進(jìn)行分組聚合group by，求出來這個期間日期的max，以及日期的min，相減，別忘了再加1【日期計算時候需要注意的】，就是這個間斷連續(xù)的天數(shù)了。

總結(jié)：

? 所以總的來說，要么就是用開窗函數(shù)對日期進(jìn)行排序，然后當(dāng)前日期減去排序序號，去看日期一樣的就是連續(xù)操作的記錄

? 當(dāng)然，如果想用統(tǒng)計間斷連續(xù)的方法去看連續(xù)n天的問題，也可以，就是取一個lag()，或者取一個lead()，進(jìn)行開窗，不用加標(biāo)記，直接一個where過濾diff=2，這就是統(tǒng)計連續(xù)3天的用戶，不過同一個用戶可能會有多個這個符合條件的，最后需要去重count(distinct(user_id)) 。如果是為了找到連續(xù)3天登錄過的用戶

? 要么就是利用開窗函數(shù)取得上一行l(wèi)ag()日期，兩個日期相減，去判斷是否符合條件，接下來加一個標(biāo)記，不符合的設(shè)為1，用作記錄改變的時間點，其余標(biāo)0。然后開窗函數(shù)對標(biāo)記進(jìn)行求和，是從第一條到當(dāng)前條，這樣就可以看出來一個用戶id，劃分了幾個連續(xù)時間段，然后再根據(jù)需求去具體的求。

然后上代碼：

1） 連續(xù)：

文章來源地址http://www.zghlxwxcb.cn/news/detail-843763.html

2） 間斷連續(xù)

select
    user_id,
    max(recent_days) max_recent_days  --求出每個用戶最大的連續(xù)天數(shù)
from
(
    select
        user_id,
        user_flag,
        datediff(max(login_date),min(login_date)) + 1 recent_days --按照分組求每個用戶每次連續(xù)的天數(shù)(記得加1)
    from
    (
        select
            user_id,
            login_date,
            lag1_date,
            concat(user_id,'_',flag) user_flag --拼接用戶和標(biāo)簽分組
        from
        (
            select
                user_id,
                login_date,
                lag1_date,
                sum(if(datediff(login_date,lag1_date)>2,1,0)) over(partition by user_id order by login_date) flag  --獲取大于2的標(biāo)簽
            from
            (
                select
                    user_id,
                    login_date,
                    lag(login_date,1,'1970-01-01') over(partition by user_id order by login_date) lag1_date  --獲取上一次登錄日期
                from
                (
                    select
                        user_id,
                        date_format(login_datetime,'yyyy-MM-dd') login_date
                    from login_events
                    group by user_id,date_format(login_datetime,'yyyy-MM-dd')  --按照用戶和日期去重
                )t1
            )t2
        )t3
    )t4
    group by user_id,user_flag
)t5
group by user_id;
      )t2
        )t3
    )t4
    group by user_id,user_flag
)t5
group by user_id;

到了這里，關(guān)于Hive SQL必刷練習(xí)題：連續(xù)問題 & 間斷連續(xù)（*****）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！