복붙노트

[SQL] CASE 및 GROUP BY와 피벗에 동적 대안

SQL

CASE 및 GROUP BY와 피벗에 동적 대안

나는 모습이 좋아하는 테이블이 있습니다 :

id    feh    bar
1     10     A
2     20     A
3      3     B
4      4     B
5      5     C
6      6     D
7      7     D
8      8     D

그리고 나는 그것이 같이 할 :

bar  val1   val2   val3
A     10     20 
B      3      4 
C      5        
D      6      7     8

나는이 수행이 쿼리를 가지고 :

SELECT bar, 
   MAX(CASE WHEN abc."row" = 1 THEN feh ELSE NULL END) AS "val1",
   MAX(CASE WHEN abc."row" = 2 THEN feh ELSE NULL END) AS "val2",
   MAX(CASE WHEN abc."row" = 3 THEN feh ELSE NULL END) AS "val3"
FROM
(
  SELECT bar, feh, row_number() OVER (partition by bar) as row
  FROM "Foo"
 ) abc
GROUP BY bar

이것은 매우 메이크업 의뭉스러운 접근 방식이며, 새로운 열이 많은이 생성 될 수있는 경우 다루기 가져옵니다. 경우 문이 쿼리보다 역동적를 만들기 위해 더 잘 할 수 있는지 궁금 해서요? 또한, 나는이 일을 다른 방법을보고 싶어요.

