91강 SQL 함수 정리 : PostgreSQL LAG Function

 PostgreSQL 기초강의, PostgreSQL 기초강좌

91강 SQL 함수 정리 : PostgreSQL LAG Function   

 

SQL 함수 정리 91강 시작합니다.

 

이번 시간에는

▶ PostgreSQL LAG Function

에 대해서 알아보는 시간입니다.

 

이번 시간에는 PostgreSQL LAG() 함수를 사용하여 특정 물리적 오프셋에서 현재 행 앞에 오는 행에 액세스 하는 방법에 대해 알아봅니다.

---

PostgreSQL LAG() 함수 소개   

PostgreSQL LAG() 함수는 지정된 물리적 오프셋에서 현재 행 앞에 오는 행에 대한 액세스를 제공합니다. 즉, 현재 행에서 LAG() 함수는 이전 행 또는 이전 행 앞의 행의 데이터에 액세스 할 수 있습니다.

LAG() 함수는 현재 행과 이전 행의 값을 비교하는 데 매우 유용합니다.

다음은 LAG() 함수의 구문을 보여줍니다:

LAG(expression [, offset [, default_value]]) 
OVER (
    [PARTITION BY partition_expression,... ]
    ORDER BY sort_expression [ASC | DESC],...
)  

이 구문은

 

expression   

지정된 간격 띄우기에서 현재 행 앞에 오는 행에 대해 식을 계산합니다. 열, 식 또는 하위 쿼리일 수 있습니다. 식은 단일 값을 반환해야 하며 창 함수가 될 수 없습니다.

 

offset   

오프셋은 데이터에 액세스 할 현재 행 앞에 오는 행의 수를 지정하는 양의 정수입니다. 오프셋은 식, 하위 쿼리 또는 열이 될 수 있습니다. 지정하지 않으면 기본값은 1입니다.

 

default_value   

LAG() 함수는 오프셋이 파티션 범위를 벗어나는 경우 default_value를 반환합니다. default_value를 생략하면 함수가 NULL을 반환합니다.

 

PARTITION BY clause   

PARTITION BY 절은 행을 LAG() 함수가 적용되는 파티션으로 나눕니다.

PARTITION BY 절을 생략하면 기본적으로 함수는 전체 결과 집합을 단일 파티션으로 처리합니다.

 

ORDER BY clause   

ORDER BY 절은 LAG() 함수가 적용되는 각 파티션의 행 순서를 지정합니다.

---

PostgreSQL LAG() 함수 예제   

시연을 위해 LEAD() 함수 강의에서 생성한 sales 테이블을 사용합니다.

다음은 sales 테이블의 데이터입니다:

sales 테이블

---

1) 결과 집합 예제에 PostgreSQL LAG() 함수 사용   

이 예에서는 LAG() 함수를 사용하여 현재 연도와 전년도의 판매 금액을 반환합니다:

WITH cte AS (
     SELECT 
          year, 
          SUM(amount) amount
     FROM sales
     GROUP BY year
     ORDER BY year
) 
SELECT
     year, 
     amount,
     LAG(amount,1) OVER (
          ORDER BY year
     ) previous_year_sales
FROM
     cte; 

결과

이 예에서

• 첫째, CTE는 연도별로 요약한 순매출을 반환한다.
• 그런 다음 외부 쿼리는 LAG() 함수를 사용하여 각 행에 대한 전년도 매출을 반환합니다. 첫 번째 행의 이전 연도가 없으므로 첫 번째 행의 이전_년_매출 열에 NULL이 있습니다.

 

이 예제에서는 두 개의 일반적인 표 식을 사용하여 현재 및 이전 연도 간의 판매 차이를 반환합니다:

WITH cte AS (
     SELECT 
          year, 
          SUM(amount) amount
     FROM sales
     GROUP BY year
     ORDER BY year
), cte2 AS (
     SELECT
          year, 
          amount,
          LAG(amount,1) OVER (
               ORDER BY year
          ) previous_year_sales
     FROM
          cte
)     
SELECT 
     year, 
     amount, 
     previous_year_sales,  
     (previous_year_sales - amount) variance
FROM 
     cte2; 

결과

---

2) 파티션 예제에서 PostgreSQL LAG() 함수 사용   

이 예에서는 LAG() 함수를 사용하여 현재 연도의 매출과 각 제품 그룹의 전년도 매출을 비교합니다:

SELECT
     year, 
     amount,
     group_id,
     LAG(amount,1) OVER (
          PARTITION BY group_id
          ORDER BY year
     ) previous_year_sales
FROM
     sales; 

결과

이 예에서

• PARTITION BY 절은 행을 그룹 ID로 지정된 제품 그룹(또는 파티션)으로 배포합니다.
• ORDER BY 절은 각 제품 그룹의 행을 연도별로 오름차순으로 정렬합니다.
• LAG() 기능은 각 파티션에 적용되어 전년도 매출을 반환합니다.

---

PostgreSQL LAG() 함수를 사용하여 특정 물리적 오프셋에서 현재 행 앞에 오는 행에 액세스 하는 방법에 대해 배웠습니다.

 

감사합니다.

---

본 강의는 PostgreSQL Tutorial을 참조합니다.  https://www.postgresqltutorial.com/

Do it! SQL을 찾아 주셔서 감사합니다. 공감 ♥ , 댓글이 큰 힘이 됩니다.