跳转至

共享单车骑行数据查询实验

本实验围绕某城市共享单车运营数据，完成 6 个查询任务，与课件《第 5 章 广电用户账单与订单数据查询进阶》的知识点一一对应，但业务场景与数据全部替换，可直接作为课堂实验或课后实训使用。

实验任务 对应知识点
任务 1 订单消费档次划分 条件函数、CAST 类型转换函数、DISTINCT、ORDER BY
任务 2 统计用户每年骑行应付总额 字符函数（SUBSTR、CONCAT、LOWER 等）、分组聚合
任务 3 统计用户每月骑行应付总额 日期获取类与日期计算类函数
任务 4 统计用户月均骑行实付金额 数学函数（四则运算、ROUND、FLOOR、CEIL）
任务 5 查询电助力订单的用户城市 内连接、外连接、左半开连接、UNION ALL
任务 6 抽样统计用户骑行订单 桶表、TABLESAMPLE 抽样查询

一. 实验目的

  • 掌握 Hive 内置函数的查看方法，能按需求选用条件、字符、日期、数学类函数。
  • 能使用 CASE 条件函数与 CAST 类型转换函数完成数据分档。
  • 能使用日期函数从时间字符串中取出年、月并完成按年、按月的分组统计。
  • 能使用数学函数计算实付金额与月均值。
  • 理解内连接、外连接与左半开连接的区别，能按业务场景选用正确的连接类型。
  • 能创建桶表并使用 TABLESAMPLE 完成抽样查询。

二. 实验环境

  • Hadoop 3.x 集群（或伪分布式），Hive 3.x，HiveServer2 已启动。
  • 客户端：beeline -u jdbc:hive2://localhost:10000 -n zhangsan。
  • 数据文件存放于本地 /opt/data/ 目录（可自行调整，注意 LOAD DATA LOCAL 读的是客户端本地路径）。

三. 实验数据准备

1. 数据表及关系

实验共 5 张表，均存放在数据库 sharedbike 中。

表名 说明 主要字段
bike_users 用户表 user_id、user_name、member_level、city、reg_time
bike_info 单车信息表 bike_id、model、park_area、put_date
bike_orders 骑行订单表（42 条） order_id、user_id、bike_id、prod_type、prod_name、order_time、cost
bike_bills 骑行账单表（42 条） bill_id、user_id、fee_code、bill_time、should_pay、favour_fee
bike_recharge 账户充值表（12 条） user_id、recharge_amt、recharge_time
  • bike_orders 记录用户下单购买的产品，订单金额 cost 为 STRING 类型（模拟原始数据），使用前需要转换。
  • bike_bills 记录每次骑行产生的费用明细，实付金额 = 应付金额 should_pay − 优惠金额 favour_fee。同一用户同一月份可有多条不同费用科目（fee_code）的明细。
  • 订单表与账单表通过 user_id 关联用户表，订单表通过 bike_id 关联单车表。
  • 数据刻意设计为：用户 U011 只充值、从未下单与骑行；用户 U012 有骑行账单但没有订单记录，供外连接与左半开连接实验对比。

2. 建库与建表

CREATE DATABASE IF NOT EXISTS sharedbike;
USE sharedbike;

