ASAC 빅데이터 분석가 과정 19일차 (24.12.31)

02_sql

rank

• 순위 관련되어서 랭킹을 부여하는 스타일
  참고) top10, top5 : order by ~ limit N
  => 단순히 보여주기만 하고 실제로 등수를 부여하지는 않는다

---

use employees;
select * from salaries limit 3;
select * from salaries order by salary desc limit 3;
# 우리 회사의 고액 연봉자 top3

---

• 랭킹) 명시적으로 너는 몇 등입니다 값을 부여함
• 랭킹 선정을 하는 "기준" & 산정방식
  1) 동일한 값에 대해서 서로 다른 등수 랭킹 부여 : row_number()
  2) 동일한 값에 대해서는 같은 등수에 랭킹 부여 : 
      2-1) 같은 값 다음에 등수를 이어지는 랭킹 사용 : dense_rank()
      2-2) 같은 값 다음에 등수를 점프해서 랭킹 사용 : rank()
• ex)
  100,150,200,200,300 : row_number() : 1,2,3,4,5
  100,150,200,200,300 : dense_rank() : 1,2,3,3,4
  100,150,200,200,300 : rank() : 1,2,3,3,5

---

• 문제의 요구 사항에 따라서 부가적으로 사용
  partion by : 끼리끼리 묶어서 진행
  정렬에 대한 추가
  over(order by 컬럼 정렬)
  over(partion by ~)

---

use employees;
select * from salaries limit 3;

• Q) 사원 정보, 월급 정보를 같이 보자
  원급을 기준으로 사원의 정보를 붙여보자

select * from salaries S left join employees E
on S.emp_no = E.emp_no;
# 필요한 테이블을 원하는대로 붙여서 보면 된다 join

---

• 참고로 임시적으로 테이블을 만들어 두겠다
  => create table ~ (select ~)

create table temp (select E.emp_no, E.first_name, E.last_name, E.gender, S.salary
from salaries S left join employees E
on S.emp_no = E.emp_no);
select * from temp;

---

• salary 정보를 기준으로 랭킹을 부여하자
• 고액 연봉 순으로 랭킹을 부여하자

select emp_no,first_name,last_name,salary,
rank() over(order by salary desc) `rank`
from temp;
-- 65, 66, 66, 68 # 동일 등수인 경우 건너뜀

select emp_no,first_name,last_name,salary,
dense_rank() over(order by salary desc) `rank`
from temp;
-- 65, 66, 66, 67 # 동일 등수인 경우 이어짐

select emp_no,first_name,last_name,salary,
row_number() over(order by salary desc) `rank`
from temp;
-- 65, 66, 67, 68 # 동일 등수인 경우 무시

---

partition

• 지금 위에 한 쿼리는 그냥 연봉이라는 하나의 기준으로 연결
  사람별로 연봉을 기준으로 1~m, 1~n
  연봉 기준으로 그냥 쭉 1~n
  => 랭킹을 부여는 하는데 전체 가로줄에 부여하는 것이 아닌 원하는 부분별로 랭킹을 산정
• ex) 부서별로 매출 1~n산정

select emp_no,first_name,last_name,salary,
rank() over(
    partition by emp_no order by salary desc
    ) `rank`
from temp;
# 영업 지점별로 매출 랭킹 순위 + 그 중에 top3만 선정
# (우수 영업사원 선정)

---

sql 데이터 불러오기

• 저장한 schema 만들기
  shema 우클릭 -> Table Data Import Wizard
  administration -> data import
• 필요에 따라서 csv 파일을 sql쪽에 밀어 넣을 때
  앞에 방식을 사용할 수 있다 => 단, 속도가 오래 걸리는 경우가 있음
  보통은 코드로 밀어 넣거나 dump, pandas -> DF -> sql 등으로 밀어넣음

---

Selete 기본문법연습

use eda_001;

select * from dataset2;
-- clothing ID : 상품 번호
-- Age : 리뷰 작성자 연령
-- Title : 리뷰 제목
-- Review Text : 실제 작성한 리뷰 내용
-- Rating : 리뷰 작성자가 제출한 평점
-- Recommended IND : 리뷰어에 의한 상품 추천 여부
-- Positive Feed ~ : 리뷰가 도움이 되었다고 해서 긍정 피드백 수
-- Division Name : 상품이 속한 Division
-- Department Nmae : 상품이 속한 Department
-- Class Name : 상품의 타입