해결법

  1. ==============================

    1.당신이 추가 모듈 tablefunc를 설치하지 않은 경우, 데이터베이스 당 한 번이 명령을 실행합니다 :

    당신이 추가 모듈 tablefunc를 설치하지 않은 경우, 데이터베이스 당 한 번이 명령을 실행합니다 :

    CREATE EXTENSION tablefunc;

    당신의 사건에 대한 아주 기본적인 크로스 탭 솔루션 :

    SELECT * FROM crosstab(
  'SELECT bar, 1 AS cat, feh
   FROM   tbl_org
   ORDER  BY bar, feh')
 AS ct (bar text, val1 int, val2 int, val3 int);  -- more columns?

    특별한 어려움은 여기에 기본 테이블에 카테고리 (CAT)가 없다는 것이다. 기본 1 파라미터 폼 방금 더미 값이 분류로서 가진 더미 열을 제공 할 수있다. 값은 어쨌든 무시됩니다.

    모든 NULL 값은이 문제의 정의에 의해 오른쪽에 열을 매달려에 표시하기 때문에, 크로스 탭 () 함수의 두 번째 매개 변수가 필요하지 않은 드문 사례 중 하나입니다. 및 순서 값에 의해 결정될 수있다.

    우리는 이름이 그 결과의 값의 순서를 결정하는 단계와, 실제 범주 열이 있다면, 우리가 교차의 2- 파라미터 형태 필요할 것이다 (). 여기서 I는 기지국 교차 ()에서의 창 함수 ROW_NUMBER ()의 도움으로 분류 칼럼을 합성 :

    SELECT * FROM crosstab(
   $$
   SELECT bar, val, feh
   FROM  (
      SELECT *, 'val' || row_number() OVER (PARTITION BY bar ORDER BY feh) AS val
      FROM tbl_org
      ) x
   ORDER BY 1, 2
   $$
 , $$VALUES ('val1'), ('val2'), ('val3')$$         -- more columns?
) AS ct (bar text, val1 int, val2 int, val3 int);  -- more columns?

    나머지는 거의 평범합니다. 이 밀접하게 관련 답변에 더 많은 설명과 링크를 찾을 수 있습니다.

    기초: 당신이 크로스 탭 () 함수에 익숙하지 않은 경우 먼저 읽어보세요!

    많은:

    즉,로 시작하는 테스트 케이스를 제공해야하는 방법은 다음과 같습니다

    CREATE TEMP TABLE tbl_org (id int, feh int, bar text);
INSERT INTO tbl_org (id, feh, bar) VALUES
   (1, 10, 'A')
 , (2, 20, 'A')
 , (3,  3, 'B')
 , (4,  4, 'B')
 , (5,  5, 'C')
 , (6,  6, 'D')
 , (7,  7, 'D')
 , (8,  8, 'D');

    @Clodoaldo가 주석으로, 매우 역동적 아직 없습니다. 동적 반환 형식은 plpgsql 달성하기 어렵다. 일부 제한 -하지만 주위에 방법이 있습니다.

    그래서 더 복잡한 나머지, 나는 간단한 테스트 케이스로 보여주지 :

    CREATE TEMP TABLE tbl (row_name text, attrib text, val int);
INSERT INTO tbl (row_name, attrib, val) VALUES
   ('A', 'val1', 10)
 , ('A', 'val2', 20)
 , ('B', 'val1', 3)
 , ('B', 'val2', 4)
 , ('C', 'val1', 5)
 , ('D', 'val3', 8)
 , ('D', 'val1', 6)
 , ('D', 'val2', 7);

    요구:

    SELECT * FROM crosstab('SELECT row_name, attrib, val FROM tbl ORDER BY 1,2')
AS ct (row_name text, val1 int, val2 int, val3 int);

    보고:

     row_name | val1 | val2 | val3
----------+------+------+------
 A        | 10   | 20   |
 B        |  3   |  4   |
 C        |  5   |      |
 D        |  6   |  7   |  8

    tablefunc 모듈은 열 정의 목록을 제공하지 않고 일반적인 크로스 탭 () 호출에 대한 간단한 인프라를 제공합니다. C (일반적으로 매우 빠르게)로 작성된 함수의 수 : crosstabN ()

    교차 1 () - 교차 4 ()가 미리 정의되어있다. 하나의 작은 점 : 그들이 필요로하고 모든 텍스트를 반환합니다. 그래서 우리는 우리의 정수 값을 캐스팅해야합니다. 그러나 전화를 단순화 :

    SELECT * FROM crosstab4('SELECT row_name, attrib, val::text  -- cast!
                         FROM tbl ORDER BY 1,2')

    결과:

     row_name | category_1 | category_2 | category_3 | category_4
----------+------------+------------+------------+------------
 A        | 10         | 20         |            |
 B        | 3          | 4          |            |
 C        | 5          |            |            |
 D        | 6          | 7          | 8          |

    더 많은 열 또는 다른 데이터 유형의 경우, 우리는 우리 자신의 복합 형과 기능 (한 번)을 만들 수 있습니다. 유형:

    CREATE TYPE tablefunc_crosstab_int_5 AS (
  row_name text, val1 int, val2 int, val3 int, val4 int, val5 int);

    함수:

    CREATE OR REPLACE FUNCTION crosstab_int_5(text)
  RETURNS SETOF tablefunc_crosstab_int_5
AS '$libdir/tablefunc', 'crosstab' LANGUAGE c STABLE STRICT;

    요구:

    SELECT * FROM crosstab_int_5('SELECT row_name, attrib, val   -- no cast!
                              FROM tbl ORDER BY 1,2');

    결과:

     row_name | val1 | val2 | val3 | val4 | val5
----------+------+------+------+------+------
 A        |   10 |   20 |      |      |
 B        |    3 |    4 |      |      |
 C        |    5 |      |      |      |
 D        |    6 |    7 |    8 |      |

    이것은 tablefunc 모듈에 의해 덮여 무엇을 넘는다. 나는이 관련 답변에 자세히 기술과 다형성 유형을 사용하는 반환 형식의 동적를 만들려면 :

  2. ==============================

    2.이 오래된 질문이지만, 내가 PostgreSQL의 최근의 개선에 의해 가능하게 다른 솔루션을 추가하고 싶습니다. 이 용액을 전혀 교차 분석 기능을 사용하지 않고 동적 데이터 세트로부터 구조화 된 결과를 리턴하는 동일한 목적을 달성한다. 즉,이 오래된 문제에 대한 새로운 해결책을 발견하지 못하도록 재검토 의도하지 않은 암시 적 가정의 좋은 예입니다. ;)

    이 오래된 질문이지만, 내가 PostgreSQL의 최근의 개선에 의해 가능하게 다른 솔루션을 추가하고 싶습니다. 이 용액을 전혀 교차 분석 기능을 사용하지 않고 동적 데이터 세트로부터 구조화 된 결과를 리턴하는 동일한 목적을 달성한다. 즉,이 오래된 문제에 대한 새로운 해결책을 발견하지 못하도록 재검토 의도하지 않은 암시 적 가정의 좋은 예입니다. ;)

    설명하기 위해, 당신은 다음과 같은 구조의 전치 데이터에 대한 방법에 대한 질문 :

    id    feh    bar
1     10     A
2     20     A
3      3     B
4      4     B
5      5     C
6      6     D
7      7     D
8      8     D

    이 형식으로 :

    bar  val1   val2   val3
A     10     20 
B      3      4 
C      5        
D      6      7     8

    종래의 해결책은 어윈 Brandstetter의 대답에 정교한 상세히 설명 동적 교차 쿼리를 만드는 재치 (그리고 믿을 지식) 접근법이다.

    특정 유스 케이스는 약간 다른 결과 형식을 받아들이는 유연한 정도 인 경우, 다음 다른 해결책은 핸들의 동적 피벗 아름답게 수 있습니다. 여기 배운이 기술,

    JSON 객체의 형태로 즉시 요동 데이터를 구성 PostgreSQL의 새로운 jsonb_object_agg 함수를 사용한다.

    나는 설명하기 위해 씨 Brandstetter의 "간단한 테스트 케이스"를 사용합니다 :

    CREATE TEMP TABLE tbl (row_name text, attrib text, val int);
INSERT INTO tbl (row_name, attrib, val) VALUES
   ('A', 'val1', 10)
 , ('A', 'val2', 20)
 , ('B', 'val1', 3)
 , ('B', 'val2', 4)
 , ('C', 'val1', 5)
 , ('D', 'val3', 8)
 , ('D', 'val1', 6)
 , ('D', 'val2', 7);

    jsonb_object_agg 기능을 사용하여, 우리는이 간결한 아름다움에 필요한 피벗 결과 집합을 생성 할 수 있습니다 :

    SELECT
  row_name AS bar,
  json_object_agg(attrib, val) AS data
FROM tbl
GROUP BY row_name
ORDER BY row_name;

    어떤 출력 :

     bar |                  data                  
-----+----------------------------------------
 A   | { "val1" : 10, "val2" : 20 }
 B   | { "val1" : 3, "val2" : 4 }
 C   | { "val1" : 5 }
 D   | { "val3" : 8, "val1" : 6, "val2" : 7 }

    당신이 볼 수 있듯이,이 기능은 샘플 데이터에서 '속성과 값 열에서 JSON 객체, ROW_NAME별로 그룹화 모두 키 / 값 쌍을 생성하여 작동합니다.

    이 결과 세트는 분명히 다른 보이지만 실제 사용 사례, 특히 데이터가 동적으로 생성 된 피벗을 필요로 어디 그, 또는 결과 데이터가 (상위 응용 프로그램에 의해 소비 (대부분이하지 않은 경우) 경우, 나는 그것이 실제로 많은 만족 것으로 예상 예를 들어, 이 프로토콜은 HTTP 응답에 전송 형식의 재 요구) 할 수 있습니다.

    이 방법의 장점 :

    차이 (가능한 단점) :

    결론

    나는 그것이 성능에 관한 특히,이 방법에 (특히 ErwinBrandstetter의 @) 다른 사람의 의견을 듣고 매우 궁금합니다. 나는 앤드류 벤더의 블로그에이 방법을 발견했을 때, 그것은 머리의 측면에 타격을 받고 같았다. 어떤 아름다운 방법은 PostrgeSQL에서 어려운 문제에 대한 새로운 접근 방식을 취합니다. 그것은 완벽하게 내 사용 사례를 해결하고, 나는 그것이 마찬가지로뿐만 아니라 많은 다른 사람들에게 봉사 것으로 판단된다.

  3. ==============================

    3.이 @Damian 좋은 답변을 완료하는 것입니다. 난 이미 9.6의 편리한 json_object_agg 기능 이전에 다른 답변에서 JSON 접근 방식을 제안했다. 그것은 단지 이전 도구 세트로 더 많은 작업을합니다.

    이 @Damian 좋은 답변을 완료하는 것입니다. 난 이미 9.6의 편리한 json_object_agg 기능 이전에 다른 답변에서 JSON 접근 방식을 제안했다. 그것은 단지 이전 도구 세트로 더 많은 작업을합니다.

    인용 가능한 단점 두 정말 없습니다. 필요한 경우 임의의 키 순서는 하찮게 수정됩니다. 누락 된 키는, 관련된 경우, 해결해야 할 코드의 거의 사소한 양을 취합니다

    select
    row_name as bar,
    json_object_agg(attrib, val order by attrib) as data
from
    tbl
    right join
    (
        (select distinct row_name from tbl) a
        cross join
        (select distinct attrib from tbl) b
    ) c using (row_name, attrib)
group by row_name
order by row_name
;
 bar |                     data                     
-----+----------------------------------------------
 a   | { "val1" : 10, "val2" : 20, "val3" : null }
 b   | { "val1" : 3, "val2" : 4, "val3" : null }
 c   | { "val1" : 5, "val2" : null, "val3" : null }
 d   | { "val1" : 6, "val2" : 7, "val3" : 8 }

    JSON을 이해하는 최종 쿼리 소비자에 대한 단점이 없습니다. 유일한 사람은 테이블 소스로 소비 될 수 없다는 것입니다.

  4. ==============================

    4.귀하의 경우에는 내가 배열이 좋은 것 같아요. SQL 바이올린

    귀하의 경우에는 내가 배열이 좋은 것 같아요. SQL 바이올린

    select
    bar,
    feh || array_fill(null::int, array[c - array_length(feh, 1)]) feh
from
    (
        select bar, array_agg(feh) feh
        from foo
        group by bar
    ) s
    cross join (
        select count(*)::int c
        from foo
        group by bar
        order by c desc limit 1
    ) c(c)
;
 bar |      feh      
-----+---------------
 A   | {10,20,NULL}
 B   | {3,4,NULL}
 C   | {5,NULL,NULL}
 D   | {6,7,8}
  5. ==============================

    5.나는 과거에 반환 미안 해요,하지만 해결책 "동적 크로스 탭은"잘못된 결과 테이블을 반환합니다. 따라서, VALN 값 오 "왼쪽 정렬"되고, 이들은 열 이름에 대응하지 않는다. 입력 테이블은 예컨대 값에 "홀"을 가질 때 "C"는 VAL1과 val3하지만 val2만큼이있다. 이것은 에러를 생성한다 : val3 값은 최종 테이블 내의 열을 val2 (즉, 비어있는 다음 컬럼)에서 원거리한다.

    나는 과거에 반환 미안 해요,하지만 해결책 "동적 크로스 탭은"잘못된 결과 테이블을 반환합니다. 따라서, VALN 값 오 "왼쪽 정렬"되고, 이들은 열 이름에 대응하지 않는다. 입력 테이블은 예컨대 값에 "홀"을 가질 때 "C"는 VAL1과 val3하지만 val2만큼이있다. 이것은 에러를 생성한다 : val3 값은 최종 테이블 내의 열을 val2 (즉, 비어있는 다음 컬럼)에서 원거리한다.

    CREATE TEMP TABLE tbl (row_name text, attrib text, val int); 
INSERT INTO tbl (row_name, attrib, val) VALUES ('C', 'val1', 5) ('C', 'val3', 7);

SELECT * FROM crosstab('SELECT row_name, attrib, val FROM tbl 
ORDER BY 1,2') AS ct (row_name text, val1 int, val2 int, val3 int);

row_name|val1|val2|val3
 C      |   5|  7 |

    오른쪽 열에서 "구멍"을 올바른 세포를 반환하기 위해, 크로스 탭 쿼리 크로스 탭에서 두번째 SELECT를 필요로, '1, 2 BY TBL ORDER FROM SELECT ROW_NAME, ATTRIB, 발'이 "크로스 탭 (같은은 '선택 '1 일까지 TBL 순서 구별 ROW_NAME) "

    6. from https://stackoverflow.com/questions/15506199/dynamic-alternative-to-pivot-with-case-and-group-by by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] MySQL의와 계산의 중간에 간단한 방법  (0) 2020.03.11
[SQL] 엄격한 관계 모델 전자 상거래 대 법인 속성 값 데이터베이스  (0) 2020.03.11
[SQL] SQL가 null = NULL [중복]  (0) 2020.03.10
[SQL] 내부는 SQL에 LINQ에서 조인 구문은 무엇입니까?  (0) 2020.03.10
[SQL] 어떻게 날짜의 범위와 테이블을 채우는?  (0) 2020.03.10
,

티스토리툴바