今天做极限测试后续内容
统计每天各个机场的销售数量和销售金额。
通过查询贩卖编号具有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
导入到数据库后就可以进行界面的制作。