《数据仓库》练习03

学生提交材料清单

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_products 表中前 3 条记录的产品ID、产品名称和产品分类。

SELECT product_id, product_name, category
FROM order_products
LIMIT 5;

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

题目： 查询使用“现金”支付且单价高于 25500 元的订单，输出订单编号、支付方式和单价，并把结果写入以自己姓名拼音创建的表中，查看前3条记录。

DROP TABLE IF EXISTS zhangsan;

CREATE TABLE zhangsan AS
SELECT order_id, payment_method, unit_price
FROM salesinfo
WHERE payment_method = '现金' AND unit_price > 25500;

select * from zhangsan limit 3;

题目 3：聚合 + 筛选（GROUP BY + HAVING）

题目： 请统计每个销售地区（region）的订单数量，筛选出订单数超过 17000 单的地区，并按订单数量升序排列。

SELECT region, COUNT(*) AS order_count
FROM salesinfo
GROUP BY region
HAVING COUNT(*) > 17000
ORDER BY order_count ASC;

题目 4：聚合分析（GROUP BY + SUM + COUNT）

题目： 统计每年的订单数量与销售总额（数量 × 单价），按年份降序排列。

题目 5：多表分析（JOIN + GROUP BY）

题目： 统计每种支付方式下的顾客数量（去重），展示支付方式和顾客数量，按顾客数量升序排列。

SELECT s.payment_method,
       COUNT(DISTINCT c.customer_id) AS customer_count
FROM salesinfo s
         JOIN order_customers c ON s.customer_id = c.customer_id
GROUP BY s.payment_method
ORDER BY customer_count ASC;

题目 6：CASE WHEN 分类统计

题目： 将客户年龄分为三类（<=30为“青年”，31–60 为“中年”，>60 为“老年”），统计每类客户数量。

SELECT
    CASE
        WHEN customer_age <= 30 THEN '青年'
        WHEN customer_age <= 60 THEN '中年'
        ELSE '老年'
        END AS age_group,
    COUNT(*) AS customer_count
FROM order_customers
GROUP BY
    CASE
        WHEN customer_age <= 30 THEN '青年'
        WHEN customer_age <= 60 THEN '中年'
        ELSE '老年'
        END;

题目 7：窗口函数分析（ROW_NUMBER）

题目： 查询每个销售地区中最早的一笔订单，按订单时间升序排列。

SELECT *
FROM (
  SELECT region, order_id, sale_date,
         ROW_NUMBER() OVER (
           PARTITION BY region
           ORDER BY sale_date ASC
         ) AS rn
  FROM salesinfo
) t
WHERE rn = 1
ORDER BY sale_date ASC;