SQL 十分位

绝地灬酷狼 2021-09-30 01:54 198阅读 0赞

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