피벗 테이블 사용

Suhyeon Lee·2024년 10월 8일
0

Pivot

  • 행과 열의 위치를 뒤바꾸는 것
  • MySQL은 해당 기능을 제공하지 않음
    • Oracle은 피벗 기능이 있다고 함
-- 테이블 생성
CREATE TABLE student(name varchar(20),subjectid int(10), marks int(10));
-- 데이터 삽입
INSERT INTO student VALUES ('Sam',1,70);
INSERT INTO student VALUES ('Sam',2,77);
INSERT INTO student VALUES ('Sam',3,71);
INSERT INTO student VALUES ('Sam',4,70);
INSERT INTO student VALUES ('Sam',1,99);
INSERT INTO student VALUES ('John',1,89);
INSERT INTO student VALUES ('John',2,87);
INSERT INTO student VALUES ('John',3,88);
INSERT INTO student VALUES ('John',4,89); 
INSERT INTO student VALUES ('Martin',1,60);
INSERT INTO student VALUES ('Martin',2,47);
INSERT INTO student VALUES ('Martin',3,68);
INSERT INTO student VALUES ('Martin',4,39);

예시

student table

namesubjectionmarks
Sam170
Sam277
Sam371
Sam470
Sam199
John189
John287
John388
John489
Martin160
Martin247
Martin368
Martin439

1. MAX() 함수와 IF문 사용

  • 학생 한 명 당 하나의 행만 필요하므로 이름으로 GROUP BY
  • 각 열에 하나의 조건을 지정
    • 똑같은 과목에서 2개 이상의 점수가 있을 경우 가장 높은 점수를 가져온다.
SELECT name,
	MAX(IF(subjectid=1, marks, NULL)) sub1,
    MAX(IF(subjectid=2, marks, NULL)) sub2,
    MAX(IF(subjectid=3, marks, NULL)) sub3,
    MAX(IF(subjectid=4, marks, NULL)) sub4
FROM student
GROUP BY name;

2. CASE문 사용

SELECT name,
	MAX(CASE WHEN subjectid=1 THEN marks ELSE NULL END) sub1,
	MAX(CASE WHEN subjectid=2 THEN marks ELSE NULL END) sub2,
	MAX(CASE WHEN subjectid=3 THEN marks ELSE NULL END) sub3,
	MAX(CASE WHEN subjectid=4 THEN marks ELSE NULL END) sub4
FROM student
GROUP BY name;

실습: MySQL Pivot 형식 만들기

  • 날짜별, 시간별 view 수 확인

  • 피벗 테이블을 만들 때에는 피벗의 컬럼이 되어 줄 키(Key)가 필요함

    • 키로 사용할 컬럼: hh(구매 시간)
  • 해당 키를 통해 피벗 형태로 만들어 주는 쿼리 작성

-- pivot table: 날짜별 구매 수
WITH RECURSIVE temp AS (
  (SELECT
    0 AS hours
  )
  UNION
  (SELECT
    hours + 1
  FROM
    temp
  WHERE
    hours < 23
  )
)
SELECT
  pt.days
  , MAX(IF(pt.hh = 0, cnt, 0)) AS AM00
  , MAX(IF(pt.hh = 1, cnt, 0)) AS AM01
  , MAX(IF(pt.hh = 2, cnt, 0)) AS AM02
  , MAX(IF(pt.hh = 3, cnt, 0)) AS AM03
  , MAX(IF(pt.hh = 4, cnt, 0)) AS AM04
  , MAX(IF(pt.hh = 5, cnt, 0)) AS AM05
  , MAX(IF(pt.hh = 6, cnt, 0)) AS AM06
  , MAX(IF(pt.hh = 7, cnt, 0)) AS AM07
  , MAX(IF(pt.hh = 8, cnt, 0)) AS AM08
  , MAX(IF(pt.hh = 9, cnt, 0)) AS AM09
  , MAX(IF(pt.hh = 10, cnt, 0)) AS AM10
  , MAX(IF(pt.hh = 11, cnt, 0)) AS AM11
  , MAX(IF(pt.hh = 12, cnt, 0)) AS PM12
  , MAX(IF(pt.hh = 13, cnt, 0)) AS PM01
  , MAX(IF(pt.hh = 14, cnt, 0)) AS PM02
  , MAX(IF(pt.hh = 15, cnt, 0)) AS PM03
  , MAX(IF(pt.hh = 16, cnt, 0)) AS PM04
  , MAX(IF(pt.hh = 17, cnt, 0)) AS PM05
  , MAX(IF(pt.hh = 18, cnt, 0)) AS PM06
  , MAX(IF(pt.hh = 19, cnt, 0)) AS PM07
  , MAX(IF(pt.hh = 20, cnt, 0)) AS PM08
  , MAX(IF(pt.hh = 21, cnt, 0)) AS PM09
  , MAX(IF(pt.hh = 22, cnt, 0)) AS PM10
  , MAX(IF(pt.hh = 23, cnt, 0)) AS PM11
FROM (
  SELECT 
    SUBSTR(event_time, 1, 10) AS days
    , SUBSTR(event_time, 12, 2) AS hh
    , COUNT(*) AS cnt
  FROM 
    ecommerce.feb
  WHERE 
    event_type = 'purchase'
    AND price > 0
  GROUP BY
    1
    , 2
  ) AS pt 
  JOIN temp ON pt.hh = temp.hours
GROUP BY
  pt.days
;
profile
2 B R 0 2 B

0개의 댓글