Comment regrouper des valeurs numériques consécutives dans PostgreSQL à l'aide de GROUP BY ?

Utiliser GROUP BY de PostgreSQL pour agréger des nombres consécutifs

PostgreSQL offre des fonctionnalités puissantes pour l'agrégation de données. Ce guide montre comment regrouper des valeurs numériques consécutives à l'aide de la clause GROUP BY, en se concentrant spécifiquement sur les scénarios impliquant des années séquentielles.

Exemple de scénario : regroupement des années par entreprise et profession

Imaginez un tableau nommé qualification avec les colonnes company, profession et year. L'objectif est d'identifier des combinaisons entreprises-profession uniques et de regrouper leurs années consécutives en tableaux.

Solution étape par étape :

1. Identifier les années non consécutives : La première étape consiste à identifier les points de rupture des séquences d'années consécutives. Cela se fait à l'aide des fonctions de fenêtre :

<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>

Cette requête utilise ROW_NUMBER() pour attribuer un classement unique au sein de chaque groupe entreprise-profession et LAG() pour comparer l'année en cours avec l'année précédente. group_cnt signale les années non consécutives avec un 1.

2. Attribution d'identifiants de groupe : Ensuite, nous attribuons un identifiant de groupe unique à chaque séquence d'années consécutives :

<code class="language-sql">SELECT
    company,
    profession,
    year,
    SUM(group_cnt) OVER (ORDER BY company, profession, year) AS group_nr
FROM (
    -- The query from Step 1 goes here
) t1;</code>

La fonction de fenêtre SUM() OVER() additionne cumulativement les valeurs group_cnt, créant un total cumulé qui sert d'identifiant de groupe (group_nr).

3. Agrégation finale : Enfin, nous utilisons array_agg() pour agréger les années au sein de chaque groupe :

<code class="language-sql">SELECT
    company,
    profession,
    ARRAY_AGG(year) AS years
FROM (
    -- The query from Step 2 goes here
) t2
GROUP BY company, profession, group_nr
ORDER BY company, profession, group_nr;</code>

Cette requête regroupe les résultats par company, profession et group_nr, en utilisant ARRAY_AGG() pour combiner les années dans des tableaux pour chaque groupe.

Résultat attendu : Le résultat final regroupera les années consécutives en tableaux :

<code> company | profession |      years
---------+------------+------------------
 Google  | Programmer | {2000}
 Google  | Sales      | {2000,2001,2002}
 Google  | Sales      | {2004}
 Mozilla | Sales      | {2002}
(4 rows)</code>

Cette méthode gère efficacement le regroupement de valeurs numériques consécutives, fournissant une solution claire et concise pour l'analyse des données dans PostgreSQL.

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!