今日报告

今天完成了HIVE数据分析的报告

 

石家庄铁道大学2023年秋季

 

  2021 级课堂测试试卷-HIVE数据分析

 

课程名称： 大型数据库应用技术  任课教师： 王建民      测试时间：极限测试

 

一、 数据说明：

 

样表（sales_sample_20170310）字段说明：

 

day_id     日期编号；

 

sale_nbr    卖出方代码；

 

buy_nbr    买入方代码；

 

cnt        数量

 

round      金额

 

卖出方和买入方又分为3种类型：

 

以'C'开头的表示类型为C，代表“航空公司”，只可以卖出，不可以买入；

 

以'O'开头的表示类型为O，代表“代理人”，既可以卖出，也可以买入，并且允许自己卖给自己（简单来讲：每个“代理人”代码可能对应多个售票点，售票点之前有交换票的情况，所以体现为自己卖给了自己）；

 

'PAX'表示类型为PAX，代表“旅客”，只可以买入，不可以卖出。

 

举例

 

day_id,sale_nbr,buy_nbr,cnt,round

 

1,C1,O1001,1,360

 

卖出方为C1，类型为C；买入方为O1001，类型为O

 

 

 

day_id,sale_nbr,buy_nbr,cnt,round

 

1,O100,O100,4,2000

 

卖出方为O100，类型为O；买入方为O100，类型为O（即自己卖给自己是允许的）

 

 

 

day_id,sale_nbr,buy_nbr,cnt,round

 

1,O100,PAX,4,2000

 

卖出方为O100，类型为O；买入方为PAX，类型为PAX

 

 

 

二、 测试题目：

 

1、数据导入：

 

首先在Dbeaver中配置出hive的环境，

 

 

 

配置好hive就进行数据的导入。

 

建表：

 

create table sales_sample_20170310 (

 

`day_id` string comment '时间',

 

`sale_nbr` string comment '卖出方',

 

`buy_nbr` string  comment '买入方',

 

`cnt` int comment '数量',

 

`round` int comment '金额')

 

row format delimited

 

fields terminated by ','

 

lines terminated by '\n';

 

 

 

load data local inpath '/home/sales_sample_20170310.csv' into table sales_sample_20170310;

 

 

 

2、数据清洗：

 

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

 

字符串拼接：

 

insert overwrite table sales_sample_20170310select concat('2022-10-',day_id),sale_nbr,buy_nbr,cnt,round from sales_sample_20170310  ;

 

转换格式：

 

create table sales_sample111 as

 

    select  to_date(from_unixtime(UNIX_TIMESTAMP(day_id,'yyyy-MM-dd'))) as day_id,

 

           sale_nbr,

 

           buy_nbr,

 

           cnt,

 

           round

 

    from sales_sample_20170310;

 

3、数据分析处理：

 

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

 

create table day_sale as

 

    select

 

        day_id,

 

        sale_nbr,

 

        sum(cnt) as cnt_max,

 

        sum(round) as round_max

 

    from sales_sample111

 

    where day_id between '2023-10-01' and '2023-10-20'

 

    group by sale_nbr,day_id;

 

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

 

create table huoyuedu as

 

    select

 

        day_id,

 

        sale_nbr,

 

        count(*) as sale_number

 

    from sales_sample111

 

     where day_id between '2022-10-01' and '2022-10-20'

 

    group by sale_nbr,day_id;

 

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

 

create table huoyuedu as

 

    select

 

        day_id,

 

        sale_nbr,

 

        count(*) as sale_number

 

    from sales_sample111

 

     where day_id between '2022-10-01' and '2022-10-20'

 

    group by sale_nbr,day_id;

 

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

 

--计算代理商买入数量金额

 

drop table mairu;

 

create table mairu as

 

    select

 

        day,

 

        buy_nbr,

 

        sum(cnt) as cnt,

 

        sum(round) as round

 

    from sales_sample111

 

    where buy_nbr like 'O%'

 

group by day, buy_nbr;

 

 

 

select * from mairu limit 1000;

 

计算代理商卖出数量金额创建卖出表

 

create table  maichu as

 

    select

 

        day,

 

        sale_nbr,

 

        sum(cnt) as cnt,

 

        sum(round) as round

 

    from sales_sample111

 

        where sale_nbr like 'O%'

 

group by day, sale_nbr;

 

select * from maichu limit 1000;

 

计算利润建立利润表

 

create table lirun as

 

    select a.day as day,

 

           b.sale_nbr as nbr,

 

           a.cnt as cnt_buy,

 

           a.round as rount_buy,

 

           b.cnt as cnt_sale,

 

           b.round as round_sale,

 

           b.round-a.round as liren

 

           from mairu a join maichu b on a.buy_nbr = b.sale_nbr and a.day = b.day

 

        where a.day between '2022-10-01' and '2022-10-15';

 

select * from lirun limit 1000;

 

4、处理结果入库：

 

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

 

在导入时，我使用了dbeaver的可视化工具，

 

 

 

然后里面的数据

 

5、数据可视化展示：

 

   

 

 具体详情可查看，转自https://www.cnblogs.com/wjingbo/p/16771463.html