예제로 익히는 SQL - 7회차

Suhyeon Lee·2024년 10월 16일
라이브 세션
0

목표

  • 데이터 행 간 비교, WINDOW FUNCTION 알아보기
  • WITH 구문 이해
  • 그 외 주요한 함수 숙지 및 활용

WINDOW FUNCTION

  • 모든 컬럼을 잃고 싶지 않을 때 사용
  • 행과 행의 관계를 알기 쉽도록 해 줌
    • 여러 행의 관계를 파악하기 위해 사용
  • 분석함수 또는 순위 함수로 알려져 있음

정의

  • 행과 행 간의 관계를 정의하기 위해서 제공되는 함수

역할

  • 순위, 합계, 평균, 행 위치 등을 조작할 수 있음

특징

  • 하나의 SELECT 구문에서 GROUP BY와 병행하여 사용할 수 없음
  • 윈도우 함수로 인해 결과 건수가 줄어들지는 않음
  • 윈도우 함수와 GROUP BY 구문은 둘 다 파티션(기준)을 분할한다는 의미에서 유사함
  • 다른 함수와 달리 중첩해서 사용은 못하지만, 서브쿼리에는 사용할 수 있음

종류(총 4가지)

종류함수
순위RANK, DENSE_RANK, ROW_NUMBER
집계SUM, MAX, MIN, AVG, COUNT
순서FIRST_VALUE, LAST_VALUE, LAG, LEAD
비율RATIO_TO_REPROT, PERCENT_RANK, CUME_DIST, NTILE

문법

  • SELECT 절에서 사용
    ROW_NUMBER() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)
    • ROW_NUMBER 라는 윈도우 함수를 사용하고, 그 기준을 컬럼1로 , 정렬은 컬럼2로 지정한다는 뜻
  • 다양한 윈도우 함수 사용을 통해 보다 용이한 데이터 핸들링 가능
# 윈도우 함수 기본 문법 
SELECT WINDOW_FUNCTION () OVER([PARTITION BY 컬럼] [ORDER BY 컬럼])
FROM 테이블명

# PARTITION BY는 필수 아님
# ORDER BY는 필수임

사용 예시

  • 사용자별 채팅 텍스트 데이터
    • 유저를 기준으로, 가장 마지막 일자의 CHAT_TEXT 데이터 살펴보기
USER_IDCHAT_TEXTREVIEW_DATE
피카츄안녕안녕2024-01-01
피카츄배고파2024-01-02
피카츄심심해2024-01-07
파이리불닭최고2024-01-01
파이리족발최고2024-01-02
꼬부기엽떡최고2024-01-01
꼬부기진화하고싶다!2024-01-04
  • 위 표에서 집계함수를 사용해 user_id별 가장 큰 날짜를 계산하면 아래와 같음
USER_IDMAX(REVIEW_DATE)
피카츄2024-01-07
파이리2024-01-02
꼬부기2024-01-04
# 일반 집계함수 사용 예
select user_id, MAX(review_date) 
from 테이블
group by user_id

→ 집계함수를 사용하게 되면, 우리는 CHAT_TEXT를 조회 할 수 없게 됨

🡆 CHAT_TEXT 를 같이 보고 싶을 때, 윈도우 함수를 사용

  • 윈도우 함수를 사용하면, 결과가 아래와 같이 나올 수 있음
    • row_number() over(partition by USER_ID order by REVIEW_DATE desc )as rown
    • 해석: row_number 이라는 윈도우 함수를 사용하여, 유저 ID 를 기준으로 나누고, 날짜를 오름차순으로 하여 rown 이라는 컬럼으로 명명
USER_IDCHAT_TEXTREVIEW_DATErown
피카츄안녕안녕2024-01-013
피카츄배고파2024-01-022
피카츄심심해2024-01-071
파이리불닭최고2024-01-012
파이리족발최고2024-01-021
꼬부기엽떡최고2024-01-012
꼬부기진화하고싶다!2024-01-041

→ PARTITION BY가 '피카츄 / 파이리 / 꼬부기'로 나눠서 순위 정할 수 있도록 함
→ ORDER BY가 시간이 큰 날이 rank 1 들어가도록 함

순위 예제

RANK ★★★

  • 정의
    • ORDER BY를 포함한 쿼리문에서 특정 컬럼의 순위를 구하는 함수
    • PARTITION 내에서 순위를 구할 수도 있고 전체 데이터에 대한 순위를 구할 수도 있음
    • 동일한 값에 대해서는 같은 순위를 부여하며 중간 순위를 비운 값이 출력
  • 문법
    • RANK() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)
  • 예제
