1 -- 十分位，这个算法不是很准确 2 select 3 family_agreement_cnt -- 字段 4 ,dt -- 分区 5 ,rn -- 排序 6 ,cnt -- 总行数 7 ,percent2 -- 分位值 8 ,rk 9 ,row_num 10 from ( 11 select 12 t1.family_agreement_cnt -- 字段 13 ,t1.dt -- 分区 14 ,t1.rn -- 排序 15 ,t1.cnt -- 总行数 16 ,ceil(t1.rn / t1.cnt * 100) as percent2 -- 分位值 17 ,row_number() over(partition by ceil(t1.rn / t1.cnt * 100) order by rn desc) as rk 18 ,row_number() over(order by rn) as row_num 19 from ( 20 select 21 family_agreement_cnt 22 ,dt 23 ,row_number() over(partition by dt order by cast(family_agreement_cnt as double)) as rn 24 ,count(1) over(partition by dt) as cnt 25 from edw_dw_safe.out_elis_ai_high_quality_kpi_collection 26 where dt=‘20180201‘ 27 ) t1 28 where t1.rn = 1 or t1.rn % cast(t1.cnt/10 as int) = 0 or t1.rn = t1.cnt 29 order by t1.dt,t1.rn 30 ) t2 31 where t2.rk = 1 32 ; 33 34 35 -- 方差 36 select 37 stddev(num) as std 38 from ( 39 select 1 as num union all 40 select 2 as num union all 41 select 3 as num union all 42 select 4 as num union all 43 select 5 as num union all 44 select 6 as num union all 45 select 7 as num union all 46 select 8 as num union all 47 select 9 as num union all 48 select 10 as num union all 49 select 11 as num union all 50 select 12 as num union all 51 select 13 as num union all 52 select 14 as num union all 53 select 15 as num union all 54 select 16 as num 55 ) t1 56 ;