[MYSQL] 프로그래머스 sql 쿼리 문제 후기 및 접근법

프로그래머스 sql 고득점 키트

sql 문제를 처음 풀어보면서 내가 생각했던 대로 안되네? 어떻게 내부적으로 동작할까? 에 대한 의문에 대해 적어본다.

 

보통 데이터베이스 문제를 푼다라고 하면 insert, delete 이런 DML 보다는 데이터를 질의하는 SQL 을 물어본다.

 

그렇다. select 문으로 답을 구한다.

 

그러면 select 문을 수행할 때 어떤 순서로 수행 되는지 알아야 하는게 우선이다. 하지만 본인은 문제풀면서 순서를 감으로 알게 되었고 스택오버플로우에 똑같은 질문이 있어서 select 문에서 수행하는 순서에 대해 링크를 남겨본다.

 

https://stackoverflow.com/questions/2617661/whats-the-execute-order-of-the-different-parts-of-a-sql-select-statement

What's the execute order of the different parts of a SQL select statement?

 

위 링크에 대해 요약하면 SELECT 문의 수행 순서는 다음과 같다.

 

SELECT 문에서 먼저 수행하는 순서

 

이제 내가 문제를 풀면서 유용했던 개념을 정리해본다. 

 

 

1) 모든 필드 검색하기

 

기본적으로 데이터가 어떻게 생겼는데 보려면 기본중에 기본!

 

select * from {TABLE}

으로 질의하면 된다. 

 

* 는 " 모든 열의 속성들" 을 다 가져온다.

 

모두 가져오기

2) 모든 필드 정렬하기

이름은 오름차순이며 이름이 같을 때 날짜는 내림차순으로 정렬하고 싶다면???

 

order by 를 쓰자.  옵션으로 asc 와 desc가 있다. asc는 오름차순이고 desc는 내림차순이다.

select *
from animal_ins
order by name asc , datetime desc

3) 특정 필드 검색하기

 

위에서 name 필드와 datetime을 가져오고 싶다면 다음과 같이 하면 된다.

 

이름과 날짜만 가져오기

 

4) 필드의 최대,최소 구하기

그렇다면 가장 최근 날짜를 가져오려면 어떻게 해야하나?? 아래와 같이 하면 된다.

 

가장 최근 날짜 가져오기

또 다른 방법으로는 order by 와 limit 의 조합을 사용하면 된다.

 

즉 정렬후에 1개 자르는 것이다.

SELECT DATETIME
FROM ANIMAL_INS
ORDER BY DATETIME 
LIMIT 1

 

5) 필드의 최대 또는 최소를 가지는 또다른 필드 구하기

 

그러면 가장 최근 날짜를 가진 동물의 이름은 무엇인지 알려면 아래와 같이 쿼리를 날리면 될까??

 

select name,max(datetime) from animla_ins;

 

미리 말하면 틀렸다.

 

가장 최근 날짜를 가진 이름을 찾으려면 2번 조회해야 한다. 먼저 가장 최근날짜가 언제인지 알아야 하며 그다음 그 날짜와 같은 날짜를 가진

동물의 이름을 찾는 것이다.

 

쿼리 하나로 뚝딱뚝딱 해결해 주지 않는다. 정답 쿼리는 아래와 같다.

 

 

 

위와 같이 where 절이든 어느절이든 또 다른 쿼리가 들어가있는 것을 서브쿼리라고 한다. 참고로 서브쿼리는 order by를 사용할 수 없다.

 

6)  필드 생성하기

 

select 라는 문구 자체가 테이블에서 어떤것을 "선택" 하는 것을 의미하기도 하지만 그렇다면 없는 필드를 선택할 수 있는 가?

 

없는 필드를 선택할 수는 없지만 필드를 만들어서 선택 할 수 있다.

 

위 쿼리는 0이라는 필드가 없지만 이상하게 정상 출력이 된다. 

 

그러면 아래의 사진을 보자.

 

