SQL Challenge 세션 #1 윈도우 함수, WITH절

240521 Today I Learn

Window Function

💡 Window Function
행과 행의 관계를 정의하기 위해 제공되는 함수로 모든 컬럼을 잃고 싶지 않을 때 사용한다.

# 윈도우 함수 기본문법 
SELECT WINDOW_FUNCTION () OVER([PARTITION BY 컬럼] [ORDER BY 컬럼])
FROM 테이블명​

 

< 윈도우 함수의 종류> 

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

순위

💡 ROW_NUMBER
' 줄세우기를 한다.' →  줄세우기를 한다고 했을 때 중복 순위 발생 불가함.
어떻게든 순위를 지정함.

ROW_NUMBER() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)

SELECT *, ROW_NUMBER() OVER(PARTITION BY job ORDER BY salary)
from basic.window1

 

💡 RANK()
만점자가 두명이라면? 동점자가 발생할 때 공동 순위를 부여한다.
그러나 공동 순위가 있는 만큼, 뒷 순위가 없어진다.
→ 10명중 만점자가 3명이라면, 99점을 받은 사람은 4등이 된다.

RANK() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)

SELECT *, RANK() OVER(PARTITION BY job ORDER BY salary)
from basic.window1

💡 DENSE_RANK()

RANK()와 마찬가지로 공동 순위를 부여한다. 차이점은 그 뒤에오는 순위를 매기는 방식으로,
DENSE_RANK의 경우 공동순위의 뒷순위는 공동순위자의 수에 영향을 받지 않는다.
→ 10명중 만점자가 3명이라면, 99점을 받은 사람은 2등이 된다.

DENSE_RANK() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)​​

SELECT *, DENSE_RANK() OVER(PARTITION BY job ORDER BY salary)
from basic.window1

순서

💡 FIRST_VALUE 
파티션별 가장 먼저 나온 값을 구해 출력한다. 공동 등수는 인정하지 않고, 처음 나온 행만 가져온다. 

FIRST_VALUE(컬럼1) OVER(PARTITION BY 컬럼2 ORDER BY 컬럼3)​

SELECT *, FIRST_VALUE(name) OVER(PARTITION BY job ORDER BY salary)
FROM basic.window1

💡 LAST_VALUE 
파티션별 가장 마지막에 나온 값을 구해 출력한다. 공동 등수는 인정하지 않고, 나중에 나온 행만 가져온다. 

LAST_VALUE(컬럼1) OVER(PARTITION BY 컬럼2 ORDER BY 컬럼3)​

SELECT *, LAST_VALUE(name) OVER(PARTITION BY job ORDER BY salary)
FROM basic.window1

💡 LAG
이전 N 번째의 행을 가져오는 함수. N 은 최대값으로 3을 가지며, 기본값은 1 이다.

 LAG(컬럼1, 숫자) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3)​

SELECT *, LAG(salary,2) OVER (ORDER BY name) as prev_sal
from basic.window1

💡 LEAD 
이후 N행의 값을 가져오는 함수로 LAG와 반대된다. 

LEAD(컬럼1, 숫자) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3)​

SELECT * , LEAD(salary,2) OVER (ORDER BY name) as prev_sal
from basic.window1

비율

💡 CUME_DIST 
파티션별 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을  출력한다.

CUME_DIST() OVER (PARTITION BY 컬럼1 ORDER BY 컬럼2)​

SELECT * , cume_dist() OVER (partition by JOB order BY SALARY)
FROM basic.window1

WITH 절  vs. 서브쿼리

임시테이블을 생성하는 WITH 절의 경우 재사용성이 좋고, 서브쿼리의 경우는 그때마다 작성해줘야 하기 때문에 같은 내용이더라도 여러번 반복해서 사용하기에는 불편할 수 있다. 또한 가독성 역시 WITH절이 좋기 때문에 2회 이상 사용하는 경우 WITH절을 사용하는 것이 좋다.

WITH절

Leetcode #602 Write a solution to find the people who have the most friends and the most friends number. The test cases are generated so that only one person has the most friends.
→ 친구가 가장 많은 사람의 이름과, 친구의 수를 구하라.

 

# WITH절을 활용해 임시 테이블 생성
WITH customer2 as (
                    SELECT id, count(*) num
                    FROM (
                            SELECT requester_id id 
                            FROM RequestAccepted
                            
                            UNION ALL
                            
                            SELECT accepter_id id 
                            FROM RequestAccepted
                         )a
                    GROUP BY 1 
                   )

# WITH절 활용하기
SELECT id, num
FROM customer2
WHERE num = (SELECT max(num) 
             FROM customer2)

WITH절을 이용해 customer2라는 이름으로 임시 테이블을 생성했다. 이렇게 복잡한 식을 서브쿼리를 이요해 매번 반복 작성하면 굉장히 불편할텐데, 이렇게 customer2라는 임시 테이블을 WITH절을 이용해 만들어두고 호출하면 번거로운 작업을 줄일 수 있다.

서브쿼리

일반 서브쿼리

• 단일행 서브쿼리 : 하나의 행만 반환하는 서브쿼리. 단일행 비교 연산자 (=, >, <, <>) 만 사용

SELECT *
FROM T_PEARSON A 
WHERE A.PERSON_NO = ( SELECT B.PERSON_NO
                      FROM T_PERSON B
                      WHERE B.NAME = 'Aiden')

• 다중행 서브쿼리 : 여러개의 행을 반환하는 서브쿼리. 다중행 비교 연산자 (IN, ANY, SOME, EXISTS 등)를 사용

SELECT A.NAME
  FROM T_PERSON A
 WHERE A.PERSON_NO IN (SELECT B.PERSON_NO
                         FROM T_GRADE B
                        WHERE GRADE = 'A')

• 다중 컬럼 서브쿼리 : 두 개 이상의 컬럼을 리턴하는 경우

SELECT *
  FROM T_PERSON A
 WHERE (A.NAME, A.AGE) IN (SELECT B.NAME, B.AGE
                             FROM T_PERSON B 
                            WHERE B.PERSON_NO < 3)

스칼라 서브쿼리 : SELECT 절에서 하나의 컬럼처럼 사용됨

SELECT col1
     , (SELECT * ...)

인라인 뷰 : 하나의 테이블처럼 사용

FROM ( SELECT ... ) AS a

쿼리 스타일 가이드

1. 예약어(SELECT, FROM..)는 대문자로 작성한다.
2. 컬럼 이름은 snake_case로 작성한다.
   
   • snake_case 🐍 : snake_case 와 같이 모든 단어들을 언더바(_)로 연결한다.
   • Camel Case 🐫 : Camel Case와 같이 띄어쓰기하는 경우 단어의 앞을 대문자로 작성한다.
3. 테이블 명이나 컬럼 이름은 가능한 명시적으로 작성한다.
4. 왼쪽 정렬을 맞춰준다.
5. 예약어나 컬럼은 한줄에 하나씩 작성한다.