[내일배움캠프] TIL 4일차 25.12.26(금)

작동 순서

FROM -> ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY

 

작성 순서

SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY

 

집계 함수

- 여러 행을 하나의 값으로 요약

- COUNT(개수) , SUM(합계), AVG(평균), MIN(최소), MAX(최대) 등

- GROUP BY와 함께 사용

- NULL 처리 규칙

    - 일반적으로 집계 함수는 NULL 무시

    - COUNT(*)는 "열 값"이 아니라 "행(row)"을 세는 형태

 

COUNT

- COUNT(*)

● 결과 행(row)의 개수(=조건을 만족한 행 수)를 셉니다.

- COUNT(col)

● col이 NULL이 아닌 값의 개수를 셉니다.

- COUNT(DISTINCT col)

● col의 서로 다른(non-NULL) 값의 개수를 셉니다. (=고유값)

 

 

미니 실습 A

문제: order_status='completed'에서 아래를 한 번에 출력하세요.

• 전체 아이템 행 수
• 주문 수(중복 제거)
• 리뷰 개수(NULL 제외)

출력 컬럼명

• completed_item_rows, orders, review_cnt

답)

select
        count(*) as completed_item_rows,
        count(order_item_id) as orders,
        count(review_score) as review_cnt
from basic.order_items
where order_status='completed'

해설)

select
        count(*) as completed_item_rows,
        count(distinct order_id) as orders,
        count(review_score) as review_cnt
from basic.order_items
where order_status='completed'

=> 주문 수를 위해서는 order_item_id가 아닌 order_id 필요. 중복 제거를 위해서는 distinct 필요.

 

GROUP BY

- '같은 값끼리' 행을 묶어서 그룹 단위로 집계 결과를 만드는 문법

- 결과의 "단위(Granularity)"를 바꿉니다.

● GROUP BY product_category -> 카테고리 단위 요약표

● GROUP BY order_date -> 일자 단위 요약표

● GROUP BY order_date, channel -> (일자x체널) 단위 요약표

 

**자주하는 실수

    그룹 기준에 없는 컬럼을 SELECT에 넣기

    - GROUP BY를 쓰는 쿼리에서 SELECT/HAVING/ORDER BY에 나온 컬럼은

      ● 집계 함수로 감싸져 있거나 (SUM, COUNT, MAX, ...)

      ● GROUP BY 컬럼이거나 해야한다.

 

미니 실습 B

basic.order_items 테이블에서 주문 상태가 completed인 건만 대상으로 한다.

그 다음 지역(region)별로 아래 3가지를 계산해 결과를 출력하라.

1. orders : 해당 지역의 서로 다른 주문 수(order_id 기준)
2. customers : 해당 지역의 서로 다른 고객 수(customer_id 기준)
3. net_sales : 각 주문 아이템의 실매출을 합한 값
   • 실매출 = unit_price × quantity × (1 - discount_rate)
   • 지역별로 위 실매출을 모두 더해 net_sales로 출력

마지막으로 결과를 net_sales 내림차순(DESC) 으로 정렬하라.

 

답)

select
        order_id as orders,
        customer_id,
        count(unit_price * quantity * (1 - discount_rate)) as net_sales
from basic.order_items
where
group by
order by net_sales desc;

해설)

select
        region,
        count(distinct order_id) as orders,
        count(distinct customer_id) as customers,
        sum(unit_price * quantity * (1 - discount_rate)) as net_sales
from basic.order_items
where order_status='completed'
group by region
order by net_sales desc;

 

=> 문제에서 언급한 제역별로 결과 출력이 있었기 때문에 SELECT문과 GROUP BY문에 region이 들어가야한다. 주문상태가 completed인 건만 대상이기에 where에 order_status='completed'을 입력해야한다. 또한 수는 count(컬럼), 합한 값은 sum(컬럼)을 입력해야한다. 문제를 읽었을때 어떤 함수, 어떤 문법을 써야하는지 우선 이해할 필요가 있을 것 같다. 문제를 여러 번 풀어보다 보면 익숙해질 것이라고 생각한다.

 

WHERE vs HAVING

- WHERE

• 행(row) 단위 조건
• 집계함수 참조 불가

- HAVING

• 그룹(group) 단위 조건
• 집계 결과에 조건 적용

**MySQL 문서에서는 거의 마지막에 적용되며 최적화가 제한적이므로, WHERE로 가능한 조건은 WHERE에 쓰는 것 권장

 

