(SQL) 고급 윈도우 함수 ALIASES, LAG/ LEAD, PERCENTILES

지난 글에서 설명한 윈도우 함수(Window function) 중 ALIASES, LAG/ LEAD, PERCENTILES 에 대해 알아보겠습니다.

 

🔻(참고)윈도우 함수 종류🔻

(SQL) 윈도우 함수 (Window Fuction) 개념 및 종류

 

1. Aliases

• 단일 쿼리에서 동일한 PARTITION BY, OVER 및 ORDER BY 를 활용하는 여러 window 함수를 작성하려는 경우 별칭(Alias)를 사용하면 구문을 강화할 수 있습니다.
• 저번 글에서 살펴본 아래의 윈도우 함수 구문을 보면 반복되는 구문이 상당이 많이 등장합니다.

출처 ❘ Udacity

• SELECT 절에서 PARTITION BY, ORDER BY가 세 번씩 반복적으로 사용됩니다. 
• 이렇게 반복되는 구문을 축약할 때 alias를 사용할 수 있습니다.
• alias를 사용해서 쿼리를 수정해보면 다음과 같습니다.

출처 ❘ Udacity

• SELECT 절에서 반복되는 PARTITION BY 및 ORDER BY 구문이 monthly_window 라는 별칭으로 쿼리의 끝부분에 별도로 정의되어 있습니다.
• 이렇게 정의한 monthly_window는 SELECT 절에 등장하면서 SUM, COUNT, AVG에서 동일하게 반복되는 구문을 대체하게 됩니다.

🔻(참고)PARTITION BY🔻

(SQL) 주요 Window 함수인 PARTITION BY, OVER 개념 구문 및 예시

 

 

2. LAG

• 데이터가 순차적 의미를 갖는 경우 행을 이전의 행이나 이후의 행과 비교해야 할 경우가 있습니다.
• 테이블의 이전 행에서 현재 행으로 반환하는 함수를 LAG 함수라고 합니다.
• 예를 들어, 지금까지 구매한 일반(standard) 용지의 총량을 살펴보겠습니다.

1단계) 먼저 서브쿼리를 살펴보고 무엇을 생성하는지 보겠습니다.

SELECT account_id, SUM(standard_qty) AS standard_sum
FROM orders
GROUP BY 1

• 이 쿼리는 각 계정이 전체 시간 동안 구매한 표준 용지를 제공하기 위해 각 account_id에 대한 standard_qty를 반환합니다. 
• 예를 들어, account_id 2951은 8181 단위의 표준 용지를 구매했습니다.
• 결과는 account_id 또는 standard_qty로 정렬되지 않습니다.

2단계) 외부 쿼리를 작성하고 서브 쿼리의 이름을 'sub' 로 지정합니다.

SELECT account_id, standard_sum
FROM (
    SELECT account_id, SUM(standard_qty) AS standard_sum
    FROM orders
    GROUP BY 1
    ) sub

• 위와 동일한 테이블이 반환됩니다.

3단계) standard_sum 열을 기준으로 오름차순으로 결과 집합을 생성하는 외부 쿼리에 window 함수 OVER(ORDER BY standard_sum) 를 추가합니다. LAG 함수는 외부 쿼리의 일부로 lag라는 LAG(standard_sum) OVER (ORDER BY standard_sum) AS lag와 같은 새 열을 만듭니다. 

SELECT account_id, standard_sum,
	LAG(standard_sum) OVER (ORDER BY standard_sum) AS lag
FROM (
	SELECT account_id, SUM(standard_qty) AS standard_sum
   	 FROM orders
   	 GROUP BY 1
    ) sub

 

• lag의 각 행 값은 이전 행에서 가져옵니다.
• 예를 들어, account_id 1901의 경우 lag의 값은 이전 행에서 가져옵니다. 하지만 가져올 이전 행이 없으므로 account_id 1901에 대한 lag의 값은 null이 됩니다. 
• account_id 3371의 경우 lag의 값은 이전 행(account_id 1901)에서 가져오며 이 값은 0이 됩니다.

4단계) 행 간의 값을 비교하려면 두 열(standard_sum 및 lag)을 모두 사용해야 합니다. 테이블의 각 행에 대해 standard_sum값에서 lag 값을 빼는 lag_difference 라는 새 열을 추가합니다.

SELECT account_id, standard_sum, 
LAG(standard_sum) OVER (ORDER BY standard_sum) AS lag,
standard_sum - LAG(standard_sum) OVER (ORDER BY standard_sum) AS lag_difference
FROM (
	SELECT account_id, SUM(standard_qty) AS standard_sum
  	FROM orders
   	GROUP BY 1
    ) sub

• lag_difference 각 값은 두 열(standard_sum 및 lag)간의 행 값을 비교합니다.
• 예를 들어, account_id 1901의 경우 lag 값이 null 이므로 account_id 1901의 lag_difference 값은 null 이 됩니다. 
• account_id 3371의 경우, lag_difference의 값은 값 79 와 0을 비교하여 79가 됩니다.

 

