[SQL] SQL 활용 - 그룹함수, 윈도우

만듀 2023. 6. 10. 15:31

그룹함수

1. ROLLUP

ROLLUP은 GROUP BY의 칼럼에 대해서 부분합계 만들어줌
GROUP BY구에 칼럼이 두 개 이상 오면 순서에 따라서 결과가 달라짐

CREATE TABLE sales (
    region VARCHAR(50),
    country VARCHAR(50),
    product VARCHAR(50),
    quantity INT,
    revenue DECIMAL(10, 2)
);

INSERT INTO sales (region, country, product, quantity, revenue)
VALUES ('North', 'USA', 'Product A', 100, 5000.00),
       ('North', 'USA', 'Product B', 200, 8000.00),
       ('North', 'Canada', 'Product A', 150, 6000.00),
       ('North', 'Canada', 'Product B', 250, 10000.00),
       ('South', 'Mexico', 'Product A', 120, 5500.00),
       ('South', 'Mexico', 'Product B', 180, 9000.00);

SELECT region, country, product, SUM(quantity) AS total_quantity, SUM(revenue) AS total_revenue
FROM sales
GROUP BY ROLLUP (region, country, product);

region  | country | product    | total_quantity | total_revenue
--------------------------------------------------------------
North   | USA     | Product A  | 100            | 5000.00
North   | USA     | Product B  | 200            | 8000.00
North   | USA     | NULL       | 300            | 13000.00
North   | Canada  | Product A  | 150            | 6000.00
North   | Canada  | Product B  | 250            | 10000.00
North   | Canada  | NULL       | 400            | 16000.00
North   | NULL    | NULL       | 700            | 29000.00
South   | Mexico  | Product A  | 120            | 5500.00
South   | Mexico  | Product B  | 180            | 9000.00
South   | Mexico  | NULL       | 300            | 14500.00
South   | NULL    | NULL       | 300            | 14500.00
NULL    | NULL    | NULL       | 1000           | 43500.00

2. GROUPING 함수

GROUPING 함수는 ROLLUP, CUBE, GROUPING SETS에서 생성되는 합계값을 구분하기 위해서 만들어진 함수
소계, 합계 등이 계산되면 GROUPING함수는 1을 반환하고 그렇지 않으면 0을 반환해서 합계값을 식별함

CREATE TABLE sales (
    region VARCHAR(50),
    country VARCHAR(50),
    product VARCHAR(50),
    quantity INT,
    revenue DECIMAL(10, 2)
);

INSERT INTO sales (region, country, product, quantity, revenue)
VALUES ('North', 'USA', 'Product A', 100, 5000.00),
       ('North', 'USA', 'Product B', 200, 8000.00),
       ('North', 'Canada', 'Product A', 150, 6000.00),
       ('North', 'Canada', 'Product B', 250, 10000.00),
       ('South', 'Mexico', 'Product A', 120, 5500.00),
       ('South', 'Mexico', 'Product B', 180, 9000.00);

SELECT region, country, GROUPING(region) AS is_region_total, GROUPING(country) AS is_country_total, SUM(quantity) AS total_quantity, SUM(revenue) AS total_revenue
FROM sales
GROUP BY ROLLUP (region, country);

region  | country | is_region_total | is_country_total | total_quantity | total_revenue
------------------------------------------------------------------------------------------
North   | USA     | 0               | 0                | 100            | 5000.00
North   | Canada  | 0               | 0                | 150            | 6000.00
North   | NULL    | 0               | 1                | 250            | 11000.00
South   | Mexico  | 0               | 0                | 120            | 5500.00
South   | NULL    | 0               | 1                | 300            | 11000.00
NULL    | NULL    | 1               | 1                | 550            | 22000.00

GROUPING 함수는 0 또는 1 값을 반환합니다. 0은 해당 열 또는 그룹이 그룹화의 일부인 경우(총계가 아닌 경우)를 나타내며, 1은 해당 열 또는 그룹이 총계인 경우를 나타낸다.

위의 결과에서, is_region_total 열은 region이 총계인 경우에 1로 표시되고, 그렇지 않은 경우에 0으로 표시된다. is_country_total 열은 country가 총계인 경우에 1로 표시되고, 그렇지 않은 경우에 0으로 표시된다.

3. GROUPING SETS 함수

