2024-03-13 오늘 배운 것

스파르타에서 지급받은 SQL 강의를 복습하던 중 의문이 생긴 부분을 정리해 보았다

 

일단 강의 노트에 적혀 있는 문제와 그 문제 풀이 과정이 동영상으로 제공되는 강의와 약간의 차이가 있어 먼저 정리가 필요한 것 같다

 

1. 강의 노트 속 문제와 문제 풀이

 

[문제] 음식 타입별로 모든 음식 점에서의 전체 주문 건 수의 합을 구하고, 각 음식점의 주문건이 해당 음식 타입에서의 주문 건이 낮은 순으로 정렬했을 때의 누적합을 구하기

 

풀이는 음식 타입별, 음식점별 주문 건수을 먼저 구해 Subquerry를 먼저 만든 뒤 음식 타입 별 주문 건수 합, 누적합을 구하는 과정을 보여준다

 

1 - 1. 음식 타입별, 음식점별 주문 건수 구하기

select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2

 

select, from, count, group by 등을 이용하여 음식 타입별, 음식점별(음식점 이름별)로 주문 건수를 쉽게 구할 수 있다

 

결과

 

 

1 - 2. 음식 타입 별 전체 주문 건수 합과 누적합 구하기

window function을 이용하여 음식 타입 별 전체 주문 건수 합과 누적 합 구할 수 있다

window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

 

음식 타입별 전체 주문 건수(아래 결과 표에서는 미국음식(American) 타입의 전체 주문 건수 584건을 볼 수 있다)를 구하고 해당 음식 타입별 주문 건수가 낮은 음식점 순서대로 누적합을 구한 것이다

 

select cuisine_type,
       restaurant_name,
       order_count,
       sum(order_count) over (partition by cuisine_type) sum_cuisine_type,
       sum(order_count) over (partition by cuisine_type order by order_count, restaurant_name) cumulative_sum
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a

 

1 - 3. 결과

 

2. 강의 영상 속 문제와 문제 풀이

같은 문제에 음식 타입별로 오름차순으로 정렬한다는 조건이 추가로 주어진다. 위의 풀이와 차이가 있는 부분도 있다(2-2. 참고)

 

[문제] 음식 타입별로 모든 음식 점에서의 전체 주문 건 수의 합을 구하고, 음식 타입별로 오름차순으로 정렬한 뒤 각 음식점의 주문건이 해당 음식 타입에서의 주문 건이 낮은 순으로 정렬했을 때의 누적합을 구하기

2 - 1. 음식 타입별, 음식점별 주문 건수 구하기

위와 완전히 동일하므로 생략한다

2 - 2. 음식 타입 별 전체 주문 건수 합과 누적합 구하기

위와 완전히 동일하지만 아래와 같이 window function안 order by절에서 restaurant_name이 빠졌다

select cuisine_type,
       restaurant_name,
       order_count,
       sum(order_count) over (partition by cuisine_type) sum_cuisine_type,
       sum(order_count) over (partition by cuisine_type order by order_count) cumulative_sum
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a

 

2 - 3. 음식 타입별로, 주문 건수 순으로 오름차순으로 정렬하기

order by 절로 음식 타입별, 주문 건수 순으로 정렬하는 것이 추가되었다

select cuisine_type,
       restaurant_name,
       order_count,
       sum(order_count) over (partition by cuisine_type) sum_cuisine_type,
       sum(order_count) over (partition by cuisine_type order by order_count) cumulative_sum
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a
order by cuisine_type, order_count

 

2 - 4. 결과

 

3. 차이점에 대해 생각해보기

3-1. 누적합을 구하는 과정에서의 window function 사용의 차이

두 풀이를 보면

sum(order_count) over (partition by cuisine_type order by order_count, restaurant_name)
sum(order_count) over (partition by cuisine_type order by order_count)

 

누적합을 구할 때 window function 안 order by 절에 restaurant_name을 추가해 줬느냐 아니냐의 차이가 있다

 

이 부분에 대해서는 강의 영상에서 어느정도 설명이 되어있다. 2-4의 결과 테이블을 보면 누적합이 주문 건수가 같은 음식점들에 대해서는 모두 같게 나오는 것을 볼 수 있다. 이런 결과가 나오게 된 이유는 누적합을 구하는 과정에서 order by에 order_count(주문 건수)를 넣었기 때문에 주문 건수에 대해 오름차순으로 정렬되어 누적합이 계산되었고, 이 때문에 주문 건수가 같은 음식 점들에 대해서는 같은 누적합이 나온 것이다.(주문 건수가 1인 음식점들의 누적합은 9, 2인 음식점들은 13, 3인 음식점들은 37인 것을 볼 수 있다)

 

하지만 강의에는 강의 노트에 있는 부분에 대해서 설명이 되어있지 않아 직접 찾아보고 생각해봐야 했다

 

