题解 | 连续签到领金币
连续签到领金币
https://www.nowcoder.com/practice/aef5adcef574468c82659e8911bb297f
with
-- 得到数字：以活动开始时间为基准，将签到日期转换为数字
t_num as (
select distinct
uid, date(in_time) dt
, row_number() over w i
, timestampdiff(day, "2021-07-07", date(in_time)) x
from tb_user_log
where artical_id = 0 and sign_in = 1 and date(in_time) between '2021-07-07' and '2021-10-31'
window w as (partition by uid, month(dt) order by date(in_time))
)
-- 构造分箱：计算数字与序号之差，连续区间和序号步长都为1，所以同一区间差一致
, t_bin as (
select uid, date_format(dt, '%Y%m') month, cast(i as signed) - cast(x as signed) bin , count(1) cnt
from t_num
group by uid, month, bin
)
-- 统计金币
select uid, month, sum(cnt div 7 * 15 + if(cnt % 7 >= 3, cnt % 7 + 2, cnt % 7)) coin
from t_bin
group by uid, month
order by uid, month
设数字序列为d、其子序列为各连续的数字序列di、行号序列为x. 元素位置为自变量，d 与x 分别为因变量，则di 与 x 变化率一致，是两条平行的线，di - x（序列内各元素相减）所得差值Δ 恒一致. 倘若数字有隔断，则表现为某线段di 较之di-1 有所上移，因此di - x = Δi 恒一致，di-1 - x = Δi-1 也恒一致，但Δi != Δi-1. 例如 [1, 2, 3, 5, 7, 8, 9] - [1, 2, 3, 4, 5, 6, 7] = [0, 0, 0, 1, 2, 2, 2]
所以，这题解法核心就是两步
- 得到数字序列：以活动初始日期d0 为基准，将签到日期di 映射为数字（di - d0）
- 构造分箱：令行号序列 - 数字序列，所得差值即可作为各连续区间（箱）的标记
查看17道真题和解析