大数据测试

1、数据导入：

要求将样表文件中的（sales_sample_20170310）数据导入HIVE数据仓库中。

2、数据清洗：

要求将day_id一列中的数值清洗为真实的日期格式，可用字符串表示。

数据1对应日期2021-09-01，依次类推，15对应日期2021-09-15

3、数据分析处理：

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

要求的输出字段

day_id,sale_nbr,,cnt,round

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

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

要求的输出字段

day_id,sale_nbr,,cnt,round

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

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

要求的输出字段

day_id,sale_nbr, sale_number

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

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

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

（5）设计分析代理商的市场地位根据市场交易次数、交易对象个数、销售机票数量、销售利润等。（选做题）

4、处理结果入库：

将上述统计分析的结果数据保存到mySQL数据库中。

5、数据可视化展示：

   利用Echarts将上述统计结果以图形化展示的方式展现出来：饼图、柱状图、地图、折线图等。

 

 

CREATE TABLE IF NOT EXISTS sales_sample (
  day_id STRING,
  sale_nbr STRING,
  buy_nbr STRING,
  cnt INT,
  round INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;


CREATE  TABLE IF NOT EXISTS target_sales_sample (
  day_id STRING,
  sale_nbr STRING,
  buy_nbr STRING,
  cnt INT,
  round INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

-- -- 将数据上传到HDFS
-- -- hadoop fs -put employees.csv /

-- -- 导入数据到Hive表
LOAD DATA INPATH '/xiyou/sales_sample_20170310.csv' INTO TABLE target_sales_sample;
--
-- -- 验证数据导入
SELECT * FROM  target_sales_sample ;
SELECT * FROM  sales_sample ;
-- -- 使用Hive的UDF将day_id映射为日期格式

insert overwrite table sales_sample
select
    date_add('2023-09-00',cast(day_id as int)) as day_id,
    sale_nbr as sale_nbr,
    buy_nbr as buy_nbr,
    cnt as cnt,
    round as round
from target_sales_sample;

-- drop table sales_sample;
-- drop table jichang;

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

CREATE TABLE IF NOT EXISTS jichang (
  day_id STRING,
  sale_nbr STRING,
  cnt INT,
  round INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

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

SELECT * FROM  jichang ;

-- （2）统计每天各个代理商的销售数量和销售金额。
-- 要求的输出字段
-- day_id,sale_nbr,,cnt,round
-- 日期编号，卖出方代码，数量，金额
CREATE TABLE IF NOT EXISTS dailishang (
  day_id STRING,
  sale_nbr STRING,
  cnt INT,
  round INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

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

SELECT * FROM  dailishang ;

-- （1）创建表存放每天代理商卖出的活跃度
CREATE TABLE IF NOT EXISTS dailishang_active (
  day_id STRING,
  sale_nbr STRING,
  sale_number INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

insert into table dailishang_active
select
    day_id,
    sale_nbr,
    count(*) as sale_number
from sales_sample
group by sale_nbr,day_id having sale_nbr like 'O%';

SELECT * FROM  dailishang_active ;

-- 2、创建表存放每天代理商买入的活跃度
CREATE TABLE IF NOT EXISTS dailishang_active2 (
  day_id STRING,
  sale_nbr STRING,
  sale_number INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

insert into table dailishang_active2
select
    day_id,
    buy_nbr as sale_nbr,
    count(*) as sale_number
from sales_sample
group by buy_nbr,day_id having buy_nbr like 'O%';

SELECT * FROM  dailishang_active2 ;

-- 3、创建表统计每天代理商活跃度
CREATE TABLE IF NOT EXISTS dailishang_huoyue (
  day_id STRING,
  sale_nbr STRING,
  sale_number INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

insert into table dailishang_huoyue
select dailishang_active.day_id as day_id,
       dailishang_active.sale_nbr as sale_nbr,
       dailishang_active.sale_number+dailishang_active2.sale_number as sale_number
from dailishang_active join dailishang_active2 on (dailishang_active.sale_nbr=dailishang_active2.sale_nbr) ;

SELECT * FROM  dailishang_huoyue;

-- （1）创建表存放每天代理商卖出的数量和金额
CREATE TABLE IF NOT EXISTS mai_chu (
  day_id STRING,
  sale_nbr STRING,
  cnt int,
  round int
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

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

SELECT * FROM  mai_chu ;

-- （2）创建表存放每天代理商买入的数量和金额
CREATE TABLE IF NOT EXISTS mai_ru (
  day_id STRING,
  buy_nbr  STRING,
  cnt int,
  round int
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

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

SELECT * FROM  mai_ru ;

-- （3）创建表存放每天代理商的销售利润
CREATE TABLE IF NOT EXISTS dai_li (
  day_id STRING,
  sale_nbr STRING,
  incnt int,
  inround int,
  outcnt int,
  outround int,
  lirun int
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

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

SELECT * FROM  sales_sample;
SELECT * FROM  jichang;
SELECT * FROM  dailishang;
SELECT * FROM  dailishang_huoyue;
SELECT * FROM  dai_li;