82강 SQL 함수 정리 : PostgreSQL Window Functions

PostgreSQL 기초강의, PostgreSQL 기초강좌

82강 SQL 함수 정리 : PostgreSQL Window Functions   

 

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

 

이번 시간에는

▶ PostgreSQL Window Functions

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

 

이번 시간에는 PostgreSQL window functions을 사용하여 현재 행과 관련된 행 집합에서 계산을 수행하는 방법에 대해 알아봅니다.

이전 강좌의 예제에서 등장한 적이 있죠. 실무에서도 유용하게 쓰이는 어려운 정의를 쉽게 해결할 수 있는 함수입니다.

---

Setting up sample tables   

먼저 데모를 위해 products 및 product_groups라는 두 개의 테이블을 만듭니다:

데모 테이블

CREATE TABLE product_groups (
group_id serial PRIMARY KEY,
group_name VARCHAR (255) NOT NULL
);

CREATE TABLE products (
product_id serial PRIMARY KEY,
product_name VARCHAR (255) NOT NULL,
price DECIMAL (11, 2),
group_id INT NOT NULL,
FOREIGN KEY (group_id) REFERENCES product_groups (group_id)
); 

 

둘째, 테이블에 일부 행을 삽입합니다:

INSERT INTO product_groups (group_name)
VALUES
('Smartphone'),
('Laptop'),
('Tablet');

INSERT INTO products (product_name, group_id,price)
VALUES
('Microsoft Lumia', 1, 200),
('HTC One', 1, 400),
('Nexus', 1, 500),
('iPhone', 1, 900),
('HP Elite', 2, 1200),
('Lenovo Thinkpad', 2, 700),
('Sony VAIO', 2, 700),
('Dell Vostro', 2, 800),
('iPad', 3, 700),
('Kindle Fire', 3, 150),
('Samsung Galaxy Tab', 3, 200); 

 

데이터를 확인해 봅니다.

SELECT * FROM product_groups;

product_groups

SELECT * FROM products;

products

---

Introduction to PostgreSQL window functions   

창 함수를 이해하는 가장 쉬운 방법은 집계 함수를 검토하는 것부터 시작하는 것입니다. 집계 함수는 행 집합의 데이터를 단일 행으로 집계합니다.

다음 예제에서는 AVG() 집계 함수를 사용하여 제품 표에 있는 모든 제품의 평균 가격을 계산합니다.

SELECT
     AVG (price)
FROM
     products; 

결과

행의 부분 집합에 집계 함수를 적용하려면 GROUP BY 절을 사용합니다. 다음 예제는 모든 제품 그룹의 평균 가격을 반환합니다.

SELECT
     group_name,
     AVG (price)
FROM
     products
INNER JOIN product_groups USING (group_id)
GROUP BY
     group_name;  

결과

출력에서 분명히 알 수 있듯이 AVG() 함수는 두 예 모두에서 쿼리가 반환하는 행 수를 줄입니다.

 

집계 함수와 유사하게 window  함수는 행 집합에서 작동합니다. 그러나 쿼리에서 반환되는 행 수는 줄어들지 않습니다.

window라는 용어는 window  기능이 작동하는 행 집합을 의미합니다. window  함수는 window의 행에서 값을 반환합니다.

 

예를 들어, 다음 쿼리는 각 제품 그룹의 평균 가격과 함께 제품 이름, 가격, 제품 그룹 이름을 반환합니다.

SELECT
     product_name,
     price,
     group_name,
     AVG (price) OVER (
        PARTITION BY group_name
     )
FROM
     products
     INNER JOIN 
          product_groups USING (group_id); 

결과

이 쿼리에서 AVG() 함수는 OVER 절에서 지정한 행 집합에서 작동하는 창 함수로 작동합니다. 각 행 집합을 창이라고 합니다.

 

이 쿼리의 새 구문은 OVER 절입니다:

AVG(price) OVER (PARTITION BY group_name) 

 

이 구문에서 PARTITION BY는 결과 집합의 행을 그룹으로 분배하고 AVG() 함수를 각 그룹에 적용하여 각 그룹의 평균 가격을 반환합니다.

윈도우 기능은 항상 평가 순서에서 JOIN, WHERE, GROUP BY 및 HAVING 절 뒤와 최종 ORDER BY 절 앞의 결과 집합에 대해 계산을 수행합니다.

