MySQL Pivot: 행을 열로 회전

MySQL Pivot: 행을 열로 회전

2022-06-03 last update

7 minutes reading MySQL MariaDB
데이터베이스 테이블은 서로 다른 유형의 데이터를 저장할 수 있으며, 때때로 우리는 줄 데이터를 열 데이터로 변환해야 한다.이 문제는 PIVOT() 함수를 사용하여 해결할 수 있습니다.이 함수는 테이블의 줄을 열 값으로 회전하는 데 사용됩니다.그러나 Oracle 또는 SQL Server와 같은 데이터베이스 서버는 지원되지 않습니다.MySQL 데이터베이스 테이블에서 동일한 작업을 수행하려면 CASE 문구를 사용하여 SELECT 질의를 작성하여 행을 열로 회전해야 합니다.본고는 관련 MySQL 데이터베이스 테이블에서 PIVOT() 함수 작업을 수행하는 방법을 보여 줍니다.

선행 조건:


데이터베이스와 관련된 테이블을 만들어야 합니다. 그 중 하나의 줄은 PIVOT () 함수와 같은 열로 변환됩니다.다음 SQL 문장을 실행하여'unidb'라는 데이터베이스를 만들고'students','courses','result'라는 세 개의 테이블을 만듭니다.학생과 성적표는 일대다 관계를 통해 관련되고, 과정과 성적표는 일대다 관계를 통해 관련된다.결과표의 CREATE 문장은 std\U id와course\U id 두 필드의 키 제약을 포함합니다.
CREATE DATABASE unidb;
USE unidb;

CREATE TABLE students (
id INT PRIMARY KEY,
name varchar(50) NOT NULL,
department VARCHAR(15) NOT NULL);

CREATE TABLE courses (
course_id VARCHAR(20) PRIMARY KEY,
name varchar(50) NOT NULL,
credit SMALLINT NOT NULL);

