-- 테이블 생성
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
name | subjection | marks |
---|---|---|
Sam | 1 | 70 |
Sam | 2 | 77 |
Sam | 3 | 71 |
Sam | 4 | 70 |
Sam | 1 | 99 |
John | 1 | 89 |
John | 2 | 87 |
John | 3 | 88 |
John | 4 | 89 |
Martin | 1 | 60 |
Martin | 2 | 47 |
Martin | 3 | 68 |
Martin | 4 | 39 |
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;
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;
날짜별, 시간별 view 수 확인
피벗 테이블을 만들 때에는 피벗의 컬럼이 되어 줄 키(Key)가 필요함
해당 키를 통해 피벗 형태로 만들어 주는 쿼리 작성
-- 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
;