테이블 데이터에서 일주일전 데이터 비교

java를 이용하여 뭔가 툴을 사용하고 있는데, 테이블에 있는 데이터 중 어제의 데이터와 일주일전의 데이터를 비교해야 할 일이 생겼다.
물론 db툴을 이용하여 쿼리를 수행하고 도출된 결과를 바탕으로 프로그램을 만들 수도 있고 간단히 엑셀에서도 비교 가능하다.
하지만, 이게 반복되는 작업이라면? 말이 달라진다.
그래서 java를 이용하여 결과를 변수에 담아 일주일 전 데이터를 빼고, 나눠서, 100을 곱해 백분율로 구할 수 있겠지.
하지만 그것도 귀찮다.
그래서 그냥 쿼리로 전부 다 하고 수행된 결과만 가지고 사용하기로 했다.

쿼리는 간단히 아래처럼 가능하다.

SELECT 
    A1.col, 
    ROUND(((CAST(A1.value AS FLOAT) - CAST(A2.value AS FLOAT)) / CAST(A1.value AS FLOAT) * 100)::numeric, 2) AS percentage_difference
FROM 
    (SELECT col, value 
    FROM A 
    WHERE date_column = CURRENT_DATE - INTERVAL '1 day') AS A1
JOIN 
    (SELECT col, value 
    FROM A 
    WHERE date_column = CURRENT_DATE - INTERVAL '8 days') AS A2
ON 
    A1.col = A2.col;

이 쿼리는 특정 테이블인 'A'에서 현재 날짜로부터 1일 전의 데이터(A1)와 8일 전의 데이터(A2)를 가져오는데 사용된다. 이때, 각 날짜에서 동일한 'col' 값을 가진 데이터만을 대상으로 한다.

가져온 데이터에서, 동일 'col'의 값에 대해 1일 전의 'value' 값과 8일 전의 'value' 값을 비교합니다. 이때 'value'는 문자열 형태이지만 계산을 위해 실수(FLOAT)로 변환(CAST)된다.

계산은 (1일 전의 'value' - 8일 전의 'value') / 1일 전의 'value' * 100 으로 수행되어, 이를 통해 1일 전과 8일 전의 'value'의 변동률을 백분율로 표현한다..

마지막으로 이 계산 결과를 소수점 이하 2자리까지 표현하기 위해 ROUND 함수를 사용합니다. ::numeric은 연산 결과를 numeric 타입으로 형 변환하는 것으로, 이는 ROUND 함수가 적절하게 작동하기 위해 필요하다.

이 쿼리의 결과는 각 'col'의 값과 그에 해당하는 'value'의 변동률(백분율)이다.

이 쿼리는 postgresql 에서 동작한다.

아래의 쿼리는 사용하는 DB를 오라클(Oracle)로 변경했을 때 약간의 구문이 변경된다.

특히 아래의 테이블 데이터에서는, 날짜 형식이 단순히 VARCHAR로 "YYYYMMDD" 형식으로 되어 있기 때문에 이를 TO_DATE()로 날짜형식으로 변경해줘야 한다.

SELECT 
    A1.col, 
    ROUND(((TO_NUMBER(A1.value) - TO_NUMBER(A2.value)) / TO_NUMBER(A1.value) * 100), 2) AS percentage_difference
FROM 
    (SELECT col, value 
    FROM A 
    WHERE TO_DATE(date_column, 'YYYYMMDD') = TRUNC(SYSDATE) - 1) A1
JOIN 
    (SELECT col, value 
    FROM A 
    WHERE TO_DATE(date_column, 'YYYYMMDD') = TRUNC(SYSDATE) - 8) A2
ON 
    A1.col = A2.col;

오늘도 지식+1