[DA] 30일 데이터 분석가 챌린지 4일차 (Analytic SQL - 2)

2023.07.31 - [/SQL] - [DA] 30일 데이터 분석가 챌린지 2일차

[DA] 30일 데이터 분석가 챌린지 2일차

기존 Analytic SQL 공부에 이어서 진행!

---

Analytic SQL

<Lead와 Lag Analytic SQL>

• LAG는 현재 행보다 이전 행의 값을 가져옴

출처:&nbsp;https://learnsql.com/blog/lead-and-lag-functions-in-sql/

LAG(expr, [,offset] [.default]) OVER([partition_by_clause] order_by_clause)

• Lead는 현재 행보다 다음 행의 값을 가져옴

LEAD(expr, [,offset] [.default]) OVER([partition_by_clause] order_by_clause)

LEAD와 LAG 함수의 특징

• 함수 내의 인자로 3개를 입력 받음
  • expr: 적용할 컬럼명(필수)
  • offset: 값을 가져올 행의 위치 offset값. 기본값은 1임.(선택)
  • default: 가져올 값이 없어서 NULL 값일 때 대체할 값. 가본값은 NULL임.(선택)
• partition by는 생략가능하지만, order by는 반드시 필요하다
  • 그래야 어떤 순으로 작성할 지 표를 구성하고, 값을 가져올지 결정할 수 있기 때문이다.
• window 절은 사용되지 않는다. 
• 특별한 이유가 없는 이상 order by는 오름차순으로 고정하는 것이 좋음. order by 내림차순의 lag()는 order by 오름차순의 lead()와 유사하다

<first_value와 last_value Analytic SQL>

window에서 order by로 기술된 컬럼에서 가장 첫번쨰/마지막 위치한 데이터를 추출함

FIRST_VALUE / LAST_VALUE (expression) OVER (
    [PARTITION BY partition_expression, ... ] ORDER BY sort_expression [ASC | DESC], ...
    WINDOW 절
)

• first_value는 window의 가장 첫번째에 위치한 데이터를 추출
• last_value는 window의 가장 마지막에 위치한 데이터를 추출. window 절이 rows between unbounded preceding and unbounded following이 되어야 함
  • last_value를 사용하는 것보다, first_value를 쓰고 order by를 desc로 사용하는 게 낫다!
• partition by는 생략 가능하지만, order by는 반드시 필요함
• window 절은 생략 가능하다. 생략시 range between unbounded preceding and unbounded following

<순위 함수>

[Ranking Function]() OVER([specific operation] column_name)

• cume_dist()
  • 분위수를 파티션 내의 건수로 적용하고 0-1 사이 값으로 변환 → 누적 분포 값
  • 파티션 내에서 자신을 포함한 이전 로우수/파티션 내의 로우 건수로 계산
  • cume_dist()는 원본 값에서 0-1 사이의 값으로 변환하고, percent_rank()는 rank()를 0-1 사이의 값으로 변환
• percent_rank()
  • Rank()를 0-1 사이의 값으로 정규함 시킴 → 백분위 순위 값
  • (파티션 내의 rank()값 - 1) / (파티션 내의 로우 건수 -1)
• ntile()
  • 지정된 숫자만큼의 분위를 정하여 그룹핑에 활용함
  • 주어진 매개변수 값으로 행의 개수를 균등하게 나누어 번호를 부여한다. 
  • NTILE 함수를 통해 equi-height histogram을 생성할 수 있다.
  • 예를 들어, 8개의 행을 5개의 분위로 나누어야 한다면, 1 1 2 2 3 3 4 5로 나눈다.

<역분위 함수>

PERCENTILE_DISC

PERCENTILE_DISC(expression1) WITHIN GROUP (ORDER BY expression2 [ASC | DESC]) [OVER (<partition_by_clause>)]

• percentile_disc 함수는 이산 분포 모델을 가정한 역분포 함수이다.
• 백분위 값을 입력받아 정렬된 값들 중 백분위에 해당하는 이산 값(discrete value)을 반환한다. (계산시 NULL 값을 무시)

PERCENTILE_CONT

PERCENTILE_CONT(expression1) WITHIN GROUP (ORDER BY expression2 [ASC | DESC]) [OVER (<partition_by_clause>)]

• percentile_cont 함수는 연속 분포 모델을 가정한 역분포 함수이다.
• 백분위 값을 입력받아 정렬된 값들 중에서 백분위에 해당하는 보간값(interpolated value)을 반환한다. (계산시 NULL 값을 무시)

PERCENTILE_CONT와 PERCENTILE_DISC 의 차이

• PERCENTILE_CONT와 PERCENTILE_DISC는 다른 결과를 반환할 수 있다.
  • PERCENTILE_CONT는 연속적인 보간을 수행한 이후 계산된 결과를 반환한다.
  • PERCENTILE_DISC는 집계된 값의 집합으로부터 값을 반환한다.
  • 아래 예에서 백분위 값이 0.5이면 PERCENTILE_CONT 함수는 짝수 원소를 가진 그룹에 대해 두 개의 중간값의 평균을 반환하는 반면, PERCENTILEP_DISC 함수는 두 개의 중간 값 중 첫번째 값을 반환한다. 홀수 개수의 원소를 가진 집계 그룹에 대해서는, 두 함수 모두 중간 원소의 값을 반환한다.
    • 실제로 MEDIAN 함수는 기본 백분위수 값(0.5)이 포함된 PERCENTILE_CONT의 특수한 경우이다. 자세한 내용은 MEDIAN() 을 참고한다.

[Reference]

https://learnsql.com/blog/lead-and-lag-functions-in-sql/

The LAG Function and the LEAD Function in SQL

https://www.cubrid.org/manual/ko/10.1/sql/function/analysis_fn.html#percentile-cont

집계/분석 함수 — CUBRID 10.1.0 documentation