강의 노트에 있는 풀이에는 order by 절에 restaurant_name이 추가되어있고 이 때문인지 주문 건수가 같은 음식점들 모두 누적합이 같게 나오는 것이 아니고, 주문 건수가 같다고 하더라도 음식점별로 하나 하나 합이 누적되어 결과가 출력되어 나오게된다.(1-3 결과 참조) 왜 이렇게 되는 것인지 선뜻 이해가 되지 않아 window function의 사용법과 결과표를 다시 잘 들여다 보게되었다.

window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

 

정렬 기준으로 restaurant_name(음식점 이름)을 추가해 줬다는 건 누적합을 구할 때 주문 건수가 낮은 음식점들 순서대로 그리고 음식점 이름별 오름차순으로 누적합을 구해준다는 뜻이다. 1-3과 2-4의 결과표를 다시 보니 두 표의 음식점 이름들이 똑같이 정렬된 것이 아니라 1-3의 경우는 숫자, 알파벳 순으로 음식점 이름 순으로 정렬되어 있는 것을 볼 수 있다. 음식점 이름은 모두 다르기 때문에 음식점별로 하나하나 누적합이 구해진 것을 볼 수 있다.

 

3-2. order by로 정렬하기

2-3처럼 강의 영상에서는 마지막에 order by로 음식 타입별, 주문 건수 순으로 정렬하는 것이 추가되어 있다. 그런데 1-3과 2-4의 결과 테이블을 보면 알 수 있듯 굳이 마지막에 정렬해주지 않아도 제대로 정렬되어 출력되는 것으로 보인다 (1-3의 경우 음식 타입별로 따로 정렬해주지 않았음에도 American, Chinese, French, Indian... 순으로 오름차순 정렬되어 있다)

 

처음에 나는 1-3의 결과가 음식 타입별 오름차순으로 정렬된 것이 우연이라고 생각했다. 다시 말해 원래 테이블이 음식 타입별 오름차순으로 이미 정렬되어 있어서 나온 결과라고 생각했다. 그래서 1-2의 문제 답에 2-3처럼 마지막에 order by 절로 cuisine_type, order_count를 추가해서 결과를 출력해 봤다

 

select cuisine_type,
       restaurant_name,
       order_count,
       sum(order_count) over (partition by cuisine_type) sum_cuisine_type,
       sum(order_count) over (partition by cuisine_type order by order_count, restaurant_name) cumulative_sum
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a
order by cuisine_type, order_count

 

출력 결과

 

결과를 구해보니 누적합 부분이 오히려 흐트러지게 되었다. order by 절에 cumulative_sum을 추가해주면 간단히 해결될 것 같다

select cuisine_type,
       restaurant_name,
       order_count,
       sum(order_count) over (partition by cuisine_type) sum_cuisine_type,
       sum(order_count) over (partition by cuisine_type order by order_count, restaurant_name) cumulative_sum
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a
order by cuisine_type, order_count, cumulative_sum

 

출력 결과

 

정상적으로 출력되었다. 음식점 개별로 누적합을 구한 뒤 음식 타입별로 내림차순 정렬하는 것까지 끝 마친 것이다. 그럼에도 불구하고 무언가 찝찝함이 남아 이것저것 다시 해보았다

3-3. partition by의 정렬

다시 맨 처음으로 되돌아가 1-1의 Subquerry의 결과를 확인해보니 음식 타입별로 오름차순은 커녕 마구잡이로 섞여있는 것을 알 수 있었다. 그러니까 3-2에서 내가 생각했던 것 처럼 음식타입별로 정렬된 것이 우연이 아니었다는 것이다.

 

window function의 partition by를 이용해 여러가지 시행착오를 거친 결과 내린 결론은 partition by를 이용하면 그 기준에 대해 자동으로 오름차순 정렬을 해준다는 것이다. 위에서는 cuisine_type으로 partition을 해주었기 때문에 American, Chinese, French, Indian... 순으로 알파벳 오름차순으로 정렬되었던 것이다

 

다시 말해 바로 위(3-2 후반부)에서 한 것 처럼 order by를 이용해 오름차순으로 정렬을 다시 한 번 하지 않아도 cuisine_type(음식 타입) 알파벳 오름차순으로 자동으로 정렬이 되는 것이다. (또한 window function을 이용했을 때 order by order_count가 있었기 때문에 주문 건수에 대해서도 오름차순 정렬이 이미 되어있는 상태이기 때문에 주문 건수에 대해서도 다시 한 번 정렬하지 않아도 된다)

 

궁금했던 부분들은 모두 해소되었고 마지막으로 이번에는 전체합, 누적합 계산이 끝난뒤 음식타입별 내림차순으로 정리하는 것을 해보고 끝내보기로 한다

select cuisine_type,
       restaurant_name,
       order_count,
       sum(order_count) over (partition by cuisine_type) sum_cuisine_type,
       sum(order_count) over (partition by cuisine_type order by order_count, restaurant_name) cumulative_sum
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a
order by cuisine_type desc, order_count, cumulative_sum

 

결과

 

정상적으로 결과가 출력된 것을 볼 수 있다