CREATE TABLE result(
std_id INT NOT NULL,
course_id VARCHAR(20) NOT NULL,
mark_type VARCHAR(20) NOT NULL,
marks SMALLINT NOT NULL,
FOREIGN KEY (std_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(course_id),
PRIMARY KEY (std_id, course_id, mark_type));
학생, 과정과 성적표에 약간의 기록을 삽입하다.테이블을 만들 때 설정된 제한에 따라 테이블에 값을 삽입해야 합니다.
INSERT INTO students VALUES
( '1937463', 'Harper Lee', 'CSE'),
( '1937464', 'Garcia Marquez', 'CSE'),
( '1937465', 'Forster, E.M.', 'CSE'),
( '1937466', 'Ralph Ellison', 'CSE');

INSERT INTO courses VALUES
( 'CSE-401', 'Object Oriented programming', 3),
( 'CSE-403', 'Data Structure', 2),
( 'CSE-407', 'Unix programming', 2);

INSERT INTO result VALUES
( '1937463', 'CSE-401','Internal Exam' ,15),
( '1937463', 'CSE-401','Mid Term Exam' ,20),
( '1937463', 'CSE-401','Final Exam', 35),
( '1937464', 'CSE-403','Internal Exam' ,17),
( '1937464', 'CSE-403','Mid Term Exam' ,15),
( '1937464', 'CSE-403','Final Exam', 30),
( '1937465', 'CSE-401','Internal Exam' ,18),
( '1937465', 'CSE-401','Mid Term Exam' ,23),
( '1937465', 'CSE-401','Final Exam', 38),
( '1937466', 'CSE-407','Internal Exam' ,20),
( '1937466', 'CSE-407','Mid Term Exam' ,22),
( '1937466', 'CSE-407','Final Exam', 40);
여기, 결과표는 줄마다 std\U id,mark\U type과course\U id 열의 여러 개의 같은 값을 포함합니다.이 강좌의 다음 부분에서는 이 줄을 이 표의 열로 바꾸어 더욱 조직적인 형식으로 데이터를 표시하는 방법을 소개할 것이다.

CASE 문을 사용하여 행을 열로 회전합니다.


다음 간단한 SELECT 문구를 실행하여 결과 테이블의 모든 기록을 표시합니다.
SELECT * FROM result;
출력은 네 명의 학생이 세 과목의 세 가지 시험 유형 중의 점수를 보여 준다.따라서 std\U id,course\U id와mark\U type의 값은 서로 다른 학생, 과정과 시험 유형에 대해 여러 번 반복됩니다.

CASE 문구를 사용하여 SELECT 질의를 보다 효과적으로 작성할 수 있는 경우 출력의 가독성이 높아집니다.다음 SELECT with CASE 문구에서는 행의 반복 값을 열 이름으로 변환하고 사용자가 쉽게 이해할 수 있는 형식으로 표의 내용을 표시합니다.
SELECT result.std_id, result.course_id,
MAX(CASE WHEN result.mark_type = "Internal Exam" THEN result.marks END) "Internal Exam",
MAX(CASE WHEN result.mark_type = "Mid Term Exam" THEN result.marks END) "Mid Term Exam",
MAX(CASE WHEN result.mark_type = "Final Exam" THEN result.marks END) "Final Exam"
FROM result
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
상술한 문장을 실행하면 다음과 같은 출력이 나타날 것이다. 이 문장은 이전의 출력보다 가독성이 더욱 강하다.

CASE 및 SUM()을 사용하여 행을 열로 회전합니다.


테이블에서 각 학생의 교과 과정 수를 계산하려면 CASE 문장에서 집합 함수 SUM () group by std\u id와course\u id를 사용해야 합니다.다음 질의는 SUM() 함수와 GROUP by 자구를 사용하여 수정하기 전의 질의를 통해 작성됩니다.
SELECT result.std_id,result.course_id,
MAX(CASE WHEN result.mark_type = "Internal Exam" THEN result.marks END) "Internal Exam",
MAX(CASE WHEN result.mark_type = "Mid Term Exam" THEN result.marks END) "Mid Term Exam",
MAX(CASE WHEN result.mark_type = "Final Exam" THEN result.marks END) "Final Exam",
SUM( result.marks) as Total
FROM result
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
출력은 Total이라는 새 열을 보여 줍니다. 이 열은 각 특정 학생이 얻은 모든 과정의 모든 시험 유형의 점수의 합을 보여 줍니다.

여러 테이블의 행을 열로 회전하려면 다음과 같이 하십시오.


앞의 두 개의 조회는 결과표에 적용될 것이다.이 표는 다른 두 표와 관련이 있다.이것들은 학생과 과정이다.학생 id가 아닌 학생 이름과 과정 id가 아닌 과정 이름을 표시하려면 세 개의 관련 표(학생, 과정 및 결과)를 사용하여 SELECT 조회를 작성해야 한다.다음 SELECT 조회는 FORM 자문 다음에 세 개의 테이블 이름을 추가하고 WHERE 자문에 적당한 조건을 설정하여 작성합니다. 세 개의 테이블에서 데이터를 검색하고 앞의 SELECT 조회보다 더 적합한 출력을 생성합니다.
SELECT students.name as `Student Name` , courses.name as `Course Name`,
MAX(CASE WHEN result.mark_type = "Internal Exam" THEN result.marks END) "CT",
MAX(CASE WHEN result.mark_type = "Mid Term Exam" THEN result.marks END) "Mid",
MAX(CASE WHEN result.mark_type = "Final Exam" THEN result.marks END) "Final",
SUM( result.marks) as Total
FROM students, courses, result
WHERE result.std_id = students.id and result.course_id= courses.course_id
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
상기 조회를 실행하면 다음과 같은 출력이 생성됩니다.

결론:


본고는 일부 가상 데이터를 사용하여 MySQL의 Pivot() 함수를 지원하지 않는 상황에서 Pivot() 함수의 기능을 실현하는 방법을 보여 줍니다.나는 독자가 본문을 읽은 후에 SELECT 조회를 사용하여 모든 줄 데이터를 열 데이터로 변환할 수 있기를 바란다.