Comment regrouper et agréger des valeurs numériques séquentielles dans PostgreSQL à l'aide de sous-requêtes ?

Regroupement et agrégation de valeurs numériques continues à l'aide de sous-requêtes dans PostgreSQL

Lorsqu'il s'agit de tableaux contenant des données numériques, il est souvent nécessaire de regrouper et d'agréger les valeurs selon leur ordre. Dans PostgreSQL 9.0 et versions ultérieures, vous pouvez utiliser une combinaison de sous-requêtes pour y parvenir.

Identifier les valeurs non continues

La première étape consiste à identifier les valeurs non consécutives dans le champ numérique cible. Cela peut être fait avec une sous-requête qui attribue à chaque ligne un nombre de groupes et réinitialise le compteur lorsque des valeurs non consécutives se produisent.

<code class="language-sql">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;</code>

Définir l'ID du groupe

Ensuite, nous créons des identifiants de groupe pour chaque séquence consécutive de valeurs. Cela implique une autre sous-requête qui additionne le nombre de groupes de la sous-requête précédente.

<code class="language-sql">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;</code>

Agrégation finale

Enfin, nous agrégeons les valeurs de chaque groupe en utilisant l'ID de groupe.

<code class="language-sql">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;</code>

Cette requête renverra un tableau contenant des combinaisons uniques d'entreprises et de professions, avec des valeurs d'année regroupées dans des tableaux en fonction de leur ordre numérique. Cette méthode regroupe efficacement des séquences numériques continues pour faciliter l’analyse et le traitement ultérieurs.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!