[SQL] GROUP BY와 집계 순차적 인 숫자 값
SQLGROUP BY와 집계 순차적 인 숫자 값
PostgreSQL의 9.0를 사용.
기업, 직업 및 연도 :의 내가 필드를 포함하는 테이블이 있다고 가정 해 봅시다. 나는 독특한 기업과 직업을 포함하는 결과를 반환하고 싶지만, 집계 숫자 순서에 따라 세 (배열로 괜찮습니다) :
예 테이블 :
+-----------------------------+
| company | profession | year |
+---------+------------+------+
| Google | Programmer | 2000 |
| Google | Sales | 2000 |
| Google | Sales | 2001 |
| Google | Sales | 2002 |
| Google | Sales | 2004 |
| Mozilla | Sales | 2002 |
+-----------------------------+
나는하는 다음과 같은 출력 행을하고자하는 쿼리에 관심이 있어요 :
+-----------------------------------------+
| company | profession | year |
+---------+------------+------------------+
| Google | Programmer | [2000] |
| Google | Sales | [2000,2001,2002] |
| Google | Sales | [2004] |
| Mozilla | Sales | [2002] |
+-----------------------------------------+
필수 기능은 년 연속이 함께 그룹화되어야한다는 것입니다.
해결법
-
==============================
1.이미 PostgreSQL을에 윈도우 함수의 다른 종류를 사용하는 방법을 학습을위한 좋은 재료로 댓글에서 말했다 a_horse_with_no_name의 대답 @, 모두 정확한 솔루션으로하고, 같은 훨씬 가치가있다.
이미 PostgreSQL을에 윈도우 함수의 다른 종류를 사용하는 방법을 학습을위한 좋은 재료로 댓글에서 말했다 a_horse_with_no_name의 대답 @, 모두 정확한 솔루션으로하고, 같은 훨씬 가치가있다.
그럼에도 불구하고 나는 그 대답에 취한 접근 방식은 조금이 같은 문제에 대해 너무 많은 노력의 것을 도움말 느낌이 없습니다. 기본적으로, 당신이 필요하면 배열에 년 집계에 가기 전에 그룹화를위한 추가 기준입니다. 이미 지금은 다른 시퀀스에 속하는 년을 구별 할 뭔가가 필요, 회사와 직업을 가지고있다.
즉 상술 한 대답 위의 제공과 그것이 내가 간단한 방법으로 수행 할 수 있습니다 생각 정확히 것입니다. 방법은 다음과 같습니다
WITH MarkedForGrouping AS ( SELECT company, profession, year, year - ROW_NUMBER() OVER ( PARTITION BY company, profession ORDER BY year ) AS seqID FROM atable ) SELECT company, profession, array_agg(year) AS years FROM MarkedForGrouping GROUP BY company, profession, seqID -
==============================
2.비 연속적인 값을 확인하는 것은 항상 조금 까다 롭습니다하고 (적어도 내가 더 나은 솔루션을 가지고 올 수 없습니다) 여러 가지 중첩 된 하위 쿼리를 포함한다.
비 연속적인 값을 확인하는 것은 항상 조금 까다 롭습니다하고 (적어도 내가 더 나은 솔루션을 가지고 올 수 없습니다) 여러 가지 중첩 된 하위 쿼리를 포함한다.
첫 번째 단계는 년 비 연속적인 값을 식별하는 것입니다 :
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이것은 다음과 같은 결과를 반환합니다 :
company | profession | year | group_cnt ---------+------------+------+----------- Google | Programmer | 2000 | 1 Google | Sales | 2000 | 1 Google | Sales | 2001 | 0 Google | Sales | 2002 | 0 Google | Sales | 2004 | 1 Mozilla | Sales | 2002 | 1
이제 group_cnt 값으로 우리는 년 연속이있는 각 그룹에 대해 "그룹 ID"를 만들 수 있습니다 :
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이것은 다음과 같은 결과를 반환합니다 :
company | profession | year | group_nr ---------+------------+------+---------- Google | Programmer | 2000 | 1 Google | Sales | 2000 | 2 Google | Sales | 2001 | 2 Google | Sales | 2002 | 2 Google | Sales | 2004 | 3 Mozilla | Sales | 2002 | 4 (6 rows)
각 "그룹"을 볼 수 있듯이 자신의 group_nr있어이 우리는 마침내 또 다른 파생 테이블을 추가하여 이상 통합 할 수 있습니다 :
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이것은 다음과 같은 결과를 반환합니다 :
company | profession | years ---------+------------+------------------ Google | Programmer | {2000} Google | Sales | {2000,2001,2002} Google | Sales | {2004} Mozilla | Sales | {2002} (4 rows)어느 내가 잘못 아니에요 경우에 당신이 원하던 것입니다.
-
==============================
3.문제는 집계 / 윈도우 기능을 가진 일반 SQL 오히려 다루기 힘든이다. 루프 동안 일반 SQL과 집합 기반 솔루션보다 일반적으로 느린 테이블 (A FOR 루프의 암시 적 커서)를 통해 하나의 순차 검색과 관련이 특별한 경우에 실질적으로 더 빠르게 처리 될 수 있습니다 plpgsql과 절차 솔루션 :
문제는 집계 / 윈도우 기능을 가진 일반 SQL 오히려 다루기 힘든이다. 루프 동안 일반 SQL과 집합 기반 솔루션보다 일반적으로 느린 테이블 (A FOR 루프의 암시 적 커서)를 통해 하나의 순차 검색과 관련이 특별한 경우에 실질적으로 더 빠르게 처리 될 수 있습니다 plpgsql과 절차 솔루션 :
테스트 테이블 :
CREATE TEMP TABLE tbl (company text, profession text, year int); INSERT INTO tbl VALUES ('Google', 'Programmer', 2000) ,('Google', 'Sales', 2000) ,('Google', 'Sales', 2001) ,('Google', 'Sales', 2002) ,('Google', 'Sales', 2004) ,('Mozilla', 'Sales', 2002);함수:
CREATE OR REPLACE FUNCTION f_periods() RETURNS TABLE (company text, profession text, years int[]) AS $func$ DECLARE r tbl; -- use table type as row variable r0 tbl; BEGIN FOR r IN SELECT * FROM tbl t ORDER BY t.company, t.profession, t.year LOOP IF ( r.company, r.profession, r.year) <> (r0.company, r0.profession, r0.year + 1) THEN -- not true for first row RETURN QUERY SELECT r0.company, r0.profession, years; -- output row years := ARRAY[r.year]; -- start new array ELSE years := years || r.year; -- add to array - year can be NULL, too END IF; r0 := r; -- remember last row END LOOP; RETURN QUERY -- output last iteration SELECT r0.company, r0.profession, years; END $func$ LANGUAGE plpgsql;요구:
SELECT * FROM f_periods();요청 된 결과를 생성합니다.
from https://stackoverflow.com/questions/8014577/group-by-and-aggregate-sequential-numeric-values by cc-by-sa and MIT license
'SQL' 카테고리의 다른 글
| [SQL] MySQL의 재귀? (0) | 2020.04.02 |
|---|---|
| [SQL] SQL은 : 2 개의 다른 AUTO_INCREMENT와의 관계 테이블 만들기 (0) | 2020.04.02 |
| [SQL] PostgreSQL은 : 같은 쿼리에서 계산 된 열을 사용하여 (0) | 2020.04.02 |
| [SQL] 어떻게 동적 SQL 쿼리 세트 테이블 이름? (0) | 2020.04.02 |
| [SQL] ON 대 WHERE 절 가입하여 (0) | 2020.04.02 |