跳转至

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

本实验围绕某城市共享单车运营数据，完成3 个查询任务，与课件《第 5 章 广电用户账单与订单数据查询进阶》的前3个知识点一一对应。

实验任务 对应知识点
任务 1 订单消费档次划分 条件函数、CAST 类型转换函数、DISTINCT、ORDER BY
任务 2 统计用户每年骑行应付总额 字符函数（SUBSTR、CONCAT、LOWER 等）、分组聚合
任务 3 统计用户每月骑行应付总额 日期获取类与日期计算类函数

一. 实验目的

  • 掌握 Hive 内置函数的查看方法，能按需求选用条件、字符、日期、数学类函数。
  • 能使用 CASE 条件函数与 CAST 类型转换函数完成数据分档。
  • 能使用日期函数从时间字符串中取出年、月并完成按年、按月的分组统计。
  • 能使用数学函数计算实付金额与月均值。

二. 实验环境

  • 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。

# TODO HiveQL

实验结果截图：

四. 实验任务

任务 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;

再按金额划分消费档次。

# TODO HiveQL

SELECT DISTINCT order_id, 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;

再统计每年应付总额。

# TODO HiveQL

SELECT user_id, SUBSTR(bill_time,1,4), 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;

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

# TODO HiveQL

预期结果

最后一条语句共 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
… … … …

实验结果

# 截图

七. 实验报告要求

  1. 记录 3 个任务的完整 SQL 语句，并附关键步骤的运行结果截图。