20251226 TIL

Today I Learn

: SQL 세션, 아티클 스터디, SQL 퀘스트, 데이터 리터러시 강의

 

- SQL 세션

• WHERE (행 필터) vs. HAVING (그룹 필터)
  • WHERE: 집계함수를 참조할 수 없음, 집계 전에 행을 필터링
  • HAVING: 그룹(집계 결과)에 조건을 적용, GROUP BY로 그룹이 만들어진 뒤 그룹에 조건을 적용
• 대부분의 집계함수는 NULL 무시
  • 예외로 COUNT(*)는 열 값이 아니라 '행'을 세는 형태임
  • COUNT(*): 결과 행(row)의 개수(=조건을 만족한 행 수)
  • COUNT(col): col이 NULL이 아닌 값의 개수
  • COUNT(DISTINCT col): col의 서로 다른(non-NULL) 값의 개수
• MySQL에서는 SELECT에서 만든 별칭을 GROUP BY, ORDER BY, HAVING에서 참조할 수 있음
  • 하지만 WHERE에서는 표준 SQL상 별칭 참조가 허용되지 않음
  • 집계 조건은 HAVING에서도 표현식 직접 쓰기
  • or, 서브쿼리/CTE로 분리하는 방식이 안전함
• 과제 2 - 채널별 취소/환불율 (주문 레벨, 조건부 집계)

- 내가 쓴 답

# 과제 2
select channel, 
	   count(distinct order_id) as total_orders, 
       count(distinct case when order_status <> 'cancelled' then NULL else order_id end) 
       	as cancelled_orders,
       count(distinct case when order_status <> 'refunded' then null else order_id end) 
       	as refunded_orders,
       count(distinct case when order_status = 'refunded' then order_id when order_status = 'cancelled' then order_id else null end) 
       	as cancel_or_refund_orders,
       round(count(distinct case when order_status = 'cancelled' then order_id when order_status = 'refunded' then order_id else null end)/count(distinct order_id)*100,1) 	
       	as cancel_or_refund_rate_pct
from basic.order_items
group by channel
having count(distinct order_id) >= 20
order by 6 desc, 1 asc;

 

- 정답

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 refunded_orders,
	count(distinct case when order_status in ('cancelled', 'refunded') then order_id end) as cancel_or_refund_oders,
	round(count(distinct case when order_status in ('cancelled', 'refunded') then order_id end)/count(distinct order_id)*100,2) as cancel_or_refund_rate_pct
from basic.order_items 
group by channel 
having count(distinct order_id) >= 20 
order by cancel_or_refund_rate_pct desc, channel asc

 

- 개선해야할 점

1) 내 쿼리를 보면 case를 나눌 때 긍정조건으로 할 수 있는 것을 굳이 부정조건을 사용해서 더 복잡하게 작성됨. (distinct case when order_status = 'cancelled' then order_id end) 라고 할 수 있는 것을 (distinct case when order_status <> 'cancelled' then null else order_id end) 라고 작성함 

2) 어차피 'cancelled'인 경우만 order_id로 치환하여 이의 개수를 세려고 하는 것이므로 굳이 else null과 같은 것은 작성하지 않아도 됨. 왜냐면, 정답 쿼리에서 else 절을 생략하면 조건에 맞지 않는 모든 값은 자동으로 null 처리 되기 때문!!

3) 'cancelled'와 'refunded' 두 조건을 함께 비교할 때는 in 연산자를 활용하는 것이 훨씬 효율적임

 

• 과제 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

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

 

- 어려웠던 점
: 쿠폰 사용 주문 수의 경우, distinct order_id의 개념이 포함되어야 하므로 count 집계함수를 사용하고 그 안에서 case when으로 경우를 나누어주고 이때 쿠폰을 사용한 경우라면 그 행의 order_id를 포함하는 코드를 만들어야했음

but, then 다음에 order_id가 아닌 coupon_code를 그대로 사용하거나 sum 함수를 사용하는 등 아래와 같은 형식으로 작성하여 문제에서 원하던 '중복되지 않는 주문 수'의 값이 나오지 않았음

count(case when coupon_code is not null then coupon_code else null end)

