[ICT인턴십] SQL관련 학습

Select Query Processing

SQL SELECT 문 처리 순서에 대해서 알아보았다.

SELECT BPLCNM, MAX(APVPERMYMD)
FROM L_O_C_A_L_D_A_T_A_031203__S_D
WHERE TRDSTATENM = '폐업'
GROUP BY BPLCNM
HAVING MAX(APVPERMYMD) < 20150000000000
ORDER BY BPLCNM DESC

 SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY 순서로 쿼리문이 작성되었을 때,
DBMS 내부적으로 FROM - WHERE - GROUP BY - HAVING - ORDER BY- SELECT 순서로 처리된다.
일반적으로 SELECT가 가장 나중에 처리된다고 생각하면 될 것으로 보인다.

다만, WHERE 절에서 사전에 처리할 수 있는 조건을 걸어둔다면 HAVING까지 도달하는 레코드 수가 줄어들기 때문에
실행 시간에서 이득을 볼 수 있다.

Referenced: https://myjamong.tistory.com/172

SELF INNER JOIN

SELF INNER JOIN에 대해서 알아보았다.

 일반적으로 두 개 이상의 테이블이 있는 경우에 JOIN을 사용하는데 특수한 경우(한 테이블 내에 컬럼이 서로 관련이 있는 경우)에 사용할 수 있다. 아래 링크에서 확인할 수 있듯이 customer_id와 spouse_id가 서로 관련이 있다. 따라서 다음과 같이 JOIN할 경우 spouse_id를 통해 관련된 내용을 컬럼으로 가져올 수 있다.

SELECT *
FROM customer T1
JOIN customer T2
ON T1.customer_id = T2.spouse_id

 테이블 내에 관련된 컬럼이 전혀 없는 경우에는 SELF JOIN을 수행하기 어렵다. 의미 상으로 적절한 SELF INNER JOIN을 찾는 것이 중요하다고 할 수 있겠다.

Referenced: https://kimsyoung.tistory.com/entry/SELF-JOIN-上-같은-테이블을-조인하기

 가장 최근 날짜에 해당하는 레코드를 검색하려면 MAX(날짜) 또는 LIMIT/ROWNUM와 같은 키워드를 사용해야 하는데 LIMIT/ROWNUM는 RDBMS 공통 문법이 아니기 때문에 사용할 수 없고 MAX(날짜) 함수를 사용할 경우, 해당 날짜의 컬럼을 가져올 수 없다...
따라서 매우 제한적으로 MAX(날짜)와 같은 함수를 사용해 해당 날짜에 대한 정보를 가져올 수 있는 것이다.

Referenced: https://homeresidents.tistory.com/entry/가장-최근-날짜의-값을-가져오는-방법

-> 서브 쿼리를 이용해 가장 최근 날짜 정보를 모두 가져오는 방법을 알아냈다! 아래의 쿼리를 확인하자.

SELECT *
FROM L_O_C_A_L_D_A_T_A_010106__D_J
WHERE APVPERMYMD = (
  SELECT MAX(APVPERMYMD)
  FROM L_O_C_A_L_D_A_T_A_010106__D_J
)

 APVPERMYMD는 인허가 일자를 나타내는데 서브 쿼리에서 가장 최근 인허가 일자를 검색하고 해당 일자와 같은 인허가 일자를 메인 쿼리의 WHERE 절에서 검사하면 가장 최근 날짜에 대한 레코드 정보를 검색할 수 있다.

Referenced: https://pingukim.tistory.com/5

모호한 날짜 표현

SELECT SUM(TOTAL_CNT) - (
  SELECT SUM(TOTAL_CNT)
  FROM JUNGGU_JUVENILE_HARMFUL_BIZ
  WHERE YOUTH_CTA_COB_NM = '단란주점'
)
FROM JUNGGU_JUVENILE_HARMFUL_BIZ
WHERE YOUTH_CTA_COB_NM = '관광호텔'

 위 쿼리문은 청소년 유해업소에 관한 테이블에 대한 질의이다. 내가 궁금증을 느낀 부분은 업종이 '관광호텔'인 사업장에서 발생한 사건 수의 합과 업종이 '단란주점'인 사업장에서 발생한 사건 수의 합의 차를 구하는 부분인데, 서브 쿼리를 적용하지 않고는 계산할 수가 없는 질의이기 때문에 SELECT문에 단란주점에서 발생한 사건 수의 합을 구하는 서브 쿼리를 작성하였다.

이처럼 테이블 내에 두 개 이상의 레코드의 정보를 이용하는 연산에서는 서브 쿼리를 작성하는 것이 필수인 것 같다.

 7월부터 11월까지와 같은 날짜를 표현할 때, 아래 쿼리문처럼 작성하는데

SELECT FNAME, ANAME
FROM SEARCH_PUBLIC_TOILET_P_O_I_SERVICE
WHERE INSERTDATE >= 20100700000000 AND INSERTDATE <= 20101130235959
ORDER BY FNAME ASC

 7월과 11월 사이라고 표현하게 되면 7월과 11월을 모두 제외하는지 포함하는지 모호할 수 있다. 실제로 제외해야 할 것 같지만 10월과 11월 사이라고 표현하면 10월과 11월을 포함해야 하는 문제가 발생하므로 사용을 지양하는 것이 좋을 것 같다.

집계 함수 자동 캐스팅

소재지 면적에 대한 MIN, MAX 집계 함수를 사용하였을 때, 아스키코드를 기준으로 우선순위를 검사하는 것을 확인할 수 있었다. 미루어 보건데 소재지 면적이 수치 데이터 타입이 아닌 문자열 데이터 타입을 저장된 것으로 생각할 수 있다. 따라서 수치 데이터에 대해 집계 함수를 사용할 때, MIN, MAX를 사용하지 않을 것을 권장한다.

단, AVG를 사용했을 때는 DBMS 차원에서 문자열 타입을 수치 데이터 타입으로 변경해주는 것 같다.

SELECT MAX(SITEAREA), MIN(SITEAREA)
FROM L_O_C_A_L_D_A_T_A_072201__S_P

하지만 AVG를 포함시켰을 때는

SELECT MAX(SITEAREA), MIN(SITEAREA), AVG(SITEAREA)
FROM L_O_C_A_L_D_A_T_A_072201__S_P

DBMS에서 문자열로 저장된 데이터를 수치 데이터 타입으로 변환해주기 때문에 정상적으로 출력된다.

하지만 DBMS마다 차이가 있을 수 있고 쿼리마다 값이 달라져 오해를 부를 수 있기 때문에 가급적 사용하지 않도록 하자!

CASE WHEN, IF ELSE

 NL2SQL 라벨링을 진행하다가 지번 주소 컬럼과 도로명 주소 컬럼이 있는데 다수의 레코드에서 둘 중 하나의 데이터만 존재하는 것을 볼 수 있었다. DB에서 이렇게 둘 중 하나의 데이터를 조건에 맞게 고를 수 없을까?라는 의문이 떠올라 찾아보게 되었다. SQL문으로 IF 조건문을 구사하려면 CASE WHEN절을 이용한다.

SELECT DISTINCT
GENDER,
CASE WHEN GENDER = '1' THEN '여자' ELSE '남자' END
FROM USER_INFO

이렇게 쿼리문을 작성하면 GENDER 컬럼의 데이터가 1이면 여자, 그 외라면 남자로 보여준다.

마찬가지로 IF ELSE를 이용해도 같은 조건문을 구사할 수 있다. (단, MySQL만 가능)

IF(GENDER = '1') '여자' ELSE '남자' END

Referenced: https://coding-factory.tistory.com/113