實作用SQL查詢連續發文天數/連續登入天數

當月最長連續發文天數:
//臨時：id_time_table:
select distinct app_id, from_unixtime(create_date_time, 'yyyy-MM-dd') fawen_date
from bjh_ods_task_manage_df
where event_day='$baseDay' 
and object_type=2 
and from_unixtime(create_time,'yyyyMMdd')>='$firstDay'
and from_unixtime(create_time,'yyyyMMdd')<='$lastDay'


//最終：longest_days_table
select app_id, max(cnt) longest_continuous_days
from 
(
    select app_id, group_no, count(*) cnt
    from 
    (
        select app_id, fawen_date,(day(fawen_date) - ROW_NUMBER() OVER (partition by app_id order by fawen_date asc)) group_no 
        from id_time_table
    ) T1
    group by app_id, group_no
) T2
group by app_id