SQL-마지막 수업

예전에 했던 내용들 중에 헷갈렸던 부분은 다시 작성했다.

SELECT 
  cuisine_type,
  restaurant_name,
  COUNT(1) AS cnt_order
FROM food_orders
GROUP BY 1,2;

 

 

COUNT 함수

COUNT(1)은 선택된 행의 수를 계산합니다. 여기서 1은 모든 행을 의미하며, 이는 COUNT(*)와 동일합니다. 이 함수는 각 그룹에 몇 개의 행이 있는지 셉니다. 이 경우, 각 cuisine_type과 restaurant_name 조합에 대해 총 주문 건수를 계산합니다.

ROUP BY 절:
GROUP BY 1,2는 결과를 첫 번째와 두 번째 열(여기서는 cuisine_type과 restaurant_name)에 따라 그룹화합니다. 이는 쿼리 결과를 해당 열의 값이 같은 행끼리 그룹으로 묶는 것을 의미합니다. 즉, 같은 음식 유형(cuisine_type)과 같은 음식점 이름(restaurant_name)을 가진 주문들을 하나의 그룹으로 봅니다.
그룹화된 각 그룹에 대해 COUNT(1)이 실행되어, 해당 그룹에 속하는 행의 수(여기서는 주문 수)를 계산합니다.

 

 

<없는 값을 제외하기>

• Mysql 에서는 사용할 수 없는 값일 때 해당 값을 연산에서 제외해줍니다. → 0으로 간주
• 즉 평균 rating 을 구하는 쿼리를 아래와 같이 작성했을 때 실제 연산에 사용되는 데이터는 다음과 같습니다.

select restaurant_name,
       avg(rating) average_of_rating,
       avg(if(rating<>'Not given', rating, null)) average_of_rating2
from food_orders
group by 1

 

 

 

 

 

 

 

<조회한 데이터가 상식적이지 않은 값을 가지고 있다면 어떻게 해야할까?>

보통 음식을 주문한 고객은 20세 이상인 성인인 경우가 많습니다.데이터에 보면 2세와 같이 상식적이지 않은 값들을 확인할 수 있습니다.

 

 

 

 

조건문으로 값의 범위를 지정하기

• 조건문으로 가장 큰 값, 가장 작은 값의 범위를 지정해 줄 수 있습니다. → 상식적인 수준 안에서 범위를 지정해줍니다.
• 위의 나이의 경우 아래와 같은 범위를 지정해 줄 수 있습니다.

select customer_id, name, email, gender, age,
       case when age<15 then "어린아이"
            when age>80 then "노인"
            else age end "범위를 지정해준 age"
from customers

 

 

<SQL 로 Pivot Table 만들어보기>

• Pivot table 이란? : 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것을 의미합니다
• Pivot table 의 기본 구조

Pivot Table

 

[실습] 음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)

 

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

 

결과값)

 

 

<Window Function - RANK, SUM>

 

1. RANK

N 번째까지의 대상을 조회하고 싶을 때, Rank

SELECT 
	cuisine_type,
	restaurant_name,
	cnt_order,
	ranking
FROM 
(
SELECT cuisine_type,restaurant_name,cnt_order,
RANK() over (PARTITION by cuisine_type order by cnt_order desc) ranking
FROM 
(
SELECT 
	cuisine_type ,
	restaurant_name ,
	COUNT(1) cnt_order 
FROM food_orders fo 
group by 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
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a

 

결과)

 

 

 

<날짜 포맷과 조건까지 SQL 로 한 번에 끝내기 (포맷 함수)>

 

1. date type 을 date_format 을 이용하여 년, 월, 일, 주 로 조회해보기
   1. 년 : Y (4자리), y(2자리)
   2. 월 : M, m
   3. 일 : d, e
   4. 요일 : 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

 

결과)