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 table_name 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 ;
1 -- 这个算法更准确 2 select 3 t3.cookieid 4 ,t3.createtime 5 ,t3.pv 6 ,t3.percent -- 分位值 7 ,t3.pt --分组内将数据分成N片 8 ,t3.rn 9 ,t3.cn 10 ,t3.rn2 11 from ( 12 select 13 t2.cookieid 14 ,t2.createtime 15 ,t2.pv 16 ,t2.pt --分组内将数据分成N片 17 ,t2.rn 18 ,t2.cn 19 ,row_number() over(partition by t2.pt order by t2.pv) as rn2 20 ,ceil(t2.rn / t2.cn * 100) as percent -- 分位值 21 from ( 22 select 23 t1.cookieid 24 ,t1.createtime 25 ,t1.pv 26 ,ntile(10) over(order by t1.pv) as pt --分组内将数据分成N片 27 ,row_number() over(order by t1.pv) as rn 28 ,count(1) over() as cn 29 from ( 30 select 'cookie1' as cookieid ,'2015-04-10' as createtime, 1 as pv union all 31 select 'cookie1' as cookieid ,'2015-04-11' as createtime, 2 as pv union all 32 select 'cookie1' as cookieid ,'2015-04-12' as createtime, 3 as pv union all 33 select 'cookie1' as cookieid ,'2015-04-13' as createtime, 4 as pv union all 34 select 'cookie1' as cookieid ,'2015-04-14' as createtime, 5 as pv union all 35 select 'cookie1' as cookieid ,'2015-04-15' as createtime, 6 as pv union all 36 select 'cookie1' as cookieid ,'2015-04-16' as createtime, 7 as pv union all 37 select 'cookie2' as cookieid ,'2015-04-10' as createtime, 8 as pv union all 38 select 'cookie2' as cookieid ,'2015-04-11' as createtime, 9 as pv union all 39 select 'cookie2' as cookieid ,'2015-04-12' as createtime, 10 as pv union all 40 select 'cookie2' as cookieid ,'2015-04-13' as createtime, 11 as pv union all 41 select 'cookie2' as cookieid ,'2015-04-14' as createtime, 12 as pv union all 42 select 'cookie2' as cookieid ,'2015-04-15' as createtime, 13 as pv union all 43 select 'cookie2' as cookieid ,'2015-04-15' as createtime, 14 as pv union all 44 select 'cookie2' as cookieid ,'2015-04-15' as createtime, 15 as pv union all 45 select 'cookie2' as cookieid ,'2015-04-15' as createtime, 16 as pv union all 46 select 'cookie2' as cookieid ,'2015-04-15' as createtime, 17 as pv union all 47 select 'cookie2' as cookieid ,'2015-04-15' as createtime, 18 as pv union all 48 select 'cookie2' as cookieid ,'2015-04-15' as createtime, 19 as pv union all 49 select 'cookie2' as cookieid ,'2015-04-15' as createtime, 20 as pv union all 50 select 'cookie2' as cookieid ,'2015-04-15' as createtime, 21 as pv union all 51 select 'cookie2' as cookieid ,'2015-04-15' as createtime, 22 as pv union all 52 select 'cookie2' as cookieid ,'2015-04-15' as createtime, 23 as pv union all 53 select 'cookie2' as cookieid ,'2015-04-15' as createtime, 24 as pv union all 54 select 'cookie2' as cookieid ,'2015-04-15' as createtime, 25 as pv union all 55 select 'cookie2' as cookieid ,'2015-04-15' as createtime, 26 as pv union all 56 select 'cookie2' as cookieid ,'2015-04-15' as createtime, 27 as pv union all 57 select 'cookie2' as cookieid ,'2015-04-15' as createtime, 28 as pv union all 58 select 'cookie2' as cookieid ,'2015-04-15' as createtime, 29 as pv union all 59 select 'cookie2' as cookieid ,'2015-04-16' as createtime, 30 as pv 60 ) t1 61 ) t2 62 ) t3 63 where t3.rn2 = 1 or t3.rn = t3.cn 64 order by t3.rn 65 ;