학생들의 각 과목의 오늘 최고 점수와 어제 마지막 점수를 비교하여 5% 이상 또는 이하로 떨어진 과목과 그 과목의 현재 점수를 알고 싶은 쿼리를 만드려고 합니다
학생들 각각의 과목의 최고 점수
select COUNT(DISTINCT ct.mb_idx) as id ,subject ,mb_idx ,max(`point`) as a from math ct where DATE(ct.reg_date) = date('2021-10-17') GROUP by subject ,mb_idx
그리고 여기서 어제 시험에서 마지막 점수 에서 오늘 점수와 (+-)5%
차이가 나는 과목과 학생을 찾는 쿼리를 짜려고 하는데요
두번째 쿼리를 어떻게 조합해야 할지 전혀 모르겠네요....
CREATE TABLE `math` ( `seq` int NOT NULL AUTO_INCREMENT, `reg_date` datetime DEFAULT NULL, `point` int DEFAULT NULL, `name` varchar(100) DEFAULT NULL, `subject` varchar(100) DEFAULT NULL, `mb_idx` varchar(100) DEFAULT NULL, )
INSERT INTO test.math (reg_date,`point`,name,subject,mb_idx) VALUES ('2021-10-12 22:38:08',30,'철수','영어','232'), ('2021-10-12 22:38:08',50,'미미','수학','111'), ('2021-10-13 22:38:08',50,'철수','국어','232'), ('2021-10-13 22:38:08',50,'미미','국어','111'), ('2021-10-15 22:38:08',79,'철수','영어','232'), ('2021-10-16 22:38:08',100,'미미','수학','111'), ('2021-10-16 11:38:08',33,'철수','영어','232'), ('2021-10-17 02:38:08',80,'미미','국어','111'), ('2021-10-17 12:38:08',100,'동수','영어','332'), ('2021-10-16 22:38:08',22,'동수','영어','332'); INSERT INTO test.math (reg_date,`point`,name,subject,mb_idx) VALUES ('2021-10-15 22:38:08',2,'동수','국어','332'), ('2021-10-16 22:38:08',43,'철수','국어','232'), ('2021-10-17 22:38:08',34,'철수','수학','232'), ('2022-01-19 02:38:08',99,'미주','영어','333'), ('2022-01-19 12:38:08',99,'대주','수학','333'), ('2022-01-20 02:38:08',22,'미주','영어','333'), ('2022-01-20 02:38:08',55,'대주','국어','333'), ('2021-10-15 22:38:08',90,'미미','영어','111'), ('2021-10-16 22:38:08',59,'미미','국어','111'), ('2021-10-16 21:38:08',79,'미미','수학','111'); INSERT INTO test.math (reg_date,`point`,name,subject,mb_idx) VALUES ('2021-10-16 07:38:08',89,'미미','국어','111'), ('2021-10-16 11:38:08',69,'미미','수학','111'), ('2021-10-16 02:38:08',100,'미미','국어','111'), ('2021-10-17 12:38:08',80,'동수','영어','332'), ('2021-10-17 11:38:08',60,'동수','영어','332'), ('2021-10-16 22:28:08',33,'철수','국어','232'), ('2021-10-17 13:38:08',80,'미미','국어','111'), ('2021-10-17 19:38:08',90,'철수','영어','232'), ('2021-10-17 08:38:08',72,'동수','수학','332'), ('2021-10-17 22:38:08',100,'미미','영어','111'); INSERT INTO test.math (reg_date,`point`,name,subject,mb_idx) VALUES ('2021-10-17 22:38:08',100,'동수','국어','332'), ('2021-10-16 21:38:08',79,'미미','수학','111'), ('2021-10-16 21:38:08',79,'미미','수학','111'), ('2021-10-17 14:38:08',80,'미미','수학','111'), ('2021-10-17 14:58:08',98,'미미','수학','111'), ('2021-10-17 17:38:08',70,'미미','수학','111'), ('2021-10-17 12:38:08',100,'동수','영어','332'), ('2021-10-17 13:28:08',79,'동수','수학','332'), ('2021-10-17 22:38:08',55,'동수','수학','332'), ('2021-10-17 14:38:08',100,'동수','영어','332');
어떻게 짜야 답이 나올까요?
5% 차이의 기준(분모)이 뭔가요? 어제인가요? 오늘인가요?
SELECT subject , mb_idx , name , today , yesterday -- , (today - yesterday) / today * 100 rat , (today - yesterday) / yesterday * 100 rat FROM (SELECT subject, mb_idx, name , MAX(CASE WHEN dt = '2021-10-17' THEN point END) today , MAX(CASE WHEN dt != '2021-10-17' THEN point END) yesterday FROM (SELECT DATE(reg_date) dt , subject, mb_idx, name, point , ROW_NUMBER() OVER(PARTITION BY subject, mb_idx, DATE(reg_date) ORDER BY reg_date DESC) rn FROM math WHERE reg_date >= '2021-10-17' - INTERVAL 1 DAY AND reg_date < '2021-10-17' + INTERVAL 1 DAY ) a WHERE rn = 1 GROUP BY subject, mb_idx, name ) a -- WHERE ABS(today - yesterday) / today * 100 > 5 WHERE ABS(today - yesterday) / yesterday * 100 > 5 ;
SELECT subject , mb_idx , name , today , yesterday , (today - yesterday) / yesterday * 100 rat FROM (SELECT b.subject, b.mb_idx, b.name , MAX(CASE WHEN a.dt = '2021-10-17' THEN b.point END) today , MAX(CASE WHEN a.dt != '2021-10-17' THEN b.point END) yesterday FROM (SELECT subject , mb_idx , DATE(reg_date) dt , MAX(reg_date) reg_date FROM math WHERE reg_date >= '2021-10-17' - INTERVAL 1 DAY AND reg_date < '2021-10-17' + INTERVAL 1 DAY GROUP BY subject, mb_idx, DATE(reg_date) ) a INNER JOIN math b ON a.subject = b.subject AND a.mb_idx = b.mb_idx AND a.reg_date = b.reg_date GROUP BY b.subject, b.mb_idx, b.name ) a WHERE ABS(today - yesterday) / yesterday * 100 > 5 ;