프로그래머스 SQL 고득점 Kit

링크

이번에 sw마에스트로 지원을 해서 코딩 테스트를 보게 되었다.

SQL 문제 대비로 풀이.

전체 문제가 아니라, 내가 기록하고 싶었던 것 위주로 남긴다.

시험 전에 다시 훑어볼 용도.

공통

• 정렬 조건 잊지 않기.
• JOIN이 그렇게 단골이라고 한다.

SELECT

여러 기준으로 정렬하기

SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME, DATETIME DESC

• order by 기준1, 기준2
• 기준1 -> 1이 같을 경우 2로 정렬
• 기준 1, 2에 각각 별개로 asc desc가 붙는다.

상위 N개 레코드

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME ASC
LIMIT 1

• DBMS마다 문법이 다른데, MYSQL의 경우 LIMIT
• LIMIT 개수 / LIMIT 시작번호, 개수
• 시작번호는 0부터 시작하는 인덱스. 예를 들어서 LIMIT 1, 10 이거는 두번째 (인덱스=1) 부터 10개의 튜플을 가져옴.

SUM, MAX, MIN

중복 제거하기

SELECT COUNT(DISTINCT NAME) AS 'count'
FROM ANIMAL_INS
WHERE NAME IS NOT NULL

• COUNT와의 조합은 이렇게 한다.
  • COUNT(DISTINT 컬럼명)
• 여러개 기준? DISTINCT A, B FROM 테이블명
  • 이렇게 하면 (A, B) 조합에 대해 중복을 검사한다.

GROUP BY

고양이와 개는 몇마리 있을까

SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) AS 'count'
FROM ANIMAL_INS
-- WHERE ANIMAL_TYPE = 'Cat' OR ANIMAL_TYPE = 'Dog'
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE

• 동물 종류가 고양이, 개 뿐이라서 WHERE절 의미 X

동명 동물 수 찾기

SELECT NAME, COUNT(NAME) AS 'COUNT'
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) >= 2
ORDER BY NAME

• WHERE은 GROUP BY 하기 전의 조건,
• HAIVING은 GROUP BY 한 후의 조건.

입양 시각 구하기 (1)- HOUR()

SELECT HOUR(DATETIME) AS 'HOUR', COUNT(HOUR(DATETIME)) AS 'COUNT'
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) < 20
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)

• HOUR() 함수는 DATETIME에서 시각만 가져와 반환한다.
• 유사하게 YEAR, MONTH, DAY, MINUTE, SECOND 등

입양 시각 구하기 (2) - SET

SET @HOUR = -1;
SELECT (@HOUR := @HOUR +1) AS HOUR,
    (SELECT COUNT(HOUR(DATETIME))
     FROM ANIMAL_OUTS
     WHERE HOUR(DATETIME)=@HOUR) AS COUNT
FROM ANIMAL_OUTS
WHERE @HOUR < 23;

• 키트에서 제일 어려웠던 문제
• SQL에서도 변수 있습니다. SET @변수명

IS NULL

NULL 처리하기 - IFNULL()

SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name') AS NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

• IFNULL() 함수에 주목.
• IFNULL(대상컬럼명, 채울내용)

JOIN

우선 이 사진을 보자. 이 사진에 모든 답이 나와 있다.

추가로 다음과 같은 것을 주의한다.

• SELECT에서 컬럼명 쓸 때, 어느 TABLE의 컬럼인지 명시. A.NAME
• OUTER JOIN은 키워드가 아니라 WHERE B.ID IS NULL 이렇게 쓴다.
• INNER, OUTER 어디에 해당하는지 쿼리 쓰기 전에 똑바로 확인하자.

보호소에서 중성화한 동물 - LIKE

SELECT O.ANIMAL_ID, O.ANIMAL_TYPE, O.NAME
FROM ANIMAL_INS I
RIGHT JOIN ANIMAL_OUTS O
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.SEX_UPON_INTAKE LIKE 'Intact%' AND
      (O.SEX_UPON_OUTCOME LIKE 'Spayed%' OR
      O.SEX_UPON_OUTCOME LIKE 'Neutered%');

• 문자열 완전 일치는 =로 검사하나, 포함 같은 경우엔 LIKE사용.
• 와일드카드 %
  • Intact%: Intact로 시작하는 문자열 (Intact 그자체도 가능)
  • %intact: intact로 끝나는 문자열 (intact 그자체도 가능)

String, Date

이름에 el이 들어가는 동물 찾기 - LOWER()

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE LOWER(NAME) LIKE '%el%' AND ANIMAL_TYPE='Dog'
ORDER BY NAME;

• 와일드카드 %의 사용 방법 주목
• 대소문자 무시
  • LOWER 걸어서 변환 후 소문자로 LIKE 서치

중성화 여부 파악하기 - IF(), REGEXP()

SELECT ANIMAL_ID, NAME, IF(REGEXP_LIKE (SEX_UPON_INTAKE, 'Neutered|Spayed'), 'O', 'X') AS '중성화'
FROM ANIMAL_INS

• 생소한 함수들이라 두번째로 어려웠던 문제

• IF(조건, 참일때, 거짓일때채울값)

• REGEXP_LIKE() 정규표현식을 이용한 LIKE문.

  • 앞서 REGEXP_LIKE 쓰기 전엔 이렇게 표현했었음.

  • O.SEX_UPON_OUTCOME LIKE 'Spayed%' OR
    O.SEX_UPON_OUTCOME LIKE 'Neutered%'

• 정규표현식에 대해서

  • 포함 여부 알기: 그냥 A
  • 공백 포함되면 안 됨 (공백도 문자로 취급)

• IF의 결과가 하나의 컬럼이므로, 그대로 SELECT에 갖다붙인다.

오랜 기간 보호한 동물(2) - DATEDIFF

SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_INS I
JOIN ANIMAL_OUTS O
ON I.ANIMAL_ID = O.ANIMAL_ID
ORDER BY DATEDIFF(O.DATETIME, I.DATETIME) DESC
LIMIT 2

• 오랜 기간 = datediff 값이 큰 것. 그러므로 DESC 정렬

DATETIME에서 DATE로 형변환

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS "날짜" 
FROM ANIMAL_INS

• DATE(DATETIME) 이렇게 하면 원하는 대로 안 나옴.
  • 2017-04-13 00:00:00 이렇게 나온다.
• DATE_FORMAT 함수에 주목. 그리고 Y만 대문자다.
  • %y (소문자)라면 16-04-19 이렇게 연도에서 두자리만 나온다.
  • %M (대문자)라면 숫자 04 대신 April 이라고 나온다.
  • %D (대문자)라면 숫자 19 대신 19th라고 나온다.
  • 2016-April-19th와 16-04-19의 차이.