**자주하는 실수

    집계조건을 WHERE에 쓰기

    - WHERE는 그룹이 만들어지기 전 단계이므로, SUM() 같은 집계함수 조건을 쓸 수 없습니다.

    잘못된 예시)

    SELECT
                product_category,
                SUM(unit_price* quantity) AS gross_sales
     FROM basic.order_items
     WHERE SUM(unit_price* quantity)>=300000
     GROUP BY product_category;

     해설)

     SELECT
                product_category,
                SUM(unit_price* quantity) AS gross_sales
     FROM basic.order_items
     GROUP BY product_category
     having SUM(unit_price* quantity)>=300000;

     => 집계함수는 WHERE에 쓸 수 없으며, HAVING에 쓸 수 있다. WHERE이 없어도 HAVING 쓸 수 있다.

 

미니 실습 C

basic.order_items 테이블에서 주문 상태가 completed인 데이터만 대상으로, 고객 세그먼트(customer_segment)별 리뷰 지표를 집계해 출력하세요.

출력 컬럼은 아래 3개입니다.

• customer_segment
• review_cnt : 해당 세그먼트의 리뷰 점수 개수 (COUNT(review_score) 기준 — 즉 review_score가 NULL이 아닌 것만 카운트)
• avg_review : 해당 세그먼트의 평균 리뷰 점수 (AVG(review_score))

추가 조건:

• review_cnt가 20개 이상인 세그먼트만 결과에 포함 (HAVING 사용)
• 정렬: avg_review 내림차순(DESC)

답)

select
        as customer_segment,
        count(review_score) as review_cnt,
        avg(review_score) as avg_review
from basic.order_items
where order_status='completed'
group by
order by avg_review desc
having count(review_score) >= 20;

해설)

select
        customer_segment,
        count(review_score) as review_cnt,
        avg(review_score) as avg_review
from basic.order_items
where order_status='completed'
group by customer_segment
having count(review_score) >= 20
order by avg_review desc;

=>  고객 세그먼트(customer_segment)는 별명이 아닌 기존 컬럼명이기에 as를 쓸 필요 없다. 앞서 언급한 자주 하는 실수 중 하나인 SELECT/HAVING/ORDER BY에 나온 컬럼은 GROUP BY 컬럼에도 쓰여야 한다. 그러므로 GROUP BY에도 customer_segment를 입력해야한다. 작성 순서도 SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY를 기억해야한다. HAVING은 ORDER BY보다 먼저 작성해야한다.

 

조건부 집계

- GROUP BY로 만든 그룹 안에서, "특정 조건을 만족하는 행만" 세거나 합산하는 패턴

SUM(CASE WHEN 조건 THEN 1 ELSE 0 END) -- 조건 만족 '건수'

 

ROUND

- ROUND(컬럼, 숫자) = 컬럼을 숫자만큼 소숫점 자리를 표시한다.

 

미니 실습 D

delivery_type별로 완료 주문(completed)의 평균 배송일을 계산하세요.

• avg_delivery_days = AVG(delivery_days) (소수점 2자리)
• delivery_days가 NULL인 경우(예: pickup)는 평균에서 어떻게 처리되는지 결과로 확인해보세요.

답)

select
        delivery_type,
        round(avg(delivery_days),2) as avg_delivery_days
        from basic.order_items
        where order_status='complted'
group by delivery_type

해설)

select
         delivery_type,
         count(*) as completed_items,
         round(avg(delivery_days),2) as avg_delivery_days
from basic.order_items
where order_status='completed'
group by delivery_type
order by delivery_type;

=> order_status='completed' 중 completed를 complted로 작성해 실행값 출력 안되는 현상 발생. 오타 주의 필요. 문제에서 언급한 ' delivery_days가 NULL인 경우(예: pickup)는 평균에서 어떻게 처리되는지 결과로 확인'하기 위해서는 평균값만으로는 알 수 없기에 필요하다. 주문 수가 존재함에도 평균이 NULL로 나오는 경우, AVG()가 NULL을 제외하고 계산한다는 것을 결과로 설명하기 위해 COUNT(*) AS completed_items 입력이 필요하다.

 

별칭(alias)과 GROUP BY/HAVING

- MySQL에서는 SELECT에서 만든 별칭(alias)을 GROUP BY, ORDER BY, HAVING에서 참조 가능

- WHERE에서는 표준 SQL상 별칭 참조 허용 X

- 실무 팁: 혼동/모호성을 줄이려면, 중요한 집계 조건은 표현식을 직접 쓰거나, 다음 회차에서 배울 서브쿼리/CTE로 분리하는 방식이 안전

 