3. LEAD 

• LAG 함수와 반대로 테이블의 현재 행 다음 행에서 값을 반환합니다.
• 위와 같은 예시를 들어보겠습니다.

1단계) 서브 쿼리를 살펴보고 무엇을 생성하는지 보겠습니다.

SELECT account_id, SUM(standard_qty) AS standard_sum
FROM orders
GROUP BY 1

2단계) 서브 쿼리 이름을 'sub'으로 지정하고 상위 쿼리를 작성하겠습니다.

SELECT account_id, standard_sum
FROM (
    SELECT account_id, SUM(standard_qty) AS standard_sum
    FROM orders
    GROUP BY 1
    ) sub

 

• 위와 같은 테이블이 생성됩니다.

3단계) standard_sum열의 오름차순으로 정렬된 결과 집합을 생성하려는 외부 쿼리에 window 함수 (OVER BY standard_sum)를 추가합니다. window 함수 문의 LEAD 함수는 외부 쿼리의 일부로 lead 라는 새 열을 생성합니다.

SELECT account_id, standard_sum,
	LEAD(standard_sum) OVER (ORDER BY stnadard_sum) AS lead
FROM (
    SELECT account_id, SUM(standard_qty) AS standard_sum
    FROM orders
    GROUP BY 1
    ) sub

 

4단계) 행 간의 값을 비교하려면 두 열(standard_sum 및 lead)를 모두 사용해야 합니다. 테이블의 각 행에 대한 lead 에서 standard_sum 값을 빼는 lead_difference라는 열을 추가합니다.

SELECT account_id,standard_sum,
	LEAD(standard_sum) OVER (ORDER BY standard_sum) AS lead,
	LEAD(standard_sum) OVER (ORDER BY standard_sum) - standard_sum AS lead_difference
FROM (
    SELECT account_id,
    SUM(standard_qty) AS standard_sum
    FROM orders
    GROUP BY 1
    ) sub

 

4. PERCENTILES (백분위수)

• 순위를 매겨야 하는 레코드가 많은 경우 개별 순위(1,2,3,..)는 팀이 어느 정도의 좋은 분포를 가지고 있는지 결정하는데 도움이 되지 않습니다. 
• 백분위수는 대규모 데이터세트를 더 잘 설명하는데 도움이 됩니다.
• 예를 들어, 팀에서 상위 5%의 고객에게 연락하려고 할 수 있습니다.
• Window 함수를 사용하여 주어진 행이 속하는 백분위수(또는 사분위수 등)를 확인할 수 있습니다.
• 구문 :
  • NTILE(몇 개의 구간으로 나눌지 지정하는 숫자/ ex. 100이면 1~100까지의 백분위수)
  • OVER (ORDER BY 구간을 분할할 열)
  • AS new_column_name
• 예를 들어, 홍보를 목적으로 접촉할 고객을 우선순위에 따라서 선별해보겠습니다.
• 고객의 백분위를 나타내는 열을 만들어 구간의 개수를 지정하고 위의 구문 순서대로 입력합니다.

출처 ❘ Udacity

문제1) 'NTILE' 함수를 사용하여 주문에 대한 'standard_qty' 금액에 따라 계정을 4단계로 나누십시오. 결과 테이블에는 account_id, 각 주문에 대한 occurred_at 시간, 구매한 standard_qty 용지의 총량, standard_quartile 열의 4개 수준 중 하나가 있어야 합니다. (account_id를 기준으로 정렬하십시오.)

SELECT account_id, occurred_at, standard_qty,
	NTILE(4) OVER (PARTITION BY account_id ORDER BY standard_qty) AS standard_quartile
FROM orders
ORDER BY account_id DESC

 

 

문제2) 'NTILE' 함수를 사용하여 주문에 대한 'gloss_qty' 금액에 따라 계정을 두 개 수준으로 나누십시오. 결과 테이블에는 account_id, 각 주문에 대한 occurred_at 시간, 구매한 gloss_qty 용지의 총량, gloss_half 열의 두 수준 중 하나가 있어야 합니다. (account_id를 기준으로 정렬하십시오.)

SELECT account_id, occurred_at, gloss_qty,
	NTILE(2) OVER (PARTITION BY account_id ORDER BY gloss_qty) AS gloss_half
FROM orders
ORDER BY account_id DESC

 

 

문제3) 'NTILE' 함수를 사용하여 각 계정의 주문을 주문에 대한 'total_amt_usd' 금액으로 100개 수준으로 나누십시오. 결과 테이블에는 'account_id', 각 주문에 대한 'occurred_at' 시간, 구매한 'total_amt_usd' 용지의 총액, 'total_percentile' 열의 100개 수준 중 하나가 있어야 합니다. (account_id를 기준으로 정렬하십시오.)

SELECT account_id, occurred_at, total_amt_usd,
	NTILE(100) OVER (PARTITION BY account_id ORDER BY total_amt_usd) AS total_percentile
FROM orders
ORDER BY account_id DESC