按特定順序在後續 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	事件