GROUPING SETS 함수는 GROUP BY에 나오는 칼럼의 순서와 관계없이 다양한 소계를 만들 수 있음
GROUP BY에 나오는 칼럼의 순서와 관계없이 개별적으로 모두 처리함(서로 관계가 없음)

CREATE TABLE sales (
    region VARCHAR(50),
    country VARCHAR(50),
    product VARCHAR(50),
    quantity INT,
    revenue DECIMAL(10, 2)
);

INSERT INTO sales (region, country, product, quantity, revenue)
VALUES ('North', 'USA', 'Product A', 100, 5000.00),
       ('North', 'USA', 'Product B', 200, 8000.00),
       ('North', 'Canada', 'Product A', 150, 6000.00),
       ('North', 'Canada', 'Product B', 250, 10000.00),
       ('South', 'Mexico', 'Product A', 120, 5500.00),
       ('South', 'Mexico', 'Product B', 180, 9000.00);

SELECT region, country, product, SUM(quantity) AS total_quantity, SUM(revenue) AS total_revenue
FROM sales
GROUP BY GROUPING SETS ((region), (country), (region, country), ());

GPT-3.5

한테 물어본 결과

region  | country | product    | total_quantity | total_revenue
--------------------------------------------------------------
North   | NULL    | NULL       | 550            | 22000.00
NULL    | USA     | NULL       | 100            | 5000.00
NULL    | Canada  | NULL       | 400            | 16000.00
NULL    | NULL    | Product A  | 370            | 17000.00
NULL    | NULL    | Product B  | 180            | 18000.00
North   | USA     | Product A  | 100            | 5000.00
North   | USA     | Product B  | 200            | 8000.00
North   | Canada  | Product A  | 150            | 6000.00
North   | Canada  | Product B  | 250            | 10000.00
South   | Mexico  | Product A  | 120            | 5500.00
South   | Mexico  | Product B  | 180            | 9000.00

뤼튼(지능모드 -gpt 4)한테 물어본 결

| region | country | product | total_quantity | total_revenue |
|--------|---------|---------|----------------|---------------|
| North  |         |         |            700 |      29000.00 |
| South  |         |         |            300 |      14500.00 |
|        | USA     |         |            300 |      13000.00 |
|        | Canada  |         |            400 |      16000.00 |
|        | Mexico  |         |            300 |      14500.00 |
| North  | USA     |         |            300 |      13000.00 |
| North  | Canada  |         |            400 |      16000.00 |
| South  | Mexico  |         |            300 |      14500.00 |
|        |         |         |           1000 |      43500.00 |

4. CUBE 함수

CUBE는 CUBE 함수에 제시한 칼럼에 대해서 결합 가능한 모든 집계를 계산함
다차원 집계를 제공해 다양하게 데이터를 분석할 수 있음
조합할 수 있는 모든 경우의 수가 조합 됨

CREATE TABLE sales (
    region VARCHAR(50),
    country VARCHAR(50),
    product VARCHAR(50),
    quantity INT,
    revenue DECIMAL(10, 2)
);

INSERT INTO sales (region, country, product, quantity, revenue)
VALUES ('North', 'USA', 'Product A', 100, 5000.00),
       ('North', 'USA', 'Product B', 200, 8000.00),
       ('North', 'Canada', 'Product A', 150, 6000.00),
       ('North', 'Canada', 'Product B', 250, 10000.00),
       ('South', 'Mexico', 'Product A', 120, 5500.00),
       ('South', 'Mexico', 'Product B', 180, 9000.00);

SELECT region, country, product, SUM(quantity) AS total_quantity, SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE (region, country, product);

