코딩/SQL

SQL - Day3

americanoallday 2025. 1. 16. 16:00

1. 예상하지 못한 값이 Query 결과에 나올 때 어떻게 처리해야 할까?

없는 값 제외시켜주기
예1) 다른 형식, 제거 값을 null로 치환

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

예2) 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



다른 값 대신 사용하기 : coalesce 함수

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

* coalesce : b.age가 null일 경우 20으로 대체시키는 함수

조건문으로 값의 범위 지정하기 
예) 성인 부터 조회를 해야하는데 나이가 2살이 포함되어 있거나, 최근 10년 이내 데이터를 보고 싶은데, 1960년대 데이터가 포함되어 있거나 등, 

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

 

 

2. 엑셀에서 해야하는 Pivot, SQL 로 한 번에 구현할 수 있을까?

* 피벗 테이블이란 : 

  • 행과 열을 기준으로 데이터를 그룹화하여 합계, 평균, 개수 등을 계산 가능
  • 합계, 평균, 최대값, 최소값, 비율 등의 계산식을 자동으로 지원
  • 요약된 데이터를 클릭하면 상세 데이터를 확인 가능
  • 피벗 테이블을 기반으로 피벗 차트를 생성하여 시각적으로 데이터를 표현 가능

SQL에서 피벗 테이블 생성하기
1. 베이스 데이터 생성

2. 베이스 데이터를 이용하여 피벗 테이블 뷰 생성

실습 1) 음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)
> food_orders 테이블 (구성 컬럼 정보 : order_id, customer_id, restaurant_name, cuisine_type, price, quantity, day_of_the_week, rating, food_preparation_time, delivery_time, addr)

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. 피벗 테이블 뷰 생성

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



실습 2) 성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순)

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 desc



Window Function

행(row) 단위로 계산을 수행하는 기능으로, 결과를 그룹별로 나누지 않고 전체 데이터에서 계산된 값과 함께 각 행에 결과를 출력하는 것.

그룹별 통계나 순위를 계산하면서도 원래의 데이터 행을 유지하고 싶을 때 사용.

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

실습 1) 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기

1. 음식 타입별, 음식점별 주문 건수 집계하기

2. Rank 함수 적용하기

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,
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a
) b
where rn<=3


실습 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

 

  • SUM(cnt_order) OVER (PARTITION BY cuisine_type) :
    • cuisine_type별로 cnt_order의 합계를 계산합니다. 이 경우에는 모든 행이 동일한 값(해당 cuisine_type 그룹의 합계)을 가집니다.
  • SUM(cnt_order) OVER (PARTITION BY cuisine_type ORDER BY cnt_order) :
    • cuisine_type별로 그룹을 나누고, cnt_order를 기준으로 누적합을 계산합니다. 즉, 같은 cuisine_type 그룹 내에서 cnt_order가 작은 값부터 더하면서 누적합을 생성합니다.
    • 일반적으로 **ORDER BY**는 결과를 정렬하는 데 사용되지만, 윈도우 함수에서 **ORDER BY**는 계산의 순서를 정의하는 역할을 합니다. 따라서 **SUM()**과 같은 윈도우 함수 안에서 사용되는 **ORDER BY**는 누적합을 계산할 때 기준이 되는 순서를 설정합니다.

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

날짜 데이터 : 년, 월, 일, 시, 분, 초 등의 값을 모두 갖고 있으며 목적에 따라 ‘월’, ‘주’, ‘일’ 등으로 포맷을 변경할 수도 있음


date_format 함수 :

날짜와 시간을 원하는 형식으로 변환하여 문자열로 반환하는 함수

DATE_FORMAT(date, format)

• date: 변환하려는 날짜 또는 시간 값.

• format: 반환할 날짜와 시간 형식. 형식은 문자열로 작성되며, 특정 형식 지시자를 사용합니다.

예시)
1. date컬럼 값을 date 데이터로 변환 : date(date)
2. 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

* 0 : 일, 1: 월 ~ 6:토

날짜관련 함수

 

1. DATEDIFF()

