How to Group and Aggregate Sequential Numeric Values in PostgreSQL Using Subqueries?

Grouping and aggregating continuous numeric values ​​using subqueries in PostgreSQL

When dealing with tables containing numerical data, it is often necessary to group and aggregate the values ​​according to their order. In PostgreSQL 9.0 and above, you can use a combination of subqueries to achieve this.

Identify non-continuous values

The first step is to identify non-consecutive values ​​in the target numeric field. This can be done with a subquery that assigns each row a group count and resets the counter when non-consecutive values ​​occur.

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

Define group ID

Next, we create group IDs for each consecutive sequence of values. This involves another subquery that sums the group counts from the previous subquery.

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

Final aggregation

Finally, we aggregate the values ​​for each group using the group ID.

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

This query will return a table containing unique company and occupation combinations, with year values ​​grouped into arrays based on their numerical order. This method effectively groups continuous numerical sequences to facilitate subsequent analysis and processing.

The above is the detailed content of How to Group and Aggregate Sequential Numeric Values in PostgreSQL Using Subqueries?. For more information, please follow other related articles on the PHP Chinese website!