10月3日

今天做极限测试后续内容

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

通过查询贩卖编号具有C开头的数据并按日期和贩卖编号排序放入存储机场数据的表

create table jc row format delimited fields terminated by ',' as select day_id,sale_nbr,sum(cnt),sum(round)  from saless where sale_nbr like 'C%' group by day_id,sale_nbr;

5.

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

查询以O开头的贩卖编号按日期贩卖编号给存储代理商数据的表

create table dlxs row format delimited fields terminated by ',' as select day_id,sale_nbr,sum(cnt),sum(round)  from salet where sale_nbr like 'O%' group by day_id,sale_nbr;

6.

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

先创建一个表，存储代理商出售的次数，代理商购买的次数，

再查询表中编号相同的sum（number）便是交易次数总和 放入新的表中

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

insert into table dln select day_id,buy_nbr,count(cnt)  from salet where  buy_nbr like 'O%' group by day_id,buy_nbr;

insert into table dln select day_id,sale_nbr,count(cnt)  from salet where  sale_nbr like 'O%' group by day_id,sale_nbr;

create table dlns row format delimited fields terminated by ',' as select day_id,sale_nbr,sum(number)  from dln where  sale_nbr like 'O%' group by day_id,sale_nbr;

7.

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

创建两个表分别存储代理商买入的数据和卖出的数据

create table  mc(day_id string,sale_nbr string,chucnt int,churound int) Row format delimited fields terminated by ',';

create table  mr(day_id string,sale_nbr string,chucnt int,churound int) Row format delimited fields terminated by ',';

insert into table mc select day_id,sale_nbr,sum(cnt),sum(round)  from saless where  sale_nbr like 'O%' and  day_id > '2021-09-01' and day_id < '2021-09-15' group by day_id,sale_nbr;//查询结果写入以创建表

insert into table mr select day_id,buy_nbr,sum(cnt),sum(round)  from saless where  buy_nbr like 'O%' and  day_id > '2021-09-01' and day_id < '2021-09-15' group by day_id,buy_nbr;

再通过连接两个表输出结果，将具有相同编号相同日期的数据整合在一起

create table dlz Row format delimited fields terminated by ','  as select mc.day_id ,mc.sale_nbr,collect_set(mc.chucnt)[0] as sale_cnt,collect_set(mc.churound)[0] as sale_round,collect_set(mr.chucnt)[0] as buy_cnt,collect_set(mr.churound)[0]  as buy_round,(collect_set(mc.churound)[0] - collect_set(mr.churound)[0]) as profit

from mc join mr on mc.sale_nbr=mr.sale_nbr and mc.day_id=mr.day_id group by mc.sale_nbr,mc.day_id ;

8.将四个结果表导出到mysql

bin/sqoop export -connect jdbc:mysql://192.168.43.244:3306/tsgl?serverTimezone=UTC --username root --password root --table jc --fields-terminated-by ',' --export-dir /user/hive/warehouse/my.db/jc

bin/sqoop export -connect jdbc:mysql://192.168.43.244:3306/tsgl?serverTimezone=UTC --username root --password root --table dlxs --fields-terminated-by ',' --export-dir /user/hive/warehouse/my.db/dlxs

bin/sqoop export -connect jdbc:mysql://192.168.43.244:3306/tsgl?serverTimezone=UTC --username root --password root --table dlnss --fields-terminated-by ',' --export-dir /user/hive/warehouse/my.db/dlns

bin/sqoop export -connect jdbc:mysql://192.168.43.244:3306/tsgl?serverTimezone=UTC --username root --password root --table dlz --fields-terminated-by ',' --export-dir /user/hive/warehouse/my.db/dlz

导入到数据库后就可以进行界面的制作。