CREATE TABLE bike_users(
  user_id STRING,
  user_name STRING,
  member_level STRING,
  city STRING,
  reg_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

CREATE TABLE bike_info(
  bike_id STRING,
  model STRING,
  park_area STRING,
  put_date STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

CREATE TABLE bike_orders(
  order_id STRING,
  user_id STRING,
  bike_id STRING,
  prod_type STRING,
  prod_name STRING,
  order_time STRING,
  cost STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

CREATE TABLE bike_bills(
  bill_id STRING,
  user_id STRING,
  fee_code STRING,
  bill_time STRING,
  should_pay DOUBLE,
  favour_fee DOUBLE)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

CREATE TABLE bike_recharge(
  user_id STRING,
  recharge_amt DOUBLE,
  recharge_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

3. 数据文件

将下列内容分别保存为本地文件（文件末尾保留一个空行）。

/opt/data/bike_users.csv

U001,张伟,GOLD,杭州,2024-03-11 09:20:00
U002,李娜,SILVER,杭州,2024-05-02 10:05:00
U003,王强,NORMAL,杭州,2024-07-19 14:30:00
U004,刘洋,GOLD,宁波,2024-01-08 08:45:00
U005,陈静,SILVER,杭州,2024-09-23 16:10:00
U006,杨帆,NORMAL,宁波,2024-11-05 11:25:00
U007,赵敏,GOLD,杭州,2025-01-14 09:00:00
U008,孙浩,NORMAL,绍兴,2024-06-30 13:40:00
U009,周婷,SILVER,绍兴,2024-08-17 15:55:00
U010,吴磊,NORMAL,杭州,2024-12-01 10:30:00
U011,郑爽,GOLD,宁波,2025-02-20 09:15:00
U012,冯雷,NORMAL,杭州,2025-03-05 17:20:00

/opt/data/bike_info.csv

B01,EP-200,西湖景区,2024-05-10
B02,CL-100,西湖景区,2024-05-10
B03,EP-200,拱墅商圈,2024-06-18
B04,CL-100,拱墅商圈,2024-06-18
B05,EP-300,滨江园区,2024-08-02
B06,CL-100,滨江园区,2024-08-02
B07,EP-300,余杭科技城,2024-10-15
B08,CL-200,余杭科技城,2024-10-15

/opt/data/bike_orders.csv

O0001,U001,B01,E,电助力单车-单次骑行,2025-01-05 08:12:00,22.50
O0002,U002,B02,B,经典单车-单次骑行,2025-01-06 12:30:00,6.00
O0003,U003,B03,M,月卡-30天,2025-01-08 09:05:00,29.90
O0004,U001,B01,E,电助力单车-单次骑行,2025-01-12 18:40:00,25.00
O0005,U004,B04,M,月卡-30天,2025-01-15 10:20:00,29.90
O0006,U005,B05,B,经典单车-单次骑行,2025-01-18 07:55:00,5.50
O0007,U006,B06,E,电助力单车-单次骑行,2025-01-20 19:10:00,21.00
O0008,U007,B07,S,季卡-90天,2025-01-22 08:00:00,79.00
O0009,U002,B02,B,经典单车-单次骑行,2025-01-25 13:15:00,7.50
O0010,U008,B08,S,季卡-90天,2025-01-28 16:45:00,79.00
O0011,U001,B03,M,月卡-30天,2025-02-03 09:30:00,29.90
O0012,U003,B01,E,电助力单车-单次骑行,2025-02-05 17:25:00,23.50
O0013,U004,B04,B,经典单车-单次骑行,2025-02-08 08:05:00,6.50
O0014,U005,B05,M,月卡-30天,2025-02-11 12:00:00,29.90
O0015,U006,B06,B,经典单车-单次骑行,2025-02-14 18:30:00,8.00
O0016,U007,B07,E,电助力单车-单次骑行,2025-02-17 07:40:00,20.50
O0017,U009,B08,S,季卡-90天,2025-02-20 15:10:00,79.00
O0018,U001,B01,M,月卡-30天,2025-02-23 10:55:00,29.90
O0019,U010,B02,B,经典单车-单次骑行,2025-02-26 20:05:00,9.50
O0020,U002,B03,B,经典单车-单次骑行,2025-02-28 11:35:00,6.00
O0021,U003,B04,E,电助力单车-单次骑行,2025-03-02 08:20:00,24.00
O0022,U004,B05,M,月卡-30天,2025-03-05 09:45:00,29.90
O0023,U005,B06,B,经典单车-单次骑行,2025-03-08 18:15:00,7.00
O0024,U006,B07,E,电助力单车-单次骑行,2025-03-11 07:30:00,22.00
O0025,U007,B08,S,季卡-90天,2025-03-14 14:25:00,79.00
O0026,U001,B01,B,经典单车-单次骑行,2025-03-17 12:40:00,5.50
O0027,U008,B02,M,月卡-30天,2025-03-20 10:10:00,29.90
O0028,U009,B03,E,电助力单车-单次骑行,2025-03-23 19:50:00,26.50
O0029,U010,B04,B,经典单车-单次骑行,2025-03-26 08:55:00,8.50
O0030,U002,B05,B,经典单车-单次骑行,2025-03-29 17:05:00,6.50
O0031,U001,B06,E,电助力单车-单次骑行,2025-04-03 09:15:00,23.00
O0032,U004,B07,M,月卡-30天,2025-04-07 11:20:00,29.90
O0033,U007,B08,S,季卡-90天,2025-04-11 16:00:00,79.00
O0034,U005,B01,B,经典单车-单次骑行,2025-04-15 18:45:00,9.00
O0035,U002,B02,M,月卡-30天,2025-05-06 08:35:00,29.90
O0036,U003,B03,E,电助力单车-单次骑行,2025-05-12 19:20:00,25.50
O0037,U006,B04,B,经典单车-单次骑行,2025-05-18 12:55:00,7.50
O0038,U009,B05,S,季卡-90天,2025-05-22 15:30:00,79.00
O0039,U001,B06,E,电助力单车-单次骑行,2025-06-05 07:45:00,21.50
O0040,U010,B07,B,经典单车-单次骑行,2025-06-14 20:10:00,8.00
O0041,U001,B01,M,月卡-30天,2024-12-18 09:10:00,29.90
O0042,U003,B03,E,电助力单车-单次骑行,2024-12-22 17:30:00,20.00

/opt/data/bike_bills.csv

B0001,U001,RIDE,2025-01-05 08:30:00,22.50,2.50
B0002,U001,INSURE,2025-01-05 08:30:00,1.00,0.00
B0003,U002,RIDE,2025-01-06 12:45:00,6.00,0.00
B0004,U003,RIDE,2025-01-08 09:20:00,12.50,1.50
B0005,U004,RIDE,2025-01-15 10:35:00,15.00,3.00
B0006,U005,RIDE,2025-01-18 08:10:00,5.50,0.00
B0007,U006,RIDE,2025-01-20 19:25:00,21.00,2.00
B0008,U007,RIDE,2025-01-22 08:20:00,18.00,4.00
B0009,U008,DISPATCH,2025-01-28 17:00:00,5.00,0.00
B0010,U002,RIDE,2025-01-25 13:30:00,7.50,0.50
B0011,U001,RIDE,2025-02-03 09:45:00,16.50,1.50
B0012,U003,RIDE,2025-02-05 17:40:00,23.50,2.50
B0013,U004,RIDE,2025-02-08 08:20:00,6.50,0.00
B0014,U005,RIDE,2025-02-11 12:15:00,13.00,1.00
B0015,U006,RIDE,2025-02-14 18:45:00,8.00,0.00
B0016,U007,RIDE,2025-02-17 07:55:00,20.50,3.50
B0017,U009,DISPATCH,2025-02-20 15:25:00,5.00,0.00
B0018,U001,RIDE,2025-02-23 11:10:00,11.00,1.00
B0019,U010,RIDE,2025-02-26 20:20:00,9.50,0.50
B0020,U012,RIDE,2025-02-27 08:05:00,7.00,0.00
B0021,U003,RIDE,2025-03-02 08:35:00,24.00,2.00
B0022,U004,RIDE,2025-03-05 10:00:00,17.50,1.50
B0023,U005,RIDE,2025-03-08 18:30:00,7.00,0.00
B0024,U006,RIDE,2025-03-11 07:45:00,22.00,2.00
B0025,U007,DISPATCH,2025-03-14 14:40:00,5.00,0.00
B0026,U001,RIDE,2025-03-17 12:55:00,5.50,0.00
B0027,U008,RIDE,2025-03-20 10:25:00,14.00,1.00
B0028,U009,RIDE,2025-03-23 20:05:00,26.50,3.50
B0029,U010,RIDE,2025-03-26 09:10:00,8.50,0.50
B0030,U001,RIDE,2025-04-03 09:30:00,23.00,2.00
B0031,U004,DISPATCH,2025-04-07 11:35:00,5.00,0.00
B0032,U007,RIDE,2025-04-11 16:15:00,19.00,3.00
B0033,U005,RIDE,2025-04-15 19:00:00,9.00,0.00
B0034,U002,RIDE,2025-04-19 08:40:00,12.00,1.00
B0035,U012,RIDE,2025-04-21 18:25:00,10.50,0.50
B0036,U003,RIDE,2025-05-12 19:35:00,25.50,2.50
B0037,U006,RIDE,2025-05-18 13:10:00,7.50,0.00
B0038,U009,DISPATCH,2025-05-22 15:45:00,5.00,0.00
B0039,U001,RIDE,2025-06-05 08:00:00,21.50,1.50
B0040,U010,RIDE,2025-06-14 20:25:00,8.00,0.00
B0041,U001,RIDE,2024-12-18 09:20:00,18.00,2.00
B0042,U003,RIDE,2024-12-22 17:45:00,20.00,1.00

/opt/data/bike_recharge.csv

U001,50.00,2025-01-04 10:00:00
U002,30.00,2025-01-06 11:00:00
U003,50.00,2025-01-08 09:00:00
U004,100.00,2025-02-05 10:00:00
U005,50.00,2025-02-11 12:00:00
U006,30.00,2025-03-11 08:00:00
U007,100.00,2025-03-14 15:00:00
U008,50.00,2025-03-20 10:00:00
U009,100.00,2025-05-22 16:00:00
U010,30.00,2025-06-14 20:00:00
U011,50.00,2025-02-20 09:00:00
U012,30.00,2025-04-21 18:00:00

4. 导入数据并检查

LOAD DATA LOCAL INPATH '/opt/data/bike_users.csv' OVERWRITE INTO TABLE bike_users;
LOAD DATA LOCAL INPATH '/opt/data/bike_info.csv' OVERWRITE INTO TABLE bike_info;
LOAD DATA LOCAL INPATH '/opt/data/bike_orders.csv' OVERWRITE INTO TABLE bike_orders;
LOAD DATA LOCAL INPATH '/opt/data/bike_bills.csv' OVERWRITE INTO TABLE bike_bills;
LOAD DATA LOCAL INPATH '/opt/data/bike_recharge.csv' OVERWRITE INTO TABLE bike_recharge;

导入后先核对每张表的条数，应为 12、8、42、42、12。

SELECT COUNT(*) FROM bike_users;
SELECT COUNT(*) FROM bike_info;
SELECT COUNT(*) FROM bike_orders;
SELECT COUNT(*) FROM bike_bills;
SELECT COUNT(*) FROM bike_recharge;

四. 实验任务

任务 1. 划分订单消费档次

任务描述

使用条件函数与类型转换函数，对骑行订单按订单金额划分消费档次。

  • 若订单金额大于等于 20.0，则消费档次认定为“高消费”；
  • 若订单金额大于等于 10.0 且小于 20.0，则认定为“中等消费”；
  • 若订单金额小于 10.0，则认定为“低消费”。
  • 消费档次列的列名为 consume_type，转换后的金额列名为 cost_f。
  • 查询结果按订单金额降序排列，并去除重复数据。

实现思路

  • bike_orders 表中 cost 字段为 STRING 类型，需先用 CAST 显式转换为 FLOAT，才能参与数值比较与排序。
  • 用 CASE…WHEN…THEN 设置 3 个分支完成分档。
  • 用 DISTINCT 去除完全相同的重复行，用 ORDER BY 按转换后的金额降序排列。

参考代码

先用简单形式的 CASE 把产品类型编码翻译成中文。

SELECT order_id, prod_type,
CASE prod_type
WHEN 'E' THEN '电助力单车'
WHEN 'B' THEN '经典单车'
WHEN 'M' THEN '月卡'
WHEN 'S' THEN '季卡'
ELSE '未知类型'
END AS `产品类型`
FROM bike_orders
LIMIT 5;

再按金额划分消费档次。

SELECT DISTINCT user_id, order_id, prod_name, CAST(cost AS FLOAT) AS cost_f,
CASE
WHEN cost >= 20.0 THEN '高消费'
WHEN cost >= 10.0 AND cost < 20.0 THEN '中等消费'
ELSE '低消费'
END AS consume_type
FROM bike_orders
ORDER BY cost_f DESC;

预期结果

第二条语句返回 42 行，前几行形如：

user_id order_id prod_name cost_f consume_type
U007 O0008 季卡-90天 79.0 高消费
U007 O0025 季卡-90天 79.0 高消费
U009 O0038 季卡-90天 79.0 高消费
U001 O0041 月卡-30天 29.9 高消费
U003 O0028 电助力单车-单次骑行 26.5 高消费
… … … … …
U001 O0026 经典单车-单次骑行 5.5 低消费

中文列别名要用反单引号引起来；WHEN 后面的取值（20.0、10.0）是数值，与 cost 比较时 Hive 会先把 cost 隐式转换为 DOUBLE，所以即便不做 CAST 也能比较，但金额列的展示与排序仍需显式 CAST。

任务 2. 统计用户每年骑行应付总额

任务描述

使用字符函数统计每个用户每年的骑行应付总额。

实现思路

  • 使用 SUBSTR 从 bill_time 字段值中截取前 4 个字符，得到账单所属年份。
  • 以“用户编号 + 年份”两个值分组，用 SUM(should_pay) 汇总应付总额。
  • 数据中包含 2024 年 12 月与 2025 年的账单，分组后应分别成行。

参考代码

先熟悉字符函数的用法。

SELECT LOWER('GOLD'), UPPER('gold'), LENGTH('EP-200'), CONCAT('杭州', '-', '西湖景区'), TRIM('  EP-200  ');
SELECT user_name, LOWER(member_level), CONCAT(user_name, '/', city) FROM bike_users LIMIT 5;

再统计每年应付总额。

SELECT user_id, SUBSTR(bill_time, 1, 4) AS bill_year, SUM(should_pay)
FROM bike_bills
GROUP BY user_id, SUBSTR(bill_time, 1, 4);

预期结果

共 13 行（U001、U003 各有 2024、2025 两行），形如：

user_id bill_year 应付总额
U001 2024 18.0
U001 2025 101.0
U002 2025 25.5
U003 2024 20.0
U003 2025 85.5
U004 2025 44.0
U005 2025 34.5
U006 2025 58.5
U007 2025 62.5
U008 2025 19.0
U009 2025 36.5
U010 2025 26.0
U012 2025 17.5

U011 从未产生账单，因此不出现在结果中。这也说明：分组统计的结果集只包含事实表中真实存在的数据。

任务 3. 统计用户每月骑行应付总额

任务描述

使用日期函数统计每个用户每个月的骑行应付总额。

实现思路

  • 用 YEAR、MONTH 分别取出账单的年份与月份。
  • 以“用户编号 + 年份 + 月份”三个值分组后再 SUM(should_pay)，避免把 2024 年 12 月与 2025 年 12 月混为同一个月累加。
  • 日期计算类函数用于分析账单间隔、到期日等衍生指标。

参考代码

日期获取类函数。

SELECT YEAR('2025-05-01 12:30:45'), MONTH('2025-05-01 12:30:45'), DAY('2025-05-01 12:30:45');
SELECT HOUR('2025-05-01 12:30:45'), MINUTE('2025-05-01 12:30:45'), SECOND('2025-05-01 12:30:45');
SELECT TO_DATE('2025-05-01 12:30:45'), WEEKOFYEAR('2025-05-01 12:30:45');
SELECT TRUNC('2025-05-10 12:30:45', 'MM'), TRUNC('2025-05-10 12:30:45', 'YYYY');

SELECT bill_time, YEAR(bill_time), MONTH(bill_time), TO_DATE(bill_time) FROM bike_bills LIMIT 5;

日期计算类函数。

SELECT DATEDIFF('2025-07-01', '2025-05-01'), DATEDIFF('2025-05-01', '2025-07-01');
SELECT DATE_ADD('2025-05-01 00:00:00', 10), DATE_SUB('2025-05-01 00:00:00', 10);
SELECT ADD_MONTHS('2025-01-31', 1), ADD_MONTHS('2025-05-01', -2);

统计每个用户骑行记录横跨的天数。

SELECT user_id, DATEDIFF(MAX(bill_time), MIN(bill_time)) AS days
FROM bike_bills
GROUP BY user_id;

统计每个用户每个月的应付总额。

SELECT user_id, YEAR(bill_time), MONTH(bill_time), SUM(should_pay)
FROM bike_bills
GROUP BY user_id, YEAR(bill_time), MONTH(bill_time);

预期结果

最后一条语句共 39 行（各用户有账单的月份数之和），形如：

user_id 年 月 应付总额
U001 2024 12 18.0
U001 2025 1 23.5
U001 2025 2 27.5
U001 2025 3 5.5
U001 2025 4 23.0
U001 2025 6 21.5
U002 2025 1 13.5
… … … …

任务 4. 统计用户月均骑行实付金额

任务描述

利用数学函数统计 2025 年月均骑行实付金额最高的 5 个用户。

实现思路

  • 实付金额为应付金额减去优惠金额，即 should_pay - favour_fee。
  • 账单表记录的是每次骑行的费用明细，同一用户同一月份存在多条明细，因此月账单必须先用 SUM(should_pay - favour_fee) 把明细合并，再对月份求平均。直接对明细行取 AVG(should_pay - favour_fee)，得到的是单条明细的均值，不是月均账单。
  • 本任务按用户编号分组，月份数用 COUNT(DISTINCT MONTH(bill_time)) 单独统计，只计入有账单的月份。
  • 年份是行级过滤条件，写在 WHERE 中先过滤再聚合，不要写进 HAVING。
  • 用 ROUND(..., 2) 保留 2 位小数，按均值降序排序后取前 5 条。

参考代码

数学函数的用法。

SELECT ROUND(3.14159, 2), FLOOR(3.7), CEIL(3.2);
SELECT FLOOR(-3.2), CEIL(-3.2);

统计 2025 年月均骑行实付金额最高的 5 个用户。

SELECT user_id,
       ROUND(SUM(should_pay - favour_fee) / COUNT(DISTINCT MONTH(bill_time)), 2) AS avg_fee
FROM bike_bills
WHERE YEAR(bill_time) = 2025
GROUP BY user_id
ORDER BY avg_fee DESC
LIMIT 5;

预期结果

user_id avg_fee
U003 19.25
U001 18.5
U006 13.63
U007 13.0
U002 12.0
  • 结果恰好是 5 个用户，每人一行，因为只按 user_id 单列分组。
  • 去掉 LIMIT 5 后共 11 行，U011 因无账单不会出现。
  • 若去掉 WHERE YEAR(bill_time) = 2025，U001、U003 的 2024 年 12 月账单也会被计入分子与分母，结果随之改变——这正是年份过滤必须放在聚合之前的原因。
  • 若 favour_fee 存在空值，should_pay - favour_fee 的结果会变成 NULL 并被 SUM 忽略，此时需改写为 should_pay - COALESCE(favour_fee, 0)。

拓展

对月均值分别向下、向上取整。

SELECT user_id,
       FLOOR(SUM(should_pay - favour_fee) / COUNT(DISTINCT MONTH(bill_time))) AS floor_fee,
       CEIL(SUM(should_pay - favour_fee) / COUNT(DISTINCT MONTH(bill_time))) AS ceil_fee
FROM bike_bills
WHERE YEAR(bill_time) = 2025
GROUP BY user_id;

任务 5. 查询电助力订单的用户所在城市

任务描述

通过连接查询获取电助力单车订单的用户城市信息。

实现思路

  • bike_orders 表用 prod_name 字段记录产品名称，电助力订单可用 LIKE '%电助力%' 模糊匹配筛选。
  • 订单表与用户表以 user_id 为连接条件内连接，从用户表取出订单表里没有的 city 字段。
  • 分别改用左外连接、左半开连接，观察结果集的变化。

参考代码

内连接：查询电助力订单的用户城市。

SELECT o.order_id, o.prod_name, u.user_name, u.city
FROM bike_orders o
INNER JOIN
bike_users u
ON o.user_id = u.user_id
WHERE o.prod_name LIKE '%电助力%';

内连接：查询产生订单的用户等级。

SELECT u.user_id, u.user_name, u.member_level, o.order_id
FROM bike_users u
INNER JOIN
bike_orders o
ON u.user_id = o.user_id;

左外连接：保留所有用户，从未下单的用户右表字段显示为 NULL。

SELECT u.user_id, u.user_name, o.order_id, o.prod_name
FROM bike_users u
LEFT JOIN
bike_orders o
ON u.user_id = o.user_id;

左半开连接：只取“有订单的用户”，结果中不能出现右表字段。

SELECT u.user_id, u.user_name, u.city
FROM bike_users u
LEFT SEMI JOIN
bike_orders o
ON u.user_id = o.user_id;

左半开连接错误示范：SELECT 中引用了右表字段。

SELECT u.user_id, u.user_name, o.order_id
FROM bike_users u
LEFT SEMI JOIN
bike_orders o
ON u.user_id = o.user_id;

用 UNION ALL 纵向合并订单记录与充值记录。

SELECT user_id, 'order' AS rec_type, CAST(cost AS STRING) AS amount
FROM bike_orders
UNION ALL
SELECT user_id, 'recharge' AS rec_type, CAST(recharge_amt AS STRING) AS amount
FROM bike_recharge;

预期结果

  • 电助力订单共 11 条，涉及 U001、U003、U006、U007 四位用户，结果形如 O0001 | 电助力单车-单次骑行 | 张伟 | 杭州。
  • 订单与用户的内连接返回 42 行。
  • 用户表左外连接订单表返回 44 行：U011、U012 各多出一行，其 order_id、prod_name 为 NULL。
  • 左半开连接返回 10 行（U001—U010），U011、U012 被排除；把右表字段写进 SELECT 会直接报错。
  • UNION ALL 返回 54 行（42 + 12），其中 U011 只有充值记录。

UNION ALL 要求各子查询的列数、列名与列类型一致，因此 DOUBLE 类型的 recharge_amt 需要用 CAST 转成与 cost 相同的 STRING 类型。若要去重则改用 UNION。

任务 6. 抽样统计用户骑行订单

任务描述

利用抽样查询对用户骑行订单进行抽样统计。

实现思路

  • 创建桶表 bike_orders_bucket，按用户编号 user_id 分为 4 个桶。
  • 从 bike_orders 向桶表中导入数据。
  • 使用 TABLESAMPLE(BUCKET x OUT OF y ON user_id) 抽取指定桶的数据并统计条数。

参考代码

创建桶表。

CREATE TABLE bike_orders_bucket(
  user_id STRING,
  order_id STRING,
  prod_name STRING,
  order_time STRING,
  cost STRING)
CLUSTERED BY(user_id) INTO 4 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

向桶表导入数据。

INSERT OVERWRITE TABLE bike_orders_bucket
SELECT user_id, order_id, prod_name, order_time, cost
FROM bike_orders;

抽取第 3 个桶的数据。

SELECT * FROM bike_orders_bucket
TABLESAMPLE(BUCKET 3 OUT OF 4 ON user_id);

统计每个桶中的数据条数。

SELECT COUNT(*) FROM bike_orders_bucket
TABLESAMPLE(BUCKET 1 OUT OF 4 ON user_id);

预期结果

  • 桶表共 42 条数据，4 个桶的条数由 user_id 的哈希值决定，不一定完全均分。
  • TABLESAMPLE(BUCKET 1 OUT OF 4 ON user_id) 返回其中 1 个桶的数据，x 换成 2、3、4 即抽取对应桶。
  • 由于只扫描单个桶，抽样查询避免了全表遍历，可用于快速估算与数据探查。

五. 拓展练习

  1. 三表连接：查询订单的用户姓名、单车型号与停放区域。

    SELECT o.order_id, u.user_name, i.model, i.park_area
FROM bike_orders o
INNER JOIN bike_users u ON o.user_id = u.user_id
INNER JOIN bike_info i ON o.bike_id = i.bike_id
LIMIT 10;

  2. 统计每个城市的订单总额，并按总额降序排列（提示：三表连接 + GROUP BY u.city）。

  3. 用 CASE 把用户按 2025 年实付总额分为“活跃用户”（≥ 60）、“一般用户”（≥ 30）、“低频用户”三档。

  4. 用 DATEDIFF 统计每个用户首笔订单与最近一笔订单之间相隔的天数，找出骑行跨度最长的用户（提示：DATEDIFF(MAX(order_time), MIN(order_time))）。

  5. 把任务 5 的 UNION ALL 改写为 UNION，对比结果行数发生了什么变化，并说明原因。

六. 常见问题

现象 原因与处理
LOAD DATA LOCAL 报文件不存在 LOCAL 读的是 beeline 客户端本地路径，不是 HDFS 路径；若文件已在 HDFS 上，去掉 LOCAL
CASE 报类型不匹配 WHEN 后的取值类型必须与判断字段完全一致，例如 prod_type 是 STRING，就要写成 WHEN 'E'
中文列别名报错 中文别名必须用反单引号 ` 引起来
左半开连接报 SemanticException 左半开连接的 SELECT 与 WHERE 中都不能引用右表字段
UNION ALL 报列数或类型不一致 保证各子查询列数相同、列名以第一条 SELECT 为准、对应列类型一致，必要时用 CAST 统一
抽样查询报桶数不匹配 TABLESAMPLE(BUCKET x OUT OF y ON field) 中的 y 必须是桶数的倍数或因子
金额计算结果为空 参与运算的字段若存在 NULL，需先用 COALESCE(字段, 0) 兜底

七. 实验报告要求

  1. 记录 6 个任务的完整 SQL 语句，并附关键步骤的运行结果截图。
  2. 说明任务 4 中「先按月汇总、再对月份求平均」与「直接对明细行取 AVG」两种写法的区别，并给出实测结果对比。
  3. 说明任务 5 中内连接、左外连接、左半开连接三种写法的结果集差异，并指出各自的适用场景。