6. STRING&DATE

문제

1) 루시와 엘라 찾기

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE FROM ANIMAL_INS
    # WHERE NAME REGEXP '^(Lucy|Ella|Pickle|Rogan|Sabrina|Mitty)$'
    WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
    ORDER BY ANIMAL_ID;

주어진 조건에 맞는 이름을 가진 동물 친구들을 찾는 문제이다. 해당 문제에서는 REGEXP를 사용해서 해당 이름을 포함하고 있으면 결과를 리턴해주는 방법으로 문제를 풀어도 되지만, REGEXP를 사용하려면 조금 더 디테일하게 코드를 작성해야함을 알게 되었다.

먼저 REGEXP는 sql에서 정규표현식에 해당하며, python에서는 re 모듈과 굉장히 유사하다고 할 수 있다. 이전 문제처럼 REGEXP '{조건}|{조건}' 으로 사용하게 되면, 조건이 포함된 문자열이 반환되므로, 'xxLucy', 'xLucyxx'와 같은 이름이 존재한다면 이름이 'Lucy'가 아님에도 결과에 포함될 수 있다. 단순히 문자열의 일치와 or 조건을 성립하기 위해서는 조건의 처음과 끝에 ^, $ 를 작성함으로써 문제를 해결할 수 있다.

REGEXP 방법 이외에도, LIKE를 활용하는 방법과 IN을 활용하는 방법이 있는데, LIKE의 경우 모든 조건에 한번씩 매칭해줘야하기 때문에 코드가 굉장히 길어지지만 IN을 활용하게 되면 보다 심플한 코드로 문제를 해결할 수 있다.

2) 이름에 el이 들어가는 동물 찾기

SELECT ANIMAL_ID, NAME FROM ANIMAL_INS
    WHERE UPPER(NAME) REGEXP 'EL' and ANIMAL_TYPE = 'Dog'
    # WHERE UPPER(NAME) LIKE '%EL%' and ANIMAL_TYPE = 'Dog'
    ORDER BY NAME;

이번 문제에서도 문자열과 조건에 해당하는 문제이다. 동물의 유형, ANIMAL_TYPE이 개(Dog)이면서, 이름에 'EL'이 포함되어 있는 동물 친구들을 구하는 문제이다. 이 때, 이름에 대소문자 구분없이 'EL'이 포함되는 모든 경우를 고려해야하는 것이 이 문제의 해결 부분이라고 생각한다.

동물의 이름을 모두 소문자로 바꾸거나, 대문자로 바꾸어 'el' 혹은 'EL'을 포함하는 동물 친구들을 찾으면 간단하게 찾을 수 있다. 이 경우에도, REGEXP를 활용할 수 있고, LIKE 또한 가능하지만, IN의 경우에는 일치 여부의 문제이기 때문에 사용할 수 없다.

3) 중성화 여부 파악하기

SELECT ANIMAL_ID, NAME,
    # IF(SEX_UPON_INTAKE REGEXP 'Neutered|Spayed', 'O' , 'X') AS 중성화
CASE
    WHEN SEX_UPON_INTAKE REGEXP 'Neutered|Spayed' THEN 'O'
    ELSE 'X' END AS '중성화'
    FROM ANIMAL_INS
    ORDER BY ANIMAL_ID

SEX_UPON_INTAKE을 참조해 '중성화'라는 새로운 칼럼을 생성하는 것이 3번 문제이다. 가장 간단한 방법으로는 IF문, IF(조건, '참', '거짓')을 활용해서 '중성화'라는 새로운 칼럼을 생성할 수 있다. 또한 CASE문을 활용할 수 있다.

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

SELECT A.ANIMAL_ID, A.NAME
    FROM ANIMAL_INS A INNER JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID
    ORDER BY (B.DATETIME - A.DATETIME) DESC 
    LIMIT 2

입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 문제이다. 먼저 동물 보호소에 들어온 기록과 입양을 간 기록이 모두 존재한 동물만이 4번 문제에 해당함으로 ANIMAL_INS 데이터프레임과 ANIMAL_OUTS 데이터프레임과 INNER JOIN을 적용해야 한다.

보호 기간이 가장 긴 동물은 입양을 간 시점에서 들어온 시점의 차이가 가장 큰 것을 의미함으로 ANIMAL_OUTS의 DATETIME과 ANIMAL_INS의 DATETIME을 빼준 다음 내림차순으로 정렬해주면 문제는 해결된다.

5) DATETIME에서 DATE로 형 변환

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

단순히 DATETIME의 형식을 변환해주는 문제이다. DATETIME의 형식에 관한 것은 python과도 유사한 부분이 있는 것 같다. 먼저 DATE_FORMAT(날짜, 출력 형식)은 날짜형 데이터를 원하는 형식으로 출력하는 함수로, 여기서는 '%Y-%m-%d'으로 '연-월-일'로만 출력할 수 있다.

출처 : https://lightblog.tistory.com/155