Bagaimana untuk Memindahkan Baris Sejarah E-mel Pengguna ke Lajur dalam PostgreSQL?

Menstrukturkan Semula Sejarah E-mel Pengguna: Baris ke Lajur dalam PostgreSQL

Bayangkan jadual menyimpan berbilang alamat e-mel untuk setiap pengguna. Artikel ini menunjukkan cara untuk mengubah data berasaskan baris ini dengan cekap kepada format lajur, khususnya menjajarkan alamat e-mel terbaharu sebagai lajur yang berbeza. Kami akan mencapai ini menggunakan modul tablefunc PostgreSQL dan fungsi crosstab() yang berkuasa.

Penyelesaian crosstab()

Fungsi crosstab() dalam modul tablefunc sangat sesuai untuk menukar baris ke lajur pada setiap pengguna. Pertanyaan berikut menggambarkan aplikasinya:

SELECT *
FROM crosstab(
   $$SELECT user_id, user_name, rn, email_address
     FROM  (
        SELECT u.user_id, u.user_name, e.email_address
             , row_number() OVER (PARTITION BY u.user_id
                            ORDER BY e.creation_date DESC NULLS LAST) AS rn
        FROM   usr u
        LEFT   JOIN email_tbl e USING (user_id)
        ) sub
     WHERE  rn <= 3
   $$,
   $$VALUES (1),(2),(3)$$
) AS ct (user_id INT, user_name TEXT, email_1 TEXT, email_2 TEXT, email_3 TEXT);

Memahami Komponen Pertanyaan

• Argumen pertama untuk crosstab() ialah subkueri. Subkueri ini mendapatkan semula ID pengguna, nama pengguna, nombor baris (rn) dan alamat e-mel.
• row_number() OVER (PARTITION BY u.user_id ORDER BY e.creation_date DESC NULLS LAST) memberikan kedudukan unik (rn) kepada setiap alamat e-mel untuk setiap pengguna, dipesan mengikut tarikh penciptaan (terbaru dahulu).
• Klausa WHERE (WHERE rn ) mengehadkan keputusan kepada tiga alamat e-mel terbaharu bagi setiap pengguna.
• Argumen kedua untuk crosstab() ($$VALUES (1),(2),(3)$$) menentukan nilai yang berpotensi untuk lajur baharu (mewakili kedudukan alamat e-mel).

Mengatasi Potensi Cabaran

Ketiadaan kunci yang berbeza pada jadual asal untuk setiap alamat e-mel memerlukan penggunaan row_number() untuk menjana kunci pengganti. Tambahan pula, bilangan pembolehubah e-mel bagi setiap pengguna memerlukan mengehadkan output kepada tiga lajur dalam penyataan SELECT luar dan mentakrifkan julat nilai kunci yang mungkin dalam parameter crosstab() kedua.

Pendekatan ini secara berkesan mengubah alamat e-mel terkini bagi setiap pengguna kepada lajur yang berasingan, mempersembahkan paparan sejarah e-mel pengguna yang lebih bersih dan teratur.

Atas ialah kandungan terperinci Bagaimana untuk Memindahkan Baris Sejarah E-mel Pengguna ke Lajur dalam PostgreSQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!