按特定顺序在后续 EventA 之前匹配 EventA 和前一个 EventB 之间的记录

Question

我有以下数据（小提琴），

id	datec	事件
1	2022-09-19 12:16:38	EVENTA
2	2022-09-19 12:16:38	A
3	2022-09-19 12:21:08	B
4	2022-09-19 12:21:12	EVENTD
5	2022-09-19 12:25:18	C
6	2022-09-19 12:25:18	D
7	2022-09-19 12:25:28	E
8	2022-09-19 12:25:29	F
9	2022-09-19 12:25:38	EVENTA
10	2022-09-19 12:25:39	G
11	2022-09-19 12:25:40	H
12	2022-09-19 12:25:48	我
13	2022-09-19 12:27:18	EVENTD
14	2022-09-19 12:29:08	J

我不知道如何在另外两个值之间选择值，但要按照特定的顺序。仅应按顺序返回 EVENTA 和 EVENTD 之间的事件。

因此结果应该是 ID 为 1 到 4 和 9 到 13 的行

尝试执行类似以下操作，但它给了我 id 1,4,9 和 13，省略了它们之间的内容。

SELECT id, datec, event 
FROM table1 
WHERE event BETWEEN 'EVENTA' AND 'EVENTD';

然后我尝试使用这个，

SELECT id, datec, event 
FROM table1 
WHERE (id BETWEEN (SELECT id 
                   FROM table1 
                   WHERE event BETWEEN 'EVENTA' AND 'EVENTD' 
                   LIMIT 1) 
              AND (SELECT id 
                   FROM table1 
                   WHERE event BETWEEN 'EVENTA' AND 'EVENTD' 
                   LIMIT 1,1)) 
   OR (id BETWEEN (SELECT id 
                   FROM table1 
                   WHERE event BETWEEN 'EVENTA' AND 'EVENTD' 
                   LIMIT 2,1) 
              AND (SELECT id 
                   FROM table1 
                   WHERE event BETWEEN 'EVENTA' AND 'EVENTD' LIMIT 3,1));

它给了我结果，但我的表中有很多行。

请有人指导我如何重复此操作直到最后，因为我确信有办法做到这一点，但我不知道如何做？

问候，

皮埃尔

Answer 1

这是一种方法：

• 计算武装事件和解除武装事件的运行计数，按日期排序
• 按照解除武装事件的数量排序，计算每个武装事件计数的记录排序

此时您应该注意到，当我们的armed_event分区中还没有EventD时，我们生成的排名值假定为0。当找到第一个EventD时，它的值为1，直到找到下一个EventD。

因此，当此排名值为 0 或 1 并且事件恰好是“EventD”时，您可以在 WHERE 子句中进行相应的过滤。

WITH cte AS (
    SELECT *, SUM(`event`='EVENTA') OVER(ORDER BY datec, id) AS armed_events,
              SUM(`event`='EVENTD') OVER(ORDER BY datec, id) AS disarmed_events
    FROM Table1
), cte2 AS (
    SELECT *, DENSE_RANK() OVER(PARTITION BY armed_events ORDER BY disarmed_events) -1 AS rn
    FROM cte
)
SELECT `id`, `datec`, `event` 
FROM cte2
WHERE rn = 0 OR (rn = 1 AND `event` = 'EVENTD')
ORDER BY id

输出：

id	datec	事件
1	2022-09-19 12:16:38	活动
2	2022-09-19 12:16:38	一个
3	2022-09-19 12:21:08	B
4	2022-09-19 12:21:12	事件
9	2022-09-19 12:25:38	活动
10	2022-09-19 12:25:39	G
11	2022-09-19 12:25:40	H
12	2022-09-19 12:25:48	我
13	2022-09-19 12:27:18	事件