SQL 十分位

 

 

 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 ;

转载于:https://www.cnblogs.com/chenzechao/p/9042292.html