SQL 十分位

 

 

-- 十分位，这个算法不是很准确
select 
     family_agreement_cnt -- 字段
    ,dt                   -- 分区
    ,rn                   -- 排序
    ,cnt                  -- 总行数
    ,percent2             -- 分位值
    ,rk
    ,row_num
from (
    select
         t1.family_agreement_cnt                   -- 字段
        ,t1.dt                                     -- 分区
        ,t1.rn                                     -- 排序
        ,t1.cnt                                    -- 总行数
        ,ceil(t1.rn / t1.cnt * 100)                                                  as percent2 -- 分位值
        ,row_number() over(partition by ceil(t1.rn / t1.cnt * 100) order by rn desc) as rk
        ,row_number() over(order by rn)                                              as row_num
    from (
        select
             family_agreement_cnt
            ,dt
            ,row_number() over(partition by dt order by cast(family_agreement_cnt as double)) as rn
            ,count(1)     over(partition by dt)                                     as cnt
        from table_name
        where dt='20180201'
    ) t1
    where t1.rn = 1 or t1.rn % cast(t1.cnt/10 as int) = 0 or t1.rn = t1.cnt
    order by t1.dt,t1.rn
) t2
where t2.rk = 1
;


-- 方差
select 
    stddev(num) as std
from (
    select 1  as num union all
    select 2  as num union all
    select 3  as num union all
    select 4  as num union all
    select 5  as num union all
    select 6  as num union all
    select 7  as num union all
    select 8  as num union all
    select 9  as num union all
    select 10 as num union all
    select 11 as num union all
    select 12 as num union all
    select 13 as num union all
    select 14 as num union all
    select 15 as num union all
    select 16 as num 
) t1
;

 

 

-- 这个算法更准确
select
     t3.cookieid
    ,t3.createtime
    ,t3.pv
    ,t3.percent -- 分位值
    ,t3.pt      --分组内将数据分成N片
    ,t3.rn
    ,t3.cn
    ,t3.rn2
from (
    select
         t2.cookieid
        ,t2.createtime
        ,t2.pv
        ,t2.pt  --分组内将数据分成N片
        ,t2.rn
        ,t2.cn
        ,row_number() over(partition by t2.pt order by t2.pv) as rn2
        ,ceil(t2.rn / t2.cn * 100)                            as percent -- 分位值
    from (
        select
             t1.cookieid
            ,t1.createtime
            ,t1.pv
            ,ntile(10) over(order by t1.pv)    as pt  --分组内将数据分成N片
            ,row_number() over(order by t1.pv) as rn
            ,count(1) over()                   as cn
        from (
            select 'cookie1' as cookieid ,'2015-04-10' as createtime, 1   as pv union all
            select 'cookie1' as cookieid ,'2015-04-11' as createtime, 2   as pv union all
            select 'cookie1' as cookieid ,'2015-04-12' as createtime, 3   as pv union all
            select 'cookie1' as cookieid ,'2015-04-13' as createtime, 4   as pv union all
            select 'cookie1' as cookieid ,'2015-04-14' as createtime, 5   as pv union all
            select 'cookie1' as cookieid ,'2015-04-15' as createtime, 6   as pv union all
            select 'cookie1' as cookieid ,'2015-04-16' as createtime, 7   as pv union all
            select 'cookie2' as cookieid ,'2015-04-10' as createtime, 8   as pv union all
            select 'cookie2' as cookieid ,'2015-04-11' as createtime, 9   as pv union all
            select 'cookie2' as cookieid ,'2015-04-12' as createtime, 10  as pv union all
            select 'cookie2' as cookieid ,'2015-04-13' as createtime, 11  as pv union all
            select 'cookie2' as cookieid ,'2015-04-14' as createtime, 12  as pv union all
            select 'cookie2' as cookieid ,'2015-04-15' as createtime, 13  as pv union all
            select 'cookie2' as cookieid ,'2015-04-15' as createtime, 14  as pv union all
            select 'cookie2' as cookieid ,'2015-04-15' as createtime, 15  as pv union all
            select 'cookie2' as cookieid ,'2015-04-15' as createtime, 16  as pv union all
            select 'cookie2' as cookieid ,'2015-04-15' as createtime, 17  as pv union all
            select 'cookie2' as cookieid ,'2015-04-15' as createtime, 18  as pv union all
            select 'cookie2' as cookieid ,'2015-04-15' as createtime, 19  as pv union all
            select 'cookie2' as cookieid ,'2015-04-15' as createtime, 20  as pv union all
            select 'cookie2' as cookieid ,'2015-04-15' as createtime, 21  as pv union all
            select 'cookie2' as cookieid ,'2015-04-15' as createtime, 22  as pv union all
            select 'cookie2' as cookieid ,'2015-04-15' as createtime, 23  as pv union all
            select 'cookie2' as cookieid ,'2015-04-15' as createtime, 24  as pv union all
            select 'cookie2' as cookieid ,'2015-04-15' as createtime, 25  as pv union all
            select 'cookie2' as cookieid ,'2015-04-15' as createtime, 26  as pv union all
            select 'cookie2' as cookieid ,'2015-04-15' as createtime, 27  as pv union all
            select 'cookie2' as cookieid ,'2015-04-15' as createtime, 28  as pv union all
            select 'cookie2' as cookieid ,'2015-04-15' as createtime, 29  as pv union all
            select 'cookie2' as cookieid ,'2015-04-16' as createtime, 30  as pv
        ) t1
    ) t2
) t3
where t3.rn2 = 1 or t3.rn = t3.cn
order by t3.rn
;