[Oracle] LAST_VALUE

작성일자 : 2024-08-15

Ver 0.1.1

 

1. Intro

프로젝트를 하다 새로운 요건을 마주했다.

요건을 간단히 요약하자면, 평가 대상 항목에 대해서 분기별로 평가를 하는데, 평가 대상이지만 특정 분기에 평가를 하지 않았다면 가장 최신 값으로 불러와 적용해 달라는 요건이었다.

간단히 요약을 하긴 했지만, 그 과정에서 꽤나 복잡한 로직이 포함되어야 해서 해당 요건을 Tableau 대시보드에서 구현하기엔 어렵다고 생각하고, Oracle에서 해결해보고자 했다.

 

이리저리 검색 끝에 LAST_VALUE라는 함수의 존재를 알게 되었고, 이를 적용하면 요건을 구현할 수 있을것 같다는 판단을 했다.

SQL Language Reference

---

2. How to

2-1) 기본 사용법

기본 사용법은 상단의 Syntax에도 나와있듯이

LAST_VALUE(대상 컬럼 <RESPECT or IGNORE NULLS>) OVER (PARTITION BY <파티션 컬럼들> ORDER BY <정렬컬럼들>) 이다.

 

LAST_VALUE 함수에 대한 이해를 완전히 하기 위해서는 PARTITION이나 조회 범위같은 분석함수의 개념에 대해서 알아야 하는데, Partition은 분석대상이 되는 범위를 나누는 기준이 된다. ORDER BY는 Partition내에서 할당한 컬럼들로 정렬을 하게 된다.

 

이때 RESPECT NULLS or IGNORE NULLS 옵션을 지정할 수 있는데, 말 그대로 NULL 값을 포함 시킬 것인지 무시할 것인지를 지정해주는 옵션이다.

 

 

분석함수의 개념에 대한 정보가 더 필요하다면 아래 게시물을 보고, 이해하면 LAST_VALUE 함수를 이해하는데에도 수월 할 것이다.

[SQL]PARTITION/WINDOW

말로 설명하면 추상적이니 적용 예시를 통해 눈으로 보도록 하자.

 

2-2) 적용 예시

앞서 설명한 요건을 위해서 DUAL로 TEMP Table을 만들었다.

테이블의 특징은 중간중간 Account 마다 Value 컬럼이 NULL인 Row가 있다는 것이다.

요건은 고객별로 가장 최근에 입력한 값을 Null 자리에 대체하는 것이다. 

이러한 상황에서 나는 LAST_VALUE  함수를 사용했다.

WITH TEMP AS (
SELECT 'A' AS ACCOUNT, '2023' AS YEAR, 'Q1' AS QUARTER, 1 AS VALUE FROM DUAL
UNION
SELECT 'A' AS ACCOUNT, '2023' AS YEAR, 'Q2' AS QUARTER, NULL AS VALUE FROM DUAL
UNION
SELECT 'A' AS ACCOUNT, '2023' AS YEAR, 'Q3' AS QUARTER, NULL AS VALUE FROM DUAL
UNION
SELECT 'A' AS ACCOUNT, '2023' AS YEAR, 'Q4' AS QUARTER, 3 AS VALUE FROM DUAL
UNION
SELECT 'A' AS ACCOUNT, '2024' AS YEAR, 'Q1' AS QUARTER, NULL AS VALUE FROM DUAL
UNION
SELECT 'B' AS ACCOUNT, '2023' AS YEAR, 'Q1' AS QUARTER, 2 AS VALUE FROM DUAL
UNION
SELECT 'B' AS ACCOUNT, '2023' AS YEAR, 'Q2' AS QUARTER, NULL AS VALUE FROM DUAL
UNION
SELECT 'B' AS ACCOUNT, '2023' AS YEAR, 'Q3' AS QUARTER, 4 AS VALUE FROM DUAL
UNION
SELECT 'B' AS ACCOUNT, '2023' AS YEAR, 'Q4' AS QUARTER, NULL AS VALUE FROM DUAL
UNION
SELECT 'B' AS ACCOUNT, '2024' AS YEAR, 'Q1' AS QUARTER, 5 AS VALUE FROM DUAL)
SELECT ACCOUNT
      ,YEAR
      ,QUARTER
      ,VALUE AS "BEFORE"
      ,LAST_VALUE(VALUE IGNORE NULLS) OVER (PARTITION BY ACCOUNT 
                                                ORDER BY CONCAT(YEAR, QUARTER)) AS "AFTER"
  FROM TEMP

 

NULL을 무시하고 가장 최근 값을 불러오도록 옵션을 주었고,

Account 별로 구분지어서 조회르 해야하니 Partition의 대상은 Accout 컬럼을 할당했다.

그리고 기간기준으로 가장 최근 값을 불러와야하니 정렬도 중요한 기준이된다. 이에 YEAR와 QUARTER를 합친 필드로 정렬을 했다.

결과는 위 이미지와 같다. Before 행이 TEMP에 입력된 값이고 AFTER가 LAST_VALUE를 적용한 값이다.

중간중간 NULL이 있었지만, Account를 파티션으로 가장 최근 기간에 입력된 Value로 NULL을 채웠다.

 

이를 Tableau에서 적용하려면 사실 Null인 row 자체가 필요한데, Tableau는 Read Only로 없는 행을 만들 수는 없었다. 

이에 Null인 행을 만들기 위해 Date Master를 만들어 결과 Table과 Join하고 나서 LAST_VALUE 함수를 사용했다.

---

3. Wrap up

그동안 프로젝트를 하면서 다양한 요건들을 만나왔지만, 대부분은 Tableau내에서 해결할 수 있어 그렇게 했지만, 이번 요건은 Tableau에서 적용하기에는 무리가 있다는 판단 아래에 SQL로 해결해가고자 했고, 다행히도 Oracle에 LAST_VALUE라는 함수가 있었기 때문에 요건을 충족할 수 있었다. 

 

쿼리를 짜다가 가장 최신값을 대체할 필요가 있다면 LAST_VALUE를 사용하면 되며, 가장 오래된 값을 불러오고자 한다면 같은 구문에서 FIRST_VALUE로만 바꿔주면 된다.

 

SQL Language Reference

 

이 둘을 위해서는 분석함수에 대한 개념을 꼭 알고나서 적용하기를 권유한다.