우선 오늘 본격적으로 공부하기 전에, 어제 숙제를 푼 것에 아쉬움이 남아 다시 풀어보았다.
select restaurant_name,
case when avg_price<=5000 then "5000원 이하"
when avg_price<=10000 then "10000원 이하"
when avg_price<=30000 then "30000원 이하"
else "30000원 초과" end price_group,
case when avg_age<30 then "20대"
when avg_age<40 then "30대"
when avg_age<50 then "50대"
when avg_age>=50 then "50대 이상"
end age_group
from
(
select restaurant_name,
avg(price) avg_price,
avg(age) avg_age
from food_orders f left join customers c
on f.customer_id=c.customer_id
group by 1
) a
order by restaurant_name
왜 찝찝했었냐면, 어제는 서브쿼리에서 나이의 평균을 먼저 계산했던게 아니라서, 나이와 금액 평균으로 seg를 하지 못했다. (건당 계산)
그래서 서브쿼리를 어디에 inner join과 연결시켜야할지 고민하다가 끝났던 것 같은데, 어제 공부를 끝내기 전에 정답코드를 한번 쑥 훑고 오늘 다시 기억을 되짚어가며 복습을 했다.
이렇게 복습을 하니까, select에 avg같은 연산함수를 불러오는 것과 case when으로 조건주기, join, 별명붙이기에 감이 생긴 것 같다!
(이제 쿼리식 썼는데 회색으로 변해버리면 심장이 쿵 .....ㅜ😥항상 알록달록했으면 좋겠다 ㅠㅠ잘해야지 ㅠㅋㅋㅋㅋㅋㅋ😅)
.
.
5-1강은 preview였어서 pass하고 ~~
5-2.조회한 데이터에 아무 값이 없다면 어떻게 해야할까?
*일단 avg(if()) <- 괄호 연속해서 쓸 때, 미리 닫아놓고 시작하는 것도 꿀팁!
허허....실수하지 말자구
ok
이처럼 null용법은 if조건 말고도 join에도 쓰일 수 있다
select a.order_id,
a.customer_id,
a.restaurant_name,
a.price,
b.name,
b.age,
b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
**where b.customer_id is not null **where절을 이렇게 써주면 가능해진다
값을 변경
조건문이용
다른 값이 있을 때 조건문 이용하기 : if(rating>=1, rating, 대체값)
대체값 넣기
select a.order_id, a.customer_id, a.restaurant_name, a.price, b.name, b.age, **coalesce(b.age, 20) "null 제거"**, b.gender from food_orders a left join customers b on a.customer_id=b.customer_id where b.age is null
이 코드에서 쓰인 것처럼.
null 값일 때 : coalesce(age, 대체값)]
을 쓰면 null이었던 부분에 대체값으로 채워지는 걸 볼 수 있다.
5-3. 조회한 데이터가 상식적이지 않은 값을 가지고 있다면 어떻게 해야할까?
주문 고객 나이가 2세이거나, 결제일자가 1978년 같이
데이터가 상식적이지 않게 조회되는 경우도 있다.
이럴 때는 상식적인 수준 안에서 범위를 지정해주고 다음과 같은 코드를 이용할 수 있다
나이로 예시를 들면,
select name
,age
,case when age<15 then 15
when age>=80 then 80
else age end re_age
from customers이렇게 쿼리식을 작성해준다면, 15세 미만이거나 80세 초과인 경우 15, 80으로 각각 대체된 것을 확인할 수 있다 !(replace로 일일이 대체 안해도 된다)
5-4. SQL 로 Pivot Table 만들어보기
데이터를 뽑아서 엑셀로 가공하지 않고, 바로 피벗을 만들 수 있다
🔻Pivot table 구조
(쿼리문을 한 줄 이상 띄고 작성하면, 서로 다른 쿼리로 인식해서 커서를 두는 곳으로 실행된다)
<실습문제_1> 조건
두 테이블을 조인해서 레스토랑 이름과 시간(앞에 두글자만) 그리고 카운트로 주문건수를 세어준다. (15-20시 데이터만 사용)
🔻먼저 베이스 코드 짜주기
select f.restaurant_name
,substr(p.time,1,2) hh
,count(1) cnt_order
from food_orders f inner join payments p
on f.order_id=p.order_id
where p.time between 15 and 20
group by 1,2이렇게 작성해준 코드를 서브쿼리 안에 넣고
아래와 같은 코드를 최종적으로 작성해준다.
select restaurant_name,
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 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
✅max, sum 같은 함수를 쓸 때는 group by 같이 써주기
✅max 쓴게 가로로 나타남
<실습문제_2>조건
성별, 연령별 주문건수 Pivot Table 뷰 만들기
(나이는 10~59세 사이, 연령 순으로 내림차순)
문제에는 나와있지 않지만, 여전히 restaurant_name이 필요
서브쿼리 - age를 연령별로 나누고,,
음 근데 굳이 inner join을 써야해? 성별이랑 연령이면 customers 테이블만 있어도 되는거 아닌가 ? 음..?
혼자 이것저것 막 해보느라 시간 좀 뺏김
문제점 발견
food_orders와 customers 테이블을 조인하지 않으면 주문건수를 셀 수가 없음.
강의 도움 받고 풀어낸 쿼리식
select age,
max(if(gender='male',cnt_order,0)) "male",
max(if(gender='female',cnt_order,0)) "female"
from
(
select gender,
case when age between 10 and 19 then "10대"
when age between 20 and 29 then "20대"
when age between 30 and 39 then "30대"
when age between 40 and 49 then "40대"
when age between 50 and 59 then "50대" end age
,count(1) 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
5-5.업무 시작을 단축시켜 주는 마법의 문법 (Window Function - RANK, SUM)
rank
- Window Function 의 기본 구조
window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)- argument : 함수에 따라 작성하거나 생략
- partition by : 그룹을 나누기 위한 기준. group by 절과 유사.
- order by : window function 을 적용할 때 정렬 할 컬럼 기준을 적기
🔻ranking 코드 실습 쿼리
select cuisine_type
,restaurant_name
,cnt_order
,ranking
from
(
select cuisine_type
,restaurant_name
,cnt_order
,rank() over(partition by cuisine_type order by cnt_order desc) ranking
from
(
select cuisine_type,
restaurant_name,
count(1) cnt_order
from food_orders
group by 1,2
) a
) b
where ranking<=3
[실습] 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기
여기까지 혼자 써봄
카테코리별 합 구하기
sum(cnt_order) over (partition by cuisine_type) sum_cuisine
카테고리별 누적합 구하기
sum(cnt_order) over (partition by cuisine_type **order by cnt_order**) cum_cuisine
누적합의 순차적 정렬을 order by cnt_order로 해결
.
.
어려움..
강의 따라가도 마지막 노션에 써있는 내용이 실행이 안됨..😒
내가 한 거 다 지우고 노션 쿼리 복붙해봐도 사진같은 결과 안나옴,.....
......................왜지... ㅠㅠㅠㅠㅠㅠㅠ.
.
.
💡rank 과로과로 over 과로과로
sum 과로과로 over 과로과로
.
5-6.날짜 포맷과 조건까지 SQL 로 한 번에 끝내기 (포맷 함수)
나중에 필요할 때, 아래 코드를 복붙해서 필요한 곳에 사용해야겠다
🔻abd(문자형식)형태의 날짜 데이터를 date함수를 통해 date type으로 바꾸기
select date(칼럼명) date_type, date from 테이블명
🔻2. date type 을 date_format 을 이용하여 년, 월, 일, 주 로 조회해보기
1. 년 : Y (4자리), y(2자리)
2. 월 : M, m
3. 일 : d, e
4. 요일 : wselect date(칼럼명) date_type, date_format(date(칼럼명), '%Y') "년", date_format(date(칼럼명), '%m') "월", date_format(date(칼럼명), '%d') "일", date_format(date(칼럼명), '%w') "요일" from 테이블명
🟡요일 0은 일요일 1은 월요일 ..! %y%m이면 년,월 둘다 구하는 것
실습문제___년도별 3월의 주문건수 구하기
step.1_ 년도, 월을 포함하여 데이터 가공하기
select date_format(date(date), '%Y') y, date_format(date(date), '%m') m, order_id from food_orders a inner join payments b on a.order_id=b.order_id
step.2_년도, 월별 주문건수 구하기
select date_format(date(date), '%Y') y, date_format(date(date), '%m') m, count(1) order_count from food_orders a inner join payments b on a.order_id=b.order_id group by 1, 2
step.3_3월 조건으로 지정하고, 년도별로 정렬하기
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
.
.
여기까지는 할만했다 !! window함수 같은 데서 시간을 좀 많이 뺏긴것 같다.
오늘 안으로 5주차 숙제 풀어보고 완강 목표 달성해야겠다..!
연휴동안 걷기문제 풀면서 부족했던 부분 채워야겠다ㅠ!🧐