desc dataset2;
# 공백이 있는 필드명 -> back tick

---

• Q1) Division Name의 종류별로 평점의 평균+ 평점 내림차순으로 정렬 

select * from dataset2;

select `Division Name`, Rating from dataset2;

select `Division Name`, avg(Rating) from dataset2
group by 1;

select `Division Name`, avg(Rating) as `AVG_RATE` from dataset2
group by 1
    order by 2 desc;

---

• Q2) Department Name의 종류별로 평점의 평균 + 평점 내림차순으로 정렬 

select `Department Name`, avg(Rating) as `AVG_RATE` from dataset2
group by 1
    order by 2 desc;

---

• Q3) Department Name의 값이 Trend인 항목에 대해서
  + 평점을 3점 이하로 준 값

select * from dataset2 where `Department Name`= "Trend";

select * from dataset2 where `Department Name`= "Trend"
and Rating<=3;

---

• Q4) 3번의 데이터를 나이대별로 처리 10대, 20대 ~ case when 
  => 중분류 Trend인 항목에서 평점을 3점 이하로 준 사람들에 대해 그 고객들을 어느 나이대에서 구매했는지 보자
  => 어느 고객 연령대에서 중분류 Trend 항목에 불만이 있는지

select age,`Department Name`, rating from dataset2;

select case when age<10 then "0대"
when age<20 then "10대"
    when age<30 then "20대"
    when age<40 then "30대"
    when age<50 then "40대"
    when age<60 then "50대"
    when age<70 then "60대"
    else "고령층"
    end as `Age Band`
from dataset2 where `Department Name`="Trend"
and Rating<=3;

---

• cf) 위의 방식이 좀 귀찮을 때 (나이대별로 처리는 자주 사용되는 방식)

select 11/10;

select floor(11/10)*10; 

select 17/10;

select floor(20/10)*10;

select floor(age/10)*10 as `Age_Band`
from dataset2 where `Department Name`="Trend"
and Rating<=3;

---

        

• Q5) Trend 항목에 대한 리뷰 평점에 대해서 나이대별로 몇 건인지 확인        

select floor(age/10)*10 as `Age_Band`,
count(1) as `CNT`
from dataset2 where `Department Name`="Trend"
and Rating<=3
group by 1
    order by 2 desc;

---

• Q6) Trend 항목에 대한 리뷰 중에서 50대들의 3점 이하의 리뷰들을 출력(10개만)

select * from dataset2 where `Department Name`= "Trend"
and Rating<=3
    and age between 50 and 59
    limit 10;

---

 

• Q7) (Deparment and ClothID)의 항목을 기준으로 평점을 계산을 해서
  -> 출력 부분은 Deparment, clothID, 평점의 평균
  어느 상품이 평점이 좋은지 + 중분류 정보와 함께

select `Department Name`, `Clothing ID`, Rating
from dataset2
    group by 1,2;

select `Department Name`, `Clothing ID`, avg(Rating) `AVG_Rate`
from dataset2
    group by 1,2
    order by 3 desc;

select `Department Name`, `Clothing ID`, avg(Rating) `AVG_Rate`
from dataset2
    group by `Department Name`,`Clothing ID`
    order by `AVG_Rate` desc;

---

 

• Q8) 랭킹을 하기는 하는데, Department별로 랭킹을 독립적으로 부여를 하고자 한다
  랭킹의 산정 기준은 7번에서 했던 평점의 평균 -> 내림차순
  -> 출력 : Deparment, clothid, 평점의평균, 랭킹

select `Department Name`, `Clothing ID`, avg(Rating) `AVG_Rate`
from dataset2
    group by `Department Name`,`Clothing ID`
    order by `AVG_Rate` desc; -- Q7)코드

• 위의 결과를 기본으로 해서 여기서 이것을 가지고 어찌할지 생각하자