sum(case when coupon_code is not null then 1 else 0 end)

 

- 개선할 점

: 과제2와 마찬가지로 else null 은 생략가능함

 

 

- 아티클 : 사용자 데이터를 효과적으로 분석하는 법

https://yozm.wishket.com/magazine/detail/1967/

사용자 데이터를 효과적으로 분석하는 법 | 요즘IT

 

• 요약: 데이터를 수집한 뒤 이 데이터 특성에 맞는 적합한 분석을 진행하고 이를 시각화 했을 때 그 속에 감추어진 의미있는 정보를 파악할 수 있음
• 주요 포인트: 현업에서 데이터를 분석할 때 기술 통계만으로 충분할지 아니면 추론 통계가 필요할지 결정해서 효과적으로 분석을 해야하고 이에 대해 가장 이 데이터를 명확하게 잘 나타낼 수 있는 방법으로 시각화 하는 것이 중요하다. 여러 방법이 존재하지만 이 중 가장 효과적인!!! 방법을 찾는 것이 중요할 것.

 

• 핵심 개념:
  • 기술 통계
    • 빈도나 평균과 같은 수집된 데이터의 특성을 파악하는데 활용
    • 분석과 시각화는 Excel을 통해 수행할 수 있음
    • 범주형 데이터는 파이차트와 누적 막대그래프
    • 수치형 데이터는 막대그래프와 선 그래프
    • 방사형 그래프(전체적인 경향)와 산점도(두 변수 간의 관계)
  • 추론 통계
    • 표본을 통해 모집단을 추론하거나 가설을 검정하기 위한 방법
    • 데이터가 정규성이 확보되는지에 대한 확인이 필요 → 정규분포인지 확인
    • p-value가 0.05이하인지 확인하여 통계적 유의성 확인
    • t 검정(비교 대상이 2개 이하)과 분산분석(ANOVA, 2개 초과)
    • 상관 분석과 회귀 분석
      • 상관 분석: 2개 이상의 변수들의 선형적인 관계를 살펴보기 위한 분석 방법 (상관계수 r 도출)
      • 회귀 분석: 하나의 종속 변수에 대해 다수의 독립변수들이 어떻게 영향을 미치는지에 대한 인과 관계를 분석
  • 현업에서 활용하는 법
    • 그래프 그릴 때 사실을 왜곡하지 않고 사실적으로 전달하는 것이 중요함
    • 차이가 명확히 보이지 않는 상황처럼 확실한 결정조건이 없는 경우 사용자 유형에 따라, 현 상황에 따라 유연하게 사고를 전환하여 어떤 지표를 선택할지, 어떤 기준으로 분석할지와 같은 맥락적인 상황을 고려해서 분석해야함.
• 핵심 개념:
  • 기술 통계
    • 빈도나 평균과 같은 수집된 데이터의 특성을 파악하는데 활용
    • 분석과 시각화는 Excel을 통해 수행할 수 있음
    • 범주형 데이터는 파이차트와 누적 막대그래프
    • 수치형 데이터는 막대그래프와 선 그래프
    • 방사형 그래프(전체적인 경향)와 산점도(두 변수 간의 관계)
  • 추론 통계
    • 표본을 통해 모집단을 추론하거나 가설을 검정하기 위한 방법
    • 데이터가 정규성이 확보되는지에 대한 확인이 필요 → 정규분포인지 확인
    • p-value가 0.05이하인지 확인하여 통계적 유의성 확인
    • t 검정(비교 대상이 2개 이하)과 분산분석(ANOVA, 2개 초과)
  • 상관 분석과 회귀 분석
    • 상관 분석: 2개 이상의 변수들의 선형적인 관계를 살펴보기 위한 분석 방법 (상관계수 r 도출)
    • 회귀 분석: 하나의 종속 변수에 대해 다수의 독립변수들이 어떻게 영향을 미치는지에 대한 인과 관계를 분석
  • 현업에서 활용하는 법
    • 그래프 그릴 때 사실을 왜곡하지 않고 사실적으로 전달하는 것이 중요함
    • 차이가 명확히 보이지 않는 상황처럼 확실한 결정조건이 없는 경우 사용자 유형에 따라, 현 상황에 따라 유연하게 사고를 전환하여 어떤 지표를 선택할지, 어떤 기준으로 분석할지와 같은 맥락적인 상황을 고려해서 분석해야함.

