Padankan rekod antara EventA dan EventB sebelumnya sebelum EventA berikutnya dalam susunan tertentu

Question

Saya mempunyai data berikut (fiddle),

id	datec	Acara
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	saya
13	2022-09-19 12:27:18	EVENTD
14	2022-09-19 12:29:08	J

Saya tidak tahu cara memilih nilai antara dua nilai lain tetapi dalam susunan tertentu. Hanya acara antara EVENTA dan EVENTD harus dikembalikan mengikut urutan.

Jadi hasilnya mestilah baris dengan ID 1 hingga 4 dan 9 hingga 13

Cuba melakukan sesuatu seperti berikut, tetapi ia memberi saya id 1,4,9 dan 13, meninggalkan kandungan di antaranya.

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

Kemudian saya cuba menggunakan ini,

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));

Ia memberikan saya keputusan tetapi saya mempunyai banyak baris dalam jadual saya.

Bolehkah seseorang membimbing saya bagaimana untuk mengulangi ini sehingga akhir kerana saya pasti ada cara untuk melakukannya tetapi saya tidak tahu bagaimana?

Salam,

Pierre

Answer 1

Ini adalah satu cara:

• Kira kiraan larian acara persenjataan dan acara pelucutan senjata, diisih mengikut tarikh
• Isih mengikut bilangan peristiwa pelucutan senjata, kira pengisihan rekod untuk setiap kiraan peristiwa pelucutan senjata

Pada ketika ini, anda harus perasan bahawa apabila belum ada EventD dalam partition armed_event kami, nilai ranking yang kami hasilkan diandaikan sebagai 0. Apabila EventD pertama ditemui, nilainya ialah 1 sehingga EventD seterusnya ditemui.

Jadi apabila nilai kedudukan ini ialah 0 atau 1 dan acara itu menjadi "EventD", anda boleh menapis dengan sewajarnya dalam klausa 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

Output:

id	datec	Acara
1	2022-09-19 12:16:38	Aktiviti
2	2022-09-19 12:16:38	satu
3	2022-09-19 12:21:08	B
4	2022-09-19 12:21:12	Acara
9	2022-09-19 12:25:38	Aktiviti
10	2022-09-19 12:25:39	G
11	2022-09-19 12:25:40	H
12	2022-09-19 12:25:48	saya
13	2022-09-19 12:27:18	Acara