hive 统计一天基站的掉话率

统计一天的基站掉话率：

--建表

create table cellinfo

(

record_time string,

imei int,

cell array<string>,

ph_num int,

call_num int,

drop_num int,

duration int,

drop_rate int,

net_type string,

erl int

)

row format delimited

fields terminated by ','

collection items terminated by '-';

load data local inpath '/root/testdata/cdr_summ_imei_cell_info.csv' into table cellinfo;



数据查询

select * from cellinfo where imei=352024 limit 0,10



create table jizhan

(

record_time string,

imei string,

cell string,

ph_num int,

call_num int,

drop_num int,

duration int,

drop_rate int,

net_type string,

erl int

)

row format delimited

fields terminated by ',';

create table jizhan_result

(

imei string,

drop_num int,

duration int,

drop_rate double

);



from cellinfo

insert into jizhan_result

select imei,sum(drop_num) s_drop,sum(duration) s_dura, sum(drop_num)/sum(duration) s_rate group by imei order by s_rate desc

hive> select * from jizhan_result limit 10 ;