hive数据分析、2

3、数据分析处理：

（1）统计每天各个机场的销售数量和销售金额。

要求的输出字段

day_id,sale_nbr,,cnt,round

日期编号，卖出方代码，数量，金额

create table if not exists tb_air_amount
comment "飞机场销售"
as
select
  day_id,
  sale_nbr,
  buy_nbr,
  cnt,
  round from sales
where  sales.sale_nbr like concat("C","%");
select * from tb_air_amount limit 10;
//统计飞机场每天
create table if not exists tb_air_amount1
comment "飞机场销售"
as
select
    day_id,
  sale_nbr,
  sum(cnt) as count,
  sum(round) as round from tb_air_amount group by day_id,sale_nbr;

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

要求的输出字段

day_id,sale_nbr,,cnt,round

日期编号，卖出方代码，数量，金额

create table if not exists tb_o_amount
comment "代理人销售"
as
select
  day_id,
  sale_nbr,
  buy_nbr,
  cnt,
  round from sales
where  sales.sale_nbr like concat("O","%");
select * from tb_o_amount limit 10;
//统计代理人每天
create table if not exists tb_o_amount1
comment "代理人销售"
as
select
    day_id,
  sale_nbr,
  sum(cnt) as count,
  sum(round) as round from tb_o_amount group by day_id,sale_nbr;

 

 

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

要求的输出字段

day_id,sale_nbr, sale_number

日期编号，卖出方代码，交易次数（买入或者卖出均算交易次数）

create table if not exists tb_o_huoyue
comment "代理人活跃"
as
select
    day_id,
  sale_nbr,
  count(cnt) as huoyue
  from tb_o_amount group by day_id,sale_nbr;

 

 

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

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

create table if not exists tb_o_buy
comment "代理人花费"
as
select
  buy_nbr,
  sum(cnt),
  sum(round) from sales
group by buy_nbr;
create table if not exists tb_o_sale
comment "代理人卖出"
as
select
  sale_nbr,
  sum(cnt),
  sum(round) from tb_o_amount
group by sale_nbr;
select * from tb_o_sale;

drop table if exists tb_all;
create table if not exists tb_all
comment "利润汇总"
as
select a.sale_nbr,b.`_c1` as buy_cnt,b.`_c2` as buy_round,a.`_c1` as sale_cnt,a.`_c2` as sale_round,(a.`_c2`-b.`_c2`) as amount from tb_o_sale a inner join tb_o_buy b on a.sale_nbr=b.buy_nbr;