mysql - 当每组的列值发生变化时选择并连接行

Question

评估表:

+---------------+-----------+---------------------+
| 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   |
+---------------+---------+--------+

目标

我希望每次 test_id 的结果发生变化时返回一行。

例如，设备 1 有评估 1 和 3。评估 1 中的测试 A 通过，评估 3 中的测试 A 失败，因此我们希望将此更改作为一行返回。

设备 2 有评估 2、4 和 5。评估 2 和 4 中的测试结果发生变化(测试 B 从 FAIL 更改为 PASS)，我们希望为此返回一行。

我们不想返回评估 5 的行，因为测试 B 是通过的，并且在评估 4 中也是通过的。没有变化。

生成的表格如下所示:

+-----------+---------+------------------------+----------------+----------------------+--------------------+------------+----------------------+
| 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 |
+-----------+---------+------------------------+----------------+----------------------+--------------------+------------+----------------------+

我尝试过处理发现的几个查询 here on SO ，但它们要么花费很长时间并返回错误的数据，要么根本不起作用。我不认为这是重复的，因为我使用了多个表，并且我看到的所有其他问题都引用了一个表。

我还查看了 this SO question但无法获得适用于我的情况的有用答案。

我在让 SQL Fiddle 工作时遇到一些奇怪的问题，但这是我一直在修改的测试架构:

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

INSERT INTO `so_assessments` (`id`, `device_id`, `created_at`) VALUES (1, 1, '2022-07-09 22:56:00');
INSERT INTO `so_assessments` (`id`, `device_id`, `created_at`) VALUES (2, 2, '2022-07-10 22:56:06');
INSERT INTO `so_assessments` (`id`, `device_id`, `created_at`) VALUES (3, 1, '2022-07-11 22:56:11');
INSERT INTO `so_assessments` (`id`, `device_id`, `created_at`) VALUES (4, 2, '2022-07-12 22:56:17');
INSERT INTO `so_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');

Answer 1

如果您使用的是 MySQL 8，窗口函数可以提供帮助。 https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

您可以按设备和测试对结果进行分区，并添加一个作为结果<的先前值的列，然后使用结果与先前值不同的最后一行。

以下查询使用 previous_value 在结果中创建一个新列

SELECT 
    assessment_id,
    device_id,
    test,
    result,
    LAG (result) over w as `previous_value`,
    LAG (assessment_id) over w as `previous_assessment_id`
FROM assessments join results using(assessment_id)
WINDOW w AS (PARTITION BY device_id, test ORDER BY assessment_id)

产生结果:

+---------------+-----------+------+--------+----------------+------------------------+
| assessment_id | device_id | test | result | previous_value | previous_assessment_id |
+---------------+-----------+------+--------+----------------+------------------------+
|             1 |         1 | A    | PASS   | NULL           |                   NULL |
|             3 |         1 | A    | FAIL   | PASS           |                      1 |
|             2 |         2 | B    | FAIL   | NULL           |                   NULL |
|             4 |         2 | B    | PASS   | FAIL           |                      2 |
|             5 |         2 | B    | PASS   | PASS           |                      4 |
+---------------+-----------+------+--------+----------------+------------------------+

这是战斗的重要组成部分。现在我们需要获取该结果并找到具有最高assessment_id 的每个设备/测试对的行，其中 result != previous_value。

窗口是在GROUP BY、ORDER BY甚至HAVING之后计算的，因此没有太多可以做的事情查询(我想到的)将其范围缩小到每个设备/测试对的最新条目。所以上面必须是一个子查询才能得到最终结果。

注意:我假设如果结果永远不会改变，您希望显示第一次记录结果的时间。换句话说，您希望以 previous_value = NULL 作为转换来计算结果。

以下查询列出了设备/测试对的测试结果更改的所有时间:

SELECT * FROM 
(SELECT 
    assessment_id,
    device_id,
    test,
    result,
    LAG (result) over w as `previous_value`
FROM assessments join results using(assessment_id)
WINDOW w AS (PARTITION BY `device_id`, `test` ORDER BY `assessment_id`)
) AS t
WHERE result != `previous_value` OR `previous_value` IS NULL

获取结果(出于空间考虑，我省略了 previous_assessment_id 和其他内容):

+---------------+-----------+------+--------+----------------+
| assessment_id | device_id | test | result | previous_value |
+---------------+-----------+------+--------+----------------+
|             1 |         1 | A    | PASS   | NULL           |
|             3 |         1 | A    | FAIL   | PASS           |
|             2 |         2 | B    | FAIL   | NULL           |
|             4 |         2 | B    | PASS   | FAIL           |
+---------------+-----------+------+--------+----------------+

编辑

这就是问题的答案。如果第一次设置值不感兴趣，只需删除 WHERE 子句的 OR 部分即可。这个答案的其余部分是因为我说服自己问题是获取值翻转的最近时间。我把它留在这里，但只是为了兴趣。

继续

这是结果与之前不同的所有时间，加上第一次记录结果的时间。快到了。

此时很容易在外部查询中添加另一个窗口来聚合上面的行并识别正确的行。但至少在 MySQL 8 中，不支持嵌套窗口。

但鉴于该结果，我们可以使用 MAX() 和 GROUP BY 创建一个查询，给出我们最终想要的所有行的assessment_ids:

SELECT MAX(assessment_id)
FROM (
    SELECT 
       assessment_id,
       device_id,
       test,
       result,
       LAG (result) over w as `previous_value`,
       LAG (assessment_id) over w as `previous_assessment_id`
    FROM assessments join results using(assessment_id)
    WINDOW w AS (PARTITION BY device_id, test ORDER BY assessment_id)
) AS t

where result != previous_value OR previous_value IS NULL
GROUP BY device_id, test

其产量:

+--------------------+
| MAX(assessment_id) |
+--------------------+
|                  3 |
|                  4 |
+--------------------+

现在我们确切地知道我们需要哪些行；但是我们构建了有关先前值的所有数据，现在我们需要一种方法将该查询的结果与子查询的结果连接起来。

令人高兴的是，MySQL 8 有一种方法来存储查询并多次使用它，称为公共(public)表表达式，它使用 WITH 子句 docs here 。因此，我们可以创建包含所有有趣数据的表，然后将其用作子查询来获取我们最终想要的 id，然后将其与我们刚刚创建的结果连接起来:

WITH
  transitions AS (SELECT 
    assessment_id,
    device_id,
    test,
    result,
    LAG (result) over w as `previous_value`,
    LAG (assessment_id) over w as `previous_assessment_id`
FROM assessments join results using(assessment_id)
WINDOW w AS (PARTITION BY device_id, test ORDER BY assessment_id)
)

SELECT transitions.*
FROM transitions
JOIN (
    SELECT MAX(assessment_id) as assessment_id
    FROM transitions
    WHERE result != previous_value OR previous_value IS NULL
    GROUP BY device_id, test
) AS t2 using (assessment_id)

给我们最终答案(以及您可以填写的其他列):

+---------------+-----------+------+--------+----------------+------------------------+
| assessment_id | device_id | test | result | previous_value | previous_assessment_id |
+---------------+-----------+------+--------+----------------+------------------------+
|             3 |         1 | A    | FAIL   | PASS           |                      1 |
|             4 |         2 | B    | PASS   | FAIL           |                      2 |
+---------------+-----------+------+--------+----------------+------------------------+

第一部分创建一个数据集，其中包含有关每次测试之前发生的情况的所有信息。然后我们编写一个查询来获取该查询中感兴趣的行的 id，然后我们连接回到原始数据集以填充所有列。