# 윈도우 함수 - RANK 함수 예제 
select *,
rank() over(partition by JOB order by SALARY)
from basic.window1

DENSE_RANK

  • 정의
    • RANK와 작동법은 동일
    • 동일한 값에 대해서는 같은 순위를 부여하고 중간 순위를 비우지 않음
  • 문법
    • DENSE_RANK() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)
  • 예제
# 윈도우 함수 - DENSE_RANK 함수 예제
select *,
dense_rank() over(partition by JOB order by SALARY)
from basic.window1

ROW_NUMBER ★★★

  • 가장 많이 사용
  • 정의
    • 동일한 값이어도 고유한 순위를 부여
      cf. RANK, DENSE_RANK는 동일한 값에 대해 동일 순위를 부여
  • 문법
    • ROW_NUMBER() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)
  • 예시
# 윈도우 함수 - ROW_NUMBER 함수 예제
select *,
ROW_NUMBER() over(partition by JOB order by SALARY)
from basic.window1

순서 예제

FIRST_VALUE

  • 정의
    • 파티션별 가장 먼저 나온 값을 구하여 출력
    • 공동 등수를 인정하지 않고 처음 나온 행만 가져옴
    • MIN함수를 쓰는 것과 동일한 결과
  • 문법
    • FIRST_VALUE(컬럼1) OVER(PARTITION BY 컬럼2 ORDER BY 컬럼3)
      • FIRST_VALUE 다음에 오는 괄호 안에 컬럼명 꼭 들어가야 함!
  • 예시
# 윈도우 함수 - FIRST_VALUE 함수 예제
select *,
first_value(NAME) over(partition by JOB order by SALARY)
from basic.window1

LAST_VALUE

  • 정의
    • 파티션별 가장 마지막에 나온 값을 구하여 출력
    • 공동 등수를 인정하지 않고 나중에 나온 행만 가져옴
    • MAX함수를 쓰는 것과 동일한 결과
  • 문법
    • LAST_VALUE(컬럼1) OVER(PARTITION BY 컬럼2 ORDER BY 컬럼3)
  • 예시
# 윈도우 함수 - LAST_VALUE 함수 예제
select *,
last_value(NAME) over(partition by JOB order by SALARY)
from basic.window1

LAG ★★★

  • 정의
    • 이전 N번째의 행을 가져오는 함수
    • N은 최대값으로 3을 가지며, 기본값은 1
    • 가져올 행이 없을 경우 DEFAULT값을 지정
    • NVL이나 ISNULL함수의 기능과 동일
      • NVL: 오라클에서 사용하는 함수. COALESCE, IFNULL과 같은 기능.

LAG() Function

LAG(expression,offset, default_value) 
OVER (
    PARTITION BY partition_expression
    ORDER BY order_expresion ASC|DESC
)

Q. 안녕하세요~ SQL 세션 오늘 설명에서 LAG 함수 n은 최대값으로 3을 가진다고 하셨는데 dbeaver에서 써 보니까 4 이상의 값도 들어가서 문의드려요!
A. mysql 버전에 따라 다르기도 하고, 계속 업데이트 되고 있어서 만약 run 된다면 그대로 인지하고 사용하셔도 됩니다.
The offset is the number of rows to go back from the current row. The offset must be zero or a positive integer number. If offset is zero, then the LAG() function returns the current row. If you don’t provide the offset argument, it defaults to 1.

  • 문법
    • LAG(컬럼1) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3)
    • 또는 LAG(컬럼1, 숫자) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3) # 숫자는 최대 3으로 3번째 전 행을 의미
  • 예시
# 윈도우 함수 - LAG 함수 예제
select *
, LAG(SALARY) OVER (ORDER BY NAME) as PREV_SAL 
from basic.window1 

# 윈도우 함수 - LAG 함수 예제2: 2번째 전 값 구하기 
select *
, LAG(SALARY,2) OVER (ORDER BY NAME) as PREV_SAL 
from basic.window1 

# LAG 추가 예제
SELECT 
  *
  , LAG (salary) OVER (PARTITION BY job ORDER BY name) AS "e.g."
FROM 
  basic.window1

LEAD ★★★

  • 정의
    • 이후 N행의 값을 가져오는 함수
    • N은 최대값으로 3을 가지며, 기본값은 1
  • 문법
    • LEAD(컬럼1) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3)
    • 또는 LEAD(컬럼1, 숫자) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3) # 숫자는 최대 3으로 3번째 후 행을 의미
  • 예시
# 윈도우 함수 - LEAD 함수 예제
select *
, LEAD(SALARY) OVER (ORDER BY NAME) as PREV_SAL 
from basic.window1 

