hive求活跃天数

需求1：截止到当天的用户连续活跃的天数
需求2：某段时间内用户的连续活跃区间
需求3：某段时间用户的最大连续活跃天数

需求1：截止到当天的用户连续活跃的天数
思路：
按照日期倒序排序,另一个字段为与当天相差的天数，如果连续的话，则前两者之差为1
另一种办法参考需求二 取区间截止日为当天的

select
uid,
"当天",
sum(case when rn-day_num=1 then 1 else 0 end ) as active_days
from 
(
  select
    uid,
    dt,
    row_number() over (partition by uid order by dt desc) as rn,
    date_diff('当天',dt) as day_num 
  from 
    dwd_user_active
  where dt<='2021-01-14' and dt>='2021-01-01'
)
group by uid

--数据列举

--uid-----------dt----------
spark          2021-01-01
spark          2021-01-02
spark          2021-01-03
spark          2021-01-06
spark          2021-01-07
spark          2021-01-09
hive           2021-01-01
hive           2021-01-03
hive           2021-01-10
hive           2021-01-11
hive           2021-01-12
hive           2021-01-13

--子查询   以截止到14号为例
--uid-----------dt---------- --rn     --day_num
spark          2021-01-01     6         13 
spark          2021-01-02     5         12
spark          2021-01-03     4         11
spark          2021-01-06     3         7
spark          2021-01-07     2         6
spark          2021-01-09     1         5

hive           2021-01-01    7         13
hive           2021-01-03    6         11
hive           2021-01-10    5         4
hive           2021-01-11    4         3
hive           2021-01-12    3         2
hive           2021-01-13    2         1
hive           2021-01-14    1         0

--结果
spark       2021-01-14      0
hive        2021-01-14      5

需求2：某段时间内用户的连续活跃区间
思路：
按照日期正序排序，如果一段时间内，day-排序都等于某天则说明，这段时间是连续的

select 
uid,
date_sub(dt,rn) as sub,
min(dt) as "活跃开始时间",
count(1) as "活跃天数",
max(dt) ad "区间截止日"
from
( 
select
  uid,
  dt,
  row_number() over(partition by uid order by dt) as rn
from 
  dwd_user_active 
where dt<='2021-01-30' and dt>='2021-01-01'
) 
group by uid,date_sub(dt,rn)

--数据列举

--uid-----------dt----------
spark          2021-01-01
spark          2021-01-02
spark          2021-01-03
spark          2021-01-06
spark          2021-01-07
spark          2021-01-09
hive           2021-01-01
hive           2021-01-03
hive           2021-01-10
hive           2021-01-11
hive           2021-01-12
hive           2021-01-13

-- 中间子查询结果
--uid-----------dt---------- rn
spark          2021-01-01     1
spark          2021-01-02     2
spark          2021-01-03     3
spark          2021-01-06     4
spark          2021-01-07     5
spark          2021-01-09     6

hive           2021-01-01     1
hive           2021-01-03     2
hive           2021-01-10     3
hive           2021-01-11     4
hive           2021-01-12     5
hive           2021-01-13     6

--最后结果
--uid    --sub         --活跃开始区间   --活跃天数   -- 区间截止时间
spark   2020-12-31       2021-01-01      3            2021-01-03
spark   2021-01-02       2021-01-06      2            2021-01-07
spark   2021-01-03       2021-01-09      1            2021-01-09

hive    2020-12-31       2021-01-01      1            2021-01-01
hive    2021-01-01       2021-01-03      1            2021-01-03
hive    2021-01-07       2021-01-10      4            2021-01-13

需求3：某段时间用户的最大连续活跃天数
思路：在需求二的结果表中取活跃天数最大值