Alex
文章
35
标签
32
评论
1

问题背景

假设我们有一组汽车在平整路面上的行驶数据，每秒记录一次车头的绝对指向角度（取值范围为 [0,360) 度），部分数据如下：

行程ID 行程名称 行程开始后秒数 车头方向[0-360)，单位度
TRIP_ID TRIP_NAME TRIP_SECONDS TRIP_DIRECTION
1002025022200001 第一次测试行程 1 45
1002025022200001 第一次测试行程 2 45
1002025022200001 第一次测试行程 3 45
1002025022200001 第一次测试行程 4 45
1002025022200001 第一次测试行程 5 45
1002025022200001 第一次测试行程 6 49
1002025022200001 第一次测试行程 7 53
1002025022200001 第一次测试行程 8 57
1002025022200001 第一次测试行程 9 61
1002025022200001 第一次测试行程 10 66
1002025022200001 第一次测试行程 11 67
1002025022200001 第一次测试行程 12 68
1002025022200001 第一次测试行程 13 69
1002025022200001 第一次测试行程 14 210
1002025022200001 第一次测试行程 15 69
1002025022200001 第一次测试行程 16 69
1002025022200001 第一次测试行程 17 69
1002025022200001 第一次测试行程 18 69
1002025022200001 第一次测试行程 19 69
1002025022200002 第二次测试行程 1 9
1002025022200002 第二次测试行程 2 9
1002025022200002 第二次测试行程 3 9
1002025022200002 第二次测试行程 4 9
1002025022200002 第二次测试行程 5 8
1002025022200002 第二次测试行程 6 7
1002025022200002 第二次测试行程 7 3
1002025022200002 第二次测试行程 8 359
1002025022200002 第二次测试行程 9 354
1002025022200002 第二次测试行程 10 346
1002025022200002 第二次测试行程 11 341
1002025022200002 第二次测试行程 12 335
1002025022200002 第二次测试行程 13 335
1002025022200002 第二次测试行程 14 335
1002025022200002 第二次测试行程 15 335
1002025022200002 第二次测试行程 16 339
1002025022200002 第二次测试行程 17 344
1002025022200002 第二次测试行程 18 349
1002025022200002 第二次测试行程 19 355
1002025022200002 第二次测试行程 20 1
1002025022200002 第二次测试行程 21 5
1002025022200002 第二次测试行程 22 9
1002025022200002 第二次测试行程 23 10
1002025022200002 第二次测试行程 24 11
1002025022200002 第二次测试行程 25 12
1002025022200002 第二次测试行程 26 13

需求：

  1. 统计转向次数：若连续三秒的转向角度（当前秒与下一秒的方向差）均≥3度，则将第一秒标记为转向开始；若连续三秒转向角度均<3度，则将第一秒标记为转向结束。需统计总转向次数。
  2. 计算最大单次转向总角度：在每次转向过程中，累加所有转向角度，找出最大值。

数据准备

假设数据存储在表 trip_detail 中，结构如下：

