题型:连续登录,连续支付,最大连续登录,最大连续支付...
s思路:用日期-排序值得到基础日期,再对基础日期进行分组汇总
例子:求出用户最大连续支付天数
sql:
-- 4.对基础日期进行汇总统计
select user_id
, base_date as 相同日期
, count(1) as 连续登录天数
from (
-- 3.用支付日期-排序值得到基础日期
select user_id
, pay_day
, row_num
, dateadd(pay_day,-row_num,'dd') as base_date
from (
-- 2.按用户分组,支付日期升序得出排序值
SELECT user_id
, pay_day
, row_number() over (partition by user_id order by pay_day ) as row_num
from (
-- 1.求出用户支付的日期
select user_id
, datetrunc(from_unixtime(pay_time),'day') as pay_day
from yishou_data.all_fmys_order
group by user_id
, datetrunc(from_unixtime(pay_time),'day')
)
)
-- where user_id= 65
)
group by user_id
, base_date