Bagaimanakah Saya Boleh Cari Aktiviti Seterusnya daripada Kumpulan B Mengikuti Aktiviti Kumpulan A dalam PostgreSQL Menggunakan Fungsi Tetingkap?

Fungsi Lead/Lag Bersyarat dalam PostgreSQL

Tugas anda ialah menjana pertanyaan yang mendapatkan semula urutan aktiviti khusus untuk pengguna daripada jadual tertentu. Anda ingin menentukan aktiviti seterusnya daripada kumpulan B (yang selalu berlaku selepas aktiviti kumpulan A) untuk setiap pengguna.

Definisi Masalah

Pertimbangkan jadual berikut:

Name	activity	time
user1	A1	12:00
user1	E3	12:01
user1	A2	12:02
user2	A1	10:05
user2	A2	10:06
user2	A3	10:07
user2	M6	10:07
user2	B1	10:08
user3	A1	14:15
user3	B2	14:20
user3	D1	14:25
user3	D2	14:30

Output yang diingini untuk jadual ini ialah:

Name	activity	next_activity
user1	A2	NULL
user2	A3	B1
user3	A1	B2

Penyelesaian

Anda boleh menyelesaikan masalah ini dengan memanfaatkan penyataan DISTINCT ON dan CASE bersama-sama dengan fungsi tetingkap:

SELECT name
     , CASE WHEN a2 LIKE 'B%' THEN a1 ELSE a2 END AS activity
     , CASE WHEN a2 LIKE 'B%' THEN a2 END AS next_activity
FROM  (
   SELECT DISTINCT ON (name)
          name
        , lead(activity) OVER (PARTITION BY name ORDER BY time DESC) AS a1
        , activity AS a2
   FROM   t
   WHERE (activity LIKE 'A%' OR activity LIKE 'B%')
   ORDER  BY name, time DESC
   ) sub;

Penjelasan

• The subquery mengenal pasti aktiviti terkini daripada kumpulan A dan aktiviti berikut daripada kumpulan B (jika ada) untuk setiap pengguna menggunakan DISTINCT ON dan fungsi window lead() dengan ORDER BY time DESC.
• Penyata CASE mengendalikan output yang diingini: aktiviti terkini daripada kumpulan A dan aktiviti seterusnya daripada kumpulan B (jika wujud).

Bersyarat Fungsi Tetingkap

Walaupun PostgreSQL tidak menyokong fungsi tetingkap bersyarat secara langsung (cth., lead(aktiviti) FILTER (DIMANA aktiviti SEPERTI 'A%')), anda boleh menggunakan klausa FILTER dengan fungsi agregat dan penggunaan mereka sebagai fungsi tetingkap:

lead(activity) FILTER (WHERE activity LIKE 'A%') OVER () AS activity

Walau bagaimanapun, pendekatan ini tidak cekap dan tidak praktikal untuk set data yang besar. Sebaliknya, penyelesaian yang dibentangkan di atas disyorkan untuk set data kecil dan besar.

Atas ialah kandungan terperinci Bagaimanakah Saya Boleh Cari Aktiviti Seterusnya daripada Kumpulan B Mengikuti Aktiviti Kumpulan A dalam PostgreSQL Menggunakan Fungsi Tetingkap?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!