하위 쿼리를 사용하여 PostgreSQL에서 순차적 숫자 값을 그룹화하고 집계하는 방법은 무엇입니까?

PostgreSQL에서 하위 쿼리를 사용하여 연속 숫자 값을 그룹화하고 집계

숫자 데이터가 포함된 테이블을 처리할 때 순서에 따라 값을 그룹화하고 집계해야 하는 경우가 많습니다. PostgreSQL 9.0 이상에서는 이를 달성하기 위해 하위 쿼리 조합을 사용할 수 있습니다.

비연속 값 식별

첫 번째 단계는 대상 숫자 필드에서 연속되지 않은 값을 식별하는 것입니다. 이는 각 행에 그룹 수를 할당하고 연속되지 않은 값이 발생할 때 카운터를 재설정하는 하위 쿼리를 사용하여 수행할 수 있습니다.

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

그룹 ID 정의

다음으로 연속된 각 값 시퀀스에 대해 그룹 ID를 만듭니다. 여기에는 이전 하위 쿼리의 그룹 수를 합산하는 또 다른 하위 쿼리가 포함됩니다.

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

최종 집계

마지막으로 그룹 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>

이 쿼리는 숫자 순서에 따라 배열로 그룹화된 연도 값과 함께 고유한 회사 및 직업 조합이 포함된 테이블을 반환합니다. 이 방법은 연속적인 숫자 시퀀스를 효과적으로 그룹화하여 후속 분석 및 처리를 용이하게 합니다.

위 내용은 하위 쿼리를 사용하여 PostgreSQL에서 순차적 숫자 값을 그룹화하고 집계하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!