[SQL 기초] 5주차 강의

 

2024.06.24
드디어 내배캠 데이터분석 캠프 시작!! 1주차는 정정기간이라 OT랑 팀원들을 만나 서로 인사하고 개인 공부를 하며 시간을 보냈기 때문에 SQL 기초 강의를 모두 들을 수 있게 되었다! 근데 넘오 헷갈린다!!! 심화에 들어갈 생각하니까 벌써 걱정...
ㅠㅠ 내가 잘 할 수 있을까..?

 

[이론 및 실습]

✳️ NULL - 값이 없는 경우
      • 데이터가 없는 경우에 연산에 영향을 미칠 수가 있다.
          1) Mysql에서는 사용할 수 없는 값은 0으로 간주하여 연산에서 제외한다.
               (=원래 컬럼 속 데이터에 not given은 0으로 간주함.)

▶ 그래서 그냥 평균 계산식을 이용하면 0값으로 인식되어 0값을 포함한 모든 데이터 값을 계산 후 그 개수에 맞춰 나눠준 것이다.
▶ 하지만 null 이라고 지정해준 평균식은 not given은 값으로 인식하지 말고 not given 제외 하여 평균을 계산하라고 명령한 것이다. Not given 데이터를 제외한 모든 값이 있는 데이터를 더하고 값이 있는 데이터의 개수로 나누어 준 것이다.
          2) 다른 값을 대신 사용하기
              - 사용할 수 없는 값 대신 다른 값을 대체해서 사용하는 방법이 있습니다. 
              - 데이터 분석 시에는 평균값 혹은 중앙값 등 대표값을 이용하여 대체해주기도 합니다.
              - 다른 값으로 변경하고 싶을 때, 다음 두 개의 문법을 이용할 수 있습니다. 
                  → 다른 값이 있을 때 조건문 이용하기 : if(rating>=1, rating, 대체값)
                  →  null 값일 때 : coalesce(age, 대체값)

✳️ 상식적이지 않은 값
      - 다른 데이터들보다 값이 많이 차이 나거나 비교적 구형 데이터들을 걸러줘야 하는 등 상식에서 벗어난 값을 다뤄야 할 때
      • 조건문으로 값의 범위를 지정하기

✳️ Pivot Table 제작
      - 데이터를 뽑아서 엑셀로 가공함이 아니라 sql로 바로 피봇 테이블을 만들 수 있다.
      • Pivot table 기본 구조

        *️⃣ 음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)
              1- 음식점 별, 시간 별 주문 건수 집계

▶ Food_orders랑 payments 테이블을 join 해준다. 이때 inner join을 쓴 이유는 두 테이블에 있는 데이터 값을 모두 끌어와야 해서 inner를 이용하여 order_id 기준으로 두 테이블에 모두 겹치는 데이터 값만 조회 해준다. 

              2- .Pivot view 구조 만들기

▶이제 피봇 테이블을 만들기 위해 다음과 같이 작성하였다.
▶ 먼저 행축(세로축)에 해당하는 컬럼 네임으로 식당 이름을 적어준다.
▶ 그리고 열축(가로축)에 해당되는 컬럼들에 대한 내용을 위해 필요한 조건문을 이용하였다.
     ex) 시간이 15이면 주문 건수를 보여주고 아니면 0으로 표시해줘. 컬럼 이름은 15임.
         ↪ 15시쯤에 a 식당에서는 n만큼 주문했고 b 식당에서는 주문건이 없었고 c 식당에서는 …
▶ 피봇뷰를 위해서는 max를 써줘야 하는데 일단 너무 깊게 이해하려면 어려우니 max를 일단 써줘야 한다고 하심. 근데 학습 질문에서 발견했다!!

▶ 함수가 들어가서 계산을 해야 할 때는 꼭 group by 절을 써줘야 한다고 했으니까 group by 식당별을 해준다.
▶ 그리고 정렬 기준이 20시 주문 건수를 기준으로 내림차순이라서, 7번째 줄에 20시에 대한 내용이 있어서 order_by 7 desc라고 작성한다. 끝!!
        *️⃣ 성별, 연령별 주문 건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순)
              1- 성별, 연령별 주문 건수 집계하기

