《数据仓库》练习02

学生提交材料清单

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分）

【题目要求】

打开IDEA，创建以自己姓名拼音的sql文件，例如学生张三：zhangsan.sql
创建数据库 orderdb 并切换使用；
创建销售数据表 salesinfo，顾客信息表order_customers，产品信息表order_products；
将 master节点的/opt/exam_Hive_data/... 原始CSV数据加载到上述3个表中。

【评分标准】

数据库创建正确：5分
表结构正确：10分
数据加载成功：5分

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

表一：salesinfo（销售信息表）

字段名	类型	说明	备注
`order_id`	STRING	订单编号
`customer_id`	STRING	客户 ID	与 order_customers 表关联
`product_id`	STRING	产品 ID	与 order_products 表关联
`sale_date`	DATE	销售日期
`quantity`	INT	购买数量
`unit_price`	INT	单价（单位：元）
`payment_method`	STRING	支付方式
`region`	STRING	销售地区

表二：order_customers（顾客信息表）

字段名	类型	说明	示例
`customer_id`	STRING	客户 ID	主键
`customer_name`	STRING	客户姓名
`customer_gender`	STRING	客户性别
`customer_age`	INT	客户年龄

表三：order_products（产品信息表）

字段名	类型	说明	示例
`product_id`	STRING	产品 ID	主键
`product_name`	STRING	产品名称
`category`	STRING	产品类别

【参考】

-- 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 条记录，展示订单 ID、销售时间和单价。

SELECT order_id, sale_date, unit_price
FROM salesinfo
LIMIT 4;

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

题目： 查询顾客性别为“女”的周姓客户信息，展示姓名、性别与年龄。并把结果写入以自己姓名拼音创建的表中，查看前3条记录。

DROP TABLE IF EXISTS zhangsan;

CREATE TABLE zhangsan AS
SELECT customer_name, customer_gender, customer_age
FROM order_customers
WHERE customer_gender = '女' and customer_name like '周%';

select * from zhangsan limit 3;

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

题目： 统计每位顾客的总购买金额，筛选出总金额小于 60 元的顾客 ID及其总金额。

SELECT customer_id, SUM(quantity * unit_price) AS total_amount
FROM salesinfo
GROUP BY customer_id
HAVING SUM(quantity * unit_price) < 60;

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

题目： 统计每种支付方式的订单数量与总销售额（quantity * unit_price），按订单数量升序排序。

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

题目： 统计每位客户的总订单销售额（客户姓名 + 总销售额），按销售额升序排序，显示前3行。

SELECT c.customer_name, 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_name
ORDER BY total_sales ASC;

题目 6：CASE WHEN 分类统计

题目： 根据销售金额大小将订单分类（<2000 为“小额”，2000-10000 为“中额”，10000以上 为“大额”），统计各类订单数量。

SELECT
    CASE
        WHEN quantity * unit_price < 2000 THEN '小额订单'
        WHEN quantity * unit_price <= 10000 THEN '中额订单'
        ELSE '大额订单'
        END AS order_level,
    COUNT(*) AS order_count
FROM salesinfo
GROUP BY
    CASE
        WHEN quantity * unit_price < 2000 THEN '小额订单'
        WHEN quantity * unit_price <= 10000 THEN '中额订单'
        ELSE '大额订单'
        END;

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

题目： 查询每种支付方式中，销售额最高的前3笔订单。

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

李翔-大数据技术

Big data technology!