0은 되고 no_field는 왜 안되는 것인가!  왜 알아서 만들어주지 않는가? 

 

여기서 알아야 할 것은 0은 필드 이름이 될 수 없으므로 필드이름으로 검색하지 않는다. 따라서 필드를 만들어 animal_ins 의 row 수만큼 0 이라는 숫자가 출력이 된다.

 

반면 no_field는 0처럼 문자열이 아니며 필드이름으로 검색한 것이 된다.

 

그래서 위와 같은 차이가 나는것이다. 

 

참고로 필드 이름은 자동으로 0 이라는 이름으로 alias 된다. 이때 필드 이름을 바꾸고 싶다면 as를 쓰면 된다.

 

아래의 코드 처럼...

 

select 0 as '이것은 제로여' from animal_ins;

 

그러면 name 과 필드를 새로 정의하여 초기화 해보자

 

 

 

7) 새로 정의한 필드에 특정 조건문을 넣고 싶다면?

 

위에는 새로운 필드에  "new field!!" 이라는 값만 넣었다. 하지만  이름이 Sugar인 동물에게만 "이것은 설탕이여" 라는 데이터를 넣고 싶다면?? 그럴때 필요한 것이 if 이다.

 

 

 

 

8) 중복된 필드 값을 제외하고 싶다면?

 

DISTINCT

select distinct name
from animal_ins;

위와 같이 한다면 중복된 이름을 제외한 모든 이름을 볼 수 있다.

 

group by 를 사용해도 된다. 

 

SELECT NAME
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME

 

 

9) 특정필드의 필드값의 개수는 ??

select count(name)
from animal_ins;

 

참고로 count는 null의 값을 카운트하지 않는다.

그냥 모든 row의 개수가 궁금하다면 아래처럼 하자.

 

select count(*)
from animal_ins;

 

중복된 이름은 제거하고 모두 카운트 하려면 아래처럼 한다.

select count(distinct name)
from animal_ins;

 

 

10) 특정그룹별로 그룹의 사이즈가 궁금하다면?

 

동물의 종류 별로 얼마나 있는지 카운트하는 쿼리다.

select animal_type,count(*)
from animal_ins
group by animal_type

 

 

11) 그룹별로 조건걸기 

 

그렇다면 조건을 걸수 없을 까??

 

그룹별 조건을 걸때는 HAVING을 쓴다. 

 

group by 와 having은 그냥 세트라고 보면된다.

 

아래는 동물의 이름이 같은 것이 2마리 이상일 경우에 이름과 마리수를 출력하는 것이다. 

 

 

12) 새로운 필드를 정의하고 그 필드로 group by 하기

 

만약에 그룹별로 묶고 싶은데

 

기존에 있는 필드 이름이 아니라 새로운 필드를 정의하고 거기에 맞는 그룹별로 묶을 수 있을 까??

 

프로그래머스 문제를 한번 보자.

 

 

 

datetime 을 출력해보면 아래처럼 생겼는 데, 여기서 시간만 가져와야한다.

 

String 관련해서 직접 구현해야 할 것 같지만, hour 라고 이미 제공하는 함수가 있다.

 

이제 datetime에서 시간을 가져오는 법을 알았으니 hour 라는 필드이름을 하나 만들어야 한다.

그리고 그 필드를 기준으로 group by 해야한다.

 

지금까지 생각한 쿼리는 아래와 같다.

 

select hour(datetime) as 'HOUR'
{어쩌고저쩌고}
group by hour(datetime)

 

이제 그룹별로 몇개인지 카운트 해주면된다.

select hour(datetime) as 'HOUR',count(datetime)
from animal_outs
group by hour(datetime)

아래는 위 쿼리의 결과

문제에서 hour 는 9 부터 19까지 정렬 되어있다. 이 부분도 추가해주자.

 

select hour(datetime) as 'HOUR',count(datetime)
from animal_outs
where hour(datetime) >=9 
and hour(datetime) <20
group by HOUR
order by HOUR

 

 

