mysql - 根据唯一值填充新列

Question

我有一个用户输入的对话列表：

ID    Timestamp    Message
1     12:00:00     "Bird"
1     12:00:05     "Cat"
2     12:00:12     "Dog"
2     12:00:13     "Monkey"
2     12:00:18     "Fish"
2     12:00:22     "Bird"
3     13:50:55     "Fish"
3     13:55:00     "Cat"
3     14:00:00     "Monkey"
3     14:01:14     "Dog"
3     14:01:25     "Bird"

我需要创建一个根据用户输入输出特定值的列，并按其唯一 ID 进行分区。如果任何用户的消息不包含这些值，则应将其留空。

目前这就是我所拥有的

WITH sub1
AS (
    SELECT t1.id, t1.TIMESTAMP, t1.message
    FROM TABLE t1
    INNER JOIN (
        SELECT DISTINCT id,
            LAST_VALUE(message) OVER (PARTITION BY id ORDER BY TIMESTAMP) cat_or_dog
        FROM TABLE
        WHERE message LIKE "cat"
            OR message LIKE "dog"
        ) t2 ON t1.id = t2.id
    ),
SELECT TABLE.*, sub1.cat_or_dog 
FROM TABLE 
LEFT JOIN sub1 ON sub1.TIMESTAMP = TABLE.TIMESTAMP

这将创建下表：

ID    Timestamp    Message   cat_or_dog
1     12:00:00     "Bird"    "Cat"
1     12:00:05     "Cat"     "Cat"
2     12:00:12     "Dog"     "Dog"
2     12:00:13     "Monkey"  "Dog"
2     12:00:18     "Fish"    "Dog"
2     12:00:22     "Bird"    "Dog"
3     13:50:55     "Fish"    "Dog"
3     13:55:00     "Cat"     "Dog"
3     14:00:00     "Monkey"  "Dog"
3     14:01:14     "Dog"     "Dog"
3     14:01:25     "Bird"    "Dog"

这将创建一个新列，给出在 ID 上分区的值“cat”或“dog”。错误是针对 ID 3 的，其中“dog”是为整个列提供的值。我需要它如下所示：

ID    Timestamp    Message   cat_or_dog
1     12:00:00     "Bird"    "Cat"
1     12:00:05     "Cat"     "Cat"
2     12:00:12     "Dog"     "Dog"
2     12:00:13     "Monkey"  "Dog"
2     12:00:18     "Fish"    "Dog"
2     12:00:22     "Bird"    "Dog"
3     13:50:55     "Fish"    "Cat"
3     13:55:00     "Cat"     "Cat"
3     14:00:00     "Monkey"  "Dog"
3     14:01:14     "Dog"     "Dog"
3     14:01:25     "Bird"    "Dog"

谢谢！

标签： mysql