How to Group and Aggregate Consecutive Numeric Values in PostgreSQL Using GROUP BY?

How to use GROUP BY to aggregate continuous values ​​in PostgreSQL

PostgreSQL 9.0 and later can aggregate consecutive numeric values ​​in a specific field using the GROUP BY clause. The following query demonstrates how to achieve this in a table containing company, occupation, and year fields:

Step 1: Identify non-continuous values

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

This query assigns a group count (group_cnt) to each row based on whether the years are consecutive. A row with group_cnt 1 indicates the start of a new group.

Step 2: Define group ID

<code class="language-sql">-- 步骤二：定义分组 ID
SELECT
  company,
  profession,
  year,
  SUM(group_cnt) OVER (ORDER BY company, profession, year) AS group_nr
FROM qualification
WHERE group_cnt = 1;</code>

This query assigns a group number (group_nr) to each group. Each consecutive year belongs to the same group.

Step 3: Aggregate consecutive years

<code class="language-sql">-- 步骤三：聚合连续年份
SELECT
  company,
  profession,
  ARRAY_AGG(year) AS years
FROM qualification
WHERE group_cnt <> 0
GROUP BY
  company,
  profession,
  group_nr
ORDER BY
  company,
  profession,
  group_nr;</code>

This final query aggregates consecutive years into an array and filters out non-consecutive rows. Results are grouped by company and occupation.

By following the above three steps, you can effectively aggregate continuous values ​​in PostgreSQL. Note that qualification table should be replaced with your actual table name.

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