Intro
쿼리가 괴로운 나를 위해 내가 만든 가이드.
기본적인 실수 방지
- 함수로 계산된 값은 ALIAS 필수로 추가하기
- WHERE 조건절에 들어가는 컬럼 순서들이 DB 인덱스 설계 컬럼 순서와 일치하는 지 확인하기.
Inner Join 다음에 Left Outer Join
inner 조인으로 가능한 것을 left로 하지 않는다.
left여야 하는 경우
(예: 왼쪽 테이블 기준으로 다 보여주어야 하는 경우)
----- 밑에 정리하기 ----
통계를 낼때는 항상 해당 날짜에 통계값이 없는, null인 경우를 고려해서 기본값 설정을 해야 한다.
=> 나의 경우 대시보드 감정 데이터를 구할 때 기본값 설정을 고려하지 않아 관련 코멘트가 부정적으로 나왔다.
조건들을 on절로 처리했다.
이건 스프링 부트와 연관된 내용으로, 별도 쿼리를 2번 날릴 생각보다는 collection과 resultMap을 이용해서 하나의 sql id로 처리해야 커넥션 횟수를 줄이고 성능을 높일 수 있다.
평균을 내고 싶다면 어떻게 해야 할까?
- avg()함수를 쓸 수도 있다.
- 인라인 뷰를 만들어서 처리한다.
(평가 점수 총합 / 평가 개수) = 평균
평가 개수를 어떻게 구할까? 인라인 뷰에서 1 AS CNT를 써서 그룹별 갯수가 몇 개인지 카운팅할 수 있다.
IFNULL()을 잊지는 않았는가?
더 정확히는 데이터가 null일 때 데이터 전체에 어떤 결과가 미칠 지도 생각해 보야야 한다는 것이다.
substr()을 어디부터 어디까지 할거야?
프로젝트 상에서 id로 검색을 하는데 구조상 파싱해서 검색해야 했다.
- 전체 id로 검색하면 select 태그에 다른 id여도 조상이 같으면 중복으로 나타나는 이슈가 발생
나의 경우 기본으로 where 조건절이 3개는 기본으로 깔고 들어갔는데,
이 where 조건절 3개를 달아서 결괏값이 1행인가?도 봐야 한다.
조건절 3개가 필요하지만, 결과가 N개 행이면 관리번호 + 1/2개 추가 조건절로 노선 틀어야 했다.
관리번호는 PK이니까
시간, 시간, 시간 아오....
통계를 하면서 날짜를 무시할 수 없다.
아 이런게 있었다~ 식의 키워드 연상용으로 몇 자 적는다.
주어진 두 시간의 시차 구하기: TIMESTAMPDIFF
시작날짜와 끝날짜를 받아서 해당 기간동안의 날짜리스트를 출력할 수 있다.
WITH RECURSIVE DATES AS (
SELECT DATE_FORMAT('2024-07-08', '%Y-%m-%d') AS TARGETDATE
UNION ALL
SELECT TARGETDATE + INTERVAL 1 DAY FROM DATES WHERE TARGETDATE < DATE_FORMAT('2024-07-14', '%Y-%m-%d')
)
SELECT * FROM dates;
그럼 오늘 날짜를 제외하고 어제부터~그 전 7일 날짜 리스트를 출력하고 싶다면?
WITH RECURSIVE DATES AS (
SELECT DATE_FORMAT(now() - INTERVAL 7 DAY , '%Y-%m-%d') AS TARGETDATE
UNION ALL
SELECT TARGETDATE + INTERVAL 1 DAY FROM DATES WHERE TARGETDATE < DATE_FORMAT(now() - INTERVAL 1 DAY, '%Y-%m-%d')
)
SELECT * FROM dates;
이렇게 하면 8월 12일 기준 8월 5일~8월 11일까지 출력할 수 있다.
과거 내 쿼리
WITH RECURSIVE CNT AS (
SELECT 7 AS N
UNION ALL
SELECT N - 1
FROM CNT
WHERE N > 1
),
WEEKLY_LABELS AS (
SELECT DATE_FORMAT(CURDATE() - INTERVAL N DAY, '%Y-%m-%d') AS LABEL
, '1' MAPPING
FROM CNT
)
2개의 recursive가 필요했을까... 생각이 듦.
<매핑할 조건이 없을 경우>
아래는 FULL OUTER JOIN 예시 (학생과 날짜를 FULL OUTER JOIN하려는데 조건이 없는 경우)
MYSQL은 근데 FULL OUTER JOIN을 지원하지 않음.
- UNION할 수도 있음
=> 근데 왠지 원하는 데이터가 아닌 것 같음(고려해보는 시간이 필)
=> 대체안: INNER JOIN 후 LEFT OUTER JOIN
이 시간에 이 날짜에 모든 학생들의 감정 매핑을 하고 싶은데
조건이 없다?
단순 매핑용. 저걸 해야 JOIN시 매핑이 가능하다.
학생별 매핑 조건을 위해서 가상으로 MAPPING를 만든 것이다.
WITH문에서 날짜, 1(MAPPING)이 나왔으니까
강제로 맞추기가 가능하다.
전체값을 가져오려는데 날짜별로 학생들이 다 있어야 하는데 매핑을 해줄 게 없다~
그래서 강제로 하나 만들었다.
날짜별로 조인을 하면 INNER JOIN할 때 다 걸릴 것 아니야~
selectEmtAnlsByTxtbkUuidAndClasUuid
예상 데이터 수가 1건인데 N건이 조회될 경우에 대한 대비 처리
정책상 1개 데이터가 예상되더라도 실제는 어떻게 될 지 모르는 일.
MAX()
- 현재 진행중인 목표 1건 보여주기 (만약에 프론트측 요청 오류로 N건이 들어간다면?)
