83강 SQL 함수 정리 : PostgreSQL ROW_NUMBER Function

PostgreSQL 기초강의, PostgreSQL 기초강좌, ROW_NUMBER

83강 SQL 함수 정리 : PostgreSQL ROW_NUMBER Function   

 

SQL 함수 정리 83강 시작합니다.

 

이번 시간에는

▶ PostgreSQL ROW_NUMBER Function

에 대해서 알아보는 시간입니다.

 

이번 시간에는 PostgreSQL ROW_NUMBER() 함수를 사용하여 결과 집합의 각 행에 고유한 정수 값을 할당하는 방법에 대해 알아봅니다.

이전 강좌의 세부 강의라고 생각하시면 됩니다.

---

Introduction to the PostgreSQL ROW_NUMBER() function   

ROW_NUMBER() 함수는 결과 집합의 각 행에 순차 정수를 할당하는 window function입니다. 

다음은 ROW_NUMBER() 함수의 구문을 보여줍니다:

ROW_NUMBER() OVER(
    [PARTITION BY column_1, column_2,…]
    [ORDER BY column_3, column_4,…]
) 

 

ROW_NUMBER() 함수가 작동하는 행 집합을 window라고 합니다.

PARTITION BY 절은 window를 더 작은 집합 또는 파티션으로 나눕니다. PARTITION BY 절을 지정할 경우 각 파티션의 행 번호는 1로 시작하여 1씩 증가합니다.

PARTITION BY 절은 ROW_NUMBER() 함수의 선택 사항이므로 생략할 수 있으며 ROW_NUMBER() 함수는 전체 window를 파티션으로 처리합니다.

OVER절 안의 ORDER BY절은 번호가 할당되는 순서를 결정합니다.

 

다시 설명드리면, 

SELECT에서 선택된 행에 번호를 매길 때 사용되는 것입니다.

SELECT 된 행 전체가 대상이 될 수도 있고, 파티션으로 나누어진 일부가 대상이 될 수도 있습니다.

---

PostgreSQL ROW_NUMBER() function examples   

PostgreSQL window함수 이전 강의에서 생성된 products  테이블을 사용하여 ROW_NUMBER() 함수의 기능을 시연합니다.

시연 테이블

다음은 products  테이블의 데이터입니다:

데이타

다음 쿼리를 실행해 봅니다.

SELECT
     product_id,
     product_name,
     group_id,
     ROW_NUMBER () OVER (ORDER BY product_id)
FROM
     products;  

결과

PARTITION BY 절을 사용하지 않았기 때문에 ROW_NUMBER() 함수는 전체 결과 집합을 파티션으로 간주합니다.

ORDER BY 절은 결과 집합을 product_id로 정렬하므로 ROW_NUMBER() 함수는 product_id 순서에 따라 행에 정수 값을 할당합니다.

---

다음 쿼리에서는 ORDER BY 절의 열을 product_name으로 변경하고 ROW_NUMBER() 함수는 제품 이름 순서를 기준으로 각 행에 정수 값을 할당합니다.

SELECT
     product_id,
     product_name,
     group_id,
     ROW_NUMBER () OVER (
                ORDER BY product_name
             )
FROM
     products; 

결과

---

다음 쿼리에서는 PARTITION BY 절을 사용하여 group_id 열의 값을 기준으로 창을 부분 집합으로 나눕니다. 이 경우 ROW_NUMBER() 함수는 각 파티션의 시작 행에 하나를 할당하고 동일한 파티션 내의 다음 행에 대해 하나씩 증가합니다.

ORDER BY 절은 product_name 열의 값을 기준으로 각 파티션의 행을 정렬합니다.

SELECT
     product_id,
     product_name,
     group_id,
     ROW_NUMBER () OVER (
          PARTITION BY group_id
          ORDER BY
               product_name
     )
FROM
     products; 

결과

PARTITION BY 절이 어떻게 작동하는지 확인하셔야 합니다.

---

PostgreSQL ROW_NUMBER() function and DISTINCT operator   

다음 쿼리는 ROW_NUMBER() 함수를 사용하여 products 테이블의 개별 price에 정수를 할당합니다.

SELECT DISTINCT
     price,
     ROW_NUMBER () OVER (ORDER BY price)
FROM
     products
ORDER BY
     price;   

결과

다만 중복 가격이 포함되는 결과가 나옵니다. 그 이유는 ROW_NUMBER()가 DISTINCT가 적용되기 전에 결과 집합에서 작동하기 때문입니다.

넘버가 부여된 후에 DISTINCT가 작동합니다.

 

또는 FROM 절의 하위 쿼리를 사용하여 고유 가격 목록을 얻은 다음 외부 쿼리에 ROW_NUMBER() 함수를 적용할 수 있습니다. 이렇게 해서 원하는 결과를 얻을 수 있습니다.

SELECT
     price,
     ROW_NUMBER () OVER (ORDER BY price)
FROM
     (
          SELECT DISTINCT
               price
          FROM
               products
     ) prices; 

결과

SQL이 수행되는 순서가 중요합니다.

---

페이지 구분에 ROW_NUMBER() 함수 사용   

응용프로그램 개발에서 table의 모든 행 대신 행의 하위 집합을 표시하기 위해 페이지 지정 기술을 사용합니다.

LIMIT 절을 사용하는 것 외에도 페이지 구성에 ROW_NUMBER() 함수를 사용할 수 있습니다.

예를 들어, 다음 쿼리는 6번 행에서 시작하는 5개의 행을 선택합니다:

SELECT
     *
FROM
     (
          SELECT
               product_id,
               product_name,
               price,
               ROW_NUMBER () OVER (ORDER BY product_name)
          FROM
               products
     ) x
WHERE
     ROW_NUMBER BETWEEN 6 AND 10;  

결과

---

ROW_NUMBER() 함수를 사용하여 n번째로 높은/낮은 행 가져오기   

예를 들어, 세 번째로 비싼 제품을 얻기 위해서는 먼저 제품 테이블에서 개별적인 가격을 받고 3행의 가격을 선택합니다. 그러면 외부 쿼리에서는 세 번째로 높은 가격의 제품을 받습니다.

SELECT
     *
FROM
     products
WHERE
     price = (
          SELECT
               price
          FROM
               (
                    SELECT
                         price,
                         ROW_NUMBER () OVER (
                              ORDER BY price DESC
                         ) nth
                    FROM
                         (
                              SELECT DISTINCT
                                   (price)
                              FROM
                                   products
                         ) prices
               ) sorted_prices
          WHERE
               nth = 3
     ); 

결과

앞에서 테이블 데이터를 보면, 세 번째로 높은 가격은 800입니다.

이렇게  구할 수 있다는 방법을 이해하시길 바랍니다.

---

이번 강의에서는 PostgreSQL ROW_NUMBER() 함수를 사용하여 결과 집합의 행에 정수 값을 할당하는 방법을 보여드렸습니다.

 

감사합니다.

---

본 강의는 PostgreSQL Tutorial을 참조합니다.  https://www.postgresqltutorial.com/

Do it! SQL을 찾아 주셔서 감사합니다. 공감 ♥ , 댓글이 큰 힘이 됩니다.