Bagaimana untuk Mengumpul Berbilang Tatasusunan dengan Cekap dalam Satu Pertanyaan SQL?

Panggilan berbilang array_agg() dalam Satu Pertanyaan: Mengagregatkan Tatasusunan Bersarang

Dalam bidang pangkalan data hubungan, situasi timbul apabila berbilang tatasusunan perlu diagregatkan dalam satu pertanyaan. Ini boleh menjadi tugas yang sukar, terutamanya apabila terdapat cantuman yang terlibat.

Isunya

Cabaran datang apabila cuba mengagregat tatasusunan daripada jadual berbeza melalui berbilang cantuman. Tatasusunan yang terhasil mungkin mengandungi pendua dan ketidakkonsistenan yang tidak diperlukan. Sebagai contoh, anda mungkin mempunyai jadual pekerja dengan berbilang alamat (alamat) dan hari bekerja (hari bekerja).

SELECT name, age, array_agg(ad.street), arrag_agg(wd.day)
FROM employees e
JOIN address ad ON e.id = ad.employeeid
JOIN workingdays wd ON e.id = wd.employeeid
GROUP BY name, age

Pertanyaan ini menghasilkan susunan nama jalan dan hari bekerja, tetapi ia menduplikasi nilai beberapa kali.

Penyelesaian: Mengagregatkan Dahulu

Kunci untuk menyelesaikan masalah ini isu adalah untuk mengagregat tatasusunan sebelum melakukan gabungan. Dengan mengagregatkan dahulu, anda menghalang baris daripada mendarab secara tidak perlu.

SELECT e.id, e.name, e.age, e.streets, array_agg(wd.day) AS days
FROM (
   SELECT e.id, e.name, e.age, array_agg(ad.street) AS streets
   FROM   employees e 
   JOIN   address  ad ON ad.employeeid = e.id
   GROUP  BY e.id  -- PK covers whole row
   ) e
JOIN   workingdays wd ON wd.employeeid = e.id
GROUP  BY e.id, e.name, e.age;

Dalam pertanyaan ini, pengagregatan alamat dilakukan dalam subkueri sebelum menyertainya dengan jadual hari bekerja. Ini memastikan bahawa hanya satu set nama jalan dan hari bekerja dikaitkan dengan setiap pekerja.

Sebagai alternatif: Subkueri Berkorelasi dan JOIN LATERAL

Untuk penapisan terpilih pada pekerja, berkorelasi subqueries atau JOIN LATERAL (dalam Postgres 9.3 atau lebih baru) boleh bekerja:

SELECT name, age
    , (SELECT array_agg(street) FROM address WHERE employeeid = e.id) AS streets
    , (SELECT array_agg(day) FROM workingdays WHERE employeeid = e.id) AS days
FROM   employees e
WHERE  e.namer = 'peter';  -- very selective

JOIN LATERAL juga boleh digunakan dalam Postgres:

SELECT e.name, e.age, a.streets, w.days
FROM   employees e
LEFT   JOIN LATERAL (
   SELECT array_agg(street) AS streets
   FROM   address
   WHERE  employeeid = e.id
   GROUP  BY 1
   ) a ON true
LEFT   JOIN LATERAL (
   SELECT array_agg(day) AS days
   FROM   workingdays
   WHERE  employeeid = e.id
   GROUP  BY 1
   ) w ON true
WHERE  e.name = 'peter';  -- very selective

Pertanyaan ini akan mengekalkan semua pekerja yang layak dalam keputusan.

Atas ialah kandungan terperinci Bagaimana untuk Mengumpul Berbilang Tatasusunan dengan Cekap dalam Satu Pertanyaan SQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!