윈도함수

윤휘영·2024년 11월 26일
0

1. 프레임

1.1 range와 row

  • range 프레임: 논리적 값 기반으로 프레임 범위 지정. order by 값을 기준으로 동작, 동일한 값은 동일한 범위로 처리.
  • row 프레임: 물리적 행 기반으로 프레임 범위 지정. 행 단위로 동작, order by 값이 동일하더라도 물리적 순서에 따라 처리.

1.2 프레임 지정

rows는 order by 없이도 프레임을 지정 가능하나, range는 order by 없이 프레임을 지정할 수 없다.

프레임 옵션설명
UNBOUNDED PRECEDINGPARTITION의 첫 번째 로우에서 윈도우가 시작
UNBOUNDED FOLLOWINGPARTITION의 마지막 로우에서 윈도우가 시작
CURRENT ROW현재 행만 포함
n PRECEDING / n FOLLOWING현재 행 기준으로 n행 이전/이후의 범위를 포함
ROWS BETWEEN ... AND ...시작과 끝 범위를 명시적으로 지정

1.3 집계함수의 기본 프레임

  • 인자 없이 over() 사용: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING이 디폴트로 적용. 모든 행에 대해 동일한 결과 반환.

  • order by만 포함: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW이 디폴트로 적용. 동일한 order by 값에 대해 동일한 결과 반환.

  • rows 제어만 포함: 테이블에 저장된 물리적 행 순서를 기준으로 동작.

  • order by와 rows 제어 포함: order by로 정렬된 행 순서를 기준으로 동작

2. 순위 윈도우 함수

  • RANK(): 결과 집합의 각 행에 대해 순위 반환. 동률 순위에 대해 동일한 숫자값 제공. 1, 2, 2, 4, 5
  • DENSE_RANK(): 결과 집합의 각 행에 대해 순위 반환. 바로 앞의 해당 특정 행에 대한 순위값에 1을 더한 수를 제공. 1, 2, 2, 3, 4
  • ROW_NUMBER(): 결과 집합의 각 행에 대해 순위 반환. 모든 행에 대해 순차적으로 번호 지정.
  • PERCENT_RANK(): 백분율 순위 값을 의미. 파티션 내에서의 행의 상대 순위를 계산하여 0과 1 사이 값으로 표현. 0, 0.25, 0.5, 0.5, 1
  • CUME_DIST(): 파티션 내에서의 누적 분포를 계산. 즉 파티션의 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율 의미.
  • NTILE(n): 정렬된 파티션의 행을 지정된 수의 그룹으로 나누어, 해당 행이 속한 그룹 번호를 반환. 파티션 별 전체 건수를 n등분하여 표현.
  • percent_rank()는 파티션에서 순위를 백분율로 표시하고, cume_dist()는 파티션에서 행 위치를 백분율로 표시함. 둘 다 디폴트로 range 사용.

3. 행 순서 윈도우 함수

  • FIRST_VALUE(): 정렬된 값 집합의 첫 번째 값 반환
  • LAST_VALUE(): 정렬된 값 집합의 마지막 값 반환
  • LAG(): 명시된 값을 기준으로 이전 행의 값 반환
  • LEAD(): 명시된 값 기준으로 이후 행의 값 반환
  • NTH_VALUE(n): 윈도우의 n번째 행의 값 반환

4. join

4.1 조건 필터링

  • where절 필터링
    join 이후 최종 결과 테이블에서 조건 필터링함. where는 결합된 결과에서 특정 조건 만족하는 행을 남길 때 사용.
  • join 조건 필터링
    결합 과정에서 필터링 수행. 결과적으로 join의 범위를 줄임.
  • where와 join 조건 필터링 구분
    조건이 join과 무관한 칼럼에 적용될 경우 where로 처리.
    join 대상 자체를 제한해야 할 때, 결합 단계에서 불필요한 데이터 결합을 방지할 때 join 조건 필터링.

4.2 자기 자신과 join

  • 작동 원리
    자기 자신과 조인할 때 테이블에 별칭을 사용해, 같은 테이블을 두 개로 나눈 것처럼 사용. 하나의 테이블에서 두 개의 가상 테이블을 만들어 join 조건에 따라 결합.
  • 사용 상황
    데이터 간의 계층적 관계를 처리하거나, 같은 테이블의 행들끼리 비교해야 할 때.

5. distinct

distinct는 중복된 값을 단일 값으로 처리. 중복의 기준은 distinct 다음에 오는 칼럼(들)
기본 원리

  • 행 단위 비교
    • select문의 열들에 대해 각 행이 고유한지 비교한다.
    • 열의 값이 동일한 행은 중복으로 간주되고 한 번만 반환된다

예 1: 단일 열에서 중복 제거

SELECT DISTINCT category
FROM popular_products;

category 열에서 중복된 값 제거, 고유한 카테고리만 반환

예2: 다중열에서 중복 제거

SELECT DISTINCT category, score
FROM popular_products;

category와 score 두 열을 기준으로 중복된 행을 제거. 행 전체가 동일해야 중복으로 간주.

6. CTE

6.1 작동 원리

cte는 sql문에서 특정 쿼리 결과를 미리 정의하여 이름을 부여한다. 이후 메인 쿼리에서 이 이름을 마치 테이블처럼 참조한다.
1. sql 엔진은 먼저 cte를 실행하고 결과 집합을 생성한다.
2. 생성된 결과 집합은 메모리에 저장되며, 메인 쿼리에서 이를 참조해 사용한다.
3. cte는 쿼리 실행 중에만 존재하며, 쿼리가 끝나면 사라진다.

