=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=
《 “Hive SQL如何找出连续日期数据之间的较大波动” 》 有 4 条评论
`
1. 统计各账号的连续最大登录天数，这个是为了寻找嫌疑人的，因为正常人不会长期连续多天工作
2. 统计各账号在连续日期之间出现较大波动的日期和对应数据，这个是为了寻找嫌疑人以及具体的异常指标，因为正常情况下操作数量不会出现较大的波动
3. 统计比值异常 cnt / sense_op_cnt < 1.5 这个指标是在刚才的统计圈定异常人员、日期区间之后，（结合之前分析正常大量操作账号的行为模式）新想到的一个指标，更直接
–比昨天高3倍
–比前5天均值高3倍
–比前15天p90高3倍
–比同组高2倍（这个需要部门信息是准确的，以及不存在那种偏平化管理的情况，否则可用性不高）
–有晚间操作（and concat_ws(',',hour_array) rlike '23|00|01'）
–仅浏览的操作占比较高（和常规的操作行为模式不一致）
`
Hive 窗口函数
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics
`
— 对整体做sum，不做任何分区
SUM(b) OVER ()
— 基于c列分区然后做sum
SUM(b) OVER (PARTITION BY c)
— 前3行至当前行
SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM T;
— 前3行至后3行
SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)
FROM T;
`
虚假诉讼罪主观恶意的认定
http://legal.people.com.cn/n1/2020/1015/c42510-31892952.html
`
所谓“恶意”，应该是指行为人在主观上的一种认知状态，即明知自己的行为会侵犯他人的合法权益，或者明知自己的行为是违背法律或者违背公序良俗的，却故意为之。民法上与“恶意”相对的概念是“善意”。
所谓“善意”，司法实践中通常是以是否知道来界定，即对虚假行为（表象）之真相明明知道或者应当知道的，则非为善意。
所谓“应当知道”，是指第三人负有注意义务，且依据交易经验、交易常识等进行综合判断之下应当知道，属于盖然性的知悉。
恶意虽然属于头脑中的意识，但却并非虚无缥缈、不可捉摸的存在，而是必然表现于具体的行为之中。欲确认当事人是否具有主观恶意，不是靠主观臆断，而是要对原告的涉案行为进行全面的审查与考量，精心辨别其是否“以捏造的事实提起民事诉讼”。不应仅仅从表面上审查，认为有借条、有公章就可以确认不是虚假诉讼。如某市查处的多起虚假诉讼案件，原告均持有某公司原法人代表某甲的借条，并加盖了公司印章，但是，这几笔数额很大的款项并未进入某公司对公账户，也没有相应的货物购置入库，而某公司原法人代表某甲却长期居留外地不归。后经公安机关依法传唤，某甲归案后承认自己侵占了这几笔款项，然后唆使几位债主到法院提起民事诉讼。几位原告也承认是某甲个人借钱后逾期未还，后来某甲在借条上加盖了某公司更名前的旧印章，唆使几位债主通过民事诉讼向某公司追讨。法官遂对几位原告分别当庭训诫，几位原告先后撤回起诉。司法实践中发生的虚假诉讼案件，有借条且盖有公司印章的不在少数。为辨明是非，应当对双方当事人的陈述和提供的证据进行全面的、辩证的分析，由表及里，由浅入深，去粗取精，去伪存真，务必准确把握案件事实。对证据有疑点的案件，应当对相关证据进行司法鉴定。特别是被告对原告提供的证据有疑点，请求对证据进行司法鉴定时，应当果断地依照规定程序进行司法鉴定。对于债权债务纠纷，司法人员还应当到相关银行调取当事人的银行流水，查清涉案资金走向。一旦查明涉案人员通过虚假流水虚构债权债务关系的，即应果断地认定行为人具有主观恶意。
`
从4个维度判定科研不端行为的“灰色地带”
http://www.imr.cas.cn/zt/kycx/alqs_kycx/202205/t20220517_6449232.html
`
有学者认为：对于科研不端的认定存在“灰色地带”， 这在一定程度上反映了学术界在科研不端治理实践中的困惑——在学术规范和学术不端之间，有一个复杂、有时甚至是困难的主观判断空间。
近年来，一些科技主管部门和科研机构在发布重大科研不端案件的公告中频频使用：“非主观造假”、“图片误用”、“不规范”和“有缺陷”等定性词语，受到社会公众的诟病和广大科研人员窃笑，正是上述“灰色地带”“复杂和困难”的客观表现。
笔者认为：可以从四个维度来分析学术不端行为的构成要件，通过适当的定性加权，协助我们对“灰色地带”做出理解、对问题性质做出判断、为适当处理提供依据。这四个维度是：主观故意性、规范偏离度、侵权程度和社会危害程度。
一、主观故意性。科研不端是严重背离科学共同体公认准则的行为。主观故意是判定科研不端的基本要件。对于恶意抄袭、编造和操纵数据或其他违反科研行政机构三令五申严责禁止行为的，可界定为恶意故意。如“论文工厂”的委托人即属于恶意故意。
此外，对于有科研失信行为记录的科研人员再次或多次明显违反相关规范的，也可酌情界定为恶意故意。对于在署名排序、论文引文、图片美容、图片重复使用等少量不规范行为，如不影响论文的基本结论且属于初次违规，可界定为低恶意程度的主观故意；未参加实际科研工作，在不知情的情况下被他人论文署名，事后却将相关论文列入自己工作业绩的，可以看作是一种轻微恶意程度的主观故意。不能确定具有主观故意的违规行为，如疏忽性错误等，不能判定为科研不端。
二、规范偏离度。科研不端是严重背离科学共同体公认准则的行为。“严重背离”的表述隐含对背离准则的行为有一个“程度”的主观判断，这个程度难以量化，取决于学术共同体对该行为的厌恶程度。
实践中，授权处理的委员会成员对于科技奖励报奖人排名争议、中英文一稿两投、引文中未引述标志性文献等行为的厌恶程度要远小于对实验数据的恶意操纵。
从性质上说，故意忽略掉一个可能影响统计曲线优美的个别数据和无中生有地编造大量数据，均属于数据操纵，但前者对规范的偏离度要相对低一些。
三、侵权程度。科研不端一般都会产生相应的危害。其中常见的危害是对他人权力的侵害。科研成果的抄袭剽窃是典型的侵犯他人权力的行为，其本质是将他人的劳动成果窃为己有。这种侵权行为如果涉及巨大的商业利益，还可能构成刑事犯罪。
实践中，论文署名排序不当、科技奖励申报排名等是最常见的举报控告事项。剽窃他人学术思想也是学术界最为反对的恶劣行为。
在科学史上，学术发现的“首发”之争不胜枚举，反映了学术发现的复杂性本质，也体现维护科学家荣誉和利益的重要性。学者由于雇主单位变化，发表前期工作而标注新雇主单位的情形，侵犯了原隶属单位的知识产权。
四、社会危害程度。科研不端行为也会对团体、社会和公众产生巨大危害。学术造假可能导致虚假知识的传播，引起同行错误认知且导致损失。
近些年，在生命科学领域发生过多起由造假产生的“重大”科研成果引发大量学术共同体重复实验的情形，甚至制造了一个虚假繁荣的学科领域，危害甚大；一些学术造假，谎称填补国家“空白”，通过欺骗同行和政府，获取大量学术荣誉和科研经费，实质上已经构成诈骗罪；“论文工厂”的事件，严重损害了中国科学家的国际信誉，也损害了社会公众对科学的公信力。此外，违反科研伦理的行为也属于学术不端，对社会造成现实的或潜在的巨大危害。
`