핵심

- 집계 함수는 기본적으로 NULL 무시

- COUNT(*)는 행을 세고, COUNT(col)은 NULL이 아닌 값만 셉니다.

- WHERE는 집계함수를 참조할 수 없고, 그룹 조건은 HAVING에서 처리.

 

-- 이렇게 쓰면 실수 찾기 어렵습니다.

select channel, count(distinct order_id) as total_orders, count(distinct case when order_status='cancelled' then order_id end) as cancelled_orders,
round(count(distinct case when order_status='cancelled' then order_id end)/count(distinct order_id)*100,1) as cancel_rate_pct
from orders group by channel having count(distinct order_id)>=20 order by cancel_rate_pct desc

-- 이렇게 정리하면 전체적인 구조를 한눈에 파악하기 좋죠.

SELECT
   channel,
   COUNT(DISTINCT order_id) AS total_orders,
   COUNT(DISTINCT CASE WHEN order_status = 'cancelled' THEN order_id END) AS cancelled_orders,
   ROUND(
     COUNT(DISTINCT CASE WHEN order_status = 'cancelled' THEN order_id END)
     / COUNT(DISTINCT order_id) * 100, 1
  ) AS cancel_rate_pct
FROM orders
GROUP BY channel
HAVING COUNT(DISTINCT order_id) >= 20
ORDER BY cancel_rate_pct DESC

• 키워드는 대문자 : SELECT, FROM, WHERE, GROUP BY, DISTINCT, AS, CASE WHEN 등
• 함수도 대문자 : COUNT, ROUND, SUM, AVG 등
• 컬럼명, 테이블명은 소문자 : channel, order_id, orders
• 절마다 줄바꿈 : SELECT, FROM, WHERE, GROUP BY 등 각각 새 줄에서 시작
• 들여쓰기 : 스페이스 2칸 혹은 Tab으로 컬럼명, 조건 등 들여쓰기

 

SQL 세션 데일리 과제 #2

 

과제 1 - 주문 상태별 운영 지표(아이템/주문 혼합)

order_status 별로 아래 지표를 집계하세요.

● order_status

● orders = 주문 수 ( COUNT(DISTINCT order_id) )

● item_rows = 아이템 행 수 ( COUNT(*) )

● avg_items_per_order = item_rows / orders (소수점 2자리 반올림)

조건:

● 아이템 행 수가 10 이상인 상태만 남기기 ( HAVING 사용)

정렬:

● orders DESC

 

답)

select
        order_status,
        count(distinct order_id) as orders,
        count(*) as item_rows,
        round(item_rows/orders),2) as avg_items_per_order
from basic.order_items
having item_rows >= 10
order by orders desc;

해설)

select
        order_status,
        count(distinct order_id) as orders,
        count(*) as item_rows,
        round(count(*)/count(distinct order_id),2) as avg_items_per_order
from basic.order_items
group by order_status
having item_rows >= 10
order by orders desc;

=> SELECT에서 집계할때는 별명이 아닌 집계 함수가 사용된 원 데이터 문을 그대로 써야한다.

=> SELECT문에서 나온 컬럼은 GROUP BY 컬럼이어야 한다. GROUP BY에 order_status를 작성해줘야 한다.

 

과제 2 — 채널별 취소/환불율(주문 레벨, 조건부 집계)

channel 별로 주문 레벨에서 아래를 구하세요.

● channel

●  total_orders = 전체 주문 수

●  cancelled_orders = 취소 주문 수 ( order_status='cancelled' )

●  refunded_orders = 환불 주문 수 ( order_status='refunded' )

●  cancel_or_refund_orders = 취소 또는 환불 주문 수

●  cancel_or_refund_rate_pct = ( cancel_or_refund_orders / total_orders * 100 ) 소수점 1자리

조건:

●  전체 주문 수가 20 이상인 채널만 ( HAVING )

정렬:

●  cancel_or_refund_rate_pct DESC , channel ASC

| 힌트: 주문 수는 COUNT(DISTINCT order_id)

| 조건부 주문 수는 COUNT(DISTINCT CASE WHEN ... THEN order_id END) 패턴 사용

 

답)

select
        channel,
        count(distinct order_id) as total_orders,
        count(distinct case
               when order_status='cancelled' then cancelled_orders
               when order_status='refunded'then refened_orders
        end as cancel_or_refund_orders,
        round(cancel_or_refund_orders/total_orders*100,1)
from basic.order_items
group by channel
having count(order_id) >= 20
order by cancel_or_refund_pct DESC;

해설)