6.2 CTE와 JOIN

cte는 임시 테이블처럼 작동하며, 이후 join절에서 다른 테이블과 결합 가능.

7. FIRST_VALUE와 LAST_VALUE

  • order by 없이 사용
    over()에 order by를 명시하지 않으면, 전체에서 첫 번째/마지막 값 반환. 윈도우 내에서 정렬 기준이 없으므로, 행의 물리적 순서에 따라 결과가 반환될 수 있음.
  • order by 사용
    over(order by col)을 사용하면 해당 열을 기준으로 정렬된 후 첫 번째/마지막 값 반환. 기본적으로 프레임이 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW로 설정됨. 이는 min()/max()도 마찬가지.
  • order by 사용 시 전체 윈도우에서 값을 계산하려면 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 사용

8. group by와 partition by

구분group bypartition by
목적데이터 요약(그룹별 1개 결과만 출력)그룹별 계산을 유지하되, 행 유지
결과 행 수그룹 수만큼 축소원본 데이터와 동일(행 유지)
원본 데이터 사용사라짐원본 데이터와 함께 계산 결과 제공
사용 사례"제품별 총합만"같은 요약된 결과"개별 리뷰와 그룹별 총합, 평균 등"
  • 행을 유지하며 추가 계산을 한다면 partition by를
  • 그룹화된 요약 결과만 필요하다면 group by를 사용.

9. over()

over()에 아무것도 넣지 않았다면

  • partition by가 없으므로 전체 레코드가 하나의 파티션으로 처리됨
  • order by가 없으므로 레코드의 처리 순서는 sql엔진의 내부 최적화에 따라 결정됨
  • 윈도우 프레임: 기본 프레임이 설정됨
    • over()절 기본 프레임: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 즉 모든 행. order by가 있다면 이를 따라 프레임이 계산
    • 집계 함수: 전체 범위 포함
    • 순위 함수: 정렬 기준이 없으므로 순위는 무작위처럼 보일 수 있음
윈도함수 유형기본 프레임
집계함수ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
partition by 사용ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
순위함수RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

10. 서브쿼리

10.1 select절 서브쿼리

  • 작동 원리
    select 절에서 서브쿼리는 행 단위로 실행됨. 주쿼리의 현재 행에 대해 서브쿼리가 실행되고, 그 결과를 추가로 출력.
  • 사용 상황
    특정 칼럼에 대한 연산이나 집계 값을 계산해 결과에 추가해야 할 때, 서브쿼리가 행 단위로 계산되어 각 행마다 결과를 반환
  • 예시
    각 제품의 리뷰 건수와 해당 제품의 총 리뷰 건수를 함께 출력.
SELECT product_id, score,
       (SELECT COUNT(*) FROM review r2 WHERE r2.product_id = r1.product_id) AS total_reviews
FROM review r1;

10.2 from절 서브쿼리

  • 작동 원리
    from절의 서브쿼리는 주쿼리보다 먼저 실행되어 임시 테이블을 생성. 주쿼리는 이 임시 테이블에 대해 쿼리 실행. 서브쿼리에서 반환되는 칼럼은 주쿼리에서 사용 가능.
  • 사용 상황
    서브쿼리에서 반환된 결과를 임시 테이블처럼 사용해 추가 연산을 수행할 때.
  • 예시
    리뷰 점수의 평균 이상인 제품만 출력
SELECT product_id, avg_score
FROM (
    SELECT product_id, AVG(score) AS avg_score
    FROM review
    GROUP BY product_id
) AS sub
WHERE avg_score > 4.0;

10.3 where절 서브쿼리

  • 작동 원리
    서브쿼리는 주쿼리보다 먼저 실행되어 주쿼리의 where 조건에서 스칼라값 또는 리스트 또는 존재 여부 반환. 주쿼리는 서브쿼리의 결과를 이용해 필터링.
  • 사용 상황
    특정 조건에 따라 행을 필터링할 때.
  • 예시
    평균 점수 이상인 제품의 리뷰만 출력
SELECT *
FROM review
WHERE score >= (SELECT AVG(score) FROM review);

1) 스칼라값 반환

서브쿼리가 하나의 값을 반환하여 조건으로 사용된다.

SELECT *
FROM popular_products
WHERE score = (
    SELECT MAX(score)
    FROM popular_products
);

서브쿼리는 popular_products 테이블에서 최대 점수(예: 94)점을 반환한다. 주쿼리는 where score = 94 조건으로 필터링한다.

2) 리스트 반환

서브쿼리가 여러 값을 반환하고, 주쿼리는 이 값들을 조건으로 사용한다.

SELECT *
FROM popular_products
WHERE category IN (
    SELECT DISTINCT category
    FROM popular_products
    WHERE score > 80
);

서브쿼리는 점수가 80보다 큰 상품의 카테고리 리스트를 반환한다(예: ["action", "drama"]). 주쿼리는 where category in ("action", "drama") 조건으로 레코드를 필터링한다.

3) 존재 여부 반환

서브쿼리의 결과가 존재하는지 여부에 따라 조건을 충족한다.

SELECT *
FROM popular_products p
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.product_id = p.product_id
);

서브쿼리는 orders 테이블에 o.product_id = p.product_id를 만족하는 상품이 있는지 확인한다. exists는 서브쿼리가 한 행 이상 반환하면 true로 평가된다.

profile
윤휘영
이전 포스트

db설계

다음 포스트

스트림

0개의 댓글