[250123] 사전캠프 9일차 아티클 스터디 + SQL (NULL값 제외, COALESCE, Pivot Table)

이효원·2025년 1월 23일
sql내일배움캠프사전캠프아티클

내일배움캠프 사전캠프

목록 보기
10/22
post-thumbnail

아티클 스터디

[주제]

SQL 질문 잘 하는 법

[아티클 요약]

공부를 한다는 것은 문제 해결의 연속

  • 질문 전 체크리스트
    • 코드 오탈자 확인
    • 쿼리 실행 후 에러메시지 읽어보고 그에 맞는 조치하기
    • 문제 요구 조건 빠진 내용 확인
    • 질문 사항 외 다른 에러가 발생하지 않는지 확인
    • 문법에 맞는 DBMS가 선택되어 있는지 확인
  1. 검색
  • 구글에 검색 (네이버X)
  • 영문으로 검색하기
    • 구글 언어 설정을 영어로 바꿔놓으면 좋음
  • 검색 키워드를 잘 넣기
    • 너무 좁히는 것도 너무 넓히는 것도 좋지 않음
  • 신뢰할 수 있는 사이트 클릭
  • 언제 쓰였는지 확인하기
    • 버전에 맞는지 확인
  1. 질문방 활용
  • 잘 질문하는 법
    • 강의 영상 제목과 시간대, 문제 링크
    • 코드 복사 붙여넣기, (스크린샷 X)
    • 문제 상황 서술 : 기대했던 결과와 내 시도를 통해 얻은 결과의 차이 설명
    • 기타 추가 설명 : 구글 검색어, 참고한 자료 링크 등
  • 답변자의 편의를 위해서가 아닌. 질문자가 문제를 빠르게 해결하기 위해 정확한 질문을 해야함
  • 질문 ‘잘’ 하기 위해 고민하다 보면 스스로 문제를 해결할 수도 있다!

피아노 연습처럼, 막막함을 견디고 꾸준히 시도를 해야 성장한다!

[인사이트]

스스로 공부하면서 쉼표같은 아주 사소한 문제를 찾지 못한 적이 있는데, 질문 전 체크리스트를 통해 오류가 났을 때 차근차근 문제를 해결할 수 있을 것 같다. 아직 공식 문서나 영문 자료들을 확인할 정도로 sql을 잘 몰라서 혼자 공부할 때는 velog에 검색하거나 한글로 검색해 공부했다. 추후 스스로 공부할 때 유용할 것 같은 정보와 사이트를 알게 되어 도움일 될 것 같다. 또 데이터리안이라는 사이트를 알게 되어 블로그에서 많은 정보를 얻을 수 있을 것 같아 만족스럽다. 아티클에서 악기를 연습하는 예시를 들었는데 굉장히 공감이 됐다. 막막함을 견디며 포기하지 않고 꾸준히 공부하겠다고 다짐했다.

SQL

조회한 데이터가 사용할 수 없거나 아무 값도 없을 때 해결

방법 1. 사용할 수 없는 값 제외

# 평균 구하기 예시 
SELECT 
	 restaurant_name
     # 사용할 수 없는 데이터 제거하지 않고 계산할 경우 MySQL 은 0으로 간주
   , AVG(rating) average_of_rating
   		# rating에 'Not given'이 아닐 경우에만 평균 계산, 아니면 NULL
   , AVG(IF(rating<>'Not given', rating, NULL)) average_of_rating2
FROM food_orders
GROUP BY 1;


NULL 처리 하지 않으면 사용할 수 없는 데이터도 분모로 취급되어 값이 작아짐
처리 후 사용 가능한 데이터만 계산해 값이 커짐

NULL값 제외 방법

  • IF절로 NULL 값 만들어주기
  • WHERE 컬럼명 IS NOT NULL

방법 2. 대체값 사용

대체값 사용법

# 다른 값이 있을 때 조건문 이용하기 
IF(컬럼명 원하는 조건, 그대로 출력, 대체값)
IF(rating >=1, rating, 대체값)
# null 값일 때, 대체값으로 변경 
COALESCE(컬럼명, 대체값)

조회한 데이터가 상식적이지 않은 데이터일 때 해결

  • 상식적이지 않은 데이터 예시

    • 주문자 나이가 3살
    • 결제일자가 1960년
    • 등등 범주에서 크게 벗어날 경우

    상식적이지 않은 데이터를 다루기 위해 조건문으로 값의 범위를 지정하기
    CASE문 활용

SQL로 Pivot table 만들기

피벗 테이블
2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것

만드는 법
베이스 데이터 만들기 -> 피벗 뷰 만들기

실습 문제

1) 음식점별 시간별 주문건수 Pivot Table 뷰 만들기

15~20시 사이, 20시 주문건수 기준 내림차순

먼저 베이스 데이터 만들기

나의 풀이
SELECT 
restaurant_name 
, SUBSTR(time, 1, 2) hour
, COUNT(quantity) count_quantity
FROM 
	(#서브쿼리로 테이블 합치기
	SELECT 
		  fo.restaurant_name
		, fo.quantity
		, p.time
	FROM food_orders fo 
		LEFT JOIN payments p 
		ON fo.order_id = p.order_id
	) join_table
WHERE SUBSTR(time, 1, 2) BETWEEN 15 AND 20
# 음식점별, 시간별 정렬
GROUP BY 1,2
# 20시 주문건수 기준 내림차순 정렬
ORDER bY 2 DESC

답지와 비교
select a.restaurant_name,
       substring(b.time, 1, 2) hh,
       count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2

