Bagaimana untuk Mengekstrak Aktiviti 'A' Terakhir dan 'B' Seterusnya bagi setiap Pengguna dengan Cekap dalam PostgreSQL?

Fungsi Lead/Lag Bersyarat dalam PostgreSQL

Dalam jadual PostgreSQL di mana aktiviti dikumpulkan ke dalam jenis A dan B, supaya aktiviti B sentiasa mengikuti aktiviti A, pengguna mencari penyelesaian untuk mengekstrak aktiviti A terakhir dan aktiviti B seterusnya untuk setiap pengguna. Walaupun fungsi lead() pada mulanya kelihatan seperti pendekatan yang menjanjikan, ia terbukti tidak berkesan.

Fungsi Tetingkap Bersyarat

Malangnya, PostgreSQL tidak menyokong fungsi tetingkap bersyarat. Klausa FILTER, yang boleh menyediakan penapisan bersyarat untuk fungsi tetingkap, hanya tersedia untuk fungsi agregat.

Implikasi dan Penyelesaian Logik

Ceritaan utama terletak pada implikasi logik penyataan masalah: untuk setiap pengguna, terdapat paling banyak satu aktiviti B selepas satu atau lebih aktiviti A. Ini mencadangkan penyelesaian menggunakan fungsi tetingkap tunggal dengan penyataan DISTINCT ON dan CASE.

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;

Pertimbangan Prestasi

Untuk sebilangan kecil pengguna dan aktiviti, pertanyaan di atas berkemungkinan akan berfungsi dengan baik tanpa sebuah indeks. Walau bagaimanapun, apabila bilangan baris dan pengguna bertambah, teknik alternatif mungkin diperlukan untuk mengoptimumkan prestasi.

Pengoptimuman Potensi

Untuk data volum tinggi, pertimbangkan untuk menggunakan pendekatan yang lebih disesuaikan:

• Jika masa membenarkan nilai NULL, tambah NULLS LAST pada ORDER BY klausa.
• Gunakan aktiviti ungkapan padanan corak ~ '^[AB]' dan bukannya aktiviti SEPERTI 'A%' ATAU aktiviti SEPERTI 'B%'.
• Teroka teknik untuk memilih baris pertama dalam setiap kumpulan, seperti yang diterangkan dalam artikel ini: [Pilih baris pertama dalam setiap KUMPULAN OLEH kumpulan?](https://stackoverflow.com/questions/18923181/select-first-row-in-each-group-by-group)
• Siasat teknik yang lebih maju untuk mengoptimumkan pertanyaan GROUP BY, terutamanya apabila berurusan dengan bilangan baris yang tinggi bagi setiap pengguna: [Optimumkan pertanyaan GROUP BY untuk mendapatkan semula baris terkini setiap pengguna](https://dba.stackexchange.com/questions/55252/optimize-group-by-query-to-retrieve-latest-row-per-user)

Atas ialah kandungan terperinci Bagaimana untuk Mengekstrak Aktiviti 'A' Terakhir dan 'B' Seterusnya bagi setiap Pengguna dengan Cekap dalam PostgreSQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!