SELECT 4번, JOIN 2번 일어나는 쿼리를 더 효율적인 쿼리로 짜고 싶습니다 0 3 1,285

by 피타칩스 [SQL Query] 튜닝 SQL [2019.12.04 21:39:43]


유저들을 매칭시켜주는 프로그램을 만들고 있습니다.

한 명의 유저가 지난 10일간 매칭된 내역을 쫙 보여줄 수 쿼리를 만드니 SELECT가 4번, JOIN이 2번이나 일어나는데요 ㅠㅠ

 어떤 부분을 변경하면 깔끔한코드 또는 속도개선을 할 수 있을지 조언 주시면 감사하겠습니다.

 

match 테이블은 매치가 일어난  매치id, 매치장소, 매칭이 이뤄진 시간(createdAt) 를 가지고 있구요.

mysql> select * from matches;
+----+----------+---------------------+---------------------+
| id | location | createdAt           | updatedAt           |
+----+----------+---------------------+---------------------+
|  1 | yongsan  | 2019-12-01 10:14:35 | 2019-12-01 10:14:35 |
|  2 | yongsan  | 2019-12-01 10:20:15 | 2019-12-01 10:20:15 |
|  3 | yongsan  | 2019-12-01 10:21:16 | 2019-12-01 10:21:16 |
+----+----------+---------------------+---------------------+

matchTimes 테이블은 각각의 매치마다 가능한 미팅 시간을 담은 테이블로, OneToMany 관계입니다.
 

mysql> select * from matchTimes;
+----+-----------+---------+---------+
| id | timeStart | timeEnd | matchId |
+----+-----------+---------+---------+
|  1 |       750 |     780 |       1 |
|  2 |      1250 |    1290 |       1 |
|  3 |       750 |     780 |       2 |
|  4 |      1250 |    1290 |       2 |
|  5 |       700 |     780 |       3 |
|  6 |      1250 |    1300 |       3 |
+----+-----------+---------+---------+

그리고 userMatches 테이블은 User와  Match 테이블 간의 ManyToMany 테이블 입니다. 

mysql> select * from userMatches;
+---------------------+--------+---------+
| createdAt           | userId | matchId |
+---------------------+--------+---------+
| 2019-12-01 10:14:46 |      1 |       1 |
| 2019-12-01 10:20:15 |      1 |       2 |
| 2019-12-01 10:21:16 |      1 |       3 |
| 2019-12-01 10:14:46 |      2 |       1 |
| 2019-12-01 10:20:15 |      2 |       2 |
| 2019-12-01 10:21:16 |      3 |       3 |
+---------------------+--------+---------+


 

제가 하고 싶은건,

userId를 알고있는 상황에서, 해당 유저가 최근 10일 안에 매칭된 모든 match 를 찾고, 그 match에 매칭된 복수의 유저들(본인 포함)을 받고 싶고, 해당 매치의 매치장소, 그리고 매치가능한 복수의 시간대들도 받고싶습니다.

결과적으로 이런 데이터의 모습입니다.

+-------------------+---------+----------+---------------------+----------------+
| matchedTimeSets   | matchId | location | createdAt           | matchedUserIds |
+-------------------+---------+----------+---------------------+----------------+
| 750~780,1250~1290 |       1 | yongsan  | 2019-12-01 10:14:35 | 1,2            |
| 750~780,1250~1290 |       2 | yongsan  | 2019-12-01 10:20:15 | 1,2            |
| 700~780,1250~1300 |       3 | yongsan  | 2019-12-01 10:21:16 | 3,1            |
+-------------------+---------+----------+---------------------+----------------+

제가 짠 쿼리는  예를들어 userId가 1일 때,

SELECT  
 GROUP_CONCAT(CONCAT(mt.timeStart, '~', mt.timeEnd)) as 'matchedTimeSets', 
 m_and_um.matchId, 
 m_and_um.location, 
 m_and_um.createdAt, 
 m_and_um.matchedUserIds  
FROM matchTimes as mt   
INNER JOIN   
(SELECT 
 um.matchId, m.location, m.createdAt, GROUP_CONCAT(um.userId) as 'matchedUserIds'  
FROM  
 (SELECT matchId, userId from userMatches   
   WHERE matchId in (SELECT matchId from userMatches WHERE userId=1)   
 ) as um       
 INNER JOIN     
 matches AS m ON um.matchId = m.id  
 WHERE TIMESTAMPDIFF(DAY, m.createdAt, UTC_TIMESTAMP()) < 10  
 GROUP BY um.matchId   
) as m_and_um on mt.matchId = m_and_um.matchId     
GROUP BY m_and_um.matchId; 

 

이렇게 생겨서 ㅠㅠㅠ 개선하고 싶습니다.

 

matchTimes에 관한 데이터를 matchTimes 테이블로 정규화 시키지 않고 match 테이블 안에 애초에 콤마로 연결된 스트링으로 넣으면 조금 쿼리가 짧아지긴 하는데... 고수님들 도와주세요 ㅠ

 

 

 

by 우리집아찌 [2019.12.05 10:05:29]

 

모바일이라 sql은 올리기 힘들고요

매치타임과 유저매치를 각각 매치아이디로 그룹핑해서 조인 처리하면 좀 깔끔하게 보일것 같네요


by 마농 [2019.12.05 11:05:57]

 

SELECT GROUP_CONCAT(CONCAT(e.timeStart, '~', e.timeEnd)) matchedTimeSets
     , c.matchId
     , d.location
     , d.createdAt
     , c.matchedUserIds
  FROM (SELECT b.matchId
             , GROUP_CONCAT(b.userId) matchedUserIds
          FROM userMatches a
         INNER JOIN userMatches b
            ON a.matchId = b.matchId
         WHERE a.userId = 1
         GROUP BY b.matchId     -- ①
        ) c
 INNER JOIN matches d
    ON c.matchId = d.id
 INNER JOIN matchTimes e
    ON d.id = e.matchId
 WHERE d.createdAt > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 10 DAY)    -- ②
 GROUP BY c.matchId
     , d.location, d.createdAt, c.matchedUserIds    -- ③
;

① 그룹바이 위치 변경
 - 조인후 구룹바이 -> 그룹바이 후 조인
 - um 과 m 을 조인하기 전 um 안에서 그룹바이 수행
② 조건 줄 때
 - 컬럼을 가공하지 말고 조건값을 가공
 - 기준일과 날짜차이가 10일인걸 구하지 말고 -> 기준일 10일 이전 날짜와 비교
③ 표준 구문 이용
 - 알리아스에 홑따옴표 빼기
 - 그룹바이 표준 지키기
 - matchId 만으로 그룹핑해도 다른 항목들이 matchId 에 종속된 1개의 값이므로 결과엔 문제가 없지만
 - 이는 그룹바이 표준에 어긋나는 문법입니다.
 - 다른 항목들도 그룹바이에 포함시켜야 합니다.
 


by 피타칩스 [2019.12.05 22:07:48]

우와 감사합니다. 항목별로 설명해주신게 큰 도움이 되었어요. 칼럼데이터의 WHERE ... IN 을 JOIN 으로도 바꿔서 select 한번 사라진것도 이득일 것 같아요.

그리고 혹시 sql 표준 가이드는 어디서 볼 수 있나요? 공부하면 도움될 것 같은데 공인기관의 가이드가 있는건가요? 잘 안 찾아지는데,   http://www.gurubee.net/bookcafe/sqlp_1st 이 링크가 표준적인 sql이라고 보고 공부하면 될까요?

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입