《数据仓库》练习05

学生提交材料清单

1.加载3个表后，每个表查看前3行的截图

2.综合查询分析每小题（共7题）运行结果截图

3.导出IDEA项目（压缩包）

注意：

1.所有截图请统一粘贴到一个 Word 文件中，命名为：数据仓库提交截图_姓名_班级.docx；

2.最终考生提交内容打包为一个总压缩包，命名格式如下：张三-大数据高23-1班-数据仓库.rar

一、Hive 环境启动（10分）

【题目要求】

• 启动 Hadoop 分布式文件系统（HDFS）和 YARN；

• 启动 Hive Metastore 与 HiveServer2 服务。

【评分标准】

• 成功启动所有服务，每项 2.5 分，共 10 分。

【参考】

start-dfs.sh                         # 启动HDFS
start-yarn.sh                        # 启动yarn
nohup hive --service metastore &     # 启动HIVE的元数据服务
nohup hive --service hiveserver2 &   # 启动HIVE的远程访问服务

二、数据建库与导入（20分）

【题目要求】

1. 打开IDEA，创建以自己姓名拼音的sql文件，例如学生张三：zhangsan.sql

2. 创建数据库 orderdb 并切换使用；

3. 创建销售数据表 salesinfo，顾客信息表order_customers，产品信息表order_products；

4. 将 master节点的/opt/exam_Hive_data/... 原始CSV数据加载到上述3个表中。

【评分标准】

• 数据库创建正确：5分

• 表结构正确：10分

• 数据加载成功：5分

以下是根据你提供的建表语句整理出的三个 Hive 表的字段说明表格，结构清晰、适合教学使用、也可用于考试文档或项目说明文档。

表一：salesinfo（销售信息表）

表二：order_customers（顾客信息表）

表三：order_products（产品信息表）

【参考】

-- 1.创建数据库 `orderdb` 并切换使用；
-- 删除已有数据库
DROP DATABASE IF EXISTS orderdb CASCADE;
-- 创建数据库
CREATE DATABASE IF NOT EXISTS orderdb;
-- 选择数据库
USE orderdb;

-- 2.创建销售数据表 `salesinfo`，顾客信息表`order_customers`，产品信息表`order_products`；
-- 创建销售数据表 `salesinfo`
CREATE TABLE IF NOT EXISTS salesinfo (
    order_id STRING,              -- 订单编号（如：106702）
    customer_id STRING,           -- 客户ID（如：c0001）
    product_id STRING,            -- 产品ID（如：p015）
    sale_date DATE,               -- 销售日期，格式：YYYY-MM-DD
    quantity INT,                 -- 购买数量
    unit_price INT,               -- 单价（整数）
    payment_method STRING,        -- 支付方式（如：微信、支付宝、现金、信用卡）
    region STRING                 -- 销售地区（如：深圳、北京）
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
TBLPROPERTIES ("skip.header.line.count"="1"); 

-- 创建顾客信息表`order_customers`
CREATE TABLE IF NOT EXISTS order_customers (
    customer_id STRING,          -- 客户ID（如：c0001）
    customer_name STRING,        -- 客户姓名（如：王丽丽）
    customer_gender STRING,      -- 客户性别（男 / 女）
    customer_age INT             -- 客户年龄
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
TBLPROPERTIES ("skip.header.line.count"="1"); 

-- 创建产品信息表 order_products
CREATE TABLE IF NOT EXISTS order_products (
    product_id STRING,           -- 产品ID，例如：p001
    product_name STRING,         -- 产品名称，例如：华为手机
    category STRING              -- 产品类别，例如：电子产品
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
TBLPROPERTIES ("skip.header.line.count"="1"); 

-- 3.将 `master`节点的`/opt/exam_Hive_data/...` 原始`CSV`数据加载到上述3个表中。
-- 从本地文件系统导入数据到salesinfo表
LOAD DATA LOCAL INPATH '/opt/exam_Hive_data/sales_data_2020-2024.csv' INTO TABLE salesinfo;
-- 查看表数据
select * from salesinfo limit 3;

-- 从本地文件系统导入数据到order_customers表
LOAD DATA LOCAL INPATH '/opt/exam_Hive_data/customers_data.csv' INTO TABLE order_customers;
-- 查看表数据
select * from order_customers limit 3;

-- 从本地文件系统导入数据到order_products表
LOAD DATA LOCAL INPATH '/opt/exam_Hive_data/products_data.csv' INTO TABLE order_products;
-- 查看表数据
select * from order_products limit 3;

三、综合查询分析（70分）

题目 1：基础查询（SELECT + LIMIT）

题目： 查询 order_customers 表中前 5 位客户的姓名、性别与地区。

SELECT customer_name, customer_gender
FROM order_customers
LIMIT 5;

题目 2：条件查询（WHERE + 组合条件）

题目： 查询年龄在 50 岁以上，且性别为男性的顾客，展示姓名与年龄，显示前3条记录。并把结果写入以自己姓名拼音创建的表中，查看前3条记录。

DROP TABLE IF EXISTS zhangsan;

CREATE TABLE zhangsan AS
SELECT customer_name, customer_age
FROM order_customers
WHERE customer_age > 50 AND customer_gender = '男';

select * from zhangsan limit 3;

题目 3：聚合 + HAVING（客户频次分析）

题目： 统计每位客户下单次数，筛选出下单超过 30 次的客户 ID，只查看前3行。

SELECT customer_id, COUNT(*) AS order_count
FROM salesinfo
GROUP BY customer_id
HAVING COUNT(*) > 30
limit 3;

题目 4：聚合分析（区域订单分析）

题目： 统计每个地区的订单总数和总销售额，按订单数量升序排列。

题目 5：JOIN + 聚合分析

题目： 查询每个产品类别的平均单价（关联 products 表），按类别名称升序排列。

SELECT p.category, ROUND(AVG(s.unit_price), 2) AS avg_price
FROM salesinfo s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.category
ORDER BY p.category;

题目 6：CASE WHEN 分类统计

题目：

根据订单销售年份，将订单划分为：

• 2020 年及以前：早期订单

• 2021~2022 年：中期订单

• 2023 年及以后：近期订单

统计各时期的订单数量。

SELECT 
  CASE
    WHEN YEAR(sale_date) <= 2020 THEN '早期订单'
    WHEN YEAR(sale_date) <= 2022 THEN '中期订单'
    ELSE '近期订单'
  END AS period_type,
  COUNT(*) AS order_count
FROM salesinfo
GROUP BY 
  CASE
    WHEN YEAR(sale_date) <= 2020 THEN '早期订单'
    WHEN YEAR(sale_date) <= 2022 THEN '中期订单'
    ELSE '近期订单'
  END
ORDER BY order_count DESC;

题目 7：窗口函数（ROW_NUMBER）

题目： 查询每个年份中销售额（数量 × 单价）最高的订单，展示订单编号、客户 ID、销售日期、销售额，并按销售额降序排列。

SELECT *
FROM (
  SELECT order_id,
         customer_id,
         sale_date,
         quantity * unit_price AS total_amount,
         ROW_NUMBER() OVER (
           PARTITION BY YEAR(sale_date)
           ORDER BY quantity * unit_price DESC
         ) AS rn
  FROM salesinfo
) t
WHERE rn = 1
ORDER BY total_amount DESC;