생각해보니 서브쿼리에서 딱히 미리 계산할 내용이 없어서 안쓰고 답지처럼 하는게 나을 것 같았다. 지난 시간에 배운거라 아무 생각없이 그냥 쓴 느낌이라 잘 생각하고 써야겠다. 그리고 정렬은 미리 하지 않고 피벗 테이블 만든 이후에 하는 것 같다. 아직 피벗테이블 만드는 법은 몰라서 강의를 보면서 공부했다.

피벗 테이블 만드는 방법

SELECT
		# 행 축 지정
	, restaurant_name,
    	# 열 축 지정
        # MAX를 쓰는 이유 : 시간대(hh)별 데이터를 유일한 값으로 뽑아내는 역할
        # 피벗테이블 형식으로 만들기 위해! 쓴다고 이해
    , MAX(IF(hh='15', cnt_order, 0)) "15"
    , MAX(IF(hh='16', cnt_order, 0)) "16"
    , MAX(IF(hh='17', cnt_order, 0)) "17"
    , MAX(IF(hh='18', cnt_order, 0)) "18"
    , MAX(IF(hh='19', cnt_order, 0)) "19"
    , MAX(IF(hh='20', cnt_order, 0)) "20"
FROM
( # 아까 만든 베이스 데이터를 서브쿼리로 만들기
SELECT fo.restaurant_name,
       substring(p.time, 1, 2) hh,
       count(1) cnt_order
FROM food_orders fo 
INNER JOIN payments p 
ON fo.order_id = p.order_id
WHERE substring(p.time, 1, 2) BETWEEN 15 AND 20
GROUP BY 1, 2
) base_data
# MAX 썼으니까 GROUP BY로 정렬 
GROUP BY 1
# 최종 정렬 20시 기준 내림차순
ORDER BY 7 DESC;

MAX를 왜 쓰는지 설명을 안하고 넘어가서 GPT에 질문했는데 답변이 좀 어려웠다. 특정 조건의 값을 가져오면서 동시에 여러 값을 한 행으로 정리하려면 집계 함수가 필요하기 때문이라고 이해했다. 완벽히 이해는 안되지만 일단 넘어가고 필요하면 다시 찾아봐야겠다.

2) 성별, 연령별 주문건수 Pivot Table 뷰 만들기

나이는 10~59세 사이, 연령 순으로 내림차순

SELECT 
	  gender
	, MAX(IF(age=10, count_order, 0)) "10s"
	, MAX(IF(age=20, count_order, 0)) "20s"
	, MAX(IF(age=30, count_order, 0)) "30s"
	, MAX(IF(age=40, count_order, 0)) "40s"
	, MAX(IF(age=50, count_order, 0)) "50s"
FROM 
	(
SELECT 
	COUNT(fo.quantity) count_order
	, c.gender 
	, CASE 
		WHEN c.age < 20 THEN 10
		WHEN c.age BETWEEN 20 AND 29 THEN 20
		WHEN c.age BETWEEN 30 AND 39 THEN 30
		WHEN c.age BETWEEN 40 AND 49 THEN 40
		ELSE 50
	END AS age
FROM food_orders fo 
INNER JOIN customers c 
ON fo.customer_id = c.customer_id 
WHERE c.age BETWEEN 10 AND 59
GROUP BY 2,3
)base_data
GROUP BY 1
ORDER BY 2;

몇 번 오류가 났지만 성공했다.
근데 답지엔 가로 세로 축이 반대로 되어있다.
생각해보니 연령순으로 내림차순이라 나이가 행 줄이어야 한다...

# 축 바꿈
SELECT 
	  age
	, MAX(IF(gender='male', count_order, 0)) "Male"
	, MAX(IF(gender='female', count_order, 0)) "Female"
FROM 
	(
SELECT 
	COUNT(fo.quantity) count_order
	, c.gender 
	, CASE 
		WHEN c.age < 20 THEN 10
		WHEN c.age BETWEEN 20 AND 29 THEN 20
		WHEN c.age BETWEEN 30 AND 39 THEN 30
		WHEN c.age BETWEEN 40 AND 49 THEN 40
		ELSE 50
	END AS age
FROM food_orders fo 
INNER JOIN customers c 
ON fo.customer_id = c.customer_id 
WHERE c.age BETWEEN 10 AND 59
GROUP BY 2,3
)base_data
GROUP BY 1
ORDER BY 1;

느낀점

윈도우 함수 실습하다가 시간이 없어서 내일 전부 정리하려고 좀 뺐다. 내일 SQL 마무리하고 문제 풀면서 연휴에 어떻게 할지 계획 좀 세워야겠다. 파이썬 간단히 하고 SQLD 강의를 들으면서 공부를 할지, 달리기 문제 전부 풀어볼지 생각해봐야겠다. 어제 오늘 아티클 내용이 굉장히 도움이 되는 것 같아 좋다. 팀원들이랑 은근 나름 친해진 것 같다... 그래도 같이 공부하는 사람이 있어서 훨씬 수월하다. 부트캠프 신청하길 잘한 것 같다. 제대로 시작하기 전에 사전캠프에서 실력을 많이 키우고 싶다!

profile
이효원
이전 포스트

[250122] 사전캠프 8일차 아티클 스터디 + SQL (LEFT JOIN, INNER JOIN)

다음 포스트

[250124] 사전캠프 10일차 SQL(Window 함수, OVER, PARTITION BY, RANK, SUM, DATE_FORMAT)

0개의 댓글