• 용어 정리:
  • 정량 데이터: 만족도 점수, 과제 수행시간, 에러 수와 같은 숫자로 표현 가능한 데이터
  • 정성 데이터: 사용자의 리얼 보이스, 행동 관찰 기록, 사진과 같은 숫자로 표현 불가능한 데이터
  • 통계 분석
    • 기술 통계: 수집된 데이터의 특성을 파악하기 위해 활용
    • 추론 통계: 샘플을 통해 모집단을 추론하거나 가설을 검정하기 위해 활용
  • 모집단: 전체 사용자 집단
  • 표본: 사용자 조사의 참가자

그래프 그릴 때 사실을 왜곡하지 않고 사실적으로 전달하는 것이 중요함
차이가 명확히 보이지 않는 상황처럼 확실한 결정조건이 없는 경우 사용자 유형에 따라, 현 상황에 따라 유연하게 사고를 전환하여 어떤 지표를 선택할지, 어떤 기준으로 분석할지와 같은 맥락적인 상황을 고려해서 분석해야함.

 

 

- SQL 퀘스트

 

- inner join과 left join의 차이

select em.name, dp.name
from employees em 
left join departments dp 
on em.department_id = dp.id

SELECT e.name, d.name 
FROM employees e 
INNER JOIN departments d 
ON e.department_id = d.id;

• 만약 모든 직원이 유효한 부서 ID를 갖고있다면 left join을 해도 '매칭되지 않는 경우'가 발생하지 않으므로 사실상 inner join과 같은 결과가 나옴
• 그럼에도 inner join이 정답인 이유
  • 의도의 명확성
    • '부서 정보가 있는 직원만 뽑겠다'는 목적을 가장 직접적으로 표현하는 것이 inner join임
  • 성능 최적화
    • 아주 미세한 차이지만, SQL 옵티마이저는 inner join을 처리할 때 더 다양한 최적화 경로를 선택할 수 있음
    • left join은 왼쪽 테이블을 기준으로 순서를 강제하는 경향이 있기 때문
  • 데이터의 불확실성 대비
    • 실무에서는 not null 제약 조건이 없거나 부서 정보가 누락된 데이터가 들어올 수 있음
    • 이때 left join을 쓰면 부서명이 null인 행도 포함되지만 inner join은 이를 자동으로 걸러줌

 

- 직원이 한명도 없는 부서 찾기

select dp.id, dp.name
from departments dp left join employees em
on em.department_id = dp.id 
group by dp.id, dp.name
having count(*) = 0

• 문제점: LEFT JOIN을 하면 부서에 직원이 없더라도 departments 테이블의 행은 살아있고 employees 쪽 컬럼들만 NULL로 채워진 '1개의 행'이 결과에 포함됨
• 결과: 직원이 없는 부서라도 count(*)의 값은 0이 아니라 1이 됨 (해당 부서 정보 자체가 한 줄 존재하기 때문)
• 수정방법: having count(em.id) = 0로 수정! count(컬럼명)은 NULL 값을 제외하고 세기 때문.

select dp.name
from departments dp left join employees em
on em.department_id = dp.id 
where em.id is null

• 작동 원리: LEFT JOIN을 했을 때 오른쪽(employees)에 매칭되는 데이터가 없으면 모두 NULL이 들어가는 특성을 정확히 이용했음. em.id가 NULL이라는 것은 해당 부서에 소속된 직원이 한 명도 없다는 뜻이므로 완벽한 정답!

 

- 가장 많이 판매된 상품의 이름 찾기

select p.name, sum(o.quantity) 
from orders o join products p on o.product_id = p.id 
group by p.name
order by sum(o.quantity) desc
limit 1;

 

 

- 데이터 리터러시 강의

 

https://myun0506.tistory.com/46

[데이터 리터러시 강의] 심슨의 역설, 샘플링 편향, 문제정의 방법