[Oracle] 오라클 증감률 계산 방법 (일별, 월별, 시간대별)

오라클 SQL에서 일별, 월별, 시간대별 등 증감률을 계산하기 위해서는 LAG 분석함수를 사용하면 된다. LAG 함수는 OVER 절과 함께 사용하며, OVER 절 내부의 ORDER BY 절 기준으로 데이터를 정렬 후 증감률을 계산하며, 정렬 기준이 잘못되면 결과가 부정확해질 수 있으니 주의해야 한다. 또한 칼럼에 NULL 값이 존재할 경우 증감률 연산이 안되기 때문에, NULL 값이 존재할 경우 NVL 함수를 사용하여 0으로 치환 후 증감률을 계산하는 것이 좋다.

 

목차 일별 매출 증감률 계산 방법 월별 매출 증감률 계산 방법 시간대별 매출 증감률 계산 방법

 

일별 매출 증감률 계산 방법

WITH sales_data AS (
    SELECT DATE '2024-01-01' AS sale_date, 1500 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-01-02' AS sale_date, 1700 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-01-03' AS sale_date, 1650 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-01-04' AS sale_date, 1800 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-01-05' AS sale_date, 1750 AS sales_amount FROM dual
)

SELECT sale_date
     , sales_amount
     , LAG(sales_amount) OVER (ORDER BY sale_date) AS prev_sales_amount
     , ROUND(
           (sales_amount - LAG(sales_amount) OVER (ORDER BY sale_date)) /
           LAG(sales_amount) OVER (ORDER BY sale_date) * 100
         , 2
       ) AS growth_rate
  FROM sales_data
 ORDER BY sale_date

 

 

LAG 함수를 사용하여 이전(어제) 매출을 가져와서 현재날짜의 매출과 계산하여 증감률을 구할 수 있다.

 

현재매출: sales_amount

이전매출: LAG(sales_amount) OVER (ORDER BY sale_date))

 

(현재매출 - 이전매출) / 이전매출 * 100 = 증감률

ROUND 함수를 사용하여 소수점 2자리까지 반올림

 

같은 일자에 데이터가 여러 건 있을 경우 증감률 계산 법

WITH sales_data AS (
    SELECT DATE '2024-01-01' AS sale_date, 1500 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-01-01' AS sale_date, 1300 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-01-02' AS sale_date, 1700 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-01-02' AS sale_date, 1400 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-01-03' AS sale_date, 1650 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-01-03' AS sale_date, 1350 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-01-04' AS sale_date, 1800 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-01-04' AS sale_date, 1500 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-01-05' AS sale_date, 1750 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-01-05' AS sale_date, 1250 AS sales_amount FROM dual
)
SELECT sale_date
     , total_sales
     , LAG(total_sales) OVER (ORDER BY sale_date) AS prev_total_sales
     , ROUND(
           (total_sales - LAG(total_sales) OVER (ORDER BY sale_date)) /
           LAG(total_sales) OVER (ORDER BY sale_date) * 100
         , 2
       ) AS growth_rate
  FROM (
           SELECT sale_date
                , SUM(sales_amount) AS total_sales
             FROM sales_data
            GROUP BY sale_date
       )
 ORDER BY sale_date

 

 

증감률을 계산할 날짜의 데이터가 여러 행 있을 경우, 서브쿼리(인라인뷰)에서 GROUP BY 절과 SUM 함수를 사용하여 합산 후 증감률을 계산하면 된다.

 

이후 증감률 계산 방법은 첫 번째 예시와 동일하다.

 

월별 매출 증감률 계산 방법

WITH sales_data AS (
    SELECT DATE '2024-01-01' AS sale_date, 1500 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-01-10' AS sale_date, 1300 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-02-01' AS sale_date, 1700 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-02-10' AS sale_date, 1400 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-03-01' AS sale_date, 1650 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-03-10' AS sale_date, 1350 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-04-01' AS sale_date, 1800 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-04-10' AS sale_date, 1500 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-05-01' AS sale_date, 1750 AS sales_amount FROM dual UNION ALL
    SELECT DATE '2024-05-10' AS sale_date, 1250 AS sales_amount FROM dual
)

SELECT sales_month
     , total_sales
     , LAG(total_sales) OVER (ORDER BY sales_month) AS prev_total_sales
     , ROUND(
           (total_sales - LAG(total_sales) OVER (ORDER BY sales_month)) /
           LAG(total_sales) OVER (ORDER BY sales_month) * 100
         , 2
       ) AS growth_rate
  FROM (
           SELECT TO_CHAR(sale_date, 'YYYY-MM') AS sales_month
                , SUM(sales_amount)             AS total_sales
             FROM sales_data
            GROUP BY TO_CHAR(sale_date, 'YYYY-MM')
       )
 ORDER BY sales_month

 

 

월별 증감률을 계산할 경우, 서브쿼리(인라인뷰)에서 GROUP BY 절과 SUM 함수를 사용하여 월별(YYYY-MM)로 합산 후 증감률을 계산하면 된다.

 

이후 증감률 계산 방법은 첫 번째 예시와 동일하다.

 

시간대별 매출 증감률 계산 방법

WITH sales_data AS (
    SELECT TO_DATE('2024-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS') AS sale_date, 1500 AS sales_amount FROM dual UNION ALL
    SELECT TO_DATE('2024-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS') AS sale_date, 1300 AS sales_amount FROM dual UNION ALL
    SELECT TO_DATE('2024-01-01 16:00:00', 'YYYY-MM-DD HH24:MI:SS') AS sale_date, 1700 AS sales_amount FROM dual UNION ALL
    SELECT TO_DATE('2024-01-01 20:00:00', 'YYYY-MM-DD HH24:MI:SS') AS sale_date, 1400 AS sales_amount FROM dual UNION ALL
    SELECT TO_DATE('2024-01-01 22:00:00', 'YYYY-MM-DD HH24:MI:SS') AS sale_date, 1600 AS sales_amount FROM dual
)

SELECT time_slot
     , total_sales
     , LAG(total_sales) OVER (ORDER BY time_slot) AS prev_total_sales
     , ROUND(
           (total_sales - LAG(total_sales) OVER (ORDER BY time_slot)) /
           LAG(total_sales) OVER (ORDER BY time_slot) * 100
         , 2
       ) AS growth_rate
  FROM (
           SELECT TO_CHAR(sale_date, 'HH24') AS time_slot
                , SUM(sales_amount)          AS total_sales
             FROM sales_data
            GROUP BY TO_CHAR(sale_date, 'HH24')
       ) hourly_sales
 ORDER BY time_slot

 

 

시간대별 증감률을 계산할 경우, 서브쿼리(인라인뷰)에서 GROUP BY 절과 SUM 함수를 사용하여 월별(HH24)로 합산 후 증감률을 계산하면 된다.

 

이후 증감률 계산 방법은 첫 번째 예시와 동일하다.

 

[Oracle] 오라클 LAG, LEAD 함수 사용법 (이전값, 다음값)

[Oracle] 오라클 연속된 값 사이의 차이 계산 방법

[Oracle] 오라클 서브쿼리 종류 및 사용법 (SubQuery)

[Oracle] 오라클 PARTITION BY 사용법 정리 (분석함수)