Wie gruppiert und aggregiert man sequentielle numerische Werte in PostgreSQL mithilfe von Unterabfragen?

Gruppieren und Aggregieren kontinuierlicher numerischer Werte mithilfe von Unterabfragen in PostgreSQL

Beim Umgang mit Tabellen mit numerischen Daten ist es oft notwendig, die Werte entsprechend ihrer Reihenfolge zu gruppieren und zu aggregieren. In PostgreSQL 9.0 und höher können Sie dazu eine Kombination von Unterabfragen verwenden.

Identifizieren Sie nicht kontinuierliche Werte

Der erste Schritt besteht darin, nicht aufeinanderfolgende Werte im numerischen Zielfeld zu identifizieren. Dies kann mit einer Unterabfrage erfolgen, die jeder Zeile eine Gruppenanzahl zuweist und den Zähler zurücksetzt, wenn nicht aufeinanderfolgende Werte auftreten.

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;

Gruppen-ID definieren

Als nächstes erstellen wir Gruppen-IDs für jede aufeinanderfolgende Wertefolge. Dabei handelt es sich um eine weitere Unterabfrage, die die Gruppenanzahl aus der vorherigen Unterabfrage summiert.

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;

Endgültige Aggregation

Abschließend aggregieren wir die Werte für jede Gruppe anhand der Gruppen-ID.

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;

Diese Abfrage gibt eine Tabelle mit eindeutigen Unternehmens- und Berufskombinationen zurück, wobei die Jahreswerte basierend auf ihrer numerischen Reihenfolge in Arrays gruppiert sind. Diese Methode gruppiert effektiv kontinuierliche numerische Folgen, um die anschließende Analyse und Verarbeitung zu erleichtern.

Das obige ist der detaillierte Inhalt vonWie gruppiert und aggregiert man sequentielle numerische Werte in PostgreSQL mithilfe von Unterabfragen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!