Bagaimana untuk Menghimpun dan Mengagregat Nilai Angka Berjujukan dalam PostgreSQL Menggunakan Subqueries?

Menghimpun dan mengagregatkan nilai berangka berterusan menggunakan subkueri dalam PostgreSQL

Apabila berurusan dengan jadual yang mengandungi data berangka, selalunya perlu untuk mengumpulkan dan mengagregatkan nilai mengikut susunannya. Dalam PostgreSQL 9.0 dan ke atas, anda boleh menggunakan gabungan subkueri untuk mencapai ini.

Kenal pasti nilai tidak berterusan

Langkah pertama ialah mengenal pasti nilai bukan berturut-turut dalam medan angka sasaran. Ini boleh dilakukan dengan subkueri yang memberikan setiap baris kiraan kumpulan dan menetapkan semula pembilang apabila nilai tidak berturut-turut berlaku.

SELECT company, profession, year,
       CASE
           WHEN row_number() OVER (PARTITION BY company, profession ORDER BY year) = 1
               OR year - lag(year, 1, year) OVER (PARTITION BY company, profession ORDER BY year) > 1
           THEN 1
           ELSE 0
       END AS group_cnt
FROM qualification;

Tentukan ID kumpulan

Seterusnya, kami mencipta ID kumpulan untuk setiap jujukan nilai yang berturut-turut. Ini melibatkan subkueri lain yang menjumlahkan kiraan kumpulan daripada subkueri sebelumnya.

SELECT company, profession, year,
       SUM(group_cnt) OVER (ORDER BY company, profession, year) AS group_nr
FROM (
    SELECT company, profession, year,
           CASE
               WHEN row_number() OVER (PARTITION BY company, profession ORDER BY year) = 1
                   OR year - lag(year, 1, year) OVER (PARTITION BY company, profession ORDER BY year) > 1
               THEN 1
               ELSE 0
           END AS group_cnt
    FROM qualification
) t1;

Penggabungan akhir

Akhir sekali, kami mengagregatkan nilai untuk setiap kumpulan menggunakan ID kumpulan.

SELECT company, profession, array_agg(year) AS years
FROM (
    SELECT company, profession, year,
           SUM(group_cnt) OVER (ORDER BY company, profession, year) AS group_nr
    FROM (
        SELECT company, profession, year,
               CASE
                   WHEN row_number() OVER (PARTITION BY company, profession ORDER BY year) = 1
                       OR year - lag(year, 1, year) OVER (PARTITION BY company, profession ORDER BY year) > 1
                   THEN 1
                   ELSE 0
               END AS group_cnt
        FROM qualification
    ) t1
) t2
GROUP BY company, profession, group_nr
ORDER BY company, profession, group_nr;

Pertanyaan ini akan mengembalikan jadual yang mengandungi gabungan syarikat dan pekerjaan yang unik, dengan nilai tahun dikumpulkan ke dalam tatasusunan berdasarkan susunan berangkanya. Kaedah ini secara berkesan mengumpulkan jujukan berangka berterusan untuk memudahkan analisis dan pemprosesan seterusnya.

Atas ialah kandungan terperinci Bagaimana untuk Menghimpun dan Mengagregat Nilai Angka Berjujukan dalam PostgreSQL Menggunakan Subqueries?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!