region  | country | product    | total_quantity | total_revenue
--------------------------------------------------------------
North   | USA     | Product A  | 100            | 5000.00
North   | USA     | Product B  | 200            | 8000.00
North   | USA     | NULL       | 300            | 13000.00
North   | Canada  | Product A  | 150            | 6000.00
North   | Canada  | Product B  | 250            | 10000.00
North   | Canada  | NULL       | 400            | 16000.00
North   | NULL    | Product A  | 250            | 11000.00
North   | NULL    | Product B  | 450            | 18000.00
North   | NULL    | NULL       | 700            | 29000.00
South   | Mexico  | Product A  | 120            | 5500.00
South   | Mexico  | Product B  | 180            | 9000.00
South   | Mexico  | NULL       | 300            | 14500.00
South   | NULL    | Product A  | 120            | 5500.00
South   | NULL    | Product B  | 180            | 9000.00
South   | NULL    | NULL       | 300            | 14500.00
NULL    | USA     | Product A  | 100            | 5000.00
NULL    | USA     | Product B  | 200            | 8000.00
NULL    | USA     | NULL       | 300            | 13000.00
NULL    | Canada  | Product A  | 150            | 6000.00
NULL    | Canada  | Product B  | 250            | 10000.00
NULL    | Canada  | NULL       | 400            | 16000.00
NULL    | NULL    | Product A  | 550            | 22000.00
NULL    | NULL    | Product B  | 850            | 28000.00
NULL    | NULL    | NULL       | 1400           | 50000.00

ROLLUP/CUBE/GROUPING SETS의 차이

ROLLUP (a, b, c) : (a, b, c) / (a, b) / (a) / ()

CUBE (a, b, c) : (a, b, c) / (a, b) / (a, c) / (b, c) / (a) / (b) / (c) / ()

GROUPING SETS(a, b, c) : (a) / (b) / (c)

윈도우함수

행과 행간의 관계를 쉽게 정의 하기 위해 만든 함수를 이름

group by와 차이점

윈도우 함수의 생김새를 살펴보면 어떤 기준에 따라 Partition by, 즉 나누어 집계한다는 것을 알 수 있습니다.
그렇다면 group by 와는 어떤 차이가 있을까요?

	group by	윈도우 함수
기능	자르기 + 집약	자르기
특징	1. group by 구에 지정된 컬럼으로 데이터를 자르고 2. 집계 함수를 이용해 집약시킨다.	1. partition by 구에 지정된 컬럼으로 데이터를 자른다.
차이점	행의 수가 줄어든다	행의 수가 그대로 유지된다.

< group by 를 사용한 경우>

select address, count(*) 
from address 
group by address;

< 윈도우 함수를 이용한 경우 >

select address , count(*) over(partition by address) 
from address;

위 두 경우를 살펴보면 차이점을 알 수 있습니다.

group by 는 집약 기능으로 인해 행 수가 줄어든 반면, 윈도우 함수는 행 수가 그대로 남아있습니다.
윈도우 함수에는 집약의 기능이 없기 때문입니다.

ROW_NUMBER()

각 행에 고유한 번호를 할당합니다.

"students" 테이블에서 학생의 이름, 점수를 조회하고, 점수를 기준으로 내림차순으로 순위를 할당합니다.

SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM students;

RANK()

순위를 할당합니다. 동일한 값을 가진 행은 동일한 순위를 가집니다.

RANK 함수는 동일한 값이면 중복 순위를 부여하고, 다음 순위는 해당 개수만큼 건너뛰고 반환합니다.

"students" 테이블에서 학생의 이름, 점수를 조회하고, 점수를 기준으로 내림차순으로 순위를 할당합니다.

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

DENSE_RANK()

DENSE_RANK 함수는 동일한 값이면 중복 순위를 부여하고, 다음 순위는 중복 순위와 상관없이 순차적으로 반환합니다.

"students" 테이블에서 학생의 이름, 점수를 조회하고, 점수를 기준으로 내림차순으로 밀집 순위를 할당합니다.

SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

NTILE(n)

데이터를 n개의 동일한 크기의 그룹으로 분할합니다.

"students" 테이블에서 학생의 이름, 점수를 조회하고, 점수를 기준으로 내림차순으로 4개의 동일한 크기 그룹으로 분할합니다. 결과에는 "quartile" 열이 포함되며, 각 학생이 속한 그룹 번호를 나타냅니다.

SELECT name, score, NTILE(4) OVER (ORDER BY score DESC) AS quartile
FROM students;

name    | score | quartile
---------------------------
Alice   | 95    | 1
Bob     | 90    | 1
Charlie | 85    | 2
David   | 80    | 2
Eve     | 75    | 3
Frank   | 70    | 3
Grace   | 65    | 4

LAG(column, offset)

이전 행의 특정 열 값을 가져옵니다. offset은 몇 개의 행을 건너뛸지를 나타냅니다.

"students" 테이블에서 학생의 이름, 점수를 조회하고, 점수를 기준으로 오름차순으로 이전 행의 점수를 가져옵니다. "previous_score" 열에 이전 행의 점수가 나타납니다.