두 날짜 간의 차이를 일 단위로 계산합니다.

주의:

• 반환 값은 양수 또는 음수일 수 있습니다.

• 시간 정보(DATETIME)는 무시됩니다.

DATEDIFF(date1, date2)

• date1: 기준 날짜.

• date2: 비교 대상 날짜.

• 결과: date1 - date2의 **날짜 차이(일)**를 반환합니다.

예제)

SELECT DATEDIFF('2025-01-16', '2025-01-01') AS days_diff;

• 결과: 15 (2025년 1월 16일 - 2025년 1월 1일)

2. TIMESTAMPDIFF()

두 날짜 간의 차이를 특정 단위(예: 연, 월, 일, 시 등)로 계산합니다.

주의:

• unit이 단위별로 결과를 조정합니다. 예를 들어:

• MONTH: 두 날짜 간의 전체 달 차이.

• YEAR: 두 날짜 간의 전체 연도 차이.

TIMESTAMPDIFF(unit, datetime1, datetime2)

• unit: 계산 단위(YEAR, MONTH, DAY, HOUR, MINUTE, SECOND).

• datetime1: 기준 날짜/시간.

• datetime2: 비교 대상 날짜/시간.

• 결과: datetime1 - datetime2의 차이를 지정한 단위로 반환합니다.

예제)

SELECT TIMESTAMPDIFF(YEAR, '2018-01-01', '2025-01-16') AS year_diff;

• 결과: 7 (7년 차이)

3. DATE_ADD() & DATE_SUB()

날짜에 특정 기간을 더하거나 빼는 함수입니다.

DATE_ADD(date, INTERVAL value unit)
DATE_SUB(date, INTERVAL value unit)

• date: 기준 날짜.

• INTERVAL value unit: 더하거나 뺄 기간 (예: 5 YEAR, 3 MONTH, 10 DAY 등).

• 결과: 계산된 새로운 날짜를 반환합니다.

 

예제)

SELECT DATE_ADD('2025-01-16', INTERVAL 5 DAY) AS new_date;

• 결과: 2025-01-21 (5일 추가)

SELECT DATE_SUB('2025-01-16', INTERVAL 1 MONTH) AS new_date;

• 결과: 2024-12-16 (1달 감소)

4. CURDATE()

현재 날짜를 반환합니다.

• 반환 값: 현재 날짜 (YYYY-MM-DD 형식).

• 시간 정보는 포함되지 않습니다.

CURDATE()

 

예제)

SELECT CURDATE() AS today_date;

• 결과: 2025-01-16

5. NOW()

현재 날짜와 시간을 반환합니다.

NOW()

 

예제)

SELECT NOW() AS current_datetime;

• 결과: 2025-01-16 15:30:45

6. DATE_FORMAT()

날짜를 특정 형식으로 변환합니다.

DATE_FORMAT(date, format)

• date: 변환할 날짜.

• format: 날짜 형식을 지정하는 문자열.

• 주요 형식:

• %Y: 연도 (예: 2025).

• %m: 월 (예: 01).

• %d: 일 (예: 16).

• %H: 시간 (24시간제).

• %i: 분.

• %s: 초.

 

예제)

SELECT DATE_FORMAT('2025-01-16', '%Y/%m/%d') AS formatted_date;

• 결과: 2025/01/16

7. YEAR(), MONTH(), DAY()

특정 날짜에서 연도, 월, 일 정보를 추출합니다.

YEAR(date)
MONTH(date)
DAY(date)

 

예제)

SELECT YEAR('2025-01-16') AS year_part, 
       MONTH('2025-01-16') AS month_part, 
       DAY('2025-01-16') AS day_part;

 

요약

• DATEDIFF(): 두 날짜의 차이를 일 단위로 반환.

• TIMESTAMPDIFF(): 두 날짜의 차이를 특정 단위로 반환.

• DATE_ADD() & DATE_SUB(): 날짜에 특정 기간을 더하거나 빼는 함수.

• CURDATE(): 현재 날짜 반환.

• NOW(): 현재 날짜와 시간을 반환.