250207 TIL

수이·2025년 2월 7일
Data_6기sql내일배움캠프
0

🟡 TIL

목록 보기
6/41

개인스터디

엑셀보다 쉬운 SQL 5주차

3주차 복습

1) 서브쿼리

  • 쿼리 결과를 쿼리에 다시 활용하는 것
  • 기본 형식
select column1, special_column
from
    ( /* subquery */
    select column1, column2 special_column
    from table1
    ) a

2) JOIN

  • 두 개 이상의 테이블을 결합해 사용하는 것
  • JOIN의 형태에 따라 LEFT JOIN, INNER JOIN 등이 있음
  • 기본 형식
-- LEFT JOIN
select 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명
-- INNER JOIN
select 조회 할 컬럼
from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명

조회한 데이터에 값이 없을때

1) 없는 값 제외해주기

사용할 수 없는 값일때 0으로 간주

  • 조건문(IF) 사용 
SELECT restaurant_name,
       avg(rating) average_of_rating, -- 그냥 연산 시 
       avg(if(rating<>'Not given', rating, null)) average_of_rating2 -- 데이터가 없는 값 제외 후 연산 시 
FROM food_orders
GROUP BY 1
  • IS NOT NULL 사용 
SELECT 
FROM
WHERE 비어있는 데이터 is not null -- 비어있는 값 제거 / NULL이 아닌 것들만 불러오기!

2) 다른 값을 대신 사용

평균값 혹은 중앙값 등 대표값을 이용하여 대체해주기도 함

  • 조건문(IF) 사용 예시
SELECT restaurant_name,
       avg(rating) average_of_rating, -- 그냥 연산 시 
       avg(if(rating >= 1, rating, 대체값)) average_of_rating2 -- 대체값을 넣어 연산 시  
FROM food_orders
GROUP BY 1
  • COALESCE 사용
SELECT COALESCE(변경대상, 대체값) -- NULL 제거
FROM

조회한 데이터가 상식적이지 않은 값을 가지고 있을때

예시) 음식 주문한 고객의 데이터가 2세
-> 값을 포함시킬지, 포함시키지 않을지 고민해볼 수 있음

조건문(CASE)으로 범위 지정하기

select customer_id, name, email, gender, age,
       case when age<15 then 15
            when age>80 then 80
            else age end "범위를 지정해준 age"
from customers

SQL로 Pivot Table 만들어보기

Pivot Table 구조

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

실습

음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)

나눠서 생각하기

베이스 데이터 만들기
1) JOIN / 시간별 주문건수 세기

SELECT SELECT f.restaurant_name,
	   SUBSTR(p.time, 1, 2) AS hh,
       COUNT(1)
FROM food_orders f INNER JOIN payments p ON f.order_id = p.order_id
GROUP BY 1,2

2) 15~20시 사이 주문 조건 추가

SELECT SELECT f.restaurant_name,
	   SUBSTR(p.time, 1, 2) AS hh,
       COUNT(1)
FROM food_orders f INNER JOIN payments p ON f.order_id = p.order_id
WHERE SUBSTR(p.time, 1, 2) BETWEEN 15 AND 20 -- 조건 추가 
GROUP BY 1,2

피봇 뷰 만들기
여기는 도저히 모르겠어서 강의 참조🙄

-- 피봇 뷰 사용시 max 사용해야함 (이해하려고 하면 어려움)
select restaurant_name,
       max(if(hh='15', cnt_order, 0)) "15", --15시일때의 주문건수 
       max(if(hh='16', cnt_order, 0)) "16", --16시일때의 주문건수 
       max(if(hh='17', cnt_order, 0)) "17", --17시일때의 주문건수 
       max(if(hh='18', cnt_order, 0)) "18", --18시일때의 주문건수 
       max(if(hh='19', cnt_order, 0)) "19", --19시일때의 주문건수 
       max(if(hh='20', cnt_order, 0)) "20" --20시일때의 주문건수 
from -- 서브쿼리 시작
(
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
) a
group by 1
order by 7 desc -- 20시 기준으로 내림차순 정렬

성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순)

나눠서 생각하기

베이스 데이터 만들기
1) JOIN / 성별, 연령별 주문건수 세기

SELECT c.gender,	
       CASE WHEN c.age BETWEEN 10 AND 19 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 age,
       	COUNT(1) AS cnt_order
FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id
GROUP BY 1,2

2) 10~59세 사이 조건 추가

SELECT c.gender,	
       CASE WHEN c.age BETWEEN 10 AND 19 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 age,
       	COUNT(1) AS cnt_order
FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id
WHERE c.age BETWEEN 10 AND 59 -- 조건 추가 
GROUP BY 1,2

3) 피봇 뷰 만들기 

SELECT age,
	   max(if(gender = 'male', cnt_order, 0)) AS male,
	   max(if(gender = 'female', cnt_order, 0)) AS female
FROM
(SELECT c.gender,	
       CASE WHEN c.age BETWEEN 10 AND 19 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
       		WHEN c.age BETWEEN 50 AND 59 THEN 50 
       		END age,
       	COUNT(1) AS cnt_order
FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id
WHERE c.age BETWEEN 10 AND 59 
GROUP BY 1,2
) A
GROUP BY 1

