SQL 테이블 열연결 응용

테이블정보

ramen, drink, canned_food는 상품정보 테이블,
rating은 상품평점정보 테이블.

# ramen
 id |    name    | quantity | is_spicy | shelf_life
----+------------+----------+----------+------------
  1 | 진라멘     |        2 | f        |         90
  2 | 고추짜장면 |       15 | f        |         30
  3 | 리얼짬뽕   |       47 | t        |         40
  4 | 추풍라면   |       18 | f        |         90
  5 | 겨울이라면 |       50 | t        |         60
(5 rows)

# drink
 id |     name     | quantity | shelf_life | volume
----+--------------+----------+------------+--------
  1 | 에너지드링크 |       36 |         30 |    300
  2 | 오렌지주스   |       38 |         25 |   1500
  3 | 무안단물     |       41 |         35 |    600
  4 | 순진맥주     |       47 |         60 |    900
  5 | 더맛         |        3 |         35 |   1000
(5 rows)

# canned_food
 id |   name    | quantity | weight
----+-----------+----------+--------
  1 | 참치      |       14 |    350
  2 | 고추참치  |        5 |    200
  3 | 연어      |        3 |    400
  4 | 곰과 연어 |       14 |    400
  5 | 삼치      |       50 |    450
(5 rows)

# rating
 id |  item_type  | user_id | rating | item_id
----+-------------+---------+--------+---------
  1 | ramen       |       2 |      1 |       2
  2 | drink       |      10 |      4 |       5
  3 | ramen       |       9 |      3 |       5
  4 | drink       |       5 |      3 |       5
  5 | ramen       |       6 |      4 |       5
  6 | ramen       |       2 |      4 |       1
  7 | canned_food |       8 |      4 |       3
  8 | ramen       |       8 |      3 |       2
  9 | canned_food |       4 |      1 |       4
 10 | canned_food |      10 |      2 |       3
 11 | ramen       |      10 |      1 |       2
 12 | canned_food |       9 |      3 |       5
 13 | ramen       |       7 |      2 |       2
 14 | canned_food |       5 |      1 |       3
 15 | canned_food |       4 |      4 |       3
 16 | ramen       |      10 |      1 |       2
 17 | drink       |       7 |      1 |       1
 18 | ramen       |       7 |      1 |       5
 19 | ramen       |      10 |      1 |       2
 20 | ramen       |       4 |      1 |       4
(20 rows)

---

모든 상품의 상품 이름, 상품 종류, 평균 평점 출력.

rating테이블 사용하여 상품타입, 상품id 별 평균평점 그룹화

select item_type, item_id,round(avg(rating),4) as rating
from rating
group by 1,2;
  item_type  | item_id | rating
-------------+---------+--------
 ramen       |       2 | 1.5000
 ramen       |       1 | 4.0000
 canned_food |       3 | 2.7500
 ramen       |       5 | 2.6667
 drink       |       5 | 3.5000
 drink       |       1 | 1.0000
 ramen       |       4 | 1.0000
 canned_food |       4 | 1.0000
 canned_food |       5 | 3.0000
(9 rows)

3개의 상품 테이블 결합

(select name,id as item_id, 'drink' as item_type from drink)
union all
(select name,id as item_id, 'ramen' as item_type from ramen)
union all
(select name,id as item_id, 'canned_food' as item_type from canned_food);
     name     | item_id |  item_type
--------------+---------+-------------
 에너지드링크 |       1 | drink
 오렌지주스   |       2 | drink
 무안단물     |       3 | drink
 순진맥주     |       4 | drink
 더맛         |       5 | drink
 진라멘       |       1 | ramen
 고추짜장면   |       2 | ramen
 리얼짬뽕     |       3 | ramen
 추풍라면     |       4 | ramen
 겨울이라면   |       5 | ramen
 참치         |       1 | canned_food
 고추참치     |       2 | canned_food
 연어         |       3 | canned_food
 곰과 연어    |       4 | canned_food
 삼치         |       5 | canned_food
(15 rows)

두 테이블 결합

평점이 남지 않은 상품이 누락되지 않도록 RIGHT JOIN 사용 + coalesce함수 사용하여 0점처리

