[PostgreSQL] 특정 값 비율 구하기

데이터 구조 예시

컬럼:

• event_time (시간 데이터)
• asset_id (장치 ID)
• context_id (문맥 ID, 필요시 추가 사용)
• state (R 포함 상태 값)
• condition (상태 조건, 필요시 추가 사용)

쿼리 작성

WITH Total_Asset_Count AS (
    SELECT 
        asset_id,
        COUNT(*) AS total_count
    FROM your_table
    WHERE event_time BETWEEN 'YYYY-MM-DD HH:MI:SS' AND 'YYYY-MM-DD HH:MI:SS'
    GROUP BY asset_id
),
State_R_Count AS (
    SELECT 
        asset_id,
        COUNT(*) AS r_count
    FROM your_table
    WHERE state = 'R' 
      AND event_time BETWEEN 'YYYY-MM-DD HH:MI:SS' AND 'YYYY-MM-DD HH:MI:SS'
    GROUP BY asset_id
)
SELECT 
    t.asset_id,
    COALESCE(s.r_count, 0) AS r_count,
    t.total_count,
    CASE 
        WHEN t.total_count = 0 THEN 0
        ELSE COALESCE(s.r_count, 0) * 1.0 / t.total_count
    END AS r_ratio
FROM Total_Asset_Count t
LEFT JOIN State_R_Count s
ON t.asset_id = s.asset_id;

주요 부분 설명

1. Total_Asset_Count CTE: 특정 기간 동안 각 asset_id에 대해 발생한 전체 이벤트 수를 계산.
2. State_R_Count CTE: 특정 기간 동안 각 asset_id에 대해 state = 'R'인 이벤트 수를 계산.
3. 최종 SELECT:
   • r_count: state = 'R'인 수.
   • total_count: 전체 이벤트 수.
   • r_ratio: 비율 계산 (r_count / total_count).

쿼리 사용 예시

• your_table를 실제 테이블 이름으로 변경.
• YYYY-MM-DD HH:MI:SS를 필요한 기간으로 수정

---

분 단위로 집계해야할 경우

1분에 여러 개의 event_time이 존재할 경우, 이를 1분 단위로 그룹화하여 total_count를 계산해야합니다.

WITH Total_Asset_Count AS (
    SELECT 
        asset_id,
        COUNT(DISTINCT DATE_TRUNC('minute', event_time)) AS total_count
    FROM your_table
    WHERE event_time BETWEEN 'YYYY-MM-DD HH:MI:SS' AND 'YYYY-MM-DD HH:MI:SS'
    GROUP BY asset_id
),
State_R_Count AS (
    SELECT 
        asset_id,
        COUNT(DISTINCT DATE_TRUNC('minute', event_time)) AS r_count
    FROM your_table
    WHERE state = 'R'
      AND event_time BETWEEN 'YYYY-MM-DD HH:MI:SS' AND 'YYYY-MM-DD HH:MI:SS'
    GROUP BY asset_id
)
SELECT 
    t.asset_id,
    COALESCE(s.r_count, 0) AS r_count,
    t.total_count,
    CASE 
        WHEN t.total_count = 0 THEN 0
        ELSE COALESCE(s.r_count, 0) * 1.0 / t.total_count
    END AS r_ratio
FROM Total_Asset_Count t
LEFT JOIN State_R_Count s
ON t.asset_id = s.asset_id;

변경 사항

1. DATE_TRUNC('minute', event_time) 사용:
   • event_time을 1분 단위로 잘라내어, 1분에 발생한 이벤트는 하나로 간주합니다.
   • COUNT(DISTINCT DATE_TRUNC('minute', event_time))로 1분 단위의 고유 이벤트를 카운트합니다.
2. Total_Asset_Count와 State_R_Count:
   • 두 CTE 모두 COUNT 계산 방식에 DISTINCT DATE_TRUNC('minute', event_time)을 적용해 1분 단위로 집계합니다.

쿼리 동작

• total_count: 각 asset_id별로 특정 기간 내에 1분 단위로 그룹화한 총 이벤트 수.
• r_count: 각 asset_id별로 특정 기간 내 state = 'R'인 1분 단위 이벤트 수.
• r_ratio: r_count / total_count 비율.