MSSQL 그룹별 최신 데이터 추출(+최대값, 순위) 구하기

MSSQL에서 그룹별 최신 데이터를 가장 깔끔하고 성능 좋게 가져오는 방법은 ROW_NUMBER() 윈도우 함수를 사용하는 것입니다.

1. ROW_NUMBER()를 활용한 추천 쿼리

MATERIALDEFINITIONID별로 그룹을 묶고(PARTITION BY), 생성시간(CREATETIME)의 역순(DESC)으로 번호를 매긴 뒤, 가장 첫 번째(RN = 1) 데이터만 필터링하는 방식입니다.

CTE 방식으로 WITH문사용하여 MATERIALDEFINITIONID 그룹별로 최신순으로 정렬하고 순번을 부여한다.

마지막 SELECT문에서 순번을 부여한 항목에 1번만 나오도록 조회를 하면 그룹별 최신 데이터를 추출할수 있다.

WITH RankedLots AS (
    SELECT 
        SITEID,
        LOTID,
        PRODUCTDEFINITIONID,
        STATE,
        QTY,
        PROCESSSEGMENTID,
        LOTSETYN,
        WORKORDERID,
        CREATETIME,
        WORKORDERTYPE,
        MATERIALDEFINITIONID,
        WORKORDERSEQ,
        WORKORDERSERL,
        -- MATERIALDEFINITIONID 그룹별로 최신순 정렬하여 순번 부여
        ROW_NUMBER() OVER (
            PARTITION BY MATERIALDEFINITIONID 
            ORDER BY CREATETIME DESC
        ) AS RN
    FROM 
        LOTTABLE
)

SELECT SITEID
             , LOTID
             , PRODUCTDEFINITIONID
             , STATE
             , QTY
             , PROCESSSEGMENTID
             , LOTSETYN
             , WORKORDERID
             , CREATETIME
             , WORKORDERTYPE
             , MATERIALDEFINITIONID
             , WORKORDERSEQ
             , WORKORDERSERL
    FROM RankedLots
 WHERE RN = 1;

 

실무에서 은근히 많이 사용하는 쿼리인거 같다. 해당 쿼리를 사용할수 있는 케이스는 본테이블과 HIST테이블이 존재하는데 LOT 상태가 PDA SCAN, COMPLETE 상태로 변경이 된다면 HISTORY 테이블은 해당 이벤트가 발생할때마다 테이블에 적재한다. 취소했다가 다시 SCAN하고 COMPLETE 할수도 있다.

 이런상황에서 화면에서는 마지막 SCAN한 날짜를 표현해줄때 해당LOT의 그룹별 가장 마지막 SCAN한 날짜를 가져올때 위 쿼리를 적용하면 될꺼같다.

 추가적으로 만약 CREATETIME이 소수점 이하 초 단위까지 동일한 데이터가 존재할 가능성이 있다면, ORDER BY CREATETIME DESC, LOTID DESC 처럼 정렬 기준에 순차적인 식별자(LOTID 등)를 추가해주시면 예외 없이 정확히 한 행씩만 추출할 수 있도록 한다.