▶ 먼저 food_orders 테이블과 customers 테이블에서 customer_id가 겹치는 컬럼이어서 이 컬럼을 기준으로 두 테이블에 ▶  모두 포함되는 데이터를 불러올 것이라 inner join이용.
▶  그리고 조건문으로 나이가 10세에서 59세 사이의 데이터를 요구하고 있어서 where 절로 범위를 지정해줬다.
▶  다음은 연령 별이기 때문에 각 연령 별 범위를 case문으로 세워 분류하고 컬럼에 표시 될 이름을 각각 붙여준다.
▶  그리고 주문 건수를 카운트 해줘야 해서 count를 쓰고 모든 주문 건수에 해당하는 데이터를 카운팅 할 거라 1을 써줬다.
▶ 그리고 그룹바이로 성별과 나이를 묶어 준다.

▶ 피봇뷰를 만들기 위해 위에 작성한 베이스데이터를 subquery로 지정해주고 각 행과 열에 들어갈 컬럼을 지정한다.
▶ 여기서는 행에서 나이, 열에서 성별을 보여주고자 했다.
▶ 다음 group by와 order by를 이용하여 제시문에 맞게 작성한다.


✳️ Window Function - RANK, SUM
      • Window Function 의 기본 구조

window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

         - window_function : 기능 명을 사용해줍니다. (sum, avg 와 같이 기능명이 있습니다)
         - argument : 함수에 따라 작성하거나 생략합니다.
         - partition by : 그룹을 나누기 위한 기준입니다. group by 절과 유사하다고 생각해주시면 됩니다. 
         - order by : window function 을 적용할 때 정렬 할 컬럼 기준을 적어줍니다.

      • Window Function 의 사례
          - 순위를 매기고 싶을 때
          - 전체 주문 건수 중, a 식당이 차지하는 비율을 알고 싶은 경우
          - 2건 이상 주문을 한 소비자 중에, 처음 주문한 식당과 2번째로 주문한 식당을 같이 조회할 때

        ✔️ N 번째 까지의 대상을 조회하고 싶을 때, Rank
              • Rank : ‘특정 기준으로 순위를 매겨주는’ 기능

        *️⃣ 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기
              1- 음식 타입별, 음식점 별 주문 건수 집계하기

             2- Rank 함수 적용하기

▶  Rank 함수 적용하기
▶  윈도우 함수 중에는 () 안에 대상을 적어줘야 하는 것도 있고 생략해도 되는 것도 있다.
▶  Rank 함수의 경우는 대상의 값이 필요한 경우가 아니기 때문에, -그냥 순위 뽑기라서- 생략해줘도 된다.
▶  Rank () over () 안에 구분을 해줄 파티션 컬럼과 순서 컬럼을 적어준다.
▶  Rank () over (partition by 컬럼 order by 컬럼)
▶  Desc를 썼으니 내림차순이라 주문건수가 많은 순서대로 1위라고 지정한 것.

             3- 3위까지 조회하고 음식 타입별, 순위별 정렬

▶ Subquery로 다시 묶어주고 보여줄 컬럼을 작성한 뒤, where 절로 3위 안에 드는 것들만 제한 한다.

        ✔️ 전체에서 차지하는 비율, 누적합을 구할 때, Sum
              • Sum : 앞서 배운 합계를 구하는 기능과 동일,
                           다만, 누적합이 필요하거나 카테고리별 합계컬럼와 원본 컬럼을 함께 이용할 때 유용하게 사용
        *️⃣ 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기
              1- 음식 타입별, 음식점 별 주문 건수 집계하기

             2- 카테고리별 합, 카테고리별 누적합 구하기

