MySQL의 피벗 테이블
이 기사에서는 테이블 데이터를 행에서 열로 변환하는 방법을 설명합니다. 이 프로세스를 피벗이라고 합니다. 이 변환의 결과는 일반적으로 보고서 생성에 적합한 정보를 제공하는 요약 테이블입니다.
MySQL에는 피벗 테이블을 생성하는 내장 함수가 없으므로 피벗 테이블을 생성하려면 MySQL 쿼리를 작성해야 합니다. 다행히 MySQL을 사용하여 피벗 테이블을 만드는 세 가지 방법이 있습니다.
IF문을 사용하여 MySQL에서 피벗 테이블 생성CASE문을 사용하여 MySQL에서 피벗 테이블 생성- 동적 피벗 열을 사용하여 MySQL에서 피벗 테이블 생성
다음 스크립트는 세 개의 열(name, subjectid 및 marks)이 있는 학생 테이블을 만듭니다.
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);
이 값을 삽입한 후 데이터베이스의 레코드를 보기 위해 select 쿼리를 작성해 보겠습니다.
Select * from student;
출력은 다음과 같습니다.
MAX 함수와 IF 문을 사용하여 MySQL에서 피벗 테이블 생성
이것은 문제에 대한 매우 간단한 접근 방식입니다. 이 접근 방식에서는 MAX 기능과 IF 문을 사용합니다. 데이터베이스의 한 학생에 대해 단일 과목에 대한 여러 항목이 있는 경우 최대 2점을 받습니다.
이제 SELECT 쿼리와 함께 이것을 사용하는 방법을 살펴보겠습니다.
SELECT name,
MAX(IF(subjectid=1, marks, NULL)) AS Sub1,
MAX(IF(subjectid=2, marks, NULL)) AS Sub2,
MAX(IF(subjectid=3, marks, NULL)) AS Sub3,
MAX(IF(subjectid=4, marks, NULL)) AS Sub4
FROM student
GROUP BY name;
출력은 다음과 같습니다.
이 출력은 각 학생의 과목별 컴파일된 결과입니다. 각 학생에 대해 하나의 행을 원하기 때문에 이름 열로 그룹화해야 합니다. 또한 각 열에 대해 하나의 조건, 즉 주제당 하나의 조건을 지정해야 합니다.
CASE 문을 사용하여 MySQL에서 피벗 테이블 생성
이것은 또한 문제에 대한 직접적인 접근 방식입니다. 이 접근 방식에서 CASE 문과 함께 MAX 기능을 사용합니다. 또한 데이터베이스의 학생에 대해 단일 과목에 대한 여러 항목이 있는 경우 최대 2점을 받습니다. 마찬가지로 일부 학생에게 표시가 없으면 NULL 값을 취합니다.
이제 SELECT 쿼리와 함께 이것을 사용하는 방법을 살펴보겠습니다.
SELECT name,
MAX(CASE WHEN subjectid=1 THEN marks ELSE NULL END) AS Sub1,
MAX(CASE WHEN subjectid=2 THEN marks ELSE NULL END) AS Sub2,
MAX(CASE WHEN subjectid=3 THEN marks ELSE NULL END) AS Sub3,
MAX(CASE WHEN subjectid=4 THEN marks ELSE NULL END) AS Sub4
FROM student
GROUP BY name;
출력은 다음과 같습니다.
동적 피벗 열을 사용하여 MySQL에서 피벗 테이블 생성
집계 함수(MAX), IF 문 및 CASE 문은 위의 예에서 피벗 테이블을 생성했습니다. 이 접근 방식을 사용할 때의 단점은 쿼리를 작성하는 동안 열 머리글을 알아야 하고 열 수가 증가하면 코드도 증가한다는 것입니다. 더 작은 결과와 가능한 모든 값을 위해 위의 두 가지 접근 방식을 모두 사용할 수 있습니다.
따라서 이러한 한계를 극복하기 위해 동적 피벗 열을 사용할 수 있습니다. 여기에서 GROUP_CONCAT 함수는 PIVOT 테이블 출력의 열을 동적으로 생성할 수 있습니다.
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT(
' MAX(CASE WHEN subjectid = ', subjectid, ' THEN marks ELSE 0 END)
AS "', subjectid, '"')
)
INTO @sql FROM student;
SET @sql = CONCAT('SELECT name, ', @sql,
' FROM student GROUP BY name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
GROUP_CONCAT 함수에서 DISTINCT 키워드를 사용하여 고유한 마크 목록을 얻습니다. 그렇지 않으면 목록이 너무 길기 때문에 쿼리에서 오류가 발생합니다. Select * from @sql을 작성하여 언제든지 동적 쿼리를 확인할 수 있습니다.
이제 이 쿼리를 실행할 때 결과는 다음과 같습니다.
열 머리글은 테이블의 값을 기반으로 동적으로 생성되며 열 머리글은 subjectid를 나타냅니다.