select * ,rank() over(partition by `Department Name`
order by `AVG_Rate` desc) `Rank`
    from (select `Department Name`, `Clothing ID`, avg(Rating) `AVG_Rate`
from dataset2
    group by `Department Name`,`Clothing ID`
    order by `AVG_Rate` desc)A;
# 필요한 테이블만 추가  (별칭 필수)

---

• # Q9) 8번 문제에서 너무 많은 항목들이 있어 Department 별로 평점 평균 순위가 Top 10만 출력

select * ,rank() over(partition by `Department Name`
order by `AVG_Rate` desc) `Rank`
    from (select `Department Name`, `Clothing ID`, avg(Rating) `AVG_Rate`
from dataset2
    group by `Department Name`,`Clothing ID`
    order by `AVG_Rate` desc)A; -- Q8)코드

• # 위의 쿼리문으로 생성한 테이블이 B라고 생각하고 무엇을 할지 

select * from ()B where `Rank`<=10
order by `Department Name`;

• # 위의 기본 쿼리에서 구체적인 B를 넣으면 된다

select * from (select * ,rank() over(partition by `Department Name`
order by `AVG_Rate` desc) `Rank`
    from (select `Department Name`, `Clothing ID`, avg(Rating) `AVG_Rate`
from dataset2
    group by `Department Name`,`Clothing ID`
    order by `AVG_Rate` desc)A)B 
    where `Rank`<=10
order by `Department Name`;

---

 

• Q10) Department & 연령대를 기준으로 그룹을 만들어서 평점의 평균 매기기
  -> 출력 : Deparment, 연령대, 평점평균
  위에서 한 내용인데 차이점은 기존 필드를 사용해서 한 것
  내가 새롭게 연령대를 만들어서 해야하는 점 차이

select `Department Name`, Age, Rating from dataset2;

select floor(age/10)*10 as `Age_Band`from dataset2;
-- 위에서 했던 내용

select `Department Name`, Age, Rating, floor(age/10)*10 as `Age_Band` 
from dataset2;

• 필요한 정보는 만들었으니 묶어서보자 +DP +AhBand

select `Department Name`, floor(age/10)*10 as `Age_Band`, avg(Rating) `Avg_Rate`
from dataset2
    group by 1,2;

• 핵심 : 기존에 없는 값을 가지고 뭔가 할 때
  생성을 하고 그 다음에 어떻게 할지 선택

---

 

• Q11) 연령대별로 생성한 평점평균에 대한 점수를 기준으로 랭킹을 부여
  위에서 한 10번을 기준으로 랭킹을 부여해보자
  연령대별로 랭킹을 부여하자 (partition) 기준 : 평점 평균
  => 10번의 결과를 A라는 테이블

select *, rank() over(partition by `Age_Band` order by `Avg_Rate` desc) `Rnk`
from ()A;
-- 기본구조

select *, rank() over(partition by `Age_Band` order by `Avg_Rate` desc) `Rnk`
from (select `Department Name`, floor(age/10)*10 as `Age_Band`, avg(Rating) `Avg_Rate`
from dataset2
    group by 1,2)A;
-- A에 rank 추가한 구조

---

 

• Q12) 리뷰중에서 size 관련된 언급이 있는 리뷰인지 아닌지 체크용 필드 생성
  -> 리뷰 필드에 size라는 단어가 있으면 1, 없으면 0으로 출력
  -> 출력 : 리뷰, size언급유무
• 핵심 : 내가 원하는 조건에 맞는 데이터가 몇 개인지 case when + sum // count

select `Review Text` from dataset2;

• 전체 리뷰 수 // size 언급된 리뷰 수

select count(1) from dataset2;

• 원하는 조건 : 내용에 size 단어가 있으면 ok

select case when `Review Text` like "%size%" then 1
else 0
    end `Size Cnt` 
    from dataset2;

---

• Q13) 전체 리뷰 데이터 수하고 size가 언급된 리뷰데이터 수 하고 출력

select count(1) `Total Cnt`, sum(case when `Review Text` like "%size%" then 1
else 0
    end) `Size Cnt` 
    from dataset2;
-- case when으로 size업급조건은 1 나머지는 0으로 해서 sum()으로 다 더한다

