=Start=

缘由：

数据运营人员常常会需要查找活跃用户名单，而活跃用户很多情况下被定义为连续在线n天及以上的用户。一方面我们可以根据n的值直接进行筛选；更具一般性地，就要求我们去求取每个用户某段时间内的最大连续在线天数了。

SQL求连续在线天数是一个非常经典的问题，该问题在不考虑计算成本下有非常多的解法。该问题也是我在面试实习生时最喜欢深入问的一个问题，在引导一个候选人去完成这个问题的过程中可以看出其对SQL的理解深度以及其思维是否灵敏。

做异常分析时需要对一些特殊人群做筛选，其中有一类就是连续操作日期很长（比如超过10天）的人员，要么是非常热爱工作，要么是可能有点异常了（比如写程序/脚本做操作、账号共用、账号被盗、……），通过Hive SQL找出连续操作天数超长的一批账号进行抽样分析是一个入手点。除了连续操作天数较大之外，还有操作量陡增的情况也需要及时发现和关注，比如：

1. Hive SQL如何找出最大的连续操作天数？
2. Hive SQL如何找出连续日期数据之间的较大波动？
3. Hive SQL如何找出比前7天平均值要高的数据？

正文：

参考解答：

-- 原始表数据样例
select
user_name, date, cnt
from
t1
;

u0001    20241010    11
u0001    20241011    21
u0001    20241012    13
u0001    20241014    49
u0001    20241015    5
u0001    20241017    26
u0001    20241018    27
u0001    20241019    83
u0001    20241020    29
u0002    20241020    1
u0002    20241022    1

-- 统计各账号的连续操作天数
SELECT
user_name
,cnt1 as consecutive_days
,date_min as date_start
,date_max as date_end
,cnt_max, cnt_avg ,cnt_p90
FROM
(SELECT
*
,row_number() over(partition by user_name order by cnt1 desc) as rk
FROM
    (SELECT
    user_name, day_diff
    ,count(1) as cnt1
    ,min(date) as date_min
    ,max(date) as date_max
    ,max(cnt) as cnt_max
    ,avg(cnt) as cnt_avg
    ,percentile_approx(cnt,0.90) as cnt_p90
    FROM
        (SELECT
        user_name, date
        --因为这里 date 字段是 yyyymmdd 格式，而非 yyyy-mm-dd 格式的，因此在给 date_sub 处理之前需要先格式化
        ,date_sub(concat(substr(date,1,4),'-',substr(date,5,2),'-',substr(date,7,2)),rn) as day_diff
        ,cnt
        FROM
            (SELECT
            user_name, date
            ,row_number() over(partition by user_name order by date) as rn
            ,cnt
            FROM
            t1
            GROUP BY user_name, date
            )m1 --先对各账号基于操作日期进行排序并添加序号
        )m2 --再借助 date_sub 函数用当前的日期和序号计算出一个“基准”日期
        GROUP BY user_name, day_diff
    )m3 --对同一“基准”日期的数据(user_name,day_diff)统计出次数和起/止日期，顺带统计出一些数值列的最大值/平均值/百分位数信息
)m4 --按照连续次数进行排序并添加序号，方便后面进行过滤/统计
WHERE
cnt1 > 8
-- or rk = 1

参考链接：

Hive计算最大连续登陆天数
https://www.cnblogs.com/data-magnifier/p/15506609.html

hive 时间最大值 hive连续最大天数
https://blog.51cto.com/u_16213658/7128503

Hive_HQL_复杂SQL_连续发单天数
https://blog.csdn.net/u010003835/article/details/106739031
https://blog.csdn.net/Adrian_Wang/article/details/89791948

HIVE_HIVE函数_窗口函数_LAG()/LEAD() 详解
https://blog.csdn.net/u010003835/article/details/106739353

mysql/hive求最大连续天数(间隔1天也算连续)
https://blog.csdn.net/weixin_42430074/article/details/121967436

Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE
http://lxw1234.com/archives/2015/04/190.htm

标签：hive分析函数
http://lxw1234.com/archives/tag/hive-window-functions

Hive 分析函数lead、lag实例应用
https://developer.aliyun.com/article/903855

Hive 分析函数lead、lag实例应用
https://blog.csdn.net/kent7306/article/details/50441967

hive开窗函数-lag和lead函数
https://cloud.tencent.com/developer/article/2377588

通义千问
https://tongyi.aliyun.com/qianwen/

=END=