2020年全国高校计算机能力挑战赛

第三部分：大数据分析（hive）

product.csv 链接：https://pan.baidu.com/s/1dGap6B_ub_bucx4BzfgH-w
提取码：nu68

sales.csv 链接：https://pan.baidu.com/s/1nsi6Hd5ah7Fc_0mqhK2xEw
提取码：s08r

数据说明：

Product：产品表

product_id, 产品ID

product_name, 产品名称

unit_price, 产品单价

inventory 产品库存

salse： 销售表

order_id, 订单ID

product_id, 产品ID

customer_id,客户ID

year, 年

month,月

day, 日

freight_charges,运费

unit_price,单价

quantity 销售数量

任务一、hive创建数据库

1. 创建一个数据库，以你的用户名命名，创建成功后使用use命令切换为该库，并执行set hive.cli.print.current.db=true;截图作为答案

   create database zhaoxiaofeng;
   use zhaoxiaofeng;
   set hive.cli.print.current.db=true;
   
   
   

2. 在HIVE中创建需要的表，并写出建表语句，表结构如下：

   (1).产品表(product)

   字段	字段描述	字段类型
   product_id	产品ID	String
   product_name	产品名称	String
   unit_price	产品单价	decimal(20,3)
   inventory	产品库存	decimal(20,3)

   (2)销售表(salse)

   字段	字段描述	字段类型
   order_id	订单ID	String
   product_id	产品ID	String
   customer_id	客户ID	String
   year	年	Int
   month	月	Int
   day	日	Int
   freight_charges	运费	decimal(20,3)
   unit_price	单价	decimal(20,3)
   quantity	销售数量	decimal(20,3)

   1)创建product表

   create table product(product_id string,product_name string,unit_price  decimal(20,3),   inventory decimal(20,3) ) row format delimitad fields terminated by ',';
   

   补充：

   decimal(M,D)

   • M是最大位数（精度），范围是1到65。可不指定，默认值是10。
   • D是小数点右边的位数（小数位）。范围是0到30，并且不能大于M，可不指定，默认值是0。

   2）创建salse表

   create table salse( order_id  stirng, product_id  string, customer_id  string, year  int,month int,day int,freight_charges , decimal(20,3),unit_price   decimal(20,3) ,  quantity   decimal(20,3) )  row format delimitad fields terminated by ','; 
   

3. 将数据加载到表中，写出加载数据的语句

   1)导入product表

   load data local inpath '/home/zhaoxiaofeng/product.csv' into table product;
   #参看数据发现第一行数据不是我们想要插进表的数据
   alter table product set TBLPROPERTIES ('skip.header.line.count'='1');
   

   2)导入salse表

   load data local inpath '/home/zhaoxiaofeng.sales.csv' into table salse;
   alter table salse set TBLPROPERTIES ('skip.header.line.count'='1';
   

   任务二、hive数据分析

4. 查询每个产品的平均运费，平均销售额

   create table a1 as
   select product_id ,avg(freight_charges),avg(unit_price*quantity)
   from salse
   group by product_id;
   

5. 查询选购了3种产品以上（包含3种产品）的客户ID

   create table a2 as
   select customer_id
   from salse
   group by customer_id
   having count(product_id)>=3;
   

6. 使用所建的表统计各产品总销售数量，总销售额（单价*销售数量），按照总销售额降序排列。

   输出的字段包含

   字段	字段描述
   product_name	产品名称
   Total_ quantity	总销量
   Total_salse	总销售额

   create table a3 as
   select p.product_name product_name,sum(s.quantity) Total_quantity,sum(s.unit_price*s.quantity) Total_salse
   from product p,salse s
   where p.product_id = s.product_id
   group by p.product_name
   order by Total_salse desc;
   
   #查看表结构
   desc as;
   

7. 使用所建的表统计每年销售额最多的4个产品信息。 输出的字段 包含 产品id，年，总销售额

   create table a4 as
   select s.product_id,s.year,a.Total_salse
   from salse s,a4 a
   order by Total_salse desc;
   
   
   #查看销售量最多的4个产品信息
   select * from a4 limit 4;