rubus0304 님의 블로그
［데이터 분석 3일차 ② ］데이터가 없거나 상식적이지 않을 때 NULL, COALESCE, Pivot Table, Window function (rank, sum, date(포맷함수) 본문
［데이터 분석 3일차 ② ］데이터가 없거나 상식적이지 않을 때 NULL, COALESCE, Pivot Table, Window function (rank, sum, date(포맷함수)
rubus0304 2024. 10. 2. 14:06
데이터가 없을 때의 연산 결과 변화 케이스
[방법1] 없는 값을 제외해주기
[방법2] 다른 값을 대신 사용하기
조회한 데이터가 상식적이지 않은 값을 가지고 있다면 범위지정해주기
Pivot Table 만들기
1. 없는 값을 제외해주기 NULL
ex)
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
2. 다른 값을 대신 사용하기
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
3. 조회한 데이터가 상식적이지 않은 값을 가지고 있다면 범위지정해주기
[방법] 조건문으로 값의 범위를 지정하기
- 조건문으로 가장 큰 값, 가장 작은 값의 범위를 지정해 줄 수 있습니다. → 상식적인 수준 안에서 범위를 지정해줍니다.
- 위의 나이의 경우 아래와 같은 범위를 지정해 줄 수 있습니다.
select customer_id,
name,
email,
gender,
age,
case when age<15 then 15
when age>80 then 80
else age end "범위를 지정해준 age"
from customers
4. Pivot Table 만들기
- Pivot Table : 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것을 의미
ex) 음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)
1) 음식점별, 시간별 주문건수 집계하기
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
2) Pivot view 구조 만들기
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
ex) 성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순)
1) 성별, 연령별 주문건수 집계하기
select b.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)
from food_orders a inner join customers b on a.customer_id=b.customer_id
where b.age between 10 and 59
group by 1, 2
2) Pivot view 구조 만들기
select age,
max(if(gender='male', order_count, 0)) male,
max(if(gender='female', order_count, 0)) female
from
(
select b.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) order_count
from food_orders a inner join customers b on a.customer_id=b.customer_id
where b.age between 10 and 59
group by 1, 2
) t
group by 1
order by age
5. 업무 시작을 단축시켜 주는 마법의 문법 (Window Function - RANK, SUM)
window Function : 각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어줍니다.
- RANK, SUM
1) N 번째까지의 대상을 조회하고 싶을 때, Rank
ex) 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기
(1) 음식 타입별, 음식점별 주문 건수 집계하기
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
(2) Rank 함수 적용하기
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
(3) 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
ex) 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기
(1) 음식 타입별, 음식점별 주문 건수 집계하기
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
(2) 카테고리별 합, 카테고리별 누적합 구하기
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) 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
*** 누적합 고쳐보기
위 정답에서 order_count가 동일한 값을 일괄로 더해 cum_sum으로 출력되는 문제가 있었어요.
- 문제 상황 이해
- 이 상황은 SQL에서 WINDOW 함수(SUM 등)를 사용할 때, SUM으로 동일한 cnt_order 값을 가진 여러 행이 있을 경우, SQL 엔진은 이 값을 한꺼번에 더하는 현상이 발생합니다. cnt_order의 순서를 결정할 명확한 기준이 없으므로 발생한 문제입니다.
- 해결 방법누적합을 순서대로 표기하기 위해 order by에 cum_cuisine 을 추가해줍니다.
- 이 문제를 해결하려면 ORDER BY 절에 cnt_order 외에 추가적인 열에 순서를 부여할 수 있는 restaurant_name을 포함시켜야 합니다. 이렇게 하면 동일한 cnt_order 값을 가진 행들이 명확하게 순서가 정해져 누적합이 정상적으로 처리됩니다.
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
*** 이외 함수들은 검색해서 공부!!
6. 날짜 포맷과 조건까지 SQL 로 한 번에 끝내기 (포맷 함수)
- 날짜 데이터의 이해
- 문자타입, 숫자타입과 같이 날짜 데이터도 특정한 타입을 가지고 있습니다.
- 년, 월, 일, 시, 분, 초 등의 값을 모두 갖고 있으며 목적에 따라 ‘월’, ‘주’, ‘일’ 등으로 포맷을 변경할 수도 있습니다.
ex) 날짜 데이터의 여러 포맷
yyyy-mm-dd 형식의 컬럼을 date type 으로 변경하기
select date(date) date_type,
date
from payments
ex) date type 을 date_format 을 이용하여 년, 월, 일, 주 로 조회해보기
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
ex) 3월 조건으로 지정하고, 년도별로 정렬하기
select date_format(date(date), '%Y') "년",
date_format(date(date), '%m') "월",
date_format(date(date), '%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, 3
order by 1
5주차 강의 숙제
음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)
순위 매기기
rank () over (partition by 음식타입별 order by cnt_order desc (주문건수가 많으면 1위)
누적합 구할 때
'Data Analyst > daily' 카테고리의 다른 글
| [데이터 분석 5일차] SQL 코드카타 (Lv.1 -Lv.2 26번 까지) (0) | 2024.10.07 |
|---|---|
| ［데이터분석 4일차］ (라이브세션 과제: where구문, subquery) (1) | 2024.10.04 |
| ［데이터 분석 3일차 ①］ Subquery, JOIN (1) | 2024.10.02 |
| ［데이터 분석 2일차 ②］ (Replace, Substring, Concat, 문자데이터 바꾸기 , IF, Case, User Segmentation, 오류해결) (0) | 2024.10.01 |
| ［데이터분석 2일차 ①］ (연산, 평균, 갯수, 최대, 최소, Group by, Order by) (1) | 2024.10.01 |
