How to Group Consecutive Numeric Values in PostgreSQL using GROUP BY?

Using PostgreSQL's GROUP BY to Aggregate Consecutive Numbers

PostgreSQL offers powerful features for data aggregation. This guide demonstrates how to group consecutive numeric values using the GROUP BY clause, specifically focusing on scenarios involving sequential years.

Example Scenario: Grouping Years by Company and Profession

Imagine a table named qualification with columns company, profession, and year. The goal is to identify unique company-profession combinations and group their consecutive years into arrays.

Step-by-Step Solution:

1. Identifying Non-Consecutive Years: The first step involves pinpointing where consecutive year sequences break. This is done using window functions:

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;

This query uses ROW_NUMBER() to assign a unique rank within each company-profession group and LAG() to compare the current year with the preceding year. group_cnt flags non-consecutive years with a 1.

2. Assigning Group IDs: Next, we assign a unique group ID to each consecutive sequence of years:

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;

The SUM() OVER() window function cumulatively sums the group_cnt values, creating a running total that serves as the group ID (group_nr).

3. Final Aggregation: Finally, we use array_agg() to aggregate the years within each group:

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;

This query groups the results by company, profession, and group_nr, using ARRAY_AGG() to combine the years into arrays for each group.

Expected Output: The final output will group consecutive years into arrays:

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

This method efficiently handles the grouping of consecutive numeric values, providing a clear and concise solution for data analysis in PostgreSQL.

The above is the detailed content of How to Group Consecutive Numeric Values in PostgreSQL using GROUP BY?. For more information, please follow other related articles on the PHP Chinese website!