DROP TABLE IF EXISTS `trip_detail`;
CREATE TABLE `trip_detail`  (
  `TRIP_ID` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `TRIP_NAME` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `TRIP_SECONDS` decimal(38, 16) NULL DEFAULT NULL,
  `TRIP_DIRECTION` decimal(38, 16) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;


INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 1.0000000000000000, 45.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 2.0000000000000000, 45.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 3.0000000000000000, 45.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 4.0000000000000000, 45.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 5.0000000000000000, 45.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 6.0000000000000000, 49.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 7.0000000000000000, 53.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 8.0000000000000000, 57.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 9.0000000000000000, 61.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 10.0000000000000000, 66.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 11.0000000000000000, 67.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 12.0000000000000000, 68.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 13.0000000000000000, 69.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 14.0000000000000000, 210.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 15.0000000000000000, 69.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 16.0000000000000000, 69.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 17.0000000000000000, 69.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 18.0000000000000000, 69.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200001', '第一次测试行程', 19.0000000000000000, 69.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 1.0000000000000000, 9.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 2.0000000000000000, 9.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 3.0000000000000000, 9.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 4.0000000000000000, 9.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 5.0000000000000000, 8.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 6.0000000000000000, 7.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 7.0000000000000000, 3.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 8.0000000000000000, 359.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 9.0000000000000000, 354.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 10.0000000000000000, 346.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 11.0000000000000000, 341.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 12.0000000000000000, 335.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 13.0000000000000000, 335.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 14.0000000000000000, 335.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 15.0000000000000000, 335.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 16.0000000000000000, 339.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 17.0000000000000000, 344.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 18.0000000000000000, 349.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 19.0000000000000000, 355.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 20.0000000000000000, 1.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 21.0000000000000000, 5.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 22.0000000000000000, 9.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 23.0000000000000000, 10.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 24.0000000000000000, 11.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 25.0000000000000000, 12.0000000000000000);
INSERT INTO `trip_detail` VALUES ('1002025022200002', '第二次测试行程', 26.0000000000000000, 13.0000000000000000);

理解问题

首先，我需要明确问题的核心：

  • 输入是一组汽车行驶数据，每秒记录一次车头的绝对指向角度，范围是0到360度。
  • 输出有两个：
    1. 统计每个行程中汽车转向的总次数。
    2. 计算每次转向的总角度，并找出最大单次转向总角度。

转向的定义：

  • 如果连续三秒的转向角度（当前秒与下一秒的方向差）都大于或等于3度，则将第一秒标记为转向开始。
  • 如果连续三秒的转向角度都小于3度，则将第一秒标记为转向结束。

注意事项：

  • 噪声数据的处理
  • 问题用Excel表格比较清晰明了，但是SQL写起来就比较复杂，需要分步拆解
  • 一开始我采用原生的连表做，但是发现难度太大，后面使用窗口函数进行分析

理论准备

MySQL 8 窗口函数

MySQL 8 引入了窗口函数（Window Functions），用于在 SQL 查询中执行基于某一窗口（数据子集）的计算，而不会影响行数。窗口函数与聚合函数（如 SUM()、AVG()）不同，它们不会将多行合并为一行，而是为每一行返回计算结果。

1. 排名类

这些函数用于计算排名，常用于排名查询、去重等：

  • RANK()：排名相同的行会有相同的排名，排名会跳过相同排名的数量。
  • DENSE_RANK()：类似 RANK()，但不会跳过排名。
  • ROW_NUMBER()：给每一行一个唯一的排名，不考虑值是否相同。
  • NTILE(N)：将数据划分为 N 组，每组分配一个组号。

示例

SELECT 
    name, 
    department, 
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_num,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

解释

  • PARTITION BY department 按 department 分区，每个部门分别排名。
  • ORDER BY salary DESC 依据 salary 进行降序排序。
2. 累积计算类
  • SUM()：累积求和
  • AVG()：累积平均值
  • MAX()：累积最大值
  • MIN()：累积最小值
  • COUNT()：累积计数

示例

SELECT 
    name, 
    department, 
    salary,
    SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS cumulative_salary
FROM employees;

解释

  • 计算 同一部门 按 工资降序排列 的累积工资总和。
3. 偏移函数
  • LAG(column, offset, default)：返回当前行往前第 offset 行的值（若无则返回 default）。
  • LEAD(column, offset, default)：返回当前行往后第 offset 行的值（若无则返回 default）。
  • FIRST_VALUE(column)：返回当前窗口的第一行的值。
  • LAST_VALUE(column)：返回当前窗口的最后一行的值。
  • NTH_VALUE(column, N)：返回窗口中第 N 行的值。

示例

SELECT 
    name, 
    department, 
    salary,
    LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary,
    LEAD(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary DESC) AS next_salary
FROM employees;

解释

  • LAG() 获取上一行的 salary（即前一名员工的工资）。
  • LEAD() 获取下一行的 salary（即后一名员工的工资）。
4. 移动窗口计算

可定义一个滑动窗口，在当前行的前后一定范围内进行聚合计算：

SELECT 
    name, 
    department, 
    salary,
    AVG(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM employees;

解释

  • ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING：窗口范围是当前行前 2 行到后 1 行。
  • AVG(salary) 在该窗口范围内计算平均工资。

分析步骤

学习完理论知识直接上实战

1. 计算每秒转向角度

首先需要计算每秒钟的转向角度（即当前秒与下一秒的方向差）。由于方向是环状的（0-360度），需处理跨360度的情况：

SELECT *,
LEAD(TRIP_DIRECTION, 1) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS) AS pre_dir,

-- 标准化角度差值 (处理环状差值)
CASE
    WHEN ABS(TRIP_DIRECTION - LEAD(TRIP_DIRECTION, 1) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS)) > 180  THEN
        360 - ABS(TRIP_DIRECTION - LEAD(TRIP_DIRECTION, 1) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS))
    ELSE
        ABS(TRIP_DIRECTION - LEAD(TRIP_DIRECTION, 1) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS))
