to do list 📋
✅ ~SQL 5강 까지
✅ Java 복기 (3/4지점까지)
✅ 깃허브 1커밋
오늘의 순공부시간 🕰️
9시간 51분
Today I Learned 👨💻
<1>. 개념정리
📕 SQL 데이터 타입 변경 ( cast : 문자 ↔ 숫자)
숫자인줄 알았는데 이런 식으로 컬럼명 옆에 ABC로 뜬다면 헷갈려서 오류날 가능성이 크긴 하다 -> 타입 변경하는 함수를 이용해도 된다!
- cast : 변수(컬럼)가 지정한 타입(숫자, 문자...)으로 변환
- 형식) cast (변수 as 타입)
--숫자로 변경
cast(if(rating='Not given', '1', rating) as decimal) -- decimal : 아래참조
--문자로 변경
concat(restaurant_name, '-', cast(order_id as char))
+)
- decimal : 실수형(양/음수+소수점이 있는 수)으로 변환
- signed : 정수형(양/음수)으로 변환
📗 복잡한 다수의 연산을 보기 편하게 한 번의 SQL 문으로.. - Subquery(서브쿼리)
- Subquery 기본구조 -> 괄호 (( )) - 가장 안에 있는 괄호부터 연산 진행
-- 예시 1)
select 컬럼1, 컬럼2
from
( /* subquery */
컬럼1, 컬럼2
from 테이블
) 별명 --> 괄호가 많아지면 순서대로 a, b, c순으로 붙이기
-- 예시 2)
select 컬럼1, 컬럼2
from 테이블
where 컬럼1 = (select col1 from 컬럼2)
-- (수수료 구간 - ~5000원 미만 0.05%, 20000원 미만 1%, ~30000원 미만 2%, 30000원 초과 3%)
select restaurant_name,
price_per_plate*ratio_of_add "수수료"
from
(
select restaurant_name,
case when price_per_plate<5000 then 0.005
when price_per_plate between 5000 and 19999 then 0.01
when price_per_plate between 20000 and 29999 then 0.02
else 0.03 end ratio_of_add,
price_per_plate
from
(
select restaurant_name, avg(price/quantity) price_per_plate
from food_orders
group by 1
) a
) b
📘 다른 테이블끼리 합치기(조회하기) - join
ex) 주문 가격은 주문테이블에 있으나 어떤 수단으로 결제했는지는 결제테이블에 있을 때...
주문건별 수수료를 계산하기 위한 수수료율이 필요한데 결제테이블에 있어서 합쳐서 사용해야 할 때...
- join : 여러 개의 테이블을 한 번에 출력하는 함수 (≒ Vlookup)
<join의 기본 구조>
-- LEFT JOIN
select 조회 할 컬럼
from 테이블1 left join 테이블2 on 테이블1.공통컬럼명= 테이블2 .공통컬럼명 -- 전체 가져오는 테이블은 테이블1
+) 별명으로 붙였을 경우
-- INNER JOIN
select 별명.조회 할 컬럼
from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명 -- 별명은 a, b로 설정
- 조인하는 방법 :
1) left join : (공통 컬럼을 기준)왼쪽에 있는 테이블 전체 + 해당하는 데이터만 있는 다른 테이블 조회 (반대 : right join - left join으로 순서 대로 쓰면 돼서 굳이 잘 안 쓴다.)
2) inner join : (공통 컬럼을 기준)두 테이블 모두에 있는 값만 조회 (반대 : full join - 전체 조회)
-- 예시 1) 고객의 주문 식당 조회하기
-- 조건 : 1. 한국 음식만 (조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당 이름)
-- 2. 고객명으로 정렬/중복 없도록 조회하기
select distinct a.name, -- 중복값을 없애준다 : distinct
a.age,
a.gender,
b.restaurant_name
-- +) 혹은 수식으로 '새로운' 조회 컬럼을 넣을 수 있음
-- a.age*b.price "곱해준 값"
from customers a inner join food_orders b on a.customer_id=b.customer_id
where cuisine_type='Korean' -- 한국 음식만 조회
--예시 2) 50세 이상 고객 대상, 연령에 따라 '경로 우대 할인율'을 적용하기&음식 타입별 원래 가격과 할인 적용한 가격 합을 구하기
-- 조건 : 1. (조회칼럼 : 음식타입, 원래 가격, 할인 적용 가격, 할인 가격)
-- 2. 고객 정보가 없는 경우도 포함하여 조회하기
-- 3. 할인 가격이 큰 순서대로 정렬하기
select cuisine_type, -- 아래 괄호()로 묶어준 곳과 다르게 a. 은 생략해도 된다. (괄호 안에서만 적용되기 때문)
-- 혹은 t.cuisine_type으로 적어도 된다.
sum(price) "원래 가격",
sum(price)-sum(discount_price) "할인 적용 가격",
sum(discount_price) "할인 가격"
from
(
select a.cuisine_type,
a.price,
a.price*((b.age-50)*0.005) discount_price
from food_orders a inner join customers b on a.customer_id=b.customer_id -- 공통 컬럼 붙여주기
where b.age>=50 -- 50세 이상만 뽑겠다~
) t
group by 1 -- 범주별로 sum해준 것이기 때문에 1
order by 4 desc -- 할인 가격이 큰 순서대로 정렬하기 위해 4번째 컬럼을 사용하고 내림차순(desc)를 붙여줌
-- 혹은 확실하게 하고 싶다면 명칭 자체를 적어주어도 됨 : order by sum(discount_price) desc
📙 조회 데이터에 아무 값이 없다면? - 없는 값 제외(0 → null) 시켜주는 방법도 있다!
mySQL에서는 사용할 수 없는 값일 때 0으로 간주
-> ⚠️위험성 : 평균값을 구할 때 5명 중 2명의 총값이 0이면 총값/총인원(3명)이 아닌 총값/총인원(5명)으로 들어가거나 평균값의 오류가 생김
-> 💡해결 : 1. 값을 제외하기 - 0을 null문법으로 이용해 null 값으로 지정하여 포함을 아예 안 시켜준다.
2. 다른 값을 대신 사용하기 - 0 대신 다른 평균 값으로 대체해준다.
1) 값을 제외하기 - null
-- 예시 1)
select restaurant_name,
avg(rating) average_of_rating,
avg(if(rating<>'Not given', rating, null)) average_of_rating2 -- Not given이 아닐 땐 그대로, 맞으면 null로
from food_orders
group by 1
-- 예시 2)
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 -- null이 아닌 애들은 다 제외시켜준다~
-> 🤔 유의사항 : 반드시 0을 모두 null 값으로 지정해야한다는 것이 아니다 → 상황에 따라 판단 필수 🌟
2) 다른 값을 대신 사용하기 - coalesce
- coalesce : null값을 변환처리해주는 기능
- 기본 구조 : coalesce (컬럼, 대체할 값) → 뜻 : '컬럼' 이 값을 안 가지고 있다면 '대체할 값' 으로 바꿔줘~
select a.order_id,
a.customer_id,
a.restaurant_name,
a.price,
b.name,
b.age,
coalesce(b.age, 20) "null 제거", -- b.age 컬럼이 값을 안 가지고 있으면 모두 20으로 대체
b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.age is null
📕 여러 가지 상황에 대한 대처 방법들
1. 조회한 데이터가 상식적이지 않은 값을 가지고 있을 때
예) 앱으로 주문한 사람의 연령이 1살인 경우, 결제일자가 1600년일 경우 등
- 💡해결 방법 : 조건문으로 값의 범위를 지정하기 -> 예) 10세 미만은 10살로 대체하기
2. 업데이트 예정
📗 Pivot Table(피벗 테이블) 만들기
예) 음식점별, 시간별 주문건수 집계하는 피벗 테이블 만들기
(15~20시 사이, 20시 주문건수 기준 내림차순)
1. 베이스 데이터 만들기 - 음식점별. 시간별 주문건수 집계하기
select a.restaurant_name,
substring(b.time, 1, 2) hh, -- b.time 컬럼 데이터의 1번째 글자에서 2글자 가져온다는 뜻
count(1) cnt_order -- restaurant_name을 count해서 주문건수 구하기
from food_orders a inner join payments b on a.order_id=b.order_id -- 두 테이블을 order_id 기준으로 묶기
where substring(b.time, 1, 2) between 15 and 20 -- 15~20시까지만 가져온다는 뜻
group by 1, 2 -- 위의 두 조건을 묶어줌
2. 피벗 테이블 만들기
select restaurant_name, -- 피벗 테이블 만들기
max(if(hh='15', cnt_order, 0)) "15", -- 15시일때 주문건수를 불러와달라는 뜻
max(if(hh='16', cnt_order, 0)) "16", -- 최댓값(max)을 붙이는 이유는 복잡해서 나중에.. 피벗뷰를 깔끔하게 만들기 위해 max 붙이기
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 --1번째 컬럼 (restaurant_name) 기준으로 묶어준다
order by 7 desc -- 7번째줄 컬럼 (20시) 으로 내림차순 정렬
3. 결과
📘 Window Funtion - rank, sum
기본구조 : window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)
- window funtion : 각 행의 관계를 정의하기 위한 함수(rank, sum 등)
- argument : 함수에 따라 작성 혹은 생략
- partition by : 그룹을 나누기 위한 기준( ≒ group by)
- order by : 정렬한 컬럼 기준 적기 - 순차적으로 정렬할 때 사용
-- 예시 1) rank 함수 활용하기 : 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기
select cuisine_type,
restaurant_name,
order_count,
ranking "순위"
from
(
select cuisine_type,
restaurant_name,
rank() over (partition by cuisine_type order by order_count desc) ranking, -- partition by : 음식 타입(cuisine_type)별로 파티션을 나눠 랭킹을 구할 것이라는 의미
order_count -- order by : 그리고 랭킹을 구할 때 주무건수가 어떤 순서대로 순위를 매길건지
from
( -- 1. 베이스 데이터 만들기 (음식 타입별, 음식점별 주문 건수 집계)
select cuisine_type,
restaurant_name,
count(1) order_count -- 주문건수 세기
from food_orders
group by 1, 2 -- 1, 2번째 컬럼을 컬럼으로 각각 주문 건수 나오게 하기
) a
) b
where ranking<=3
-- 예시 2) sum함수 활용하기 : 전체에서 차지하는 비율과 누적합을 구하기
select cuisine_type,
restaurant_name,
order_count,
sum(order_count) over (partition by cuisine_type) sum_cuisine_type, -- 전체에서 차지하는 비율
sum(order_count) over (partition by cuisine_type order by order_count, restaurant_name) cumulative_sum -- 누적합
from -- cuisine_type으로 덩어리를 묶어주고 순차적으로 누적된 값을 합쳐야하기 때문에 order by 사용
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a
📙 날짜를 새로운 데이터 형식으로 포맷하기 - date, date_format
- ABC 형식의 컬럼을 date type으로 변경하기
select date(date) date_type, -- 주의할 점 : 여기서 date()는 함수, 괄호 안의 date는 컬럼
date
from payments
- date_format( )을 이용해서 년, 월, 일, 주로 조회하기
기본구조 : date_format(date(날짜 컬럼), %날짜 단축어)
- 날짜 단축어 모음 :
1) 년 : %Y(4자리) 혹은 %y(2자리)
2) 월 : %M %m
3) 일 : %d %e
4) 요일 : %w --> 일요일은 0, 월요일은 1 ...
select date(date) date_type,
date_format(date(date), '%Y') "년",
date_format(date(date), '%m') "월",
date_format(date(date), '%d') "일",
date_format(date(date), '%w') "요일"
from payments
📕 group by 숫자, order by 숫자의 의미
- group by 숫자 : 숫자를 기준으로 묶어준다는 의미 (화면에 이것들이 뜨게 하는 것)
- order by 숫자 : 숫자를 기준으로 정렬하겠다는 의미 (ex : 년이면 년도 순서대로 정렬하거나 할 수 있다)
select date_format(date(date), '%Y') "년",
date_format(date(date), '%m') "월",
date_format(date(date), '%m') "년월",
count(1) order_count
from food_orders a inner join payments b on a.order_id=b.order_id
where date_format(date(date), '%m')='03' -- +) 이런 식으로 뒤에 추가로 조건을 지정해주어도 된다(3월만 불러오기)
group by 1, 2, 3 -- 1~3번째 컬럼을 묶어준다
order by 1 -- 1번째 컬럼(년)을 기준으로 정렬하겠다
<2>. 짤막한 정리
▶ 드래그 + ctrl + enter : 코드 부분 실행 단축키
▶ desc : 내림차순 (예 : 3,2,1)
▶ asc : 오름차순 (예 : 1,2,3)
오늘의 회고 💭
- 나에게 mySQL이 최선일까? - 이유 찾기와 관계형DB 성능 개선
dbeaver로 mySQL을 사용하다가 익히 알려져 있는 mySQL과 oracle, postqreSQL, MariaDB 등등 많은 관계형 DB중에 내가 공부하면서 써야하는 것들과 현업에서 써야할 것들을 파악하고 그것에 대한 각각의 장/단점들은 아직 몰라서 하루 빨리 찾아보고 정리해야겠다는 생각이 들었다. 파악이 되면 미리 선수되어야할 것들도 스케줄에 넣고 나중에 팀 프로젝트에서 성능을 잘 뽑아내기 위해서 어떤 식으로 다가가야 하는지도 파악해놔야겠다.
- java 약점 파악..!
자바에 대한 강의도 많고 책도 많은데 내가 어떤 부분은 잘 알고 어떤 부분은 잘 모르는지 목록별로 정리해두어야겠다. 인프런에서 자바에 대한 강의를 들었었는데 지금까지처럼 내가 배운 자바를 수동적으로 떠먹는 것이 아니라 어디에서 부족한지 파악하고 파고드는 것이 중요한 것..(파악할 것들 투성이..ㅎ) 자바 강의 또 듣기 전에(금요일 전까지) 꼭..!
다음 계획 🌈
✅ SQL/NO SQL 종류 찾고 장단점 정리하기
✅ 코드블럭 스킨 적용하기
✅ Java 복기 (4/4지점까지)
✅ 깃허브 1커밋
'mySQL' 카테고리의 다른 글
| [TIL] SQL 기능의 이모저모 (1) (1) | 2023.12.26 |
|---|