=Start=
缘由：
用Hive SQL做一些异常分析时经常会面临的几个问题，比如：
- Hive SQL如何找出连续日期数据的较大波动？
- Hive SQL如何找出比前一段时间平均值要高的数据？
简单的说，就是拿各自当天的数据和自己/同组人员/同类人员昨天/前一段时间的数据相比较，发现波动较大的情况，然后再进行进一步分析。这个时候就需要用到 Hive SQL 的 lag/lead 窗口函数获取昨天/前一段时间的数据，然后借助 sum/avg/max/percentile 等集合统计函数计算出一个阈值，然后将当天的数据与阈值进行比较，从而发现一些超出阈值的异常情况。
正文：
参考解答：
LAG, LEAD 函数简单介绍
Lag和Lead分析函数可以**在同一次查询中**取出同一字段的前N行的数据（Lag）和后N行的数据（Lead）作为独立的列。
在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag和Lead函数的应用就显得尤为重要。当然，这种操作可以用表的自连接实现，但是LAG和LEAD与left join、rightjoin 等自连接相比，**效率更高，SQL更简洁**。下面我就对这两个函数做一个简单的介绍。
函数语法
lag(exp_str,offset,defval) over(partion by order by...)
lead(exp_str,offset,defval) over(partion by ..order by ...)
其中exp_str是字段名。
Offset是偏移量，即是上1个或上N个的值，假设当前行在表中排在第5行，则offset为3，则表示我们所要找的数据行就是表中的第2行
Defval默认值，当两个函数取上N/下N个值，当在表中从当前行位置向前数N行已经超出了表的范围时，lag函数将defval这个参数值作为函数的返回值，若没有指定默认值，则返回NULL，那么在数学运算中，总要给一个默认值才不会出错。
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值(与LAG相反)
第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
ROW_NUMBER() –从1开始，按照顺序，生成分组内记录的序列
RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位
DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位
提示：在使用分析函数的过程中，要特别注意ORDER BY子句，用的不恰当，统计出的结果就不是你所期望的。-- 原始表数据样例（可以基于更底层数据做统计得到）
select
user_name, date, action_desc, order_id_cnt, cnt
from
t1
;
u0001 20241010 查看详情 10 11
u0001 20241011 查看详情 13 21
u0001 20241012 查看详情 10 13
u0001 20241014 查看详情 19 49
u0001 20241015 查看详情 1 5
u0001 20241017 查看详情 18 26
u0001 20241018 查看详情 15 27
u0001 20241019 查看详情 41 83
u0001 20241020 查看详情 17 29
u0002 20241020 查看详情 1 1
u0002 20241022 查看详情 1 1-- 统计各账号在连续日期之间出现较大波动的日期和对应数据（当天数据和前一天/前一段时间数据做比较）
-- 需要注意的一点是，这里显示出来的日期是出现波动的前一天，而非波动当天，显示出来是当3月24日，但实际波动日期可能是3月26日，因为3月25日没有数据
-- 还有就是这个不会把连续几天出现高位的情况给显示出来，因为这里展示的只是有剧烈波动的情况
,abnormal1_spike_cnt as (
-- 操作量陡增-4倍-和自己昨天比
SELECT
user_name, action_desc, date
,'操作量陡增>4倍-和自己昨天比' as tag
,order_id_cnt
,lag(order_id_cnt) over(partition by user_name,action_desc order by date) as prev_oid_cnt
,lead(order_id_cnt) over(partition by user_name,action_desc order by date) as next_oid_cnt
,cnt
,lag(cnt) over(partition by user_name,action_desc order by date) as prev_cnt
,lead(cnt) over(partition by user_name,action_desc order by date) as next_cnt
FROM
t1
WHERE
action_desc like '%查看%'
and ((prev_oid_cnt > 39 or order_id_cnt > 199) and abs((order_id_cnt - prev_oid_cnt) / prev_oid_cnt) > 4)
)
,abnormal1_spike_cnt2 as (
-- 操作量陡增-4倍-和自己近期均值比
SELECT
user_name, action_desc, date
,'操作量陡增>4倍-和自己近期均值比' as tag
,order_id_cnt
,cnt
,avg(order_id_cnt) over(partition by user_name,action_desc order by date ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS prev_5d_oid_cnt_avg
FROM
t1
WHERE
action_desc like '%查看%'
and order_id_cnt > 39 and order_id_cnt > CAST(prev_5d_oid_cnt_avg AS BIGINT)*4
)
,abnormal1_spike_cnt3 as (
-- 操作量陡增-2倍-和同组均值比
SELECT
user_name, action_desc, date
,'操作量陡增>2倍-和同组均值比' as tag
,order_id_cnt
,cnt
,avg(order_id_cnt) over(partition by user_group,action_desc order by date ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS same_group_prev5d_avg
FROM
t1
WHERE
action_desc like '%查看%'
and order_id_cnt > 39 and order_id_cnt > CAST(same_group_prev5d_avg AS BIGINT)*2
)8 集合统计函数
8.1、个数统计函数: count
8.2、总和统计函数: sum
8.3、平均值统计函数: avg
8.4、最小值统计函数: min
8.5、最大值统计函数: max
8.6、非空集合总体变量函数: var_pop
8.7、非空集合样本变量函数: var_samp
8.8、总体标准偏离函数: stddev_pop
8.9、样本标准偏离函数: stddev_samp
8.10、中位数函数: percentile
8.11、中位数函数: percentile
8.12、近似中位数函数: percentile_approx
8.13、近似中位数函数: percentile_approx
8.14、直方图: histogram_numeric-- 快速造数据，用于测试 percentile 函数的效果和准确性是否符合预期
with t1 as (
select '2024-01-01' as dt_start, '2024-10-12' as dt_end
)
,t2 as (
select
date_add(to_date(dt_start), idx) as dt, idx
from t1
lateral view posexplode(split(space(datediff(t1.dt_end,t1.dt_start)),' ')) pi as idx, empty
)
select
min(idx) as idx_min
,avg(idx) as idx_avg
,max(idx) as idx_max
,percentile(idx,0.9) as idx_p90
,percentile(idx,0.99) as idx_p99
from
t2
/*
-- 0-285
0
142.5
285
256.5
282.15
*/
百分位数是一个表示数据分布的统计量，其中p百分位数是指小于该数值的数据占所有数据的比例为p%。因此，90%百分位数意味着90%的数据都小于或等于这个数值。参考链接：
Built-in Aggregate Functions (UDAF)
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-Built-inAggregateFunctions(UDAF)
Windowing function in Hive
https://stackoverflow.com/questions/55909029/windowing-function-in-hive
HIVE_HIVE函数_窗口函数_LAG()/LEAD() 详解
https://blog.csdn.net/u010003835/article/details/106739353
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=