[PostgreSQL] 배열 기반 데이터에서 특정 요소의 기울기 계산하기

이번 포스트에서는 PostgreSQL에서 배열을 활용한 데이터 처리와 특정 조건에 따른 기울기 계산 방법을 소개합니다. 특히 데이터가 배열 형태로 저장되어 있고, 각 배열의 인덱스에 해당하는 값들 간의 관계를 기반으로 중간값 및 기울기를 계산하는 시나리오를 다룹니다.

문제 설명

주어진 테이블은 다음과 같은 컬럼을 포함하고 있습니다:

• timestamp_col: 이벤트가 발생한 시간
• asset_identifier: 자산 식별자
• feature_names: 이름을 포함한 배열
• feature_values: 값들을 포함한 배열

데이터 예시는 다음과 같습니다:

timestamp_col	asset_identifier	feature_names	feature_values
2025-01-01 12:00:00	ASSET_XYZ	['a', 'b', 'c']	[0.8, 1.5, 2.0]
2025-01-02 12:00:00	ASSET_XYZ	['a', 'b', 'c']	[0.9, 1.4, 1.9]

이 테이블에서 다음을 수행하려고 합니다.

1. 특정 시점 a부터 일주일 전까지의 데이터 중 feature_names 배열에 특정 요소(b, c)가 포함된 데이터를 필터링합니다.
2. 각 요소(b, c)에 대해 중간값(median)을 구합니다.
3. 시점 a의 데이터와 중간값을 비교하여 기울기를 계산합니다.

쿼리 작성

아래는 문제를 해결하기 위한 SQL 쿼리입니다.

WITH recent_data AS (
    SELECT
        timestamp_col,
        unnest(feature_names) AS name,
        unnest(feature_values) AS value,
        generate_subscripts(feature_names, 1) AS index
    FROM your_table
    WHERE asset_identifier = 'ASSET_XYZ'
      AND timestamp_col BETWEEN a - interval '7 days' AND a
      AND feature_names && ARRAY['b', 'c']
),
median_values AS (
    SELECT
        name,
        index,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) AS median_value
    FROM recent_data
    WHERE name IN ('b', 'c')
    GROUP BY name, index
),
current_data AS (
    SELECT
        timestamp_col,
        unnest(feature_names) AS name,
        unnest(feature_values) AS value,
        generate_subscripts(feature_names, 1) AS index
    FROM your_table
    WHERE asset_identifier = 'ASSET_XYZ'
      AND timestamp_col = a
      AND feature_names && ARRAY['b', 'c']
)
SELECT
    c.index,
    c.name,
    c.value AS current_value,
    m.median_value,
    (c.value - m.median_value) / 7 AS slope  -- 기울기 계산
FROM current_data c
JOIN median_values m ON c.name = m.name AND c.index = m.index
WHERE c.name IN ('b', 'c')
ORDER BY c.index;

쿼리 설명

1. recent_data
   • unnest 함수를 사용해 feature_names과 feature_values 배열을 각각 펼쳐서 인덱스별로 데이터를 생성합니다.
   • generate_subscripts 함수로 배열의 인덱스를 생성하여, 나중에 인덱스를 기준으로 데이터를 조인할 수 있도록 합니다.
   • 조건으로 asset_identifier = 'ASSET_XYZ'와 feature_names 배열에 b 또는 c가 포함된 데이터만 선택합니다.
2. median_values
   • PERCENTILE_CONT(0.5) 함수를 사용해 각 인덱스별로 중간값(50번째 백분위수)을 계산합니다.
   • GROUP BY name, index를 통해 각각의 이름과 인덱스에 대한 중간값을 따로 구합니다.
3. current_data
   • 시점 a에 해당하는 데이터를 배열 인덱스별로 펼쳐서 가져옵니다.
   • 조건으로 asset_identifier = 'ASSET_XYZ'와 feature_names 배열에 b 또는 c가 포함된 데이터만 선택합니다.
4. 최종 선택:
   • current_data와 median_values를 name과 index 기준으로 조인하여, 현재 값과 중간값의 차이를 7일로 나눈 기울기를 계산합니다.
   • 최종 결과는 인덱스별로 정렬됩니다.

결과 예시

예를 들어, 특정 시점 a에서 feature_names = ['a', 'b', 'c'], feature_values = [0.8, 1.5, 2.0]이고, 지난 일주일간 b와 c에 대한 중간값이 각각 1.2, 1.8이라면 기울기는 다음과 같이 계산됩니다:

• b의 기울기: (1.5−1.2)/7=0.0429
• c의 기울기: (2.0−1.8)/7=0.0286

출력 결과는 다음과 같습니다

index	name	current_value	median_value	slope
2	b	1.5	1.2	0.0429
3	c	2.0	1.8	0.0286

마무리

이번 포스트에서는 PostgreSQL에서 배열 형태의 데이터를 다루고, 특정 조건에 따른 중간값과 기울기를 계산하는 방법을 살펴보았습니다. 이 과정에서 unnest, generate_subscripts, PERCENTILE_CONT와 같은 유용한 함수들을 활용했습니다.

이 방법은 시계열 데이터 분석이나, 특정 조건에 따른 변화 추세를 파악할 때 유용하게 사용할 수 있습니다. 필요에 따라 쿼리를 최적화하거나 확장할 수 있으며, PostgreSQL을 활용한 고급 데이터 처리를 할 때 좋은 참고가 될 수 있습니다.