需求:根据主叫被叫通话记录,取出每个号码呼出前三的记录
--建表,calling主叫,called被叫
create external table beijiao(calling string,called string) row format delimited
fields terminated by '%' location 'beijiao/'
--通过group统计出被叫号码呼叫次数,rnk是对分组数据进行标号1,2,3......,分组是根据calling
select a.calling,a.called,count(*) num,row_number() over(partition by calling) as rnk
from beijiao a group by a.calling,a.called;
--上面语句rnk分组的序号不是按呼叫次数排序的,所以还要执行下面的语句,用num进行排序,重新打上标号,并建成一张表
select a.calling,a.called,num,row_number() over(partition by calling order by num desc)
as rnk2 from cl_bj_2 a;
--根据rnk2取出前3条记录
create table cl_bj_4 as select called from cl_bj_3 where rnk2<4;