《数据仓库》练习04

学生提交材料清单

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）

题目： 查询 salesinfo 表中前 5 条记录，展示订单编号、销售地区、销售日期。

SELECT order_id, region, sale_date
FROM salesinfo
LIMIT 5;

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

题目： 查询销售价格大于10000元，且支付方式为“现金”或“微信”的订单记录，输出订单号、支付方式与数量、价格。并把结果写入以自己姓名拼音创建的表中，查看前3条记录。

DROP TABLE IF EXISTS zhangsan;

CREATE TABLE zhangsan AS
SELECT order_id, payment_method, quantity,unit_price
FROM salesinfo
WHERE unit_price > 10000 AND payment_method IN ('现金', '微信');

select * from zhangsan limit 3;

题目 3：聚合 + HAVING（筛选高频支付方式）

题目： 统计每种支付方式的订单数量，并筛选出下单次数超过 40 次的支付方式。

SELECT payment_method, COUNT(*) AS order_count
FROM salesinfo
GROUP BY payment_method
HAVING COUNT(*) > 40;

题目 4：聚合分析（销售区域汇总）

题目： 统计在使用“支付宝”支付的订单中，各销售地区的销售金额，按销售金额降序排序。

题目 5：多表 JOIN + 聚合分析

题目： 统计不同性别的顾客产生的销售总额，展示顾客性别和对应的总销售金额（以 quantity * unit_price 为销售额），按销售金额降序排序。

SELECT c.customer_gender,
       SUM(s.quantity * s.unit_price) AS total_sales
FROM salesinfo s
         JOIN order_customers c ON s.customer_id = c.customer_id
GROUP BY c.customer_gender
ORDER BY total_sales DESC;

题目 6：CASE WHEN 分类统计

题目：将订单按购买数量划分为三类：

• 数量 ≤ 1 为“单件订单”

• 数量 2~5 为“少量订单”

• 数量 > 5 为“批量订单”

  统计每类订单数量。

SELECT
    CASE
        WHEN quantity <= 1 THEN '单件订单'
        WHEN quantity <= 5 THEN '少量订单'
        ELSE '批量订单'
        END AS quantity_level,
    COUNT(*) AS order_count
FROM salesinfo
GROUP BY
    CASE
        WHEN quantity <= 1 THEN '单件订单'
        WHEN quantity <= 5 THEN '少量订单'
        ELSE '批量订单'
        END
ORDER BY order_count DESC;

题目 7：窗口函数（ROW_NUMBER）

题目： 请查询每位顾客的第一笔订单（按订单日期最早的一笔），展示顾客 ID、订单编号和销售日期，结果按日期升序排列，只显示前3行。

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