SQL Review(5th 서브쿼리, with)

1. 서브쿼리

 

1-1.  where 절에서 사용되는 서브쿼리

 

주문수가 3건 이상인 유저의 id와 이름 조회

select
id,
first_name,
last_name
from `thelook_ecommerce.users`  # 우선 user 테이블에서 아이디와 이름 조회
                                # 주문 수는 order 테이블에서 order_id를 count 해야하므로 서브쿼리로 불러오기
-----------------------------
where id in (
  select user_id 
  from `thelook_ecommerce.orders`
  group by user_id
  having count(order_id) >= 3   # 주문수 3 이상
)
order by id

예제들을 보면 

group by 로 그룹화해주지 않아서 having은 사용하지 못하고,

where절을 쓰기위해선 다른 테이블에서 가져와야 하기 때문에 서브쿼리를 쓰는 것 같다

 

1-2. FROM 절에서 사용되는 서브쿼리

 

select id,
  a.first_name,
  a.last_name,
  b.order_count as order_count
from `thelook_ecommerce.users` a
left join (
    select user_id, count(order_id) as order_count 
    from `thelook_ecommerce.orders`
    group by user_id
  ) b on a.id = b.user_id
order by a.id
limit 10;

유저의 id와 이름 그리고 주문수를 조회

users테이블의 정보와 user별 주문수를 조회하는 서브쿼리를 left join을 이용하여 연결해서 유저의 주문수를 조회

 

 

1-3. SELECT 절에서 사용되는 서브쿼리

select id,
  first_name,
  last_name,
  (select count(order_id) from `thelook_ecommerce.orders` where user_id = a.id) as order_count
from `thelook_ecommerce.users` a
order by a.id
limit 10;

user 정보를 조회

해당 유저의 주문수(order_count)를 조회하기 위해 select 절에서 서브쿼리를 사용

 

같은 내용을 각각 where, from select 문에서 사용한 것 같은데 where 가 제일 이해도 되고 잘 쓸 수 있을 것 같다.

 

 

2. WITH (Common Table Expressions)

with 절은 쿼리 내에서 임시 결과를 정의하고 사용

주요 사용 목적은 복잡한 추출 과정을 분할하여 단계적으로 처리하면서 전체 데이터 추출과정을 단순화시키는 것 입니다.

(가상의 임시 테이블 만든다고 생각)

 

사용법

WITH CTE(유사 테이블)명 AS ( 쿼리 표현식 )

WITH user_data AS (select id from `thelook_ecommerce.users`)
select * from user_data

1. user_data CTE(유사 테이블)을 정의합니다. 내용은 users의 id값을 조회하는 서브쿼리
2. user_data 로 부터 데이터를 조회 합니다.

2.1 유저 아이디별 주문수 조회

 

1) user_order_counts 라는 유사 테이블을 정의. 내용은 유저아이디별 주문수 데이터를 조회하는 서브쿼리

# 서브쿼리 생성
with user_order_count as (
  select user_id,
  count(order_id) as order_count
  from `thelook_ecommerce.orders`
  group by user_id
)

2) CTE( user_order_counts ) 로 부터 데이터를 조회

WITH user_order_counts AS (
  select user_id, 
  count(order_id) as order_count 
  from `thelook_ecommerce.orders` 
  group by user_id
) 
select * from user_order_counts
order by order_count DESC

중간에 이런 오류 났었다가 처음부터 다시 쳐보니까 안나네 왜지...

 

 

2.2 회원수가 4000명 이상인 국가명과 국가의 회원수

with user_counts as(
  select country,
  count(id) as user_count
  from `thelook_ecommerce.users`
  group by country
  having count(id) >= 4000
)
select * from user_counts

서브쿼리 없이도 똑같이 값이 나오긴 하는데..흠

 

2.3 브라질과 일본의 유저 아이디 목록 조회

with user_id_brasil as (
  select 
  id,
  country
  from `thelook_ecommerce.users`
  where country = 'Brasil'
  limit 10
),
user_id_japan as(
  select 
  id,
  country
  from `thelook_ecommerce.users`
  where country = 'Japan'
  limit 10
)
select id,country from user_id_brasil
union all
select id,country from user_id_japan

비슷한 내용의 두가지 서브쿼리 만들때는 따로 만들지 말고 한 서브쿼리 내에 만들면 된다.

 

 

 

 

 

 

 

 

 

 

★union all 사용시 괄호

제가 파악한 부분으로는 union all 안에 order by 또는 limit을 사용하면 에러가 발생하는 것으로 확인하였어요.

 

 

+ 자체 연습문제

SQL 연습문제 10-2

-- thelook_ecommerce 데이터 세트 - 회원(users) 테이블과 주문(orders) 테이블에서 
-- 연령대별(user_count) 회원수와 주문횟수합계(order_count)를 조회하세요.

-- 조회 항목

-- - 연령대 (age_group)
--     - 예 : 10대, 20대, 30대, 40대 ~~ 70대
--     - 참고 함수 trunc : 소수점 버리기
--         - select trunc(78, -1) || '대'  -->  70대
--         - select trunc(32, -1) || '대'  --> 30대
-- - 회원수(user_count)
-- - 주문횟수합계 (order_count)

-- 정렬순서

-- - 연령대 오름차순

select 
  g1.age_group,
  g1.user_count,
  g2.order_count
from (
  select 
    trunc(age, -1) || '대' as age_group,
    count(id) as user_count
  from `thelook_ecommerce.users`
  group by age_group
) g1
join (
  select 
    trunc(t2.age, -1) || '대' as age_group,
    count(t1.order_id) as order_count
  from `thelook_ecommerce.orders` t1
  left join `thelook_ecommerce.users` t2 on t1.user_id = t2.id
  group by age_group
) g2 on g1.age_group = g2.age_group
order by age_group;


# 다른 풀이
-- SELECT
--   TRUNC(age, -1) || '대' as age_group,
--   COUNT(age) as user_count,
--   COUNT(order_id) as order_count
-- FROM `thelook_ecommerce.users` a
-- LEFT JOIN `thelook_ecommerce.orders` b ON a.id = b.user_id
-- GROUP BY age_group
-- ORDER BY age_group;

 

 

SQL 연습문제 10-4

-- weniv 데이터 세트에서 모니터를 구입한 회원(user)의 이름(name)과 우편번호(postal_code)를 서브쿼리만 이용하여 조회하시오. (join을 사용하지 않습니다.)
select name, postal_code
from `weniv.weniv_user` 
where id in (
  select user_id from `weniv.weniv_order`
  where product_id in (
    select id from `weniv.weniv_product` 
    where name = 'monitor'
  )
)