SELECT name, score, LAG(score) OVER (ORDER BY score) AS previous_score
FROM students;

LEAD(column, offset)

다음 행의 특정 열 값을 가져옵니다. offset은 몇 개의 행을 건너뛸지를 나타냅니다.

"students" 테이블에서 학생의 이름, 점수를 조회하고, 점수를 기준으로 오름차순으로 다음 행의 점수를 가져옵니다. "next_score" 열에 다음 행의 점수가 나타납니다.

SELECT name, score, LEAD(score) OVER (ORDER BY score) AS next_score
FROM students;

SUM(), AVG(), MAX(), MIN()

특정 열에 대해 합계, 평균, 최댓값, 최솟값을 계산합니다. 집계함수 같은 경우 over이 붙었을 경우에 윈도우 함수로 사용되었다고 볼 수 있습니다. 윈도우 함수에서는 해당 열의 부분 집합에 대한 계산을 수행합니다.

"sales" 테이블에서 날짜와 매출을 조회하고, 날짜를 기준으로 오름차순으로 누적 매출을 계산합니다. "cumulative_revenue" 열에 누적 매출이 표시됩니다.

SELECT date, revenue, SUM(revenue) OVER (ORDER BY date) AS cumulative_revenue
FROM sales;

date       | revenue | cumulative_revenue
----------------------------------------
2022-01-01 | 100     | 100
2022-01-02 | 150     | 250
2022-01-03 | 200     | 450
2022-01-04 | 120     | 570
2022-01-05 | 180     | 750

"sales" 테이블에서 날짜, 매출 및 카테고리를 조회하고, 각 카테고리에 대해 매출의 평균을 계산합니다. "category_average" 열에는 해당 행의 카테고리 평균 매출이 표시됩니다. PARTITION BY 절을 사용하여 카테고리별로 평균을 계산합니다.

SELECT date, revenue, AVG(revenue) OVER (PARTITION BY category) AS category_average
FROM sales;

참고자료

https://porimp.tistory.com/entry/SQLD%EA%B7%B8%EB%A3%B9%ED%95%A8%EC%88%98%EC%9C%88%EB%8F%84%EC%9A%B0-%ED%95%A8%EC%88%98Window-Function

(SQLD)그룹함수/윈도우 함수(Window Function)

1. ROLLUP ROLLUP은 GROUP BY의 칼럼에 대해서 Subtotal을 만들어줌 GROUP BY구에 칼럼이 두 개 이상 오면 순서에 따라서 결과가 달라짐 2. GROUPING 함수 GROUPING 함수는 ROLLUP, CUBE, GROUPING SETS에서 생성되는 합계

porimp.tistory.com

https://schatz37.tistory.com/12

[SQL] 윈도우 함수(Window Function)의 소중함을 느껴보자

0. 들어가며 요즘에는 대부분의 DBMS에서는 윈도우 함수(Window Function)를 제공하고 있습니다. 업무를 하다보면 여러 서브쿼리를 이용하여 만들어야 할 결과물을 윈도우 함수를 활용해 아주 간단하

schatz37.tistory.com

https://m.blog.naver.com/gglee0127/221318160003

[Oracle] 순위를 반환하는 함수 (RANK, DENSE_RANK, ROW_NUMBER)

테이블에서 특정 값을 기준으로 순위를 매겨서 보고 싶을 때 ORDER BY 절을 사용합니다. 동일한 점...

blog.naver.com

'DB' 카테고리의 다른 글

[SQL] SQL 활용 - 절차형 SQL (0)	2023.06.18
[SQL]SQL 활용 - 계층형 질의,서브쿼리 (2)	2023.06.09
[SQL]SQL활용 - DCL에 대하여 (0)	2023.06.06
[SQL] SQL 활용 - 표준 JOIN과 집합 연산자에 대하여(ANSI) (1)	2023.06.06
SQL Server DB Table Partitioning (0)	2022.04.24

현재글[SQL] SQL 활용 - 그룹함수, 윈도우

만두의 IT

일상, 백준, TCP, Process, thread, 운영체제, 네트워크, Python, 계충형 질의, greedy, scheduling, OS, SQL, 코테, Java 17, db, 네이버 경력, Multilevel Queue, RAM의구조, Spring,

Today :
Yesterday :

만두의 IT