select
        channel,
        count(distinct order_id) as total_orders,
        count(distinct case when order_status='cancelled' then order_id end) as cancelled_orders,
        count(distinct case when order_status='refunded' then order_id end) as refund_orders,
        count(distinct case when order_status in ('cancelled','refunded') then order_id end) as cancel_or_refund_orders,
        round(count(distinct case when order_status in ('cancelled','refunded') then order_id end)/count(distinct order_id)*100,1) as cancel_or_refund_rate_pct
from basic.order_items
group by channel
having count(order_id) >= 20
order by cancel_or_refund_rate_pct DESC;

=> COUNT(DISTINCT CASE WHEN ~ THEN ~ END) 문을 사용할때는 별명 마다 하나씩 각자 따로 해야한다. 하나의 CASE에 모두 담으려 해 오류가 발생했다.

=> SELECT에서 집계할때는 별명이 아닌 집계 함수가 사용된 원 데이터 문을 그대로 써야한다.

 

과제 3 — 지역별 쿠폰 사용률(주문 레벨 + NULL 활용)

region 별로 아래를 집계하세요. (주문 레벨)

●  region

●  total_orders

●  coupon_orders = 쿠폰 사용 주문 수 ( coupon_code IS NOT NULL )

●  coupon_order_pct = 쿠폰 사용 주문 비율(%), 소수점 1자리

●  no_coupon_orders = 쿠폰 미사용 주문 수 ( coupon_code IS NULL )

●  pickup_orders = 픽업 주문 수 ( delivery_type='pickup' )

조건:

●  쿠폰 사용 주문 수가 5 이상인 지역만 ( HAVING )

정렬:

●  coupon_order_pct DESC , coupon_orders DESC

 

답:

select
        region,
        count(distinct order_id) as total_orders,
        count(coupon_code) as coupon_orders,
        round(count(coupon_code)/count(distinct order_id),1) as coupon_order_pct
        count(*) as no_coupon_orders,
        delivery_type='pickup' as pickup_orders
from basic.order_items
group by region
having count(coupon_code) >= 5
order by coupon_order_pct desc, coupon_orders desc;

해설

select
        region,
        count(distinct order_id) as total_orders,
        count(distinct case when coupon_code is not null then order_id end) as coupon_orders,
        round(count(distinct case when coupon_code is not null then order_id end)/count(distinct order_id)*100,1) as coupon_order_pct,
        count(distinct case when coupon_code is null then order_id end) as no_coupon_orders,
        count(distinct case when delivery_type='pickup'then order_id end) as pickup_orders
from basic.order_items
group by region
having coupon_orders >= 5
order by coupon_order_pct desc, coupon_orders desc;

=> coupon_orders 잘못 COUNT하면서 뒤가 연쇄적으로 틀림. coupon IS NOT NULL 이라는 조건이 뒤에 붙었기 때문에 DISTINCT CASE WHEN 사용 필요.

=> 비율을 구할때는 나눈 후 *100 필수

=> no_coupon_orders도 coupon_orders과 동일 문제

 

과제 4 — 카테고리별 “매출 + 반품 + 리뷰”(완료 주문 기반)

order_status='completed' 만 대상으로 product_category 별 아래를 집계하세요.

●  product_category

●  completed_items = 완료 아이템 행 수

●  net_sales = SUM(unit_price * quantity * (1 - discount_rate)) (반올림해서 정수)

●  returned_items = 반품 아이템 수 ( is_returned=1 )

●  return_rate_pct = returned_items / completed_items * 100 (소수점 1자리)

●  review_cnt = 리뷰가 달린 아이템 수 ( COUNT(review_score) )

●  review_rate_pct = review_cnt / completed_items * 100 (소수점 1자리)

조건:

●  완료 아이템 행 수가 15 이상인 카테고리만 ( HAVING )

정렬:

●  net_sales DESC

 

답)

select
        product_category,
        count(case when order_status='completed' then order_id end) as completed_items,
        sum(unit_price*quantity*(1-discount_rate)) as net_sales,
        count(distinct case when is_returned=1 then order_id end) as returned_items,
        round(count(distinct case when is_returned=1 then order_id end)/count(case when order_status='completed' then order_id end)*100,1) as return_rate_pct,
        count(review_score) as review_cnt,
        round(count(review_score)/count(case when order_status='completed' then order_id end)*100,1) as review_rate_pct
from basic.order_items
group by product_category
having completed_items >= 15
order by net_sales desc

 