▶ 서브 쿼리로 베이스 데이터를 묶어주고 카테고리 별 합과 카테고리 별 누적합을 구한다.
▶  먼저 카테고리 별 합은 sum을 이용하여 주문 건수의 합계를 구하고 음식 타입별로 구분한다고 쓴다. 
▶  그리고 누적합은 sum을 이용하여 구분하는 것까지 같지만 누적합이기 때문에 order by를 이용하여 순서대로 정렬해 더할 수 있도록 한다. 누적합 표시는 cum이라 표현한다.
▶  그리고 order by로 음식 타입별로, 주문 건수가 잘 보이기 위한 정렬을 해준다.
▶  Sum_cuisine은 덩어리 합이라고 보면 되는데, 예를 들면 cuisine_type에 미국음식의 주문건수를 모두 더했을 때 584개라고 하는 것이다.
▶ 누적합은 cnt_order로 정렬한 것으로, 1부터 1개의 주문건은 우열을 구할 수 없어서 1에 해당하는 것은 모두 같은 순위라 생각해서 1을 다 더해준 것이고, 13은 9+2+2로 나온 값이다.

✳️ 포맷 함수
      • 날짜 데이터를 지정하거나 조건에 날짜를 사용할 때 사용할 수 있는 기능이 있다.
      • 날짜 데이터도 문자나 숫자처럼 특정한 형태로 갖출 수 있다.
      • 년, 월, 일, 시, 분, 초 등의 값을 모두 갖고 있고 목적에 따라 월, 주, 일 등으로 포맷 변경 가능

    *️⃣ 날짜 데이터의 여러 포맷
          - yyyy-mm-dd 형식의 컬럼을 date type 으로 변경하기

select date(date) date_type,
 date
from payments

          - date type 을 date_format 을 이용하여 년, 월, 일, 주 로 조회해보기

▶ Date_format->date 타입의 컬럼을 date 형식으로 포맷팅해준다 라는 의미
▶ 그리고 요일에는 %w로 표시하는데 0은 일요일, 1~6까지 월~토를 의미한다.

    - 년도와 월을 포함하여 데이터를 가공하고 년, 월, 년월 별 주문 건수를 구해 3월달 주문 건수를 년도 별로 정렬

⭐ 대소문자 구분이 필요!
%Y : 2024
%y : 24
%M : June
%m : 06
%D : 24th
%d : 24

[숙제]

✏️ 음식 타입별, 연령별 주문건수 pivot view 만들기

select cuisine_type,
       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 age between 10 and 59
group by 1,2

▶ 먼저 음식타입별, 연령별 이어서 food_orders 테이블과 customers 테이블을 이용해주고 inner join을 이용하여 customer_id로 조인해줬다.
▶ 그리고 보여야하는 컬럼으로는 cuisine_type과 연령을 보이고 주문건수를 보여야 했다.
▶ 그래서 연령은 case 문으로 세분화를 해주고 주문건수는 count 함수를 이용하여 계산하였다.
또한 음식타입별, 연령별로 보여줘야 해서 group by를 이용하여 묶어줬다.

select cuisine_type,
       max(if(age=10,cnt_order, 0)) "10대",
       max(if(age=20,cnt_order, 0)) "20대",
       max(if(age=30,cnt_order, 0)) "30대",
       max(if(age=40,cnt_order, 0)) "40대",
       max(if(age=50,cnt_order, 0)) "50대" 
from
(
select cuisine_type,
       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 age between 10 and 59
group by 1,2
) a
group by 1

▶ 베이스 데이터를 subquery 처리 한 뒤에 본 쿼리로 피봇을 형성 하기 위한 max를 이용하였다.
▶ 연령별 주문건수에 대한 피봇을 위해 연령이 섭쿼리에서 지정해준 것처럼 10일 때는 주문건수 그대로 표시, 아닐 때는 0으로 표시하고 이를 10대라 명칭한다. 라는 의미로 작성했다.
▶ 그리고 마지막으로는 음식타입별 데이터를 보여야하기 때문에 group by를 이용하여 cuisine_type을 묶어줬다.

 

[고찰]

5주차 내용까지 모두 들었지만 아직 sql이 익숙하지 않다.ㅠㅠ
정정기간 동안 익숙해지게끔 코드카타를 열심히 해봐야겠다.