빅데이터의 기본 크로스 집계 분석에 대해서 공부한 것을 공유한다.
크로스 집계
개념
- 트랜잭션 테이블
- 크로스 테이블
- 피벗 테이블
트랜잭션 테이블 -> 크로스 테이블로 변환화는 과정을 크로스 집계라고 함
크로스 테이블(Cross table)
행과 열이 교차하는 부분에 데이터를 넣는 형태의 테이블
컬럼과 행으로 구성된 양방향 테이블을 말함
아래와 같은 형태다
카테고리 | 2020-01 | 2020-02 | 2020-03 |
---|---|---|---|
Fashion | 201 | 228 | 346 |
Food | 420 | 333 | 490 |
Beauty | 93 | 232 | 182 |
트랜잭션 테이블(Transaction table)
데이터가 행(row)만 증가하고 열(column)은 증가하지 않는 형태의 테이블
우리가 흔히 사용하는 RDB에서의 테이블이라고 생각하면 되지 않을까 싶다.
형태는 아래와 같다
년월 | 카테고리 | 개수 |
---|---|---|
2020-01 | Fashion | 201 |
2020-01 | Food | 420 |
2020-01 | Beauty | 93 |
2020-02 | Fashion | 228 |
2020-02 | Food | 333 |
2020-02 | Beauty | 232 |
2020-03 | Fashion | 346 |
2020-03 | Food | 490 |
2020-03 | Beauty | 182 |
피벗 테이블(Pivot table)
엑셀에서 흔히 볼 수 있는 피벗테이블을 생각하면 됨
광범위한 테이블의 데이터를 요약하는 통계 테이블이다
보통 여기에는 SUM
, AVERAGE
와 같은 aggregatge된 값들이 테이블의 값이 되곤 한다
피벗 테이블을 이용한 크로스 집계
피벗팅을 통하여 피벗 테이블을 만들어 이를 통해 크로스 집계를 하는 것은 다음과 같이 쉽게 할 수 있다.
SELECT "category",
SUM(CASE WHEN SUBSTRING("date"::TEXT, 1, 7) = '2020-01' THEN "count" ELSE 0 END) AS "JAN",
SUM(CASE WHEN SUBSTRING("date"::TEXT, 1, 7) = '2020-02' THEN "count" ELSE 0 END) AS "FEB",
SUM(CASE WHEN SUBSTRING("date"::TEXT, 1, 7) = '2020-03' THEN "count" ELSE 0 END) AS "MAR",
SUM(CASE WHEN SUBSTRING("date"::TEXT, 1, 7) = '2020-04' THEN "count" ELSE 0 END) AS "APR",
SUM(CASE WHEN SUBSTRING("date"::TEXT, 1, 7) = '2020-05' THEN "count" ELSE 0 END) AS "MAY"
FROM data1
GROUP BY "category";
피벗팅 코드들은 작성자의 GitHub Repository에서 볼 수 있다.
포스트에 대한 피드백이 있으시다면 여기로 메일 부탁드립니다. 읽어주셔서 감사합니다.