꿈꾸는 개발자, DBA 커뮤니티 구루비

self-join 어제의 값과 오늘 값의 비교 쿼리 질문입니다.

by harus4 [SQL Query] [2022.02.15 11:49:19]

학생들의 각 과목의 오늘 최고 점수와 어제 마지막 점수를 비교하여 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');

어떻게 짜야 답이 나올까요?

by 마농 [2022.02.15 13:21:39]

1. 오늘은 최고 점수이고, 어제는 마지막 점수인가요?
- 둘다 최고 이거나 둘다 마지막 이거나 기준이 동일해야 하는 것 아닌가요?
- 이 기준이 맞는 건가요?
- 어제자 자료가 없는 경우도 보이는데? 이 경우 어떻게 결과가 나와야 하는지?
2. subject 가 null 인 자료도 있나요?
3. 점수의 자료형이 varchar 네요
- 숫자형으로 선언해야 합니다.
- 문자형의 경우 최고값을 뽑기 어렵습니다.

by harus4 [2022.02.15 13:33:23]

1. 둘다 마지막 점수입니다

- 어제자 자료에 없는 경우는 무시 해도 됩니다.

2. 질문에서 수정했습니다.

3. 수정했습니다.

by 마농 [2022.02.15 14:03:59]

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
;

by harus4 [2022.02.15 14:55:26]

어제 입니다

mysql 5.6 에서 partition 이 되나요?

by 마농 [2022.02.15 15:40:28]

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
;

by harus4 [2022.02.15 16:47:47]

감사합니다 마농님

쿼리 짜는게 쉬운게 아니네요 ㅜ.,ㅜ

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변