《数据仓库》练习01

学生提交材料清单

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

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

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

注意：

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

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

一、Hive 环境启动

【题目要求】

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

• 启动 Hive Metastore 与 HiveServer2 服务。

【参考代码】

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

二、数据建库与导入

【题目要求】

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

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

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

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

表一：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;

三、综合查询分析

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

任务： 查询 salesinfo 表中前3条记录，展示订单编号、客户 ID ,销售日期和地区。

SELECT order_id, customer_id, sale_date,region
FROM salesinfo
LIMIT 3;

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

题目： 查询使用“微信”或“现金”支付，且销售数量不少于6 的订单记录，且只显示前5条记录。

参考答案：

SELECT order_id, payment_method, quantity
FROM salesinfo
WHERE (payment_method = '微信' OR payment_method = '现金')
  AND quantity >= 6
limit 5;

题目 3：排序 + LIMIT 的销售分析

题目： 查询平均购买数量小于3 的客户 ID 和对应平均值，并把结果写入以自己姓名拼音创建的表中，查询前3条记录。

参考答案：

DROP TABLE IF EXISTS zhangsan;

CREATE TABLE zhangsan AS
SELECT customer_id, AVG(quantity) AS avg_qty
FROM salesinfo
GROUP BY customer_id
HAVING AVG(quantity) < 3;

select * from zhangsan limit 3

题目 4：聚合分析（GROUP BY + SUM/count/avg/max/min + HAVING）

题目： 统计每个地区的订单数量与销售总额（数量 × 单价），并按销售额升序排序。

题目 5：多表聚合分析

题目： 查询每类产品的总销售额（数量 × 单价），按总额升序排序，展示类别与总额。

参考答案：

SELECT p.category,
       SUM(s.quantity * s.unit_price) AS total_sales
FROM salesinfo s
JOIN order_products p ON s.product_id = p.product_id
GROUP BY p.category
ORDER BY total_sales ASC;

题目 6：CASE WHEN 分类

题目： 统计每类“支付类型”的订单总数。分类规则如下：

• “微信”或“支付宝” → 线上

• “现金”或“信用卡” → 线下

参考答案：

SELECT
    CASE
        WHEN payment_method IN ('微信', '支付宝') THEN '线上'
        WHEN payment_method IN ('现金', '信用卡') THEN '线下'
        END AS pay_type,
    COUNT(*) AS order_count
FROM salesinfo
GROUP BY
    CASE
        WHEN payment_method IN ('微信', '支付宝') THEN '线上'
        WHEN payment_method IN ('现金', '信用卡') THEN '线下'
        END;

题目 7：窗口函数（ROW_NUMBER）

题目： 查询每个地区销售额前2名的订单（按销售额 = 数量 × 单价）。

参考答案：

SELECT * FROM (
  SELECT order_id, region,
         quantity * unit_price AS total_sales,
         ROW_NUMBER() OVER (
             PARTITION BY region 
             ORDER BY quantity * unit_price DESC
         ) AS rn
  FROM salesinfo
) t
WHERE rn <= 2;