PostgreSQL에서 피벗 테이블 생성

피벗 테이블은 대규모 데이터 흐름을 요약하는 데 사용되는 원래 테이블에 이미 제공된 일부 값을 집계하는 경향이 있습니다. PostgreSQL에서는 결과에 요약된 행의 데이터 유형인 사용자 지정 N 값 열과 함께 반환된 테이블입니다.

오늘 우리는 기존 값이 있는 미리 정의된 테이블을 사용하여 PostgreSQL에서 피벗 테이블을 만드는 방법을 배울 것입니다.

crosstab()을 사용하여 PostgreSQL에서 피벗 테이블 생성

PostgreSQL 설명서의 TABLE_FUNC 제목 아래 crosstab()을 찾을 수 있습니다. 여러 행이 있는 테이블을 반환하는 함수입니다.

통사론:

crosstab ( sql text ) ? setof record

이 쿼리는 행 이름과 N 값 열을 포함하는 피벗 테이블을 생성합니다. 여기서 N은 호출 쿼리에 지정된 행 유형에 의해 결정됩니다.

crosstabN ( sql text ) , setof table_crosstab_N

이 쿼리는 행 이름과 N 값 열을 포함하는 피벗 테이블을 생성합니다. crosstab2, crosstab3 및 crosstab4가 미리 정의되어 있습니다.

이제 어떻게 사용할 수 있는지 봅시다. 열이 있는 APARTMENT라는 간단한 테이블을 생성합니다. ID, UNIT, PRICE, AREA.

예:

CREATE TABLE apartment (
   ID int PRIMARY KEY,
   UNIT int,
   PRICE int,
   AREA int
)

이제 테이블에 몇 가지 값을 추가해 보겠습니다.

INSERT INTO apartment VALUES (1, 20, 200, 10) , (2, 20, 200, 9), (3, 50, 190, 8);

그래서 지금 우리 테이블을 보면 다음과 같을 것입니다.

출력:

id    unit   price  area
1     20     200     10
2     20     200     9
3     50     190     8

피벗 테이블에서 이 테이블은 다음과 같이 표시됩니다.

unit    ..180    190        200          210...
20         -      -      19/2 = 9.5       -
50         -      8         -             -

따라서 여기서는 특정 PRICES에서 각 UNIT에 대한 AREA의 평균을 취합니다. 우리는 언급된 PRICE에서 얻을 수 있는 평균 AREA를 보고 싶습니다.

따라서 20의 UNIT에 대해 200의 PRICE에 대한 평균 AREA를 찾으면 (10 + 9) / 2 = 8로 평균을 얻게 됩니다. 이러한 방식으로 집계가 있는 피벗 테이블을 찾습니다.

이제 개념을 이해했으므로 계속해서 구현해 보겠습니다. 다음과 같이 쿼리를 작성할 수 있습니다.

Select UNIT, PRICE, avg(area)
from apartment
group by UNIT, PRICE

이 쿼리는 우리가 원하는 기본 기능을 구현합니다. GROUP BY 절을 사용하여 집계하려는 테이블의 열을 가져옵니다.

특정 UNIT 및 PRICE에 대한 AREA의 평균을 찾고자 합니다. 따라서 이 두 열을 사용하여 그룹화합니다. 다음과 같이 테이블이 반환됩니다.

출력:

unit   price        avg
50     190     8.0000000000000000
20     200     9.5000000000000000

이제 이 테이블도 제대로 작동하지만 계속해서 crosstab()을 사용하겠습니다. 작동 방식과 제약 조건을 살펴봐야 합니다.

crosstab()을 사용하려면 두 가지 중요한 사항을 확인해야 합니다.

1. crosstab()에는 각 행에 대해 고유한 값이 있어야 합니다.
2. crosstab()은 각 열에 대해 동일한 데이터 유형을 가져야 합니다.

따라서 데이터 유형이 다른 열이 없는지 확인하십시오. 이제 이 GROUP BY 쿼리의 결과를 피벗하기 위해 다음과 같이 작성해 보겠습니다.

select *
from crosstab
(
'Select UNIT::float, PRICE::float, avg(AREA)::float
from apartment
group by UNIT, PRICE') as ct(
    UNIT float,
    avge float
);

여기서 고려해야 할 몇 가지 중요한 사항이 있습니다. UNIT 및 avge라는 두 개의 열로 정의된 결과 테이블 CT로 반환된 crosstab에서 열을 선택합니다.

그룹화 후 APARTMENT에서 열을 가져오고 평균을 찾습니다. 이 쿼리 테이블은 최종 결과를 위해 피벗됩니다.

또한 각 열을 동일한 데이터 유형으로 변환해야 합니다. 물론 평균은 배정밀도이거나 실수입니다.

따라서 불필요하더라도 int 열을 float로 캐스트하는 것이 좋습니다. 캐스팅하지 않고 쿼리를 실행하면 아래와 같은 오류가 반환됩니다.

출력:

ERROR:  return and sql tuple descriptions are incompatible
SQL state: 42601

따라서 이를 방지하기 위해 float로 캐스팅하거나 float 유형으로 값이 삽입된 새 테이블을 생성해야 합니다. 데이터 유형의 차이는 튜플을 호환되지 않게 만드는 것입니다.

출력:

unit    avg
50       8
20      9.5

다른 경우에는 crosstab()을 사용할 필요가 없습니다. GROUP BY를 사용해도 평균을 얻기 위한 고유한 값이 있는 테이블을 반환하므로 효과적입니다.

crosstab() 함수를 사용하지 않았다면 여전히 다음과 같은 결과를 얻었을 것입니다.

unit   price   avg
50     190      8
20     200     9.5

crosstab()에서 세 개의 열이 있는 반환 목록을 사용하여 여전히 UNIT와 area의 평균을 포함하는 값만 반환한다는 것을 알 수 있습니다. 왜?

SQL 문은 일련의 범주와 값을 사용하며 이 경우 범주는 UNIT가 되고 값은 평균 열이 되는 경향이 있습니다. 따라서 반환 테이블에는 두 개의 열만 유지됩니다.

crosstab()이 필요에 따라 작동하는 방식을 알았으므로 이제 사용할 수 있습니다. 다른 PRICES가 있는 UNITS가 있는 경우 AVG()를 가져오면 데이터에 따라 N개의 열이 있게 됩니다.

테이블의 값을 다음과 같이 변경해 보겠습니다.

id    unit   price  area
1      20    200     10
2      20    170     9
3      50    190     8

이제 여기에서 crosstab() 쿼리를 실행하면 다음과 같은 결과가 반환됩니다.

출력:

unit   avge   avge1
50      8    [NULL]
20     10      9

PRICE를 변경하면 AREA 값이 다른 평균으로 분산되는 경향이 있음을 알 수 있습니다. 이는 평균을 가격별로 그룹화하기 때문에 발생합니다.

동일한 PRICE 값은 모든 값에 대해 존재하는 경우 서로 다른 값의 평균을 가지지만 다른 PRICE 값은 각 PRICE에 해당하는 AREAS의 평균으로만 구성됩니다. 따라서 표는 이런 식으로 정렬됩니다.