해설)

SELECT
        product_category,
        COUNT(*) AS completed_items,
        SUM(unit_price*quantity*(1-discount_rate)) AS net_sales,
        COUNT(DISTINCT CASE WHEN is_returned=1 THEN order_id END) AS returned_items,
        ROUND(
          COUNT(DISTINCT CASE WHEN is_returned=1 THEN order_id END)
          / COUNT(CASE WHEN order_status='completed' THEN order_id END)*100,1
        ) AS return_rate_pct,
        COUNT(review_score) AS review_cnt,
        ROUND(
          COUNT(review_score)
           / COUNT(CASE WHEN order_status='completed' THEN order_id END)*100,1
        ) AS review_rate_pct
FROM basic.order_items
WHERE order_status='completed'
GROUP BY product_category
HAVING completed_items >= 15
ORDER BY net_sales DESC

=> 쿼리 포맷팅 습관화

=> 대상을 앞서 언급했기에 WHERE절 이용 필요

 

SQL 세션 데일리 퀴즈 #2

 

Q1. COUNT(review_score) 는 무엇을 세나요?

A. 전체 행 수

B. review_score 가 NULL이 아닌 행 수

C. review_score 의 서로 다른 값 개수

D. review_score 의 평균

 

답) B. review_score 가 NULL이 아닌 행 수

 

Q2. 다음 중 올바른 설명은?

A. WHERE 는 집계함수 조건을 사용할 수 있다

B. HAVING 은 그룹이 만들어지기 전에 적용된다

C. HAVING 은 그룹(집계 결과)에 조건을 적용할 수 있다

D. WHERE 는 그룹을 필터링한다

 

답) C. HAVING 은 그룹(집계 결과)에 조건을 적용할 수 있다

 

Q3. 다음 쿼리가 문제가 될 수 있는 이유는?

SELECT product_category, product_name, SUM(unit_price*quantity)
FROM basic.order_items
WHERE order_status='completed'
GROUP BY product_category;

A. SUM() 은 SELECT에서 사용할 수 없다

B. product_name 이 GROUP BY에도 없고 집계도 아니라서

C. WHERE 절이 없어서

D. ORDER BY 가 없어서

 

답) B. product_name 이 GROUP BY에도 없고 집계도 아니라서

 

Q4. 다음 중 WHERE 에 대한 설명으로 맞는 것은?

A. 그룹 생성 후에 적용된다

B. 집계함수를 참조할 수 있다

C. 행(row) 단위 필터에 사용된다

D. 그룹 단위 필터에 사용된다

 

답) C. 행(row) 단위 필터에 사용된다

 

Q5. GROUP BY 없이 집계 함수만 쓰면 결과는 어떻게 되나요?

A. 에러가 난다

B. 각 행이 한 그룹이 된다

C. 전체 행이 하나의 집계 그룹으로 처리된다

D. DISTINCT가 자동으로 적용된다

 

답) D. DISTINCT가 자동으로 적용된다

 

해설) C. 전체 행이 하나의 집계 그룹으로 처리된다

 

=> DISTINCT는 중복 제거의 이미이며, 집계 그룹화와는 별개의 함수이다.

=> GROUP BY 없이 집계 함수만 사용하면, 테이블 전체를 하나의 그룹으로 보고 집계. 그래서 결과는 단 한 행이 됨.

 

 

팀 진행 및 결정사항

2025.12.26 결정사항

다음주 아티클 스터디 일정

12월 29일 월요일 아티클 “비개발자를 위한 엑셀로 이해하는 SQL : WHERE와 ORDER BY”

• https://yozm.wishket.com/magazine/detail/1747/1

1월2일 금요일 아티클 “개발 블로그는 어떻게 써야할까? (f-lab)”

• https://f-lab.kr/blog/graduate-interview-habihow-write-developer-blog

 

오늘의 회고

SQL이 더 길어졌다. 오늘은 라이브 세션도 점점 이해가 안되더니 데일리 과제도 한번에 푼 문제가 하나도 없다. 다음주 시험을 위해서는 좀 더 마스터해야하는데 이런식으로는 어림도 없다. 문제를 최대한 이해해야한다. 지금으로는 어림도 없다.. 또한 튜터님이 말씀하신 쿼리 포맷팅도 중요한 것 같다. 오류 찾는데 한 세월이었다. 주말에도 튜터님 말씀처럼 최소 1시간은 쳐다보면서 되새겨야겠다. sql 4주차 강의 관련은 주말에 정리해서 다시 올려야겠다.