[SQL] 차례차례 최종 값을 확인해야 할 때

가끔 동일한 테이블의 특정 컬럼들을 차례차례 최종 값을 확인한 후 가져와야 하는 쿼리를 작성해야 하는 경우가 생긴다.
이번 프로젝트 진행 중에도 비슷한 상황이 생겨서 생각해둔 쿼리를 기록한다.

(물론 테이블명, 컬럼명, 구조 등은 예시로 들기 좋게 변형했다.)

DB: mysql
테이블: period
컬럼: year, seq, month, day, start_dt, end_dt
PK: year, seq

작성할 내용
- year, seq를 파라미터로 받는다.
- [year - seq]가 파라미터 보다 작은 것 중 최종 값을 찾아 그 행을 반환한다.
- 예시:

year	seq
2023	52
2024	1
2024	3
2024	4

파라미터로 {"year":2024, "seq":3}을 받았다면 {"year":2024, "seq":1}인 행을 반환하고,
{"year":2024, "seq":1}을 받았다면 {"year":2023, "seq":52}인 행이 반환돼야 한다.

 

몇 가지 떠오른 방법들 중에 어느 것이 더 효율적으로 동작할지 (실행 속도가 빠른지), 쿼리 읽기가 더 수월할지 모르겠어서 실제 적용할 쿼리는 아직 확정하지 못했다.
약간 문제가 있어서 수정을 거쳐 최종 쿼리를 작성했다. 아주 마음에 드는 상태는 아니지만 이 정도로 만족..

 

select  period.*
  from  period
        inner join
        (select year, max(seq) as m_seq,
                max(year) over () as m_year
          from  period
         where  ( (year = :pYear and seq < :pSeq)
                or year < :pYear
                )
         group  by year
        ) m_pr
    on  m_pr.year = m_pr.m_year
   and  m_pr.year = period.year
   and  m_pr.m_seq = period.seq

 

select  period.*
  from  period
 where  (year, seq) = (
            select  year, max(seq)
              from  period
             where  year = (
                        select  max(year)
                          from  period
                         where  (year = :pYear and seq < :pSeq)
                            or  year < :pYear
                    )
               and  ( (year = :pYear and seq < :pSeq)
                    or  year < :pYear
                    )
             group  by year
        )

 

/* 현재와 이전 최종 값의 PK를 한 줄로 */
with prev_key as (
    select  app.year, app.seq,
            case when app.seq = 1 then max(last_app.year)
                else max(prev_app.year)
            end as prev_year,
            case when app.seq = 1 then max(last_app.seq)
                else max(prev_app.seq)
            end as prev_seq
      from  period app
            left outer join
            period prev_app
        on  prev_app.year = app.year
       and  prev_app.seq  < app.seq
            left outer join
            (select app.year, max(app.year) over () as m_year, max(app.seq) as seq
              from  period app
             where  app.pp_year < :year  -- '2024'
             group  by app.year
            ) last_app
        on  last_app.year = last_app.m_year
     where  app.year = :year
     group  by app.year, app.seq
)
select  prev_app.year, prev_app.seq,
        app.*
  from  period app
        inner join
        prev_key
    on  prev_key.year = app.year
   and  prev_key.seq  = app.seq
        left outer join
        period prev_app
    on  prev_app.year = prev_key.prev_year
   and  prev_app.seq  = prev_key.prev_seq