END AS normalized_dir
FROM trip_detail;

输出示例：

TRIP_ID TRIP_NAME TRIP_SECONDS TRIP_DIRECTION PRE_DIR normalized_dir
1002025022200001 第一次测试行程 1.0000000000000000 45.0000000000000000 45.0000000000000000 0
1002025022200001 第一次测试行程 2.0000000000000000 45.0000000000000000 45.0000000000000000 0
1002025022200001 第一次测试行程 3.0000000000000000 45.0000000000000000 45.0000000000000000 0
1002025022200001 第一次测试行程 4.0000000000000000 45.0000000000000000 45.0000000000000000 0
1002025022200001 第一次测试行程 5.0000000000000000 45.0000000000000000 49.0000000000000000 4
1002025022200001 第一次测试行程 5.0000000000000000 49.0000000000000000 53.0000000000000000 4
1002025022200001 第一次测试行程 5.0000000000000000 53.0000000000000000 57.0000000000000000 4
1002025022200001 第一次测试行程 5.0000000000000000 57.0000000000000000 61.0000000000000000 4

2. 踢除噪声数据

在查看数据过程中发现有异常数据,例如一秒钟转向超过90度,我们可以视为不合理数据,需要踢除

-- 1 计算转向角度
WITH Directions AS (
    SELECT *,
    -- 计算下一个方向值
    LEAD(TRIP_DIRECTION, 1) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS) AS pre_dir,
    -- 计算标准化的角度差值 359度转为1度
    CASE
        WHEN ABS(TRIP_DIRECTION - LEAD(TRIP_DIRECTION, 1) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS)) > 180 THEN
            360 - ABS(TRIP_DIRECTION - LEAD(TRIP_DIRECTION, 1) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS))
        ELSE
            ABS(TRIP_DIRECTION - LEAD(TRIP_DIRECTION, 1) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS))
    END AS normalized_dir
    FROM trip_detail
),

-- 2.1 踢除噪声数据
NormalizedDirections AS (
	SELECT * FROM Directions WHERE  normalized_dir < 90
)

select * from NormalizedDirections;

3.标记转向开始与结束

根据规则，标记连续三秒的转向行为：

-- 1 计算转向角度
Directions,
-- 2.1 踢除噪声数据
NormalizedDirections
-- 2.2 计算转向关键帧
TurnStarts AS (
    SELECT *,
    -- 判断是否为转向开始
    CASE
        WHEN normalized_dir >= 3 AND LEAD(normalized_dir, 1) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS) >= 3 AND LEAD(normalized_dir, 2) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS) >= 3 THEN 1
        ELSE 0
    END AS start_turn,
		-- 判断是否为转向结束
		 CASE
        WHEN normalized_dir < 3 AND LEAD(normalized_dir, 1) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS) < 3 AND LEAD(normalized_dir, 2) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS) < 3 THEN 1
        ELSE 0
    END AS end_turn
    FROM NormalizedDirections
)
select * from NormalizedDirections;

输出示例：

TRIP_ID TRIP_NAME TRIP_SECONDS TRIP_DIRECTION PRE_DIR normalized_dir start_turn end_turn
1002025022200001 第一次测试行程 1.0000000000000000 45 45 0 0 0
1002025022200001 第一次测试行程 2.0000000000000000 45 45 0 0 0
1002025022200001 第一次测试行程 3.0000000000000000 45 45 0 0 0
1002025022200001 第一次测试行程 4.0000000000000000 45 45 0 0 0
1002025022200001 第一次测试行程 5.0000000000000000 45 49 4 1 0
1002025022200001 第一次测试行程 6.0000000000000000 49 53 4 1 0
1002025022200001 第一次测试行程 7.0000000000000000 53 57 4 1 0
1002025022200001 第一次测试行程 8.0000000000000000 57 61 4 1 0
1002025022200001 第一次测试行程 9.0000000000000000 61 66 5 1 0
1002025022200001 第一次测试行程 9.0000000000000000 66 67 5 1 1

3. 统计转向次数

通过标记的 start_turn 和 end_turn，统计每次转向的起止 打上标记：

-- 2.3 计算真正转向起始次数
TurnCount AS(

	SELECT * ,
	-- 判断开始转向帧 向上取3行如果有 转向结束标记 看做转向开始
	CASE 	WHEN start_turn  =1 and  LAG(end_turn,3) over(PARTITION by TRIP_ID ) = 1 THEN 1 ELSE 0 END AS real_start_turn,
	-- 判断结束转向帧 向上取3行如果有 转向开始标记 看做结束开始
	CASE 	WHEN end_turn  = 1 and  LAG(start_turn,3) over(PARTITION by TRIP_ID) = 1 THEN 1 ELSE 0 END AS real_end_turn

	 FROM  TurnStarts

)

输出：每个行程的转向次数。

4. 计算最大单次转向总角度

累加每次转向过程中的转向角度：

-- 2.4 获取最终数据合并统计转向次数与最大转向角度
FinalResult AS(
SELECT TRIP_ID,count(CASE WHEN real_start_turn = 1 THEN 1 END ) AS 转向次数,sum(normalized_dir) 最大转向角度 FROM TurnCount GROUP BY  TRIP_ID
)

SELECT * FROM FinalResult;

输出：每个行程的最大单次转向总角度。

完整SQL代码

-- 1.计算转向角度
WITH Directions AS (
    SELECT *,
    -- 计算下一个方向值
    LEAD(TRIP_DIRECTION, 1) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS) AS pre_dir,
    -- 计算标准化的角度差值 359度转为1度
    CASE
        WHEN ABS(TRIP_DIRECTION - LEAD(TRIP_DIRECTION, 1) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS)) > 180 THEN
            360 - ABS(TRIP_DIRECTION - LEAD(TRIP_DIRECTION, 1) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS))
        ELSE
            ABS(TRIP_DIRECTION - LEAD(TRIP_DIRECTION, 1) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS))
    END AS normalized_dir
    FROM trip_detail
),

-- 1.1 踢除噪声数据
NormalizedDirections AS (
	SELECT * FROM Directions WHERE  normalized_dir < 90
),

-- 1.2 计算转向关键帧
TurnStarts AS (
    SELECT *,
    -- 判断是否为转向开始
    CASE
        WHEN normalized_dir >= 3 AND LEAD(normalized_dir, 1) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS) >= 3 AND LEAD(normalized_dir, 2) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS) >= 3 THEN 1
        ELSE 0
    END AS start_turn,
		-- 判断是否为转向结束
		 CASE
        WHEN normalized_dir < 3 AND LEAD(normalized_dir, 1) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS) < 3 AND LEAD(normalized_dir, 2) OVER (PARTITION BY TRIP_ID ORDER BY TRIP_SECONDS) < 3 THEN 1
        ELSE 0
    END AS end_turn
    FROM NormalizedDirections
),

-- 1.3 计算真正转向起始次数
TurnCount AS(

	SELECT * ,
	-- 判断开始转向帧 向上取3行如果有 转向结束标记 看做转向开始
	CASE 	WHEN start_turn  =1 and  LAG(end_turn,3) over(PARTITION by TRIP_ID ) = 1 THEN 1 ELSE 0 END AS real_start_turn,
	-- 判断结束转向帧 向上取3行如果有 转向开始标记 看做结束开始
	CASE 	WHEN end_turn  = 1 and  LAG(start_turn,3) over(PARTITION by TRIP_ID) = 1 THEN 1 ELSE 0 END AS real_end_turn

	 FROM  TurnStarts

),

-- 1.4 获取最终数据合并统计转向次数与最大转向角度
FinalResult AS(
SELECT TRIP_ID,count(CASE WHEN real_start_turn = 1 THEN 1 END ) AS 转向次数,sum(normalized_dir) 最大转向角度 FROM TurnCount GROUP BY  TRIP_ID )

SELECT * FROM FinalResult;

当然我这种写法还是存在优化的点,例如获取转向关键帧取值是固定的,可以用其他的窗口函数优化,其他的写法可能也可以完成这个需求

文章作者: Alex
文章连结: /archives/complexsqlcasestudy-cartripturningstatistics
版权声明: 本部落格所有文章除特别声明外，均採用 CC BY-NC-SA 4.0 许可协议。转载请註明来自 Alex！
SQL 数据分析
上一篇 下一篇