HIVE数据分析-数据分析处理

题目

数据分析处理：

（1）统计每天各个机场的销售数量和销售金额。
要求的输出字段
day_id,sale_nbr,,cnt,round
日期编号，卖出方代码，数量，金额

1、创建jichang表存放信息：

create table jichang(day_id string,sale_nbr string,cnt string,round string) row format delimited fields terminated by ',';

2、在表里插入数据，并且统计每天各个机场的销售数量和销售金额

 insert into table jichang select day_id,sale_nbr,sum(cnt) as cnt,sum(round) as round from sales_y group by sale_nbr,day_id having sale_nbr like 'C%';

（2）统计每天各个代理商的销售数量和销售金额。

要求的输出字段
day_id,sale_nbr,,cnt,round
日期编号，卖出方代码，数量，金额

1、创建dailishang表存放信息：

create table dailishang(day_id string,sale_nbr string,cnt string,round string) row format delimited fields terminated by ',';

2、在表里插入数据，并且输出字段

insert into table dailishang select day_id,sale_nbr,sum(cnt) as cnt,sum(round) as round from sales_y group by sale_nbr,day_id having sale_nbr like 'O%';

（3）统计每天各个代理商的销售活跃度。

要求的输出字段
day_id,sale_nbr, sale_number
日期编号，卖出方代码，交易次数（买入或者卖出均算交易次数）

1、创建表存放每天代理商卖出的活跃度

create table dailishang_mc(day_id string,sale_nbr string,count int) row format delimited fields terminated by ',';

insert into table dailishang_mc select day_id,sale_nbr,count(*) as count from sales_y where sale_nbr like 'O%' group by sale_nbr,day_id; 

2、创建表存放每天代理商买入的活跃度

create table dailishang_mr(day_id string,sale_nbr string,count int) row format delimited fields terminated by ',';

insert into table dailishang_mr select day_id,buy_nbr as sale_nbr,count(*) as count from sales_y where buy_nbr like 'O%' group by buy_nbr,day_id;

3、创建表统计每天代理商活跃度

create table dailishang_h(day_id string,sale_nbr string,sale_number int) row format delimited fields terminated by ',';
 insert into table dailishang_h select dailishang_mc.day_id as day_id,dailishang_mc.sale_nbr as sale_nbr,dailishang_mc.count+dailishang_mr.count as sale_number from dailishang_mc join dailishang_mr on (dailishang_mc.sale_nbr=dailishang_mr.sale_nbr) ; 

（4）汇总统计9月1日到9月15日之间各个代理商的销售利润。

编号，卖出方代码，买入数量，买入金额，卖出数量，卖出金额，销售利润（卖出金额-买入金额）

1、创建表存放每天代理商卖出的数量和金额

create table daiout(day_id string,sale_nbr string,cnt int,round int) row format delimited fields terminated by ',';

insert into table daiout select day_id,sale_nbr,sum(cnt) as cnt,sum(round) as round from sales_y where sale_nbr like 'O%' group by day_id,sale_nbr;

2、创建表存放每天代理商买入的数量和金额

create table daiin(day_id string,buy_nbr string,cnt int,round int) row format delimited fields terminated by ',';

insert into table daiin select day_id,buy_nbr,sum(cnt) as cnt,sum(round) as round from sales_y where buy_nbr like 'O%' group by day_id,buy_nbr;

3、创建表存放每天代理商的销售利润

create table daili(day_id string,sale_nbr string,incnt int,inround int,outcnt int,outround int,lirun int) row format delimited fields terminated by ',';

insert into table daili select daiout.day_id as day_id,daiout.sale_nbr as sale_nbr,daiin.cnt as incnt,daiin.round as inround,daiout.cnt as outcnt,daiout.round as outround,daiout.round-daiin.round as lirun from daiout join daiin on (daiin.buy_nbr=daiout.sale_nbr);