4) 연령별 내림차순 정렬 추가

SELECT age,
	   max(if(gender = 'male', cnt_order, 0)) AS male,
	   max(if(gender = 'female', cnt_order, 0)) AS female
FROM
(SELECT c.gender,	
       CASE WHEN c.age BETWEEN 10 AND 19 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
       		WHEN c.age BETWEEN 50 AND 59 THEN 50 
       		END age,
       	COUNT(1) AS cnt_order
FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id
WHERE c.age BETWEEN 10 AND 59 
GROUP BY 1,2
) A
GROUP BY 1
ORDER BY 1 DESC

Window Function

  • 각 행의 관계를 정의하기 위한 함수 / 그룹 내 연산을 쉽게 만들어줌
  • 자체적으로 제공 
window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

1) RANK

특정 기준으로 순위를 매겨주는 기능

RANK() OVER (PARTITON BY 그룹 기준 컬럼 ORDER BY 정렬기준)

2) SUM

전체에서 차지하는 비율, 누적합을 구할때 사용

SUM(누적합 구할 대상) OVER (PARTITON BY 그룹 기준 컬럼 ORDER BY 정렬기준)

날짜 포맷 함수

1) yyyy-mm-dd 형식을 date type으로 변경하기

SELECT date(컬럼명)
FROM

2) date type을 년,월,일,주로 조회하기

년  Y (4자리), y(2자리)
월  M, m
일  d, e
요일  w 
0 일요일 1 월요일 2 화요일 3 수요일 4 목요일 5 금요일 6 토요일 
SELECT date(컬럼명) date_format,
       date_format(date(컬럼명), '%Y') "년",
       date_format(date(컬럼명), '%m') "월",
       date_format(date(컬럼명), '%d') "일",
       date_format(date(컬럼명), '%w') "요일"
FROM

실습

매년 3월 주문건수 (연도별 정렬)

⬇️내가 작성한 코드는 이렇게

SELECT y,
	   m,
	   cnt_order 
FROM 
(
SELECT date_format(date(p.date), '%Y') AS y,
       date_format(date(p.date), '%m') AS m,
       COUNT(p.order_id) cnt_order
FROM food_orders f INNER JOIN payments p ON f.order_id = p.order_id 
GROUP BY 1, 2
)A
WHERE m = 3
ORDER BY y

⬇️정답은 이렇게
좀 더 간단해보이는 게 이쪽이라 이렇게 작성하는 게 더 나은 것 같다!

select date_format(date(date), '%Y') "년",
       date_format(date(date), '%m') "월",
       date_format(date(data), 'Y%m') "년월",
       count(1) "주문건수"
from food_orders a inner join payments b on a.order_id=b.order_id
where date_format(date(date), '%m')='03'
group by 1, 2
order by 1

숙제

음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)

나눠서 생각하기
1) 베이스 데이터 만들기

SELECT f.cuisine_type,
	   CASE WHEN c.age BETWEEN 10 AND 19 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 age_group,
	   COUNT(1) AS cnt_order
FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id 
WHERE age BETWEEN 10 AND 59
GROUP BY 1, 2

2) 피봇뷰 만들기 / 완성✨

SELECT cuisine_type,
	   max(if(age_group = 10, cnt_order, 0)) "10대",
	   max(if(age_group = 20, cnt_order, 0)) "20대",
	   max(if(age_group = 30, cnt_order, 0)) "30대",
	   max(if(age_group = 40, cnt_order, 0)) "40대",
	   max(if(age_group = 50, cnt_order, 0)) "50대"
FROM
(
SELECT f.cuisine_type,
	   CASE WHEN c.age BETWEEN 10 AND 19 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 age_group,
	   COUNT(1) AS cnt_order
FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id 
WHERE age BETWEEN 10 AND 59
GROUP BY 1, 2
)A
GROUP BY 1

데일리퀘스트

SQL 문법을 연습해요 8 - LOL을 하다가 홧병이 나서 병원을 찾아왔습니다.

일기

SQL 강의 드디어 끝!
4~5주차 내용이 아무래도 조금 복잡해서 실제로 사용할 수 있을지 또 걱정이 되지만,
더듬더듬 기억에 남아있길 바라며(...) 모든 강의 끝내면 한번씩 다시 복습해봐야겠다.

오늘은 ADSP 4주차 강의까지 들었는데 R이 나오면서 좀 복잡해지고 있다.
파이썬이랑 비슷한 듯 하면서 다른 느낌..
그나저나 시험 대비 위해서라면 R부분은 스킵해도 된다고 하는데 스킵하려면 이전 주차의 숙제를 제출해야 해서 스킵을 할 수가 없다 🥲
너 납치된 거야..

대부분의 내용은 아무래도 시험 대비 강의다보니 외워야 하는 부분이 많다.
다음주 월요일부터는 1주차부터 다시 복습해보려고 한다.

유투브 찾아보니까 시험 대비 요약집같은 게 있어서 그것도 프린트 해뒀다!
열심히 준비해서 한 방에 자격증 따야쥐 주말에도 공부 놓지 말고 파이팅🍀

profile
수이
이전 포스트

250206 TIL

다음 포스트

250210 TIL

0개의 댓글

관련 채용 정보