# 윈도우 함수 - LEAD 함수 예제2: 2번째 후 값 구하기 
select *
, LEAD(SALARY,2) OVER (ORDER BY NAME) as PREV_SAL 
from basic.window1

비율 예제

RATIO_TO_REPORT

  • 정의
    • 파티션 내 전체 SUM값에 대한 행별 백분율을 소수점으로 출력
    • 결과값은 0~1 사이이며, 비율의 합은 1
    • MySQL 에서는 지원하지 않음
  • 문법(Oracle)
    • RATIO_TO_REPORT(컬럼1) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3)

PERCENT_RANK ★★★

  • 정의
    • 파티션별로 가장 먼저 나오는 값을 0, 가장 마지막에 나오는 값을 1로 정하여 행 순서별 백분율을 출력
    • 구간을 나누어 백분율로 출력
  • PERCENT_RANK 함수를 활용하면 상위 퍼센트를 구할 수 있다. PERCENT_RANK는 RANK 결과값의 백분율 순위를 계산해준다. 해석하자면, 자신보다 아래 전체의 몇 퍼센트가 있다는 의미이다.(less than)
  • 문법
    • PERCENT_RANK() OVER (PARTITION BY 컬럼1 ORDER BY 컬럼2)

PERCENT_RANK Function
For a specified row, PERCENT_RANK() calculates the rank of that row minus one, divided by 1 less than the number of rows in the evaluated partition or query result set:
(rank - 1) / (total_rows - 1)
In this formula, rank is the rank of a specified row and total_rows is the number of rows being evaluated.
The PERCENT_RANK() function always returns zero for the first row in a partition or result set. The repeated column values will receive the same PERCENT_RANK() value.
PERCENT_RANK 창 함수

  • 예시
# 윈도우 함수 - PERCENT_RANK 함수 예제
select *
, PERCENT_RANK() OVER (partition by JOB order BY SALARY)
from basic.window1

※ 평균과 중앙값은 세트로 다녀야 좋음 ※

CUME_DIST

  • 정의
    • 파티션별 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 출력
  • 문법
    • CUME_DIST() OVER (PARTITION BY 컬럼1 ORDER BY 컬럼2)
  • 예시
# 윈도우 함수 - CUME_DIST 함수 예제
select *
, cume_dist() OVER (partition by JOB order BY SALARY)
from basic.window1

NTILE

  • 정의
    • 파티션별 전체 건수를 계산한 값으로 N등분한 결과를 출력
    • ROW_NUM과 결과값 같음(단, N을 우리가 정할 수 있음)
  • 문법
    • NTILE(숫자) OVER (PARTITION BY 컬럼1 ORDER BY 컬럼2)
  • 예시
# 윈도우 함수 - CUME_DIST 함수 예제
select *
, cume_dist() OVER (partition by JOB order BY SALARY)
from basic.window1

WITH

  • 하나의 테이블이 여러 번 필요할 때

WITH 구문: 테이블 재사용

구분상세
정의SQL 구문에서 사용되는 임시테이블(가상테이블)
사용 이유쿼리의 가독성 및 쿼리성능 향상
특징&장점✔️ 임시테이블의 개념을 가지며, 작성한 쿼리 내에서만 실행가능
✔️ 하나의 SQL 구문에서 여러개의 WITH 문 선언 가능
✔️ 하나의 테이블에 대한 여러 조회가 필요한 경우, WITH절 사용으로 1회 조회 및 선언하게 되어 그 가독성 및 쿼리성능이 높음
✔️ 복잡한 연산을 보다 효율적으로 처리(JOIN, UNION 등의 결과를 WITH 문에 저장)
문법WITH 임시테이블명 AS (
SELECT 컬럼1, 컬럼2, ..
FROM 테이블명
)
SELECT 임시테이블에서 불러온 컬럼 중 필요한 컬럼
FROM 임시테이블명

예시

# with 구문 활용 예시1 
with soso as  # with 뒤쪽에 임시테이블명 지정
(	select etc_str2, etc_str1, count(distinct game_actor_id)as actor_cnt 
	from basic.users
	group by etc_str2, etc_str1 # 임시테이블을 만들 때 활용할 테이블 
)
select *
from soso # WITH절에서 지정한 임시테이블명 

;

#####################################################################
# with 구문 활용 예시2 - 경험치가 가장 많은 캐릭터 정보 조회하기 
with dodo as # with 뒤쪽에 임시테이블명 지정
(	select * 
	from basic.users
)
select *
from(	select max(exp)as maxexp
		from dodo
	)as a 
