[PostgreSQL] 요소 별 최신 데이터 조회하기

1. DISTINCT ON 사용 (PostgreSQL 전용)

PostgreSQL에서는 DISTINCT ON을 사용하면 간단하게 해결할 수 있습니다.

SELECT DISTINCT ON (tagname) tagname, status, timestamp
FROM your_table
ORDER BY tagname, timestamp DESC;

설명

• DISTINCT ON (tagname)을 사용하여 각 tagname별로 첫 번째 행만 선택합니다.
• ORDER BY tagname, timestamp DESC를 지정하여 최신 timestamp 값이 먼저 오도록 정렬합니다.
• 최신 데이터의 status 값을 가져올 수 있습니다.

2. 윈도우 함수 사용 (다른 DBMS에서도 활용 가능)

PostgreSQL 외의 다른 DBMS에서도 적용할 수 있는 ROW_NUMBER()를 이용하는 방법입니다.

SELECT tagname, status, timestamp
FROM (
    SELECT tagname, status, timestamp,
           ROW_NUMBER() OVER (PARTITION BY tagname ORDER BY timestamp DESC) AS rn
    FROM your_table
) sub
WHERE rn = 1;

설명

• ROW_NUMBER() OVER (PARTITION BY tagname ORDER BY timestamp DESC)를 사용해 각 tagname 그룹 내에서 최신 데이터에 번호를 매깁니다.
• WHERE rn = 1을 사용하여 각 tagname별 가장 최신 행만 선택합니다.

3. 예제 데이터 및 결과

tagname	status	timestamp
A	OK	2024-03-18 10:00:00
A	FAIL	2024-03-18 12:00:00
B	OK	2024-03-18 09:30:00
B	FAIL	2024-03-18 11:00:00
C	WARN	2024-03-18 08:45:00

위 데이터를 기준으로 실행하면 다음과 같은 결과를 얻을 수 있습니다.

tagname	status	timestamp
A	FAIL	2024-03-18 12:00:00
B	FAIL	2024-03-18 11:00:00
C	WARN	2024-03-18 08:45:00

4. 어떤 방법을 선택해야 할까?

• PostgreSQL을 사용한다면 DISTINCT ON이 더 간결하고 효율적입니다.
• 다른 DBMS에서도 적용하려면 ROW_NUMBER() 방식을 사용하면 됩니다.