문제

주어진 데이터

아래 테이블을 보자

순서대로 test_a, test_b, test_c 이다

우리가 원하는 것은 아래와 같은 테이블이다.

구하려는 테이블

여기서 price_sum이란 각 이름별로 가격 종류의 합이다. (a1이 가지고 있는 모든 가격 종류의 합)
보면, a1은 3000, 6000 두개의 가격 종류를 가지고 있으므로, 9000이 되는 것이다.

이를 어떻게 구할 것인가?

내가 생각했던 오답

딱 처음 봤을 때는 간단해 보인다. 그냥 싹 다 조인하고, 그룹바이 하면 되는거 아니야?

-- name , 총 가격, 총 갯수, 총 매출
SELECT
    ta.name,
    SUM(tb.price) as price_sum,
    SUM(tc.amount) as amount_sum,
    SUM(tb.price * tc.amount) as total
FROM
    test_a ta
JOIN test_b tb on ta.id = tb.a_id
JOIN test_c tc on tb.id = tc.b_id
GROUP BY ta.name;

결과가 틀렸다.

• 우선 a1의 가격 합이 12000원이나 나왔다.
• a3는 표시되지도 않았다.

왜 이런 결과가 나왔을까?

SELECT
    *
FROM
    test_a ta
JOIN test_b tb on ta.id = tb.a_id
JOIN test_c tc on tb.id = tc.b_id;

위 쿼리를 실행시켜보면

아래와 같은 결과가 나온다. 즉

이러한 구조가 문제이다. 그니까 6000이 두개가 만들어진 것이다.

join을 하게 되면, 만들어 질 수 있는 모든 경우의 수를 만들어낸다고 생각하면 된다.

나만의 연상팁

• 1 : n 관계에서 조인을 수행하면, 1쪽이 n번 반복된다.

즉 이 상황에서 name으로 그룹바이를 수행하면, 가격 정보가 중복되서 조회가 되는 것이다.

a1 = 3000 + 3000 + 6000

그렇다면,,, 어떻게 한단 말인가

올바른 풀이

연상 과정

우선 이 문제의 각 만들어진 열(계산된 열)이 어떤 테이블과 어떤테이블의 관계인지, 무엇으로 그룹바이 되었는지 구분지어서 생각해야한다.

우선 문제를 다시 보면,

원래 있던 컬럼

• name

만들어진 컬럼

• price_sum
  • a1_name 이 같은 price의 합
  • a_test와 b_test 컬럼을 조인 한 후, 그룹바이한 컬럼
• amount_sum
  • a1_name 이 같은 amount의 합
  • a_test와 c_test 컬럼을 조인 한 후, 그룹바이한 컬럼
• total_sum
  • a1_name이 같은 b_test.price * c_test.amount 의 합
  • a_test, b_test, c_test 를 모두 조인한 후 name으로 그룹바이 한 컬럼

그렇다 사실 이렇게 뭐를 원하는지 파악하는게 좀 어려웠다.

그 다음 든 궁금증은 이것들은 전부 각각 테이블이잖아 이걸 어떻게 합치지였다.
그냥 막무가내로 겹치면, 데카르트 곱이 발생할테니 말이다.

연상 팁
join 을 한다고 해서 다 같은 join이 아니다.
1 : N 관계에서 조인을 하면, 1 쪽이 여러번 반복되지만
1 : 1 관계에서 조인을 하면, 양쪽 테이블이 합쳐지는 효과가 있다.

1 : 1 관계에 대해 간단하게 생각하면, 조인에 사용되는 컬럼(컬럼들) 이 각 테이블에서 유니크 한 것을 의미한다.

이것을 거꾸로 생각하면, 뭔가 다른 두개의 테이블을 가로로 이어 붙이고 싶다?
양쪽 테이블에서 유니크한, 다르게 말하면, 1대1 매핑되는 컬럼으로 조인을 하면 된다.
유니크한 컬럼이 나타나는 주요 특징은, PK, unique, group by이다.

그러면 다시 문제로 돌아가서,
각각의 계산된 컬럼은 구하려는게 다르다. 그러므로 각각을 서브쿼리로 구하고, name을 기준으로 묶으면 되겠다는 생각을 하면 된다.

WITH price_per_a AS (
    SELECT
        a_id,
        SUM(price) AS total_price
    FROM test_b
    GROUP BY a_id
), amount_per_a AS (
    SELECT
        a_id,
        sum(amount) AS total_amount
    FROM test_b b
    JOIN test_c c on b.id = c.b_id
    GROUP BY b.a_id
), sales_sum AS (
    SELECT
        a_id,
        sum(price * amount) AS total_sales
    FROM test_b b2
    JOIN test_c c2 on b2.id = c2.b_id
    GROUP BY a_id
)
SELECT
    name,
    total_price,
    total_amount,
    total_sales
FROM test_a a
LEFT JOIN price_per_a ppa ON ppa.a_id = a.id
LEFT JOIN amount_per_a apa ON apa.a_id = a.id
LEFT JOIN sales_sum ss ON ss.a_id = a.id;

이 쿼리문을 보면 알겠지만, 각각 원하는 정보를 구한뒤 a.id를 기준으로 묶어줬다.
서브쿼리에서는 모두 a.id를 기준으로 그룹바이를 하고 있으므로, 1대1 매핑이다.

그러므로 테이블이 가로로 연결되는 것 같은 효과를 볼 수 있다.

몇가지가 더 부족하다

1. amount와 total_sales의 서브쿼리를 연결할 수 있다.
   • 그 이유는 amount가 1 : N 중에 1 쪽에 있기 때문이기 때문에, 헷갈린다면 구지 안해도 될거 같다.
2. Null이 표시된다.

바로 이러한 Null일 때 처리하는 방식중에 하나가 COALESCE 함수이다. 이것은 인자로 준 순서대로 첫번째 Null이 아닌 것을 반환한다.

...
SELECT
    name,
    COALESCE(total_price, 0) AS total_price, -- 이부분이 바뀌었다.
    COALESCE(total_amount, 0) AS total_amount,
    COALESCE(total_sales, 0) AS total_sales
FROM test_a a
LEFT JOIN price_per_a ppa ON ppa.a_id = a.id
LEFT JOIN amount_per_a apa ON apa.a_id = a.id
LEFT JOIN sales_sum ss ON ss.a_id = a.id;

바로 저렇게 사용하면, 만약 null일 경우에는 0으로 표시해준다.

참고로 아래 팁도 남긴다.

SELECT
    NULL::int / 3 AS null_div_3,
    3 / NULL::int AS three_div_null,
    NULL::int / NULL::int AS null_div_null,
    NULL::int * 3 AS null_mul_three;

오늘의 연상 팁 정리

1. 계산된 컬럼 각각 생각하자.
2. 계산된 컬럼이 각자 같은 테이블 조합에서 집계하지 않는다면 서브쿼리로 분리하자
3. n 대 1 관계에서 Join이 발생하면, 1 쪽이 n 번 반복된다.
4. null과 숫자 연산을 하면 null이 발생한다.
5. null 일 때 다른 값을 표시하고 싶다면, coalesce 를 사용하자