問題引入
最近經(jīng)常碰到這樣的問題，每天每個城市播放最多的10首歌，某月每支股票連續(xù)下跌/上漲的最大天數(shù)烛亦，用戶連續(xù)活躍的最大天數(shù)，初步看起來都和分析函數(shù)相關(guān)懂拾，考驗(yàn)邏輯思維和寫復(fù)雜SQL的能力煤禽。
以用戶連續(xù)活躍的最大天數(shù)為例
以O(shè)racle的分析函數(shù)語法說明，首先模擬一些用戶活躍的數(shù)據(jù)岖赋，
-- 建表語句
DROP TABLE sigin;
create table sigin(
userid int,
sigindate varchar2(20)
);
-- 模擬數(shù)據(jù)插入
insert into sigin values(1,'2017-01-01');
insert into sigin values(1,'2017-01-02');
insert into sigin values(1,'2017-01-03');
insert into sigin values(1,'2017-01-04');
insert into sigin values(2,'2017-01-01');
insert into sigin values(2,'2017-01-02');
insert into sigin values(2,'2017-01-03');
insert into sigin values(1,'2017-01-10');
insert into sigin values(2,'2017-01-10');
insert into sigin values(1,'2017-01-11');
insert into sigin values(2,'2017-01-11');
insert into sigin values(1,'2017-01-12');
insert into sigin values(2,'2017-01-12');
commit;
大體思路如下：
- 首先根據(jù)userid進(jìn)行分組檬果，將用戶所有活躍的記錄依次按照時間排序并標(biāo)上序號。
- 根據(jù)時間有序的特點(diǎn)唐断，將所有時間減去它對應(yīng)的序號选脊，獲取連續(xù)活躍時間唯一的時間點(diǎn)。如
2017-01-01 1
2017-01-02 2
2017-01-03 3
時間減去序號脸甘，得唯一時間2016-12-31恳啥。
- 根據(jù)userid和這個連續(xù)活躍時間唯一的時間點(diǎn)進(jìn)行分組，計算連續(xù)活躍天數(shù)丹诀。
-- 每個用戶的幾段連續(xù)活躍的天數(shù)
select
userid,
to_date(sigindate,'yyyy-mm-dd')-sigin_rank as date_rank,
count(1) as sigincount
from
(
select
userid,
sigindate,
row_number() over(partition by userid order by sigindate) as sigin_rank
from sigin
) c group by userid, to_date(sigindate,'yyyy-mm-dd')-sigin_rank;
得到結(jié)果1如下钝的，
USERID DATE_RANK SIGINCOUNT
1 2017/1/5 3
2 2017/1/6 3
1 2016/12/31 4
2 2016/12/31 3
上述方法可以找到每個用戶的連續(xù)活躍天數(shù)翁垂，但用戶中間有中斷時程序就無法滿足，一個用戶出現(xiàn)了多條記錄硝桩，分別為用戶的多段連續(xù)活躍所產(chǎn)生沿猜。
我們最終的目標(biāo)是得到用戶連續(xù)活躍的最大天數(shù)，可利用上述方法所得到的結(jié)果碗脊，在外面再嵌套一層啼肩，針對userid進(jìn)行g(shù)roup by，得到每個用戶的最大活躍天數(shù)望薄。
select d.userid, Max(d.sigincount) as max_sigincount from (
select
userid,
to_date(sigindate,'yyyy-mm-dd')-sigin_rank as date_rank,
count(1) as sigincount
from
(
select
userid,sigindate,row_number() over(partition by userid order by sigindate) as sigin_rank
from sigin
) c group by userid ,to_date(sigindate,'yyyy-mm-dd')-sigin_rank
) d group by d.userid
得到結(jié)果2如下疟游，
USERID MAX_SIGINCOUNT
1 4
2 3
如果還需要得到用戶連續(xù)活躍最大天數(shù)中這一段的首次活躍時間，可以把以上兩個結(jié)果進(jìn)行關(guān)聯(lián)得到痕支。
-- 每個用戶連續(xù)活躍的最大天數(shù)和連續(xù)活躍的第一天的時間
select f.userid,g.date_rank+1,f.max_sigincount from (
select d.userid, Max(d.sigincount) as max_sigincount from (
select
userid,
to_date(sigindate,'yyyy-mm-dd')-sigin_rank as date_rank,
count(1) as sigincount
from
(
select
userid,sigindate,row_number() over(partition by userid order by sigindate) as sigin_rank
from sigin
) c group by userid ,to_date(sigindate,'yyyy-mm-dd')-sigin_rank
) d group by d.userid
) f inner join (
select
userid,
to_date(sigindate,'yyyy-mm-dd')-sigin_rank as date_rank,
count(1) as sigincount
from
(
select
userid,sigindate,row_number() over(partition by userid order by sigindate) as sigin_rank
from sigin
) c group by userid ,to_date(sigindate,'yyyy-mm-dd')-sigin_rank
) g on f.userid = g.userid and f.max_sigincount = g.sigincount;
得到結(jié)果3如下，
USERID G.DATE_RANK+1 MAX_SIGINCOUNT
2 2017/1/7 3
1 2017/1/1 4
2 2017/1/1 3
結(jié)果3還存在一個問題蛮原，如果用戶有兩段連續(xù)活躍的天數(shù)相同且最大卧须，則第二段連續(xù)活躍的首次活躍時間是不對的，這個問題怎么解決呢儒陨？歡迎留言你的解決方案花嘶。
本文首發(fā)于steem，感謝閱讀蹦漠，轉(zhuǎn)載請注明椭员。
微信公眾號「padluo」，分享數(shù)據(jù)科學(xué)家的自我修養(yǎng)笛园，既然遇見隘击，不如一起成長。
讀者交流電報群
知識星球交流群