---

PostgreSQL Window Function Syntax   

PostgreSQL은 창 함수 호출을 위한 정교한 구문을 가지고 있습니다. 다음은 단순화된 버전을 보여줍니다:

window_function(arg1, arg2,..) OVER (
   [PARTITION BY partition_expression]
   [ORDER BY sort_expression [ASC | DESC] [NULLS {FIRST | LAST }])   

 

이 구문에서:

 

window_function(arg1,arg2,...)   

window_function은 창 함수의 이름입니다. 일부 창 함수는 인수를 허용하지 않습니다.

 

PARTITION BY clause   

PARTITION BY 절은 행을 창 기능이 적용되는 여러 그룹 또는 파티션으로 나눕니다. 위의 예와 같이 제품 그룹을 사용하여 제품을 그룹(또는 파티션)으로 나눕니다.

PARTITION BY 절은 선택 사항입니다. PARTITION BY 절을 건너뛰면 창 기능이 전체 결과 집합을 단일 파티션으로 처리합니다.

 

ORDER BY clause   

ORDER BY 절은 창 기능이 적용되는 각 파티션의 행 순서를 지정합니다.

ORDER BY 절은 NULLS FIRST 또는 NULLS LAST 옵션을 사용하여 결과 집합에서 NULLS 값이 처음인지 마지막인지 지정합니다. 기본값은 NULLS LAST 옵션입니다.

 

frame_clause   

frame_clause는 창 기능이 적용되는 현재 파티션의 행 하위 집합을 정의합니다. 이 행의 부분 집합을 프레임이라고 합니다.

 

쿼리에서 여러 창 함수를 사용하는 경우:

SELECT
    wf1() OVER(PARTITION BY c1 ORDER BY c2),
    wf2() OVER(PARTITION BY c1 ORDER BY c2)
FROM table_name; 

 

WINDOW 절을 사용하여 다음 쿼리와 같이 쿼리를 단축할 수 있습니다:

SELECT 
   wf1() OVER w,
   wf2() OVER w,
FROM table_name
WINDOW w AS (PARTITION BY c1 ORDER BY c2); 

 

쿼리에서 하나의 창 함수를 호출하더라도 WINDOW 절을 사용할 수도 있습니다:

SELECT wf1() OVER w
FROM table_name 
WINDOW w AS (PARTITION BY c1 ORDER BY c2); 

---

 

PostgreSQL window function List   

다음 표에는 PostgreSQL에서 제공하는 모든 창 기능이 나와 있습니다. AVG(), MIN(), MAX(), SUM(), COUNT() 등의 일부 집계 기능도 창 기능으로 사용할 수 있습니다.

 

NameDescription

Name	Description
CUME_DIST	현재 행의 상대 순위를 반환합니다.
DENSE_RANK	파티션 내에서 현재 행의 순위를 공백 없이 지정합니다.
FIRST_VALUE	파티션 내의 첫 번째 행에 대해 계산된 값을 반환합니다.
LAG	파티션 내에서 현재 행 앞의 지정된 실제 간격띄우기 행에서 계산된 값을 반환합니다.
LAST_VALUE	파티션 내의 마지막 행에 대해 계산된 값을 반환합니다.
LEAD	파티션 내의 현재 행 뒤에 있는 간격띄우기 행에서 계산된 값을 반환합니다.
NTILE	파티션의 행을 가능한 균등하게 나누고 각 행에 1부터 시작하는 정수를 인수 값에 할당합니다.
NTH_VALUE	순서가 지정된 파티션의 n번째 행에 대해 계산된 값을 반환합니다.
PERCENT_RANK	현재 행의 상대 순위를 반환합니다(순위 - 1) / (총 행 – 1)
RANK	파티션 내에서 현재 행의 순위를 공백으로 지정합니다.
ROW_NUMBER	파티션 내의 현재 행에 1부터 번호를 매깁니다.

다음 시간부터 각각의 기능을 알아보도록 하겠습니다.

저에게도 생소한 것이 많네요.

ROW_NUMBER, RANK, DENSE_RANK 정도만 사용해 본 거 같습니다.

같이 배워보는 시간이 되겠네요.

---

이번 시간에는 PostgreSQL window functions를 소개하고 데이터 쿼리에 사용하는 몇 가지 예를 보여드렸습니다.

 

감사합니다.

---

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

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