사실 이 부분은 group by 를 쓰지 않고 풀 수 있다.

 

바로 set 을 활용하는 것인데 이는 변수를 선언하는 것이다. 푸는 방법은 아래와 같다.

 

 

위 변수를 활용한 것이 입양 시각 구하기 (2) 번 문제다.

 

답은 아래와 같으니 문제와 함께 참고하자.

 

set @hours = -1; 

select  (@hours := @hours +1) as 'hour',
(select count(*) from animal_outs where hour(datetime) = @hours ) as 'count'
from animal_outs
where @hours < 23

 

 

13) NULL 을 다른 것으로 바꿔 보여주고 싶다면?

 

SELECT ANIMAL_TYPE,ifnull(NAME,'No name'),SEX_UPON_INTAKE
FROM animal_ins

위 코드는 동물의 이름이 없다면(NULL) No name으로 보여주는 코드이다.

 

 

14) 두 개의 테이블이 있어야만 원하는 정보를 얻을 수 있다면? JOIN !!

 

사실 가장 중요한 파트다.

 

조인은 크게 inner 조인과 outer 조인이 있는데 주로 outer 조인을 사용한다.

 

outer 조인은 조인연산 후에 매칭되지 않은 행도 유지 시켜주는 것을 말한다.

 

예를 들어 다음과 같은 코드가 있다고 하자.

 

select *
from set1 as s1 
left outer join set2 as s2
on s1.id = s2.id

set1 과 set2 를 조인하는데 set2 는 set1의 id를 외래키로 참조하고 있다고 가정하자.  set1 에는 존재하는 id 가 set2에는 존재하지 않을 수 있다. 그렇다면 이를 남겨둘것인가 ? 아니면 삭제할 것인가? 

 

남겨두면 outer 삭제하면 inner 이다. 즉 outer 는 본래의 원소들을 보존한다. 따라서 각종 조건문을 넣어서 판단하기 좋다.

 

프로그래머스 문제 몇개를 풀어보자.

 

 

 

먼저 2개의 테이블을 외래키를 기준으로 조인하면 테이블이 나올것이다. 여기서 조인의 대상이 된 테이블의 아이디가 null 인것만 찾아주면 된다.

정답 코드는 아래와 같다.

select ANIMAL_ID,NAME from
(
    select o.animal_id as 'ANIMAL_ID',o.name as 'NAME',i.animal_id as 'i_id'
    from animal_outs as o 
    left join animal_ins as i 
    on i.animal_id = o.animal_id 
) sq1 
where i_id is NULL

 

다른 비슷한 문제들과 정답코드를 살펴보자. 사실 다 비슷해서 조금만 생각하면 금방 풀 수 있다.

 

 

 

select i.name as 'NAME',i.datetime as 'DATETIME' 
from animal_ins as i
left outer join animal_outs as o
on i.animal_id = o.animal_id
where o.animal_id is null
order by i.datetime asc
limit 3

 

 

 

select i.animal_id as 'ANIMAL_ID',i.animal_type as 'ANIMAL_TYPE',i.name as 'NAME'
from animal_ins as i
left outer join animal_outs as o
on i.animal_id = o.animal_id
where i.sex_upon_intake != o.sex_upon_outcome

 

 

 

15) 그외 String 과 date 에 관한 것

 

만약 where 절에

like '%story%' 

 

와 같이  작성한다면 이는 story 가 들어간 모든 필드값들을 찾아준다.

 

즉 %는 모든 문자열에 대해 매칭이 가능하다. 'story%' 라면 story로 반드시 시작하는 모든 필드값을 찾아 줄것이다.

 

그리고 '아니?' 라고 한다면 아니로 시작하고 3글자인 모든 필드 값을 찾아 줄 것이다.

 

만약 date 관련해서 특정 포맷을 검색하고 싶다면

 

date_format() 에 관해 검색해보자.