某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级

SELECT
    t2.author_id,
    author_level,
    MAX(lx_cnt) AS days_cnt
FROM
    (SELECT
        author_id,
        DATE_SUB(answer_date, INTERVAL rk DAY) AS fz_dt,
        COUNT(answer_date) AS lx_cnt
    FROM
        (SELECT DISTINCT
            author_id,
            answer_date,
            DENSE_RANK()OVER(PARTITION BY author_id ORDER BY answer_date) AS rk
        FROM answer_tb) AS t1
        GROUP BY author_id, fz_dt) AS t2
    JOIN author_tb AS t3
    ON t2.author_id = t3.author_id
GROUP BY t2.author_id, author_level
HAVING days_cnt >= 3
ORDER BY t2.author_id;

select c.author_id
,author_level
,max(days_cnt) days_cnt
from(
    select b.author_id
    ,author_level
    ,count(tim) days_cnt
    from
    (
        -- （当一组日期按照升序排序时，如果某几个日期是连续的，它们减去本身的DENSE RANK，会发现都是相减的结果都是同一天）
        select a.author_id
        ,author_level
        ,answer_date
        ,cast(day(answer_date) AS SIGNED) - dense_rank() over(partition by author_id order by answer_date) tim
        from(
            select
            t1.author_id
            ,author_level
            ,answer_date
            from
            author_tb t1 join answer_tb t2 using(author_id) -- using和on的作用一样，相当于简写
            group by 1,2,3 -- 虽然写的是123，但是本质上是对第三列进行去重 / 也可以替换为 去掉这行 在answer_date前加入distinct
            order by 1,3 -- 按照作者、日期进行升序排序
        ) a
    ) b
    group by 1,2,tim -- 核心在这里
    having days_cnt >= 3
) c
group by 1,2;

with t1 as (
    select distinct
        author_id,
        answer_date,
        dense_rank() over(partition by author_id order by answer_date) rn
    from answer_tb
),
t2 as (
    select
        author_id,
        count(answer_date) days_cnt
    from t1
    group by author_id,date_sub(answer_date,interval rn day)
)
select
    a.author_id,
    author_level,
    max(days_cnt) max_days
from t2 join author_tb a on t2.author_id = a.author_id
group by a.author_id,author_level
having max_days >= 3
order by a.author_id;

【场景】：连续记录

【分类】：分组条件查询、窗口函数

分析思路

难点：

1.如何表示连接？连续的表示：日期减去排序的值相等

2.记得先对日期、用户去重

（1）对日期、创作者进行去重

• [使用]：distinct

（2）统计日期、创作者、日期减去排序的值(连续签到)

连续的表示：日期减去排序的值相等；

• [使用]：date(in_time) - row_number()

（3）统计最大连续回答问题的天数大于等于3天的用户及其等级（若有多条符合条件的数据，按author_id升序排序）

• [条件]：最大连续回答问题的天数大于等于3天

• [使用]：group by 分组条件 [创作者；等级] having count(连续天数) >= 3 order by [创作者]

最终结果

select 查询结果 [创作者;等级;连续回答天数]
from 从哪张表中查询数据[多表]
group by 分组条件 [创作者;等级]
having 判断条件 [连续回答问题的天数大于等于3]
order by 对查询结果排序 [创作者升序];

求解代码

方法一：

with
    main as(
        #对日期、创作者进行去重
        select distinct
            answer_date,
            author_id
        from answer_tb
    )
    ,main1 as(
        #统计日期、创作者、日期减去排序的值(连续签到)
        select
            answer_date,
            author_id,
            answer_date - row_number() over(partition by author_id order by answer_date) as data
        from main
    )

#统计最大连续回答问题的天数大于等于3天的用户及其等级（若有多条符合条件的数据，按author_id升序排序）
select
    author_id,
    author_level,
    count(data) as days_cnt
from author_tb
join main1 using(author_id)
group by author_id,author_level having count(data) >= 3
order by author_id

方法二：

多表连接

#统计最大连续回答问题的天数大于等于3天的用户及其等级（若有多条符合条件的数据，按author_id升序排序）
select
    author_id,
    author_level,
    count(data) as days_cnt
from author_tb
join(
    #统计日期、创作者、日期减去排序的值(连续签到)
    select
        answer_date,
        author_id,
        answer_date - dense_rank() over(partition by author_id order by answer_date) as data
    from(
        #对日期、创作者进行去重
        select distinct
            answer_date,
            author_id
        from answer_tb
    ) main
) main1 using(author_id)
group by author_id,author_level having count(data) >= 3
order by author_id

• 本题目最大的难点就在于：如何判断几个日期是否连续的 —— 当一组日期按照升序排序时，如果某几个日期是连续的，它们减去本身的DENSE RANK，会发现都是相减的结果都是同一天
• 不得不说，不知道是哪位先发现这个切入角度的的，真的是个人才。要是哪位知道这种数学规律的出处，求分享！
• 另外，我发现大多数回答忽略了一个特殊情况（虽然题目实例中并没有考虑到）：若一个用户有10次连续登录超过三天的情况，则该用户会得出10组“同一天”日期及其对应的连续天数（10个大于等于3的随机整数）

SELECT author_id, author_level, MAX(continous_days) AS days_cnt
FROM
    (SELECT author_id, 
           author_level, 
           COUNT(author_id) AS continous_days
     FROM
         (SELECT DISTINCT a1.author_id, a2.author_level, answer_date,
                 DENSE_RANK() OVER (PARTITION BY a1.author_id ORDER BY answer_date ASC) AS ranking
          FROM answer_tb AS a1 LEFT OUTER JOIN author_tb AS a2
          ON a1.author_id = a2.author_id) AS t1
     GROUP BY author_id, author_level, DATE_SUB(answer_date, INTERVAL ranking DAY)
     HAVING continous_days >= 3) AS t2
GROUP BY author_id, author_level
ORDER BY author_id ASC;

with tab as(
select *,ROW_NUMBER()over(partition by author_id order by rk3) as days
from(
    SELECT answer_date,author_id
           ,date_sub(answer_date,INTERVAL rank()over(order by answer_date,author_id) day) as rk3
#         ,rank()over(order by answer_date,author_id) as rk2
   from(
       select answer_date,author_id
       FROM answer_tb
       group by answer_date,author_id   #去重
        )ta
    )tb   #还原签到起始日期
)#起始签到日期相同计数（连续签到计数）

select author_id,author_level,max(days) as days_cnt
from author_tb as at1
     join tab using(author_id)
where days>=3
group by author_id,author_level