• 개별적으로 해야하는 것들을 한 번에 처리 가능

select `Review Text` from dataset2 where `Review Text` like "%size%";

select count(1) from dataset2 where `Review Text` like "%size%";

---

 

• Q14) 리뷰 중에서 size언급된 리뷰수, large언급된 리뷰수,
  loose언급된 리뷰수, small언급된 리뷰수,
  tight언급된 리뷰수, 전체 리뷰수
  내가 보고자 하는 것들을 1개의 쿼리로 하고 싶다

select 
sum(case when `Review Text` like "%size%" then 1 else 0 end) `size_count`,
    sum(case when `Review Text` like "%large%" then 1 else 0 end) `large_count`,
    sum(case when `Review Text` like "%loose%" then 1 else 0 end) `loose_count`,
    sum(case when `Review Text` like "%small%" then 1 else 0 end) `small_count`,
    sum(case when `Review Text` like "%tight%" then 1 else 0 end) `tight_count`,
    count(1) `TotalCnt`
    from dataset2;

---

 

• Q15) 14번의 해당한는 항목들을 Department 별로 보자

select `Department Name`,
sum(case when `Review Text` like "%size%" then 1 else 0 end) `size_count`,
    sum(case when `Review Text` like "%large%" then 1 else 0 end) `large_count`,
    sum(case when `Review Text` like "%loose%" then 1 else 0 end) `loose_count`,
    sum(case when `Review Text` like "%small%" then 1 else 0 end) `small_count`,
    sum(case when `Review Text` like "%tight%" then 1 else 0 end) `tight_count`,
    count(1) `TotalCnt`
    from dataset2
    group by 1;
    
    select * from dataset2; # gpt 이용하면 효율적으로 탐색

---

결론

1. sql을 운영하는 쿼리문이 아니라 데이터 추출 관련된 것 중심
   + 빠진 내용들도 있다 (구글링)
2. 내가 필요한 정보들을 모으자 => 어디에 있는지 체크해야 한다
    없으면 필요한 정보를 기반으로 새롭게 만들자
3. 새롭게 만들면서 추가적인 진행을 할 때 from(~)A - 별칭 필수
   내가 처리한 결과를 감싸서 진행할 수도 있다
   => 쿼리의 효율성은 문제가 있을 수 있지만 일단 원하는 결과에 대한 접근 필요
4. 기존의 값을 가지고 집계처리 : 여러 수식 함수 + 구글 / 변형 : case when ~
   + 카운팅 : count(~), sum(case when ~ 1/ 0~)
5. 필터링을 할 때, where / having에 대해서 구별
6. select 문의 순서 
   select ~(~) from ~ where ~ group by ~  having ~ order by ~ limit
   => mysql select의 순서 & 배치 잘 알아야 한다

---

sql 퀴즈

• 1 ref) https://www.kaggle.com/datasets/carrie1/ecommerce-data
• 2 ref) https://www.kaggle.com/c/instacart-market-basket-analysis/data

---

 

• 1번 파트 : sample_eda_03.csv

# -> DB : test_p1
# -> 왼쪽 test_p1에서 마우스 우클릭
#  imprt data wizrd~
#  위의 파일 선택해서 로딩
# ref: Kaggle.com/carriel/ecommerce-data
#####################
use test_p1;
show tables;
select * from sample_eda_03;

 

• 2번째 파트

# 구글 드라이브에 있는 ~.sql 다운
# 왼쪽 Admin~ -> Data Import 
use test_p2;
show tables;
# orders 테이블
select * from orders;

 

• 실제 주문 내역

# order_products__prior 테이블 : 뒤에_2개, 앞_1개
select * from order_products__prior;
# order_id : 주문 관련 id 값 -> orders
# prodcut_id : 구매 상품 id값
# 해당 상품을 카트에 담은 순서
# 해당 상품이 재주문인지 여부.. 1재구매, 0처음구매

• 개별 상품에 대한 정보 : products

select * from products;
# -> 상품 코드 번호
# 상품 이름
# 상세 카테고리id
#  대분류 카테고리id
select * from departments;
select * from aisles;