生活中的大数据分析（二）

1.利用hql进行数据分析挖掘

第一组：模版查询

把刚从手机里导出的数据还原至官网数据，保留日期、电话号码、城市、通话时间、手机类型、（呼出呼入）类型

insert overwrite directory "/user/15"

select cc.phone_data,cc.phone_number,dd.city,cc.call_duration,cc.type from

(select *,substr(phone_number,0,7) as m from call_records)cc

 left join (select *,substr(number_bigint,2,7) as n from number_field)dd

on cc.m=dd.n

where dd.city is not null;／／不显示空号数据（因为原始数据是用java代码写的，所以回出现空号）

描述：还原到官网的数据，方便对官网数据进行操作，查询更多的数据

优点：通过表连接、嵌套查询进行数据清洗，清洗掉了无意义数据，保留非空号数据，输出了全部数据的全部信息。

2.查处手机号为18211360627的电话通话记录，并显示该号码在你通话记录中“呼出”类型中所占排名

insert overwrite directory "/user/15"

select phone_number,call_duration,type,ff.tt from

 (select phone_number,call_duration,type,rank() over(partition by type order by call_duration desc)tt

from call_records)ff where ff.phone_number=18211360627 and type ="呼出";

描述：共有9次通话记录，时间最长的一次是1557s，在通话时长中排名第223，可见该号码不是十分重要的联系人，数量和较长的通话时间都不是很多。

优点：通过表连接、嵌套查询进行通话记录分析，并分析出了机主平时的工作生活情况。

3.查出该通话记录中通话时长最长的前5名，显示其通话的日期、电话号码、城市、通话时间、手机类型、（呼出呼入）类型

insert overwrite directory "/user/15"

select cc.phone_data,cc.phone_number,dd.city,cc.call_duration,cc.type from

(select *,substr(phone_number,1,7) as m from call_records order by call_duration)cc

left join (select *,substr(number_bigint,2,7) as n from number_field)dd

 on cc.m=dd.n where dd.city is not null order by call_duration desc limit 5;

描述：通话时间最长的前五名为13411027355、15968976835、18918797716、15968976839、15968976839、18921363878，分别来自广东佛山、浙江宁波、上海、浙江宁波和江苏无锡。

说明机主主要的活动地为东南方向。

优点：通过表连接、嵌套查询进行通话记录分析，并分析出了机主平时的工作生活情况。

第二组：复杂查询

描述：利用少见的函数，多重的嵌套，和自己的数据做出独特的查询过程解决较为复杂的问题。

优点：完成了较难和少见的操作。

4.查出在通话录中使用最多运营商的的全部用户的电话号，城市，运营商。

insert overwrite directory "/user/15"

select lian.pn,lian.cy,lian.ct from (select ee.card_type as ct from(

select cc.phone_number,dd.city,cc.chishu,dd.card_type from

(select phone_number,count(*) as chishu,substr(phone_number,1,7) as m from call_records group by phone_number)cc

left join (select city,card_type,substr(number_bigint,2,7) as n from number_field)dd

on cc.m=dd.n where dd.card_type is not null order by cc.chishu desc limit 1)ee)gg,(select cc.phone_number as pn,dd.city as cy,dd.card_type as ct from (select  *,substr(phone_number,1,7) as m from call_records )cc

left join (select *,substr(number_bigint,2,7) as n from number_field)dd

 on cc.m=dd.n where dd.city is not null)lian where lian.ct=gg.ct;

描述：利用了N重嵌套查询，首先查询了通话录中使用最多的电话卡类型，再通过此电话类型的运营商，找到使用此运营商服务的所有人员。可以通过此记录，猜测出机主的主要活动地，计算运营商在该活动地的占用情况，即机主个人关系网中该运营商的占有量。

优点：利用的N重嵌套查询，理清楚嵌套关系，解决复杂问题，优化代码使之能成功运行。

5.将通话记录中按时间分，按电话分，分别按时间电话分，统计通话次数，输出到一个界面，方便存储一个文件。

insert overwrite directory "/user/15"

SELECT phone_data ,phone_number AS pn,

count(phone_number) AS ci

FROM call_records

GROUP BY phone_data, phone_number

UNION ALL

SELECT phone_data, NULL AS pn, count(phone_number) AS ci

FROM call_records

GROUP BY phone_data

UNION ALL

SELECT NULL AS phone_data, phone_number AS pn,

count(phone_number) AS ci

FROM call_records

GROUP BY phone_number

UNION ALL

SELECT NULL AS phone_data, NULL AS pn,

count(phone_number) AS ci

FROM call_records;

描述：将通话记录中按时间分，按电话分，分别按时间电话分，统计通话次数，输出到一个文件。

特点：利用UNION ALL将多个查询的数据连接到一起，若没有对应字段，则置空。

6.还原通话录中的各项信息，三表联立，优化查询

insert overwrite directory "/user/15"

select distinct gg.k,ff.city,gg.sf,gg.xb from (select sf,xb,cc.m as k from

(select *,substr(phone_number,1,7) as m from xb)cc

left join (select *,substr(phone_number,1,7) as n from sf)dd

on cc.m=dd.n)gg left join (select *,substr(number_bigint,2,7) as n from number_field)ff

on gg.k=ff.n where ff.city is not null;

描述：利用三表联立，还原通话录的黄页数据，

优点：其中俩个表是利用自己的代码运行出的数据，三表联立。