12/22 with 및 union all 활용, length, 셀프조인

sql 카타

65. 우유와 요거트가 담긴 장바구니

with, union all 활용

데이터 분석 팀에서는 우유(Milk)와 요거트(Yogurt)를 동시에 구입한 장바구니가 있는지 알아보려 합니다. 우유와 요거트를 동시에 구입한 장바구니의 아이디를 조회하는 SQL 문을 작성해주세요. 이때 결과는 장바구니의 아이디 순으로 나와야 합니다.

 

1)오답 : 사실상 무답

(1)작성 이유 : 문제를 보자마자 group by와 having을 적당히 섞어서 쓰면 되겠네!!싶어서 코드 작성을 시작했는데 시작과 동시에 막혔다. 오답이랄 것도 없이 무답이었던 셈.

select cart_id
from cart_products
group by 1
having ()

어어...어..?

 

어떻게 풀어야하는 지 찾아보니 기본적으로 with 함수를 써야하는 문제였고, 거기에다가 추가로 union all을 활용해야 하는 것이었다. 그래서 답변을 보면서 차근차근 공부해서 대충 이해했다 싶어 코드를 작성했는데 또 틀렸다고 나왔다. 자세히 봐보니 distinct를 써주지 않았던 셈

with temp as (select cart_id, name
from cart_products
where name = 'yogurt'
union all
select cart_id, name
from cart_products
where name = 'milk')

select cart_id
from temp
group by cart_id
having count(name)>=2

(2)오답 이유

-with 및 union all 함수에 아직 익숙하지 않음

-distinct의 중요성 간과(사용하는 것이 아직 몸에 배지 않음)

 

2)정답

with temp as                  <----- 4) 3)의 임시테이블 생성하고 temp라 이름 붙이기
(select distinct(cart_id)     <----- 1)'yogurt'만 있는 'cart_id' 출력
from cart_products
where name = 'yogurt'
union all                     <----- 3)milk만 있는 cart_id와 yogurt만 있는 cart_id 합치기(distinct 중요!)
select distinct(cart_id)      <----- 2)'milk'만 있는 'cart_id' 출력
from cart_products 
where name = 'milk')

select cart_id                
from temp
group by cart_id              <----- 5)cart_id로 그룹화
having count(*)>=2            <----- 6)cart_id 두 번 이상 나온것들만 출력(distinct가 중요한 이유)

코드블럭에 작성했듯 distinct가 중요한 이유는 milk든 yogurt든 테이블에서 이를 가진 cart_id가 중복 출력되고, 'having count(*)>2'부분에서 이들 중복된 값이 그대로 나올 수 있기 때문이다. 즉, milk만 두 번 담아서 두 번 조회된 cart_id가 출력될 수 있으니 distinct로 한 번만 출력되게 해야한다는 것이다.

 

with와 union all은 아직 익숙하지 않다보니 문제 구조가 생각나지 않았을 순 있지만, distinct를 간과한 것은 반성해야할 부분인듯ㅠ

 

덧붙여 전에 작성했지만 간단하게 짚고 넘어가면,

with : 가상의 테이블을 임시로 생성하는 함수. 

union all : 두 테이블을 합치는 함수

 

참고 : 다양한 방식의 풀이들

https://school.programmers.co.kr/questions/52835

프로그래머스

 

 

81. Invalid Tweets

length 함수

 

글자 수 15개가 넘어가면 무효한 트윗으로 판정하는 코드를 작성하는 문제. 어려울 것은 없었으나 length를 처음 써봐서 기록.

select tweet_id
from  tweets
where length(content) >=15

 

 

85. Rising Temperature

SELF JOIN, ADDDATE

테이블 상 직전 날보다 기온이 오른 날을 고르는 문제

 

1)오답 : 사실상 무답

select (id+1) 
from weather
where temperature > (select id,
                             temperature
                     from weather)

id가 integer인 것을 확인하고 어떻게 안 되나 싶었지만...될리가 없었다. 출력자체가 되지 않았고, 테이블 출력을 하려면 칼럼 하나라도 입력해야한다는 에러메시지가 떴다. 오답 이유를 쓸 것 조차 없는 사실상의 무답...

 

select w1.id
from weather w1 inner join weather w2 using(id)
where datediff(w1.recordDate, w2.recordDate)=1 and w1.temperature>w2.temperature

대충 원리를 파악했다 생각하고 호기롭게 입력했으나 도중에 막히기 시작했고, 뭐라도 해보자 해서 써본 답.

결과는 아무 값도 출력이 안 되는 걸로 나왔다.

 

 

2)정답

#DATEDIFF 활용
select w1.id
from weather w1 inner join weather w2 on datediff(w1.recordDate, w2.recordDate)=1
where w1.temperature>w2.temperature


#ADDDATE 활용
select w1.id
from weather w1 inner join weather w2 on adddate(w2.recordDate, 1)=w1.recordDate
where w1.temperature>w2.temperature

사실 이런 식으로 작성하는 것을 잠깐 생각했다가, on에다가는 무조건 칼럼명만 나와야하는 걸로 생각했어서 작성을 안 했는데 이게 정답이었다.

self join을 할 때 이전/이후 기록과 비교하는 문제에서 유용하게 사용될 듯!

 

3)정답 설명

1)셀프 조인 사용. 하나의 weather 테이블을 복붙한 것으로 생각해서 각각 w1, w2로 명명하고 join으로 병합.

2)w1.recordDate의 시점이 w2.recordDate보다 하루 늦는 곳에서 합쳐준다.

3)출력 조건은 w1이 w2의 온도보다 높은 경우!

 

 

 

86. 1661. Average Time of Process per Machine

셀프조인2 

 

1)오답

select a.machine_id,
       round(avg(b.timestamp-a.timestamp), 3) processing_time
from Activity a join Activity b using(machine_id)
where a.activity_type='start' = b.activity_type='end'
group by 1

 

작성 이유 : start와 end를 맞춰줘야 하지 않을까 싶어서  두 곳에 등식을 붙였다.

오답 이유 : 등식 자체가 성립하기 어려움. 결과는 모든 평균이 0이 나왔다

 

2)정답

select a.machine_id,
       round(avg(b.timestamp-a.timestamp), 3) processing_time
from Activity a join Activity b using(machine_id)
where a.activity_type='start' and b.activity_type='end'
group by 1

-설명 : 셀프조인해서 만든 a.end 시간에서 b.start를 빼주기만 하면 되는 거니 각 테이블의 activity_type 칼럼의 조건을 맞춰주기만 하면 되는 문제였다.