90강 SQL 함수 정리 : PostgreSQL LEAD Function

 PostgreSQL 기초강의, PostgreSQL 기초강좌

90강 SQL 함수 정리 : PostgreSQL LEAD Function   

 

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

 

이번 시간에는

▶ PostgreSQL LEAD Function

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

 

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

---

PostgreSQL LEAD() 함수 소개   

PostgreSQL LEAD() 함수는 지정된 물리적 오프셋에서 현재 행 뒤에 오는 행에 대한 액세스를 제공합니다.
즉, 현재 행에서 LEAD() 기능은 다음 행, 다음 행의 데이터에 액세스 할 수 있습니다.
LEAD() 함수는 현재 행의 값을 현재 행 뒤에 오는 행의 값과 비교하는 데 매우 유용합니다.

 

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

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

이 구문에서

 

expression   

식은 현재 행에서 지정된 오프셋을 기준으로 다음 행에 대해 평가됩니다. 식은 단일 값으로 평가해야 하는 열, 식, 하위 쿼리일 수 있습니다. 그리고 그것은 창 기능이 될 수 없다.

 

offset   

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

 

default_value   

default_value는 오프셋이 파티션 범위를 벗어나는 경우 반환 값입니다. default_value를 생략하면 기본값이 NULL로 설정됩니다.

 

PARTITION BY clause   

PARTITION BY 절은 행을 LEAD() 함 적용되는 파티션으로 나눕니다.
PARTITION BY 절을 생략하면 기본적으로 전체 결과 집합이 단일 파티션이 됩니다.

 

ORDER BY clause   

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

---

PostgreSQL LEAD() 함수 예제   

시연을 위해 새로운 테이블을 생성합니다.

먼저 sales라는 새 테이블을 만듭니다:

CREATE TABLE sales(
year SMALLINT CHECK(year > 0),
group_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
PRIMARY KEY(year, group_id)
); 

 

둘째, sales테이블에 다음과 같은 행을 삽입합니다:

INSERT INTO 
     sales(year, group_id, amount) 
VALUES
     (2018,1,1474),
     (2018,2,1787),
     (2018,3,1760),
     (2019,1,1915),
     (2019,2,1911),
     (2019,3,1118),
     (2020,1,1646),
     (2020,2,1975),
     (2020,3,1516); 

 

셋째, 판매 테이블에서 데이터를 조회합니다:

SELECT * FROM sales; 

결과

---

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

다음 쿼리는 연도별 총판매 금액을 반환합니다:

SELECT 
     year, 
     SUM(amount)
FROM sales
GROUP BY year
ORDER BY year; 

결과

 

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

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

결과

이 예에서

• 먼저 CTE는 연도별로 요약된 매출을 반환합니다.
• 그런 다음 외부 쿼리는 LED() 함수를 사용하여 각 행에 대한 다음 연도의 매출을 반환합니다.

 

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

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

결과

---

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

다음 문장은  LEAD() 함수를 사용하여 각 제품 그룹에 대한 현재 연도의 매출과 다음 연도의 매출을 비교합니다:

SELECT
     year, 
     amount,
     group_id,
     LEAD(amount,1) OVER (
          PARTITION BY group_id
          ORDER BY year
     ) next_year_sales
FROM
     sales; 

결과

이 예에서

• PARTITION BY 절은 행을 그룹 ID로 지정된 제품 그룹(또는 파티션)으로 배포합니다.
• ORDER BY 절은 각 제품 그룹의 행을 연도별로 오름차순으로 정렬합니다.
• LEAD() 함수는 각 제품군에 대해 다음 연도의 매출을 현재 연도에서 반환합니다.

---

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

 

감사합니다.

---

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

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