inner join
	(	select *
		from dodo
	)as b 
on a.maxexp=b.exp
;

#####################################################################
# with 구문 활용 예시3 - 다중 with 구문  
with gogo as # 첫번째 with 절
(	select game_account_id, exp
	from basic.users 
	where `level` >50
),
hoho as # 두번째 with 절, with 구문은 처음 한번만 작성합니다. 
(	select distinct game_account_id, pay_amount, approved_at
	from basic.payment 
	where pay_type='CARD'
) # 이 부분에서 with 구문이 종료됩니다. 
select case when b.game_account_id is null then '결제x' else '결제o' end as gb
, count(distinct a.game_account_id)as accnt 
from gogo as a
left join hoho as b
on a.game_account_id=b.game_account_id
group by case when b.game_account_id is null then '결제x' else '결제o' end
;

그 외 중요한 함수

  • 놓치면 안 될 중요한 함수

STRING 함수

함수 이름정의문법결과
🔥CONCAT문자열을 병합할 때CONCAT(’피카츄’,’라이츄’)피카츄라이츄
🔥SUBSTRING문자열을 자를 때SUBSTRING(’피카츄라이츄’,2,4)카츄라
🔥SUBSTRING_INDEX문자열을 특정 구분기호를 통해 출력할 때SUBSTRING_INDEX(’피카츄.라이츄’, ’.’,1)피카츄
REVERSE문자열을 뒤집을 때REVERSE(’피카츄’)츄카피
특정 문자를 찾고자 할 때도 이용
LEFT, RIGHT문자열을 기준으로부터 N개 출력LEFT(’피카츄라이츄1234’, 5)피카츄라이
RIGHT(’피카츄라이츄1234’, 5)츄1234

MATH 함수

함수 이름정의문법결과
🔥 ABS절대값을 출력합니다.ABS(-1)1
🔥 ROUND숫자를 소수점 이하 자릿수에서 올림하여 출력합니다.ROUND(2.77,1)2.8
CEILING소수점을 올림하여 출력합니다.CEILING(2.77)3
FLOOR소수점을 내림하여 출력합니다.FLOOR(2.77)2
TRUNCATE숫자를 소수점 이하 자릿수에서 버림하여 출력합니다.TRUNCATE(2.77,1)2.7
RAND지정 숫자 범위 중 하나를 랜덤하게 출력합니다.ROUND(RAND()*100, 0) → 0~100 사이 랜덤값랜덤출력!

날짜 함수

함수 이름정의문법결과
🔥 NOW현재시간과 날짜를 출력NOW()현재시간출력
SYSDATESYSDATE()
CURRENT_TIMESTAMPCURRENT_TIMESTAMP
🔥DATE_ADD날짜에서 기준값 만큼 덧셈하여 출력DATE_ADD(’2024-04-03’, INTERVAL 1 DAY)2024-04-04
기준값 : YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
🔥DATE_SUB날짜에서 기준값 만큼 뺄셈하여 출력DATE_SUB(’2024-04-03’, INTERVAL 1 DAY)2024-04-02
기준값 : YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
🔥DATEDIFF두 날짜를 뺄셈하여 출력DATEDIFF('2024-04-03','2024-04-01')2
🔥DATE_FORMAT날짜를 형식에 맞게 출력DATE_FORMAT(now(),'%Y-%m-%d')현재 시간이 yyyy-mm-dd로 출력
🔥 UNIX_TIMESTAMP현재시간을 UNIXTIME 으로 구함unix_timestamp()정수출력
CURDATE현재 날짜 출력CURDATE()현재날짜출력
CURRENT_DATECURRENT_DATE()
CURTIME현재 시간 출력CURTIME()현재시간출력
CURRENT_TIMECURRENT_TIME()
YEAR날짜의 연도 출력year('2024-04-01')2024
MONTH날짜의 월 출력month('2024-04-01')4
DAY날짜의 일 출력day('2024-04-01')1

UNIXTIME(Unix Timestamp)

  • 운영체제 유닉스 등 컴퓨터에서 사용하는 시간 표현 방식
  • UTC(협정 세계시)기준 1970년 1월 1일 00:00:00로부터 몇 초가 지났는지를 나타낸 것
    • '1970년 1월 1일 00:00:00' == Unix Epoch
  • 현제 살고 있는 위치와 상관없이 어디서나 같으므로 컴퓨터에서 시간 기준으로 두기 좋음
profile
Suhyeon Lee
2 B R 0 2 B
이전 포스트

03. 데이터의 유형

다음 포스트

리스트, 튜플, 딕셔너리

0개의 댓글