Bagaimana untuk Melaksanakan Fungsi Lead/Lag Bersyarat dalam PostgreSQL?

Postgres Fungsi Lead/Lag Bersyarat?

Ramai pengguna keliru dengan cara melaksanakan fungsi petunjuk/lag bersyarat dalam Postgres menggunakan fungsi windowing. Dalam contoh ini, kami mempunyai jadual dengan data seperti ini:

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

Kami ingin mengubah jadual ini menjadi laporan yang menunjukkan aktiviti seterusnya jenis B untuk setiap pengguna dengan aktiviti sebelumnya adalah jenis A. Secara khusus, kami berhasrat untuk mencipta jadual seperti ini:

Name	Activity	Next Activity
user1	A2	NULL
user2	A3	B1
user3	A1	B2

Tetingkap Bersyarat Fungsi

Secara tradisinya, pengguna boleh cuba menggunakan fungsi lead() untuk menyelesaikan masalah ini. Walau bagaimanapun, disebabkan oleh had fungsi tetingkap Postgres, fungsi bersyarat tidak disokong secara langsung. Khususnya, klausa FILTER tidak boleh digunakan untuk lead() atau lag().

Menyelesaikan Masalah

Untuk menyelesaikan masalah ini, kita mesti meninggalkan tanggapan menggunakan fungsi petunjuk bersyarat dan sebaliknya menggunakan strategi pertanyaan yang lebih kompleks. Satu pendekatan ialah menggunakan pernyataan DISTINCT ON dan CASE untuk mencapai hasil yang diinginkan. Berikut ialah pertanyaan yang menunjukkan strategi ini:

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

• Subkueri memilih baris yang berbeza untuk setiap pengguna, disusun mengikut masa menurun.
• Fungsi lead() mengembalikan aktiviti seterusnya dalam urutan, yang digunakan untuk menentukan aktiviti seterusnya jenis B untuk setiap pengguna.
• Pernyataan CASE digunakan untuk menentukan aktiviti dan aktiviti seterusnya berdasarkan jenis aktiviti.

Pertimbangan Prestasi

Untuk set data kecil, pertanyaan di atas harus berfungsi dengan cukup baik. Walau bagaimanapun, untuk set data yang besar, mengoptimumkan pertanyaan menggunakan teknik seperti pengindeksan atau fungsi tetingkap mungkin diperlukan.

Atas ialah kandungan terperinci Bagaimana untuk Melaksanakan Fungsi Lead/Lag Bersyarat dalam PostgreSQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!