-- 十分位，这个算法不是很准确

 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=''

     ) 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

 ;