hive取等分数据

 

 

 1 %sql
 2 select
 3     t3.*
 4 from (
 5     select
 6         t2.*
 7         ,row_number() over(partition by t2.pt order by t2.pv) as rn2
 8     from (
 9         select
10              t1.cookieid
11             ,t1.createtime
12             ,t1.pv
13             ,ntile(2) over(order by t1.pv)     as pt  --分组内将数据分成2片
14             ,row_number() over(order by t1.pv) as rn
15             ,count(1) over()                   as cn
16         from (
17             select 'cookie1' as cookieid ,'2015-04-10' as createtime, 1   as pv union all
18             select 'cookie1' as cookieid ,'2015-04-11' as createtime, 2   as pv union all
19             select 'cookie1' as cookieid ,'2015-04-12' as createtime, 3   as pv union all
20             select 'cookie1' as cookieid ,'2015-04-13' as createtime, 4   as pv union all
21             select 'cookie1' as cookieid ,'2015-04-14' as createtime, 5   as pv union all
22             select 'cookie1' as cookieid ,'2015-04-15' as createtime, 6   as pv union all
23             select 'cookie1' as cookieid ,'2015-04-16' as createtime, 7   as pv union all
24             select 'cookie2' as cookieid ,'2015-04-10' as createtime, 8   as pv union all
25             select 'cookie2' as cookieid ,'2015-04-11' as createtime, 9   as pv union all
26             select 'cookie2' as cookieid ,'2015-04-12' as createtime, 10  as pv union all
27             select 'cookie2' as cookieid ,'2015-04-13' as createtime, 11  as pv union all
28             select 'cookie2' as cookieid ,'2015-04-14' as createtime, 12  as pv union all
29             select 'cookie2' as cookieid ,'2015-04-15' as createtime, 13  as pv union all
30             select 'cookie2' as cookieid ,'2015-04-16' as createtime, 14  as pv
31         ) t1
32     ) t2
33 ) t3
34 where t3.rn2 = 1 or t3.rn = t3.cn
35 ;
posted @ 2018-07-09 11:42  chenzechao  阅读(1239)  评论(0)    收藏  举报