예상하지 못한 값이 Query 결과에 나올 때 처리하는 방법
조회한 데이터에 아무 값이 없을 때 처리 하는 방법
-데이터가 없을 때의 연산 결과 변화 케이스
1. 테이블에 잘못된 값이 들어있을 경우
2. JOIN을 했을 때 값이 없는 경우
3. 사용할 수 없는 데이터가 들어있거나, 값이 없는 경우
처리하는 방법 1. 없는 값을 제외하기
-Mysql에서는 사용할 수 없는 값일 때 해당 값을 연산에서 제외 = 0으로 간주
-NULL 문법을 이용하여 명확하게 연산을 지정해주면 됨.
(null 제거를 했을 때 join 시에는 inner join과 동일)
>원본데이터의 평균 rating은 Not given의 값을 0으로 간주해 0+0+5+3+4+3 / 6 으로 계산
>null을 사용하면 평균 rating을 구할때 연산에 사용된 데이터는 5+3+4+3 / 4 로 계산
>즉 평균 rating을 구하는 쿼리를 작성해보면,
select restaurant_name,
avg(rating) average_of_rating,
avg(if(rating<>'Not given', rating, null)) average_of_rating
from food_orders
group by 1
처리하는 방법 2. 다른 값을 대신 사용
-사용할 수 없는 값 대신 다른 값을 대체해서 사용
데이터 분석 시에는 평균값 혹은 중앙값 등 대표값을 이용해 대체
-다른 값으로 변경하고 싶을 때 조건문을 이용하거나, 대체값을 이용하면 됨
다른 값이 있을 때 조건문 이용하기
if(rating>=1, rating, 대체값)
null 값일 때, 아래와 같이 대체값을 사용할 경우 테이블에 없는 데이터 중에 채울데이터만 대체값으로 채워짐
coalesce(채울데이터, 대체값)
조회한 데이터가 상식적이지 않을 값을 가지고 있을 때 처리하는 방법
-상식적이지 않은 데이터의 경우 예시
1. 주문고객의 나이
>음식을 주문한 고객은 20세 이상의 성인인 경우가 많은데 데이터에 2세와 같은 값이 있는 경우
2. 결제 일자
>결제의 경우, 비교적 최근 일자가 있어야 하는데, 데이터에 1970년대와 같은 너무 오래전의 값들이 있는 경우
처리하는 방법. 조건문으로 값의 범위를 지정하기
-조건문으로 가장 큰 값, 가장 작은 값의 범위를 지정(상식적인 수준안에서의 범위 지정)
※예시
select customer_id, name, email, gender, age,
case when age<15 then 15
when age>80 then 80
else age
end "범위를 지정해준 age"
from customers
>15세 미만인 경우에 15, 80세 초과인 경우 80으로 값 대체
SQL로 Pivot Table(피벗테이블) 만들기
-데이터를 뽑아서 엑셀로 가공하지 않고 바로 피벗테이블을 만드는 방법
-Pivot table 구조
- Pivot table : 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것
- Pivot table의 기본구조
-피벗테이블 예시
-음식점별, 시간별 주문 건수 Pivot Table 뷰 만들기
15~20시 사이
20시 주문건수 기준 내림차순
1. 음식점별, 시간별 주문건수 집계하기
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 substr(p.time,1,2) between 15 and 20
group by 1,2
2. Pivot view 구조 만들기
select restaurant_name,
max(if(hh='15', cnt_order, 0)) "15", *표 형식을 표현하기 위해 max와 if를 사용
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 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 substr(p.time,1,2) between 15 and 20
group by 1,2
)a
group by 1
order by 7 desc
업무 시작을 단축시켜주는 문법
Window Function
Rank, Sum
-Window Function은 각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어줌
-기본 SQL 구조로 해결하기 위해서는 Subquery문을 이용하거나, 여러번의 연산을 수행해야함
>이를 Window Function으로 해결
window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)
-window_function: 기능 명을 사용(ex. sum, avg)
-argument: 함수에 따라 작성하거나 생략 단, 괄호는 사용
-partition by: 그룹을 나누기 위한 기준
-order by: window function을 적용할 때 정렬할 컬럼 기준
※예시1. n번째까지의 대상을 조회하고 싶을때 Rank
음식 타입별로 주문건수가 가장 많은 상점 3개씩 조회하기
select cuisine_type,
restaurant_name,
order_count,
rn "순위"
from
(
select cuisine_type,
restaurant_name,
rank() over (partition by cuisine_type order by order_count desc) rn,
order_count
from
(
select cuisine_type, restaurant_name,
count(1) order_count
from food_orders
group by 1,2
)a
)b
where rn<=3
order by 1,4
※예시2. 전체에서 차지하는 비율, 누적합을 구할 때, Sum
각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적합 구하기
select cuisine_type,
restaurant_name,
cnt_order,
sum(cnt_order) over (partition by cuisine_type) sum_cuisine,
sum(cnt_order) over (partition by cuisine_type order by cnt_order, restaurant_name) cum_cuisine
from
(
select cuisine_type,
restaurant_name,
count(1) cnt_order
from food_orders
group by 1,2
)a
order by cuisine_type, cnt_order, cum_cuisine
-window 함수를 사용할 때, sum으로 동일한 cnt_order 값을 가진 행이 여러개일 경우, SQL이 이 값을 한꺼번에 더하는 현상이 발생
>이는 cnt_order의 순서를 결정할 명확한 기준이 없어서 발생
이를 해결하기 위해 order by 절에 cnt_order 외에 추가적인 열에 순서를 부여할 수 있는 restaurant_name을 포함시켜야함
날짜 포맷과 조건까지 SQL로 한번에 끝내기
포맷함수
-데이터에 날짜를 지정하거나 조건에 날짜를 사용해야 할 때 활용할 수 있는 기능
*날짜 데이터
- 문자타입, 숫자타입과 같이 날짜 데이터도 특정한 타입을 가지고 있음
- 년, 월, 일, 시, 분, 초 등의 값을 모두 갖고 있으며 목적에 따라 '월', '주', '일' 등으로 포맷 변경가능
1. yyyy-mm-dd 형식의 컬럼을 data type 으로 변경
select date(date) date_type,
date
from payments
2. date type을 date_format을 이용하여 년, 월, 일, 주로 조회
-년: Y(4자리), y(2자리)
-월: M,m
-일: d,e
-요일: w
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
'내배캠 > sql' 카테고리의 다른 글
| [내일배움캠프/사전캠프] SQL 강의 4주차 (3) | 2024.09.20 |
|---|---|
| [내일배움캠프/사전캠프] SQL 강의 3주차 (1) | 2024.09.19 |
| [내일배움캠프/사전캠프] SQL 강의 2주차 (1) | 2024.09.13 |
| [내일배움캠프/사전캠프] SQL 강의 1주차 (1) | 2024.09.12 |