sql 그룹별 평균 구하기

1. 테이블 생성

CREATE TABLE SubjectScores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student VARCHAR(100) NOT NULL,
    subject VARCHAR(50) NOT NULL,
    score INT NOT NULL,
    semester VARCHAR(20),
    year INT
);

 

2. 샘플 데이터 입력구문

INSERT INTO SubjectScores (student, subject, score, semester, year) VALUES
('Kim Min-soo', 'English', 85, '1st Semester', 2024),
('Kim Min-soo', 'Math', 90, '1st Semester', 2024),
('Kim Min-soo', 'Science', 80, '1st Semester', 2024),
('Lee Young-hee', 'English', 78, '1st Semester', 2024),
('Lee Young-hee', 'Math', 88, '1st Semester', 2024),
('Lee Young-hee', 'Science', 92, '1st Semester', 2024),
('Park Cheol-soo', 'English', 95, '1st Semester', 2024),
('Park Cheol-soo', 'Math', 85, '1st Semester', 2024),
('Park Cheol-soo', 'Science', 88, '1st Semester', 2024),
('Kim Ji-young', 'English', 82, '1st Semester', 2024),
('Kim Ji-young', 'Math', 79, '1st Semester', 2024),
('Kim Ji-young', 'Science', 91, '1st Semester', 2024);

 

3. 학생별 과목별 평균과 랭킹 구하는 쿼리문

SELECT student, subject, AVG(score) AS average_score,
       RANK() OVER (PARTITION BY subject ORDER BY AVG(score) DESC) AS rank
FROM SubjectScores
GROUP BY student, subject;