按分组检测测试结果变更并关联多表的SQL实现需求

评估表 #

+---------------+-----------+---------------------+
| assessment_id | device_id |     created_at      |
+---------------+-----------+---------------------+
|             1 |       1   | 2022-07-15 20:03:03 |
|             2 |       2   | 2022-07-15 21:03:03 |
|             3 |       1   | 2022-07-15 22:03:03 |
|             4 |       2   | 2022-07-15 23:03:03 |
|             5 |       2   | 2022-07-15 23:03:03 |
+---------------+-----------+---------------------+

结果表 #

+---------------+---------+--------+
| assessment_id | test    | result |
+---------------+---------+--------+
|             1 |       A | PASS   |
|             2 |       B | FAIL   |
|             3 |       A | FAIL   |
|             4 |       B | PASS   |
|             5 |       B | PASS   |
+---------------+---------+--------+

需求目标 #

需要捕获同一设备同一测试的结果发生变更的记录：

• 设备1的测试A：评估1结果为PASS，评估3结果为FAIL，属于变更，需返回该记录
• 设备2的测试B：评估2到4结果从FAIL变为PASS，属于变更，需返回；评估5结果仍为PASS，无变更，不返回

预期结果 #

+-----------+---------+------------------------+----------------+----------------------+--------------------+------------+----------------------+
| device_id | test_id | previous_assessment_id | previous_value | previous_value_date  | next_assessment_id | next_value |   next_value_date    |
+-----------+---------+------------------------+----------------+----------------------+--------------------+------------+----------------------+
|         1 | A       |                      1 | PASS           | 15/07/2022  20:03:03 |                  3 | FAIL       | 15/07/2022  22:03:03 |
|         2 | B       |                      2 | FAIL           | 15/07/2022  21:03:03 |                  4 | PASS       | 15/07/2022  23:03:03 |
+-----------+---------+------------------------+----------------+----------------------+--------------------+------------+----------------------+

测试表结构 #

CREATE TABLE `assessments` (
  `id` int,
  `device_id` int,
  `created_at` datetime
);

INSERT INTO `assessments` (`id`, `device_id`, `created_at`) VALUES (1, 1, '2022-07-09 22:56:00');
INSERT INTO `assessments` (`id`, `device_id`, `created_at`) VALUES (2, 2, '2022-07-10 22:56:06');
INSERT INTO `assessments` (`id`, `device_id`, `created_at`) VALUES (3, 1, '2022-07-11 22:56:11');
INSERT INTO `assessments` (`id`, `device_id`, `created_at`) VALUES (4, 2, '2022-07-12 22:56:17');
INSERT INTO `assessments` (`id`, `device_id`, `created_at`) VALUES (5, 2, '2022-07-13 22:56:24');

CREATE TABLE `results` (
  `assessment_id` int,
  `test` enum('A','B'),
  `result` enum('PASS','FAIL')
);

INSERT INTO `results` (`assessment_id`, `test`, `result`) VALUES (1, 'A', 'PASS');
INSERT INTO `results` (`assessment_id`, `test`, `result`) VALUES (2, 'B', 'FAIL');
INSERT INTO `results` (`assessment_id`, `test`, `result`) VALUES (3, 'A', 'FAIL');
INSERT INTO `results` (`assessment_id`, `test`, `result`) VALUES (4, 'B', 'PASS');
INSERT INTO `results` (`assessment_id`, `test`, `result`) VALUES (5, 'B', 'PASS');

解决方案 #

使用窗口函数LAG()高效获取同一设备同一测试的上一条评估记录，筛选结果变更的条目后输出完整信息：

WITH combined_data AS (
    SELECT 
        a.device_id,
        r.test,
        a.id AS assessment_id,
        r.result,
        a.created_at,
        -- 获取同设备同测试的上一条评估信息
        LAG(a.id) OVER (PARTITION BY a.device_id, r.test ORDER BY a.created_at) AS prev_assessment_id,
        LAG(r.result) OVER (PARTITION BY a.device_id, r.test ORDER BY a.created_at) AS prev_result,
        LAG(a.created_at) OVER (PARTITION BY a.device_id, r.test ORDER BY a.created_at) AS prev_created_at
    FROM assessments a
    JOIN results r ON a.id = r.assessment_id
)
SELECT 
    device_id,
    test AS test_id,
    prev_assessment_id AS previous_assessment_id,
    prev_result AS previous_value,
    DATE_FORMAT(prev_created_at, '%d/%m/%Y  %H:%i:%s') AS previous_value_date,
    assessment_id AS next_assessment_id,
    result AS next_value,
    DATE_FORMAT(created_at, '%d/%m/%Y  %H:%i:%s') AS next_value_date
FROM combined_data
-- 筛选结果发生变化的有效记录（排除无前置记录的第一条）
WHERE prev_result IS NOT NULL AND result != prev_result
ORDER BY device_id, test_id;

说明 #

1. combined_data CTE：关联评估表与结果表，通过LAG()窗口函数按设备、测试分组，按创建时间排序，获取每条记录的上一条评估数据
2. 主查询：筛选结果变更的记录，格式化日期为预期格式，输出符合要求的变更明细
3. 性能优化：确保assessments.id为主键，给assessments(device_id, created_at)和results(assessment_id)建立索引，可大幅提升查询效率

内容的提问来源于stack exchange，提问作者Arbiter