计算连续签到天数

胡作飛为

newkid 发表于 2022-1-23 21:32
最后的partition by有个bug, 修改一下：with t as (    select '2022-01-01' as dt, 0 as sign_in from dua ...

还是得看大佬呀

ThomasZhou

with t as (
        select '2022-01-01' as dt, 1 as sign_in from dual union
        select '2022-01-02' as dt, 1 as sign_in from dual union
        select '2022-01-03' as dt, 1 as sign_in from dual union
        select '2022-01-04' as dt, 0 as sign_in from dual union
        select '2022-01-05' as dt, 1 as sign_in from dual union
        select '2022-01-06' as dt, 0 as sign_in from dual union
        select '2022-01-07' as dt, 1 as sign_in from dual union
        select '2022-01-08' as dt, 1 as sign_in from dual union
        select '2022-01-09' as dt, 1 as sign_in from dual union
        select '2022-01-10' as dt, 1 as sign_in from dual union
        select '2022-01-11' as dt, 1 as sign_in from dual union
        select '2022-01-12' as dt, 1 as sign_in from dual union
        select '2022-01-13' as dt, 0 as sign_in from dual union
        select '2022-01-14' as dt, 0 as sign_in from dual union
        select '2022-01-15' as dt, 1 as sign_in from dual union
        select '2022-01-16' as dt, 1 as sign_in from dual union
        select '2022-01-17' as dt, 1 as sign_in from dual union
        select '2022-01-18' as dt, 1 as sign_in from dual
    ),
    s as (select t.dt,t.sign_in,to_date(t.dt,'YYYY-MM-DD') - row_number() over (order by dt) as grp from t where sign_in=1),
    r as (select * from s union select dt,sign_in,to_date('1900-01-01','YYYY-MM-DD') as grp from t where sign_in=0)
    select dt,sign_in,decode(grp,to_date('1900-01-01','YYYY-MM-DD'),0,row_number() over (partition by grp order by dt)) as 连续签到 from r order by 1;

ThomasZhou

with t as (
        select '2022-01-01' as dt, 1 as sign_in from dual union
        select '2022-01-02' as dt, 1 as sign_in from dual union
        select '2022-01-03' as dt, 1 as sign_in from dual union
        select '2022-01-04' as dt, 0 as sign_in from dual union
        select '2022-01-05' as dt, 1 as sign_in from dual union
        select '2022-01-06' as dt, 0 as sign_in from dual union
        select '2022-01-07' as dt, 1 as sign_in from dual union
        select '2022-01-08' as dt, 1 as sign_in from dual union
        select '2022-01-09' as dt, 1 as sign_in from dual union
        select '2022-01-10' as dt, 1 as sign_in from dual union
        select '2022-01-11' as dt, 1 as sign_in from dual union
        select '2022-01-12' as dt, 1 as sign_in from dual union
        select '2022-01-13' as dt, 0 as sign_in from dual union
        select '2022-01-14' as dt, 0 as sign_in from dual union
        select '2022-01-15' as dt, 1 as sign_in from dual union
        select '2022-01-16' as dt, 1 as sign_in from dual union
        select '2022-01-17' as dt, 1 as sign_in from dual union
        select '2022-01-18' as dt, 1 as sign_in from dual
    ),
    s as (select t.dt,t.sign_in,to_date(t.dt,'YYYY-MM-DD') - sysdate - row_number() over (order by dt) as grp from t where sign_in=1),
    r as (select * from s union select dt,sign_in,0 as grp from t where sign_in=0)
    select dt,sign_in,decode(grp,0,0,row_number() over (partition by grp order by dt)) as 连续签到 from r order by 1;

wwwwwwgame

newkid 发表于 2022-1-23 09:28
如果是12C 以上，with t as (    select '2022-01-01' as dt, 1 as sign_in from dual union    select '20 ...

和目标貌似有点区别,我小改了一下

1. with t as (
   
2.     select '2022-01-01' as dt, 1 as sign_in from dual union
   
3.     select '2022-01-02' as dt, 1 as sign_in from dual union
   
4.     select '2022-01-03' as dt, 1 as sign_in from dual union
   
5.     select '2022-01-04' as dt, 0 as sign_in from dual union
   
6.     select '2022-01-05' as dt, 1 as sign_in from dual union
   
7.     select '2022-01-06' as dt, 0 as sign_in from dual union
   
8.     select '2022-01-07' as dt, 1 as sign_in from dual union
   
9.     select '2022-01-08' as dt, 1 as sign_in from dual union
   
10.     select '2022-01-09' as dt, 1 as sign_in from dual union
    
11.     select '2022-01-10' as dt, 1 as sign_in from dual union
    
12.     select '2022-01-11' as dt, 1 as sign_in from dual union
    
13.     select '2022-01-12' as dt, 1 as sign_in from dual union
    
14.     select '2022-01-13' as dt, 0 as sign_in from dual union
    
15.     select '2022-01-14' as dt, 0 as sign_in from dual union
    
16.     select '2022-01-15' as dt, 1 as sign_in from dual union
    
17.     select '2022-01-16' as dt, 1 as sign_in from dual union
    
18.     select '2022-01-17' as dt, 1 as sign_in from dual union
    
19.     select '2022-01-18' as dt, 1 as sign_in from dual
    
20. )
    
21. select dt,sign_in,cnt from t
    
22. match_recognize(
    
23.   -- partition by person_id
    
24.   order by dt
    
25.   measures  count(decode(sign_in,0,null,1)) cnt
    
26.   ALL ROWS PER MATCH
    
27.   pattern(a* b)
    
28.   define a as sign_in = next(sign_in)
    
29. );

复制代码

newkid

wwwwwwgame 发表于 2022-2-2 14:52
和目标貌似有点区别,我小改了一下

谢谢参与，送上我以前翻译的文档：
http://www.itpub.net/forum.php?m ... ;extra=#pid23315171

jihuyao

Let’s simulate match clause in procedural way.  With t0 as ..., t1 as ... order by dt, tmp(rn, ...) as (select 1 rn, signin*1 cnt ... from t1 where rn=1 union all select tmp.rn+1, decode(t1.signin, tmp.signin, tmp.cnt+Signin*1, signin+1), ... from tmp,t1 where t1.rn =tmp.rn+1) select * from tmp.

newkid

jihuyao 发表于 2022-2-3 21:40
Let’s simulate match clause in procedural way.  With t0 as ..., t1 as ... order by dt, tmp(rn, ...) ...

你是先排序，然后用递归WITH子查询逐行拼到结果里面去。当然这样是可以，但效率很低，每行都要做一个JOIN。用MODEL也可以做类似的事情，效率也是很低。
MATCH_RECOGNIZE 在这方面是无敌的。

xiaoyaren

with  t as(
    select '2022-01-01' as dt, 1 as sign_in from dual union
    select '2022-01-02' as dt, 1 as sign_in from dual union
    select '2022-01-03' as dt, 1 as sign_in from dual union
    select '2022-01-04' as dt, 0 as sign_in from dual union
    select '2022-01-05' as dt, 1 as sign_in from dual union
    select '2022-01-06' as dt, 0 as sign_in from dual union
    select '2022-01-07' as dt, 1 as sign_in from dual union
    select '2022-01-08' as dt, 1 as sign_in from dual union
    select '2022-01-09' as dt, 1 as sign_in from dual union
    select '2022-01-10' as dt, 1 as sign_in from dual union
    select '2022-01-11' as dt, 1 as sign_in from dual union
    select '2022-01-12' as dt, 1 as sign_in from dual union
    select '2022-01-13' as dt, 0 as sign_in from dual union
    select '2022-01-14' as dt, 0 as sign_in from dual union
    select '2022-01-15' as dt, 1 as sign_in from dual union
    select '2022-01-16' as dt, 1 as sign_in from dual union
    select '2022-01-17' as dt, 1 as sign_in from dual union
    select '2022-01-18' as dt, 1 as sign_in from dual
)
select dt,sign_in ,case  when rn>0 then rn else 0 end "连续签到"
from t left join (
select dt,r,datesub,row_number()over(partition by datesub order by dt) rn
from (
select dt,r,to_date(dt,'yyyy-mm-dd')-r as datesub -----date_add 只有sql中有
from (
    select dt,dense_rank()over(order by dt) r
    from t
    where sign_in=1
) temp ) t2) t3 using(dt)
order by dt;

chengccy2010

model:
with t as (
    select '2022-01-01' as dt, 1 as sign_in from dual union
    select '2022-01-02' as dt, 1 as sign_in from dual union
    select '2022-01-03' as dt, 1 as sign_in from dual union
    select '2022-01-04' as dt, 0 as sign_in from dual union
    select '2022-01-05' as dt, 1 as sign_in from dual union
    select '2022-01-06' as dt, 0 as sign_in from dual union
    select '2022-01-07' as dt, 1 as sign_in from dual union
    select '2022-01-08' as dt, 1 as sign_in from dual union
    select '2022-01-09' as dt, 1 as sign_in from dual union
    select '2022-01-10' as dt, 1 as sign_in from dual union
    select '2022-01-11' as dt, 1 as sign_in from dual union
    select '2022-01-12' as dt, 1 as sign_in from dual union
    select '2022-01-13' as dt, 0 as sign_in from dual union
    select '2022-01-14' as dt, 0 as sign_in from dual union
    select '2022-01-15' as dt, 1 as sign_in from dual union
    select '2022-01-16' as dt, 1 as sign_in from dual union
    select '2022-01-17' as dt, 1 as sign_in from dual union
    select '2022-01-18' as dt, 1 as sign_in from dual
)
select  dt as "日期",sign_in as "签到",lxqd as "连续签到" from t
model
dimension by (row_number()over(order by dt) as rn)
measures(dt,0 as lxqd,sign_in)
rules
(
lxqd[any]= case when sign_in[cv()]=0 then 0 else nvl(lxqd[cv()-1],0)+1 end
)

chengccy2010

递归
with t as (
    select '2022-01-01' as dt, 1 as sign_in from dual union
    select '2022-01-02' as dt, 1 as sign_in from dual union
    select '2022-01-03' as dt, 1 as sign_in from dual union
    select '2022-01-04' as dt, 0 as sign_in from dual union
    select '2022-01-05' as dt, 1 as sign_in from dual union
    select '2022-01-06' as dt, 0 as sign_in from dual union
    select '2022-01-07' as dt, 1 as sign_in from dual union
    select '2022-01-08' as dt, 1 as sign_in from dual union
    select '2022-01-09' as dt, 1 as sign_in from dual union
    select '2022-01-10' as dt, 1 as sign_in from dual union
    select '2022-01-11' as dt, 1 as sign_in from dual union
    select '2022-01-12' as dt, 1 as sign_in from dual union
    select '2022-01-13' as dt, 0 as sign_in from dual union
    select '2022-01-14' as dt, 0 as sign_in from dual union
    select '2022-01-15' as dt, 1 as sign_in from dual union
    select '2022-01-16' as dt, 1 as sign_in from dual union
    select '2022-01-17' as dt, 1 as sign_in from dual union
    select '2022-01-18' as dt, 1 as sign_in from dual
),
tmp as (
select dt, sign_in,row_number()over(order by dt) as rn from t ) ,
tmp1 (dt, sign_in,lxqd,rn) as
(select dt, sign_in ,sign_in as lxqd , rn from tmp
where rn=1
union all
select b.dt,b.sign_in, decode(b.sign_in,0,0, a.lxqd+1) as lxqd,b.rn from tmp1 a, tmp b
where a.rn+1=b.rn)
select  * from tmp1