createtableifnotexists tmp.rows_preced_test_20210526
(
name string comment'用户名',seq_num intcomment'使用产品编码')partitioned by(pt string comment'YYMMDD数据入库时间')ROW FORMAT DELIMITED FIELDSTERMINATEDBY'\t' STORED AS orcfile
结果计算
select name,max(l_cnt)as m_cnt from(select name ,continue,count(1)as l_cnt
from(select name
,seq_num
-- 伪列排序与原始值差值的绝对值,abs(row_number()over(partitionby name orderby name,seq_num )- seq_num )ascontinuefrom(-- 对原始数据初步处理, 求次数无需group by , 重复数据算一次需groupby 去重select name,seq_num
from normal_stg_tmp.rows_preced_test_20210526
groupby name,seq_num
) a
) a groupby name,continue) b groupby name
原始测试数据准备nameseq_numa1a2b4b5c1c3c4创建测试表create table if not exists tmp.rows_preced_test_20210526(name string comment '用户名',seq_num int comment '使用产品编码')partitioned by (pt string comment 'YYMMDD数据入库时间')ROW FOR.