select name, item_type, round(coalesce(rating,0),3) as rating
from
(
    select item_type, item_id, avg(rating) as rating
    from rating
    group by item_type, item_id
) rating
right join
(
    (select name,id as item_id, 'drink' as item_type from drink)
    union all
    (select name,id as item_id, 'ramen' as item_type from ramen)
    union all
    (select name,id as item_id, 'canned_food' as item_type from canned_food)
) items
using (item_id, item_type);
     name     |  item_type  | rating
--------------+-------------+--------
 에너지드링크 | drink       |  1.000
 오렌지주스   | drink       |  0.000
 무안단물     | drink       |  0.000
 순진맥주     | drink       |  0.000
 더맛         | drink       |  3.500
 진라멘       | ramen       |  4.000
 고추짜장면   | ramen       |  1.500
 리얼짬뽕     | ramen       |  0.000
 추풍라면     | ramen       |  1.000
 겨울이라면   | ramen       |  2.667
 참치         | canned_food |  0.000
 고추참치     | canned_food |  0.000
 연어         | canned_food |  2.750
 곰과 연어    | canned_food |  1.000
 삼치         | canned_food |  3.000
(15 rows)

---

매운 라면, 맵지 않은 라면의 평균을 구하고
매운 라면과 맵지 않은 라면을 교차 조인하여 각 평균의 합산을 출력

rating테이블 사용하여 라면 평균평점 그룹화

select round(avg(rating),3) as avg_rating, item_type, item_id
from rating
where item_type = 'ramen'
group by 2,3;
 avg_rating | item_type | item_id
------------+-----------+---------
      1.500 | ramen     |       2
      4.000 | ramen     |       1
      2.667 | ramen     |       5
      1.000 | ramen     |       4
(4 rows)

위 테이블과 ramen테이블 조인

평점 없는 라면 출력 위해 RIGHT JOIN + coalesce함수 사용하여 0점처리

select ramen.name, ramen.id as item_id, ramen.is_spicy,
    round(coalesce(avg_rating,0),3) as avg_rating
from (
    select avg(rating) as avg_rating, item_type, item_id
    from rating
    where item_type = 'ramen'
    group by 2,3
) r_rating right join ramen
on ramen.id = r_rating.item_id;
    name    | item_id | is_spicy | avg_rating
------------+---------+----------+------------
 진라멘     |       1 | f        |      4.000
 고추짜장면 |       2 | f        |      1.500
 리얼짬뽕   |       3 | t        |      0.000
 추풍라면   |       4 | f        |      1.000
 겨울이라면 |       5 | t        |      2.667
(5 rows)

위 테이블을 내부조인

select not_spicy.name as not_spicy_name, spicy.name as spicy_name,
        (not_spicy.avg_rating + spicy.avg_rating) as avg_sum_rating
from (
    select ramen.name, ramen.id as item_id, ramen.is_spicy,
        round(coalesce(avg_rating,0),3) as avg_rating
    from (
        select avg(rating) as avg_rating, item_type, item_id
        from rating
        where item_type = 'ramen'
        group by 2,3
    ) r_rating right join ramen on ramen.id = r_rating.item_id
    where ramen.is_spicy = false
) not_spicy, (
    select ramen.name, ramen.id as item_id, ramen.is_spicy,
        round(coalesce(avg_rating,0),3) as avg_rating
    from (
        select avg(rating) as avg_rating, item_type, item_id
        from rating
        where item_type = 'ramen'
        group by 2,3
    ) r_rating right join ramen on ramen.id = r_rating.item_id
    where ramen.is_spicy = true
) spicy;
 not_spicy_name | spicy_name | avg_sum_rating
----------------+------------+----------------
 진라멘         | 리얼짬뽕   |          4.000
 진라멘         | 겨울이라면 |          6.667
 고추짜장면     | 리얼짬뽕   |          1.500
 고추짜장면     | 겨울이라면 |          4.167
 추풍라면       | 리얼짬뽕   |          1.000
 추풍라면       | 겨울이라면 |          3.667
(6 rows)