软件工程课堂测试——hive数据分析建表

这里放：sql建表语句：和清洗数据以及数据分析的方法
`--创建一张表，将数据导入进去
--表名
--字段 名称 类型 顺序
--字段之间的分隔符需要指定
create table sample(
day_id string ,--日期编号
sale_num string, --卖方代码
buy_num string ,--卖方代码
count string ,--数量
round string --金额
)
row format delimited
fields terminated by "," ; --字段之间分隔符
select * from sample LIMIT 3;--查询前三条数据
create table sample2(
day_id string ,--日期编号
sale_num string, --卖方代码
buy_num string ,--卖方代码
count string ,--数量
round string --金额
);
--update sample set day_id=date_add(sample.day_id,'2021-09-01');
select * from sample2 LIMIT 3;--查询前三条数据
--修改时间信息
insert overwrite table sample2 select date_add('2021-09-00',cast(day_id as int)) as day_id ,sale_num as sale_num, buy_num as buy_num ,count as count,round as round from sample;

--统计每天每个机场的销售数量和销售金额
------------日期编号， 卖出方代码， 卖方代码，数量，金额
--要求输出字段，day_id ,sale_num, buy_num,round

create table jieguo1(
day_id string ,--日期编号
sale_num string, --卖方代码

count string ,--数量
round  string --金额

);
--统计每个机场
insert overwrite table jieguo1 select day_id as day_id ,sale_num as sale_num,sum(count) as count ,sum(round) as round from sample2 where sample2.sale_num like 'C%' group by day_id, sale_num;
select * from jieguo1 LIMIT 3;--查询前三条数据
--统计每个代理商只需要把模糊查询的内容修改即可
create table jieguo01(
day_id string ,--日期编号
sale_num string, --卖方代码

count string ,--数量
round  string --金额

);
insert overwrite table jieguo01 select day_id as day_id ,sale_num as sale_num,sum(count) as count ,sum(round) as round from sample2 where sample2.sale_num like 'O%' group by day_id, sale_num;
select * from jieguo01 LIMIT 3;--查询前三条数据
--统计每天每个代理商的活跃程度
create table jieguo03(
day_id string ,--日期编号
sale_num string, --卖方代码
number string --活跃度

);
--将新表的数据由原来的覆盖，添加上查询分组
insert overwrite table jieguo03 select day_id ,sale_num,count(*) as number from sample2 group by day_id, sale_num,sale_num having sale_num like 'O%';
select * from jieguo03 LIMIT 20;--查询前三条数据

--计算卖出金额和数量
create table daiout as select day_id as day_id , sale_num as sale_num , sum(count) as count ,sum(round) as round from sample2 where sample2.sale_num like 'O%' group by day_id, sale_num;
select *from daiout limit 3;
--计算买入数量
create table daiin as select day_id as day_id , buy_num as buy_num , sum(count) as count ,sum(round) as round from sample2 where sample2.buy_num like 'O%' group by day_id, sample2.buy_num;
select *from daiin limit 3;
--计算利润
create table daili as select daiin.day_id as day_id, daiout.sale_num as sale_num ,daiin.count as incount, daiin.round as inround, daiout.count as outcount , daiout.round as outround,daiout.round-daiin.round as li from daiin join daiout on (daiin.buy_num=daiout.sale_num);
select * from daili limit 10;`