DB 쿼리 최신순 1건 조회하기

단건 조회 

1. limit 이용하기
SELECT *
FROM document_status
WHERE doc_uid = {document_uid}
ORDER BY docsta_created_at DESC
LIMIT 1;

2. distinct 이용하기
SELECT DISTINCT ON (doc_uid) *
FROM document_status
WHERE doc_uid = {document_uid}
ORDER BY doc_uid DESC, docsta_created_at DESC;

 

1번째 방법이 더 빠름. 

2번째 방법의 order by에는 disctinct에 들어가는 컬럼이 포함되어야 함.

 

SQLAlchemy 

            stmt = (
                select(DocumentStatus)
                .where(DocumentStatus.doc_uid == document_uid)
                .distinct(DocumentStatus.doc_uid)
                .order_by(
                    desc(DocumentStatus.doc_uid), desc(DocumentStatus.docsta_created_at)
                )
            )

            stmt = (
                select(DocumentStatus)
                .where(DocumentStatus.doc_uid == document_uid)
                .order_by(desc(DocumentStatus.docsta_created_at))
                .limit(1)
            )

여러 건 조회

SELECT DISTINCT ON (document_status.doc_uid) *
FROM document_status
WHERE document_status.doc_uid in ()
ORDER BY document_status.doc_uid DESC, document_status.docsta_created_at DESC;

SQLAlchemy

            stmt = (
                select(DocumentStatus)
                .distinct(DocumentStatus.doc_uid)
                .where(DocumentStatus.doc_uid.in_(document_uids))
                .order_by(
                    DocumentStatus.doc_uid,
                    desc(DocumentStatus.docsta_created_at),
                )
            )

 

 

아래가 성능 더 좋음. 윈도우 기반 함수

latest_status_sub = (
    select(
        SummaryStatus,
        func.row_number()
        .over(
            partition_by=SummaryStatus.smysrcfil_uid,
            order_by=desc(SummaryStatus.smysta_created_at),
        ).label("rn"),
    )
)

latest_status = aliased(
    SummaryStatus,
    select(latest_status_sub)
    .where(latest_status_sub.c.rn == 1)
)