SQL 정리 .5

조회한 데이터에 아무 값이 없을 경우엔?

 

없는 값을 제외하기

 

Mysql 에서는 사용할 수 없는 값일 때 해당 값을 연산에서 제외해줍니다. → 0으로 간주

즉 평균 rating 을 구하는 쿼리를 아래와 같이 작성했을 때 실제 연산에 사용되는 데이터는 다음과 같습니다.

 

select restaurant_name,
       avg(rating) average_of_rating,

// avg(rating)는 'Not given'과 같은 숫자가 아닌 평점들도 평균에 포함시킬 수 있습니다.
       avg(if(rating<>'Not given', rating, null)) average_of_rating2

// avg(if(rating<>'Not given', rating, null))은 if를 사용하고 있는데, 이는 표준 SQL 문법이 아닙니다. 대신에 이 목적으로는 CASE를 사용해야 합니다.
from food_orders
group by 1

 

// Not given'과 같은 숫자가 아닌 값을 제대로 처리하지 않으면 SQL 오류가 발생할 수 있습니다.

 

 

 

명확하게 연산을 지정해주기 위해 null 문법을 이용한다.

 

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 

NULL을 제거하지 않았을 때

NULL을 제거 했을 떄( join 시에는 inner join 과 동일함)

 

다른 값을 대신 사용할 경우

• 사용할 수 없는 값 대신 다른 값을 대체해서 사용하는 방법이 있습니다.
• 데이터 분석 시에는 평균값 혹은 중앙값 등 대표값을 이용하여 대체해주기도 합니다.
• 다른 값으로 변경하고 싶을 때, 다음 두 개의 문법을 이용할 수 있습니다.
  • 다른 값이 있을 때 조건문 이용하기 : if(rating>=1, rating, 대체값)
  • null 값일 때 : coalesce(age, 대체값)

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

 

// customer 테이블에 없는 데이터 중에 age 만 20으로 채워진 것을 확인하실 수 있습니다.

 

결과 값

 

 

조회한 데이터가 상식적이지 않은 값을 가지고 있는 경우엔?

 

상식적이지 않은 데이터의 예시

데이터가 비어있는 경우도 있지만, 상식적이지 않은 경우도 있다.

 

case 1 - 주문 고객의 나이

 

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

 

이상적이지 않은 값

 

case 2 - 결제 일자

 

결제의 경우, 비교적 최근인 일자가 있어야 상식적일 것입니다. 하지만, 데이터를 보면 1970년대와 같이 상식적이지 않은 값들을 확인할 수 있습니다.

상식적으로 과거에도 나올수가 없는 데이터가 나온 결과

 

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

 

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

select customer_id, name, email, gendor, age,
       case when age<15 then 15 //15세 미만이거나 80세 초과인 경우 값을 15, 80으로 각각 대체되었다.
            when age>80 then 80
            else age end "범위를 지정해준 age"
from customers

 

 

결과 값

 

SQL 로 Pivot Table 만들기

 

Pivot table 구조

 

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

 

Pivot table 의 기본 구조

Pivot table의 기본 구조

 

Pivot table 의 예시

집계 기준 : 일자, 시간

 

ex)

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

 

음식점별, 시간별 주문건수 집계하기

 

select a.restaurant_name,
       substring(b.time, 1, 2) hh, // payments 테이블의 시간 정보에서 시간 부분의 첫 두 자리를 추출하여 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  //payments 테이블에서 추출한 시간의 첫 두 자리가 15에서 20 사이인 경우에 해당하는 데이터만 선택합니다.

group by 1, 2

 

// 첫 번째와 두 번째 열(즉, a.restaurant_name 및 SUBSTRING(b.time, 1, 2))을 기준으로 결과를 그룹화합니다.

 

Pivot view 구조 만들기

 

Pivot Table 예시

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세 사이, 연령 순으로 내림차순)

 

성별, 연령별 주문건수 집계하기

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) // 컬럼을 기준으로 10대부터 50대까지의 연령 그룹을 설정합니다.
from food_orders a inner join customers b on a.customer_id=b.customer_id // food_orders와 customers 테이블을 customer_id를 기준으로 내부 조인합니다.
where b.age between 10 and 59 // 고객의 연령이 10세부터 59세 사이인 데이터만 필터링합니다.
group by 1, 2 // 첫 번째 (b.gender)와 두 번째 (CASE ... END) 열을 기준으로 결과를 그룹화합니다.

 

결과 값

 

이 자료 토대로 Pivot Table 구조 만들기

 

select age,
       max(if(gender='male', order_count, 0)) male, // 남성인 경우 order_count를 최대값으로 가져옵니다.
       max(if(gender='female', order_count, 0)) female // 여성인 경우 order_count를 최대값으로 가져옵니다.
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,

// 고객의 나이를 각 연령대(10대부터 50대까지)로 분류하여 age라는 이름으로 표시합니다.
       count(1) order_count // 각 연령대와 성별에 따른 주문 건수를 세어 order_count라는 이름으로 표시합니다.
from food_orders a inner join customers b on a.customer_id=b.customer_id
where b.age between 10 and 59 // 고객의 나이가 10세에서 59세 사이인 데이터만 필터링합니다.
group by 1, 2
) t
group by 1
order by age

 

결과 값

 

 

Window Function-RANK, SUM

 

Window 함수란?

준 SQL에 추가된 기능으로, 집계 함수(aggregation functions)를 사용할 때 특정 창(Window) 또는 그룹(Group) 내에서 계산할 수 있게 해줍니다. 이는 일반적인 집계 함수와 달리 개별 행에 대한 계산을 할 수 있어 매우 유용합니다.

 

Window Function 의 사례와 기본 구조

 

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

 

 

• 함수: SUM(), AVG(), ROW_NUMBER(), RANK(), NTILE(), LEAD(), LAG() 등의 일반적인 집계 함수와 분석 함수를 사용할 수 있습니다.
• PARTITION BY 절: 이 절을 사용하여 데이터를 그룹화하거나 분할할 기준을 설정합니다. 각 그룹은 별도의 창(Window)으로 처리됩니다.
• ORDER BY 절: 각 그룹 내에서 데이터의 순서를 정의합니다. 주로 순위를 매기거나 이동 평균 등을 계산할 때 사용됩니다.
• Window Frame: 기본적으로 현재 행을 중심으로 창(Window) 내의 행들을 어떻게 선택할지 정의하는 옵션입니다. 주로 ROWS BETWEEN 또는 RANGE BETWEEN 절을 사용하여 정의됩니다.

 

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

 

• Rank 는 이름에서 유추할 수 있듯이 ‘특정 기준으로 순위를 매겨주는’ 기능입니다.
• 예를 들어, 주문 건수별 순위 매기기, 결제 시간이 빠른 순으로 순위 매기기 등이 가능합니다.

ex)

음식 타입별로 주문 건수가 가장 많은 상점 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,

// RANK() 함수를 사용하여 cuisine_type 별로 주문 건수(order_count)를 기준으로 순위를 매깁니다.

//PARTITION BY cuisine_type: cuisine_type을 기준으로 그룹을 나누어 순위를 계산합니다.

//ORDER BY order_count DESC: order_count를 기준으로 내림차순으로 순위를 정렬합니다.
      order_count
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a
) b
where rn<=3 // 순위가 3 이하인 결과만 필터링합니다. 즉, 각 cuisine_type 별로 상위 3개의 레스토랑을 선택합니다.
order by 1, 4

 

DATE_FORMAT함수

 

날짜 데이터의 이해

 

• 문자타입, 숫자타입과 같이 날짜 데이터도 특정한 타입을 가지고 있습니다.
• 년, 월, 일, 시, 분, 초 등의 값을 모두 갖고 있으며 목적에 따라 ‘월’, ‘주’, ‘일’ 등으로 포맷을 변경할 수도 있습니다.
• 아래와 같은 형식의 데이터라면 날짜로 변경 가능합니다.

date_format문을 활용해 date와 time을 각각 분리가 가능하다.

 

 

 

날짜 데이터의 여러 포맷

 

yyyy-mm-dd 형식의 컬럼을 date type 으로 변경하기

 

select date(date) date_type,
       date
from payments

 

결과 값

 

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

 

결과 값

 

ex)

3월 조건으로 지정하고, 년도별로 정렬하기

 

select date_format(date(date), '%Y') y, // date 필드에서 연도를 추출하여 'y'라는 이름으로 선택합니다.
       date_format(date(date), '%m') m, // date 필드에서 월을 추출하여 'm'이라는 이름으로 선택합니다.
       count(1) order_count // 각 연도와 월에 대해 주문 건수를 세어 order_count로 표시합니다.
from food_orders a inner join payments b on a.order_id=b.order_id // food_orders와 payments 테이블을 order_id를 기준으로 내부 조인합니다.
where date_format(date(date), '%m')='03' //date 필드에서 월이 '03'인 데이터만 필터링합니다.

group by 1, 2 // 첫 번째(y)와 두 번째(m) 열을 기준으로 결과를 그룹화합니다. 즉, 각 연도와 월에 대한 데이터를 그룹화하여 집계 함수를 적용합니다.
order by 1

 

결과 값