[SQL 쿼리 오답노트] 미니 프로젝트 때 쿼리 실수 했던 부분들

보통 쿼리를 그냥 눈으로 배우기만 했을 때와 직접 타이핑을 해보면서 쿼리를 따라칠 때는 매우 다르다고 한다. 컴퓨터 언어를 배울 때 흔히 말하는 '백견이불여일타', '백문이불여일타'와 같은 이야기다.

 

그런데 이번에 직접 '문제 설정 - 데이터 수집 - 전처리 - 가공 - 분석(가설 검정)'의 한 사이클을 돌려보니, '코딩 테스트처럼 어느 정도 데이터와 힌트가 주어진 상황에서 쿼리를 짤 때'와 '프로젝트를 하면서 직접 수집한 데이터로 쿼리를 짤 때' 느낌이 매우 다르다는 것을 깨달았다. 

 

그리고 프로젝트를 하면서 SQL뿐만 아니라 파이썬, 통계 등 많은 내용을 다뤄야하다보니, 마치 컴퓨터 메모리가 다 차서 버벅대면서 돌아가듯이 뇌가 생각을 빠릿빠릿하게 못해서 기본적인 SQL 쿼리 오류도 내곤 했는데 (남의 코드 고쳐줄 때는 잘 잡아내도 내 코드에서는 도대체 왜 오류나는지 모르는..그런 경우), 앞으로는 그럴 일이 없도록 이런 사소한 실수들을 잘 정리하는 나만의 쿼리 오답노트를 만들어야 겠다는 생각이 들었다.

 

 

---

 

 

[ 타임스탬프 데이터를 다룰 경우 ]

타임스탬프가 초까지 찍힐 경우,  데이터를 다룰 때 내 맘대로 임의로 시-분으로 짤라오면 안된다. 시-분-초까지 모두 제대로 설정할 것.

 

상황

 

대여일시가 '년월일시분초'로 타임스탬프가 찍히는 데이터였다.  여기서 데이터를 CASE문으로 특정 시간 별로 묶어서 구별한 다음 각 대여건 수를 알고 싶었는데, 그 과정에서 초를 빼먹고 쿼리를 짜서 제대로 집계가 되지 않았다. 그 당시에는 그냥 '오후 5시30분부터 오후 7시까지'의 식으로 시간대가 되어 있으니 아무 생각 없이 분까지만 쿼리를 짠 것 같다.(DATE_FORMAT(rent_datetime, '%H:%i')) 그 결과,  9:00:51의 대여 케이스도 9:00에 포함이 되어버리니 집계가 이상하게 되었던 거였다. 

아래와 같이 (DATE_FORMAT(rent_datetime, '%H:%i:%s'))로 다시 쿼리를 짜니 제대로 집계가 되었다.

 

 

 

 

 

 

[ WITH문 여러 개를 쓰는 경우 ]

여러 WITH문을 꼭 콤마로 연결해주기. 아무 생각없이 WITH문 여러 개만 쓰면 안됨! 콤마!!!

 

예전에 공부할 때 "WITH문 여러 개를 쓸 때 WITH는 한 번만 써주면 되고 그 다음 것에는 쓸 필요 없다."라고만 공부했어서 그런지 이번에 바보같이 콤마를 빼먹고 쿼리를 짰었다. 어떤 오류인지 구체적으로 알려주는 파이썬과 달리 SQL오류는 어디쯤에 오류가 있다고만 나와서 콤마 빼먹은 줄도 모르고 바보 같이 다른 부분만 살폈었다.. 이런 기본적인 실수는 다시 하지 않도록 뇌리에 박히게 글로 써본다.

WITH example1 AS (
SELECT
FROM
) -- 그리고 아래에 또 WITH문 쓸 때 콤마 잊지 말기
, example2 AS( 
SELECT
FROM
)

 

 

 

 

[ JOIN으로 테이블을 연결시킬 때 ]

JOIN을 하면서 ON 조건을 줄 때, 여러 컬럼이 같다면 그 컬럼 모두 ON의 조건으로 넣어주기

 

상황

JOIN으로 연결하려고 했던 두 테이블 JOIN으로 연결하려고 했던 두 테이블

우리가 원했던 결과 테이블

 

'특정 장소에서 시간대 별로 집계한 대여 수 테이블'과 '특정 장소에서 시간대 별로 집계한 반납 수 테이블'을 JOIN으로 한 테이블로 합치는 과정에서 처음에는 아래와 같이 시간대로만 연결했다. 

 

 

 

그랬더니 결과로 의도한 대로 제대로 집계되어 JOIN되지않고 각 테이블 별 행이 그냥 조합될 수 있는 경우의 수로 나왔다.

대여 테이블의 첫 행(1201 대여소에서 0시에 일어난 대여 건수)에 반납 테이블의 행들이 의미없이 붙어버렸다.

 

 

아래 쿼리 처럼 두 테이블 간에 중복 되는 내용(스팟, 공원, 시간대)를 모두 조건으로 주어 연결하니 원하는 테이블을 얻을 수 있었다.

SELECT O.spot
	  ,O.park
      ,O.hour AS hour
      ,O.cntRent
      ,T.cntReturn
      ,T.cntReturn/O.cntRent AS R1          
FROM rent_jun O
JOIN return_jun T ON O.spot = T.spot  
AND O.park = T.park
AND O.hour = T.hour -- 모든 중복 컬럼을 ON 조건에 넣어주기
ORDER BY 1, 3;

 

 

(+)

사실 위의 과정 마지막에서 쿼리를 짤 때 마지막으로 한 번 더 실수 했던 과정이 아래와 같이 각 조건 컬럼 마다 ON을 써준 것이였다. 

JOIN의 ON 조건을 여러개 주고 싶을 때는 AND로만 묶는 다는 것 잊지 말기! AND ON 아님!! AND만!

/* 잘못된 쿼리 예시 */
FROM rent_jun O
JOIN return_jun T ON O.spot = T.spot
AND ON O.park = T.park  -- 이렇게 아니고 그냥 AND O.park = T.park
AND ON O.hour = T.hour  -- 이렇게 아니고 그냥 AND O.hour = T.hour

 

 

 

 

[ 임시테이블을 사용하는 경우 ]

임시테이블을 만들어두고 유니온을 쓰려하면 안됨.

 

쿼리의 깔끔함과 속도를 위해 아래와 같이 임시테이블을 만드는 쿼리를 많이 활용했었다. 

CREATE TEMPORARY TABLE example AS(
)

 

그런데 이렇게 만든 임시테이블을 UNION하는 과정에서 오류가 발생했는데, 찾아보니 임시테이블 쿼리는 해당 세션에서 딱 한 번만 실행할 수 있는 쿼리였다. 

(참고로 그래서 임시테이블 쿼리를 돌리고 난 후, 한 번 더 실행시키면 오류가 뜨면서 이미 실행된 쿼리라고 나온다.)

 

 

임시테이블을 가지고 UNION 하려 한다면, 한 번 더 임시테이블을 실행시키려하는 게 되기 때문에(임시테이블은 한 번 밖에 불러올 수 없기 때문에) 굳이 유니온을 쓰고 싶다면 같은 내용의 이름을 달리하는 다른 임시테이블을 만드는 쿼리를 짤 것을 추천한다.

CREATE TEMPORARY TABLE example AS(
)
UNION
CREATE TEMPORARY TABLE example_under_anothername AS(
) -- 같은 임시테이블이지만 이름만 다르게 지정