SQL 데이터 통계 분석을 강화하는 analytics - window functions

SQL 데이터 통계 분석을 강화하는 analytics functions

 
SQL에서의 윈도우 함수(Analytic Functions)는 데이터를 분석하고 처리하기 위한 강력한 도구를 제공합니다.
Oracle은 Analytics Functions라고 하고, Mysql 에서는 Window Function이라고 명명하고 있습니다.
 

Analytic (Window) function와 SQL Standards 

SQL 언어의 표준 규격은  데이터베이스 관리 시스템(DBMS) 간의 호환성을 증진하기 위해 정의된 것입니다. 
SQL:2003 버전 이후부터 윈도우 함수가 표준에 추가되었으며, Window 함수와 같은 분석 함수(Analytic Functions)를 지원하고 있습니다.
Window 함수는 데이터를 특정 윈도우 또는 부분 집합으로 나누어 그 안에서의 계산을 수행하는 함수로 데이터 통계를 빠르게 수행 할 수 있게 되었습니다. 

우리가 알고 있는 SQL은 1986년 ANSI X3.135에서 표준화되었으며  SQL92, SQL99 등 매년 마다 SQL 표준화가 진행되어 왔습니다. 모든 DBMS들이 SQL 표준을 지원하므로 DBMS간의 SQL 호환성을 유지 할 수 있게 되었습니다.  물론 일부 기능은 다를수 있지만 NOSQL이라는 용어가 나온 것 또한 이러한 표준을 이용하는 것과 이용하지 못하는 것을 나누는 기준이기도 했습니다.

SQL(Structured Query Language의 약자)은 관계형 데이터베이스 관리 시스템의 표준 언어입니다. 1970년대에 처음 등장했을 때 도메인 특정 언어는 원하는 위치를 찾기 위해 포인터 네트워크를 탐색할 수 있는 데이터베이스 쿼리를 수행해야 하는 필요성을 충족시키기 위한 것이었습니다. 

대표적인 SQL 표준 버전에는 SQL-86, SQL-89, SQL-92, SQL:1999(SQL 99), SQL:2003, SQL:2008, SQL:2011 등이 있습니다. 각 버전은 SQL 언어의 구문과 기능을 향상시키거나 새로운 특징을 추가하는 등의 목적으로 개발되었습니다. 최신 버전의 SQL 표준은 현재 시점에서는 SQL:2023이나 그 이후의 버전일 수 있습니다.
* https://blog.ansi.org/sql-standard-iso-iec-9075-2023-ansi-x3-135/#gref

 

 Analytics function의 장점

Analytics 함수는 데이터베이스에서 데이터를 분석하고 처리하는 데 사용되며 여러 가지 장점이 있습니다.

1. 유연한 데이터 분석: Analytics 함수를 사용하면 데이터를 부분 집합 또는 윈도우로 나누어 다양한 분석 작업을 수행할 수 있습니다. 이를 통해 특정 조건에 따른 행 간의 관계, 순위, 합 등을 쉽게 이해하고 계산할 수 있습니다.

2. 복잡한 쿼리 간소화: Analytics 함수를 사용하면 기존에는 복잡하게 작성해야 했던 쿼리를 더 간단하게 작성할 수 있습니다. 특히, 순위, 누적 합, 이동 평균과 같은 복잡한 계산을 한 문장으로 표현할 수 있어 코드의 가독성과 유지보수성을 향상시킵니다.

3. 효율적인 데이터 처리: Analytics 함수를 사용하면 데이터베이스 엔진이 내부적으로 효율적으로 데이터를 처리할 수 있습니다. 이는 대량의 데이터에 대한 계산에서 빠른 속도와 효율성을 제공합니다.

4. 패턴 및 통계 분석: Analytics 함수를 사용하면 데이터의 패턴을 식별하고 통계적 정보를 추출하는 데 도움이 됩니다. 예를 들어, 특정 기간 동안의 누적 합, 각 요소의 순위, 변동성 등을 파악할 수 있습니다.

5. 데이터 시각화 지원: Analytics 함수는 데이터를 시각화하는 데 유용합니다. 결과를 차트나 그래프로 표현하면 데이터의 특성을 직관적으로 파악할 수 있습니다.

6. 비즈니스 인텔리전스(BI) 지원: Analytics 함수는 비즈니스 인텔리전스 도구와 연동하여 데이터 분석 및 리포팅을 간편하게 수행할 수 있습니다.
 

Analytics 함수의 활용

이 쿼리에서 ROW_NUMBER() 함수는 부서별로 정렬된 급여에 따라 각 사원에게 순위를 부여합니다. PARTITION BY 구문은 부서별로 데이터를 분할하는 역할을 합니다.

-- ROW_NUMBER()를 사용하여 각 부서별로 사원을 순위 지정
SELECT
    employee_id,
    employee_name,
    department_id,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_department
FROM
    employees;

 

Analytics 함수의 종류

 
다음과 같은 테이블을 가정하여 ROW_NUMBER(), RANK(), LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE() 등의 analytics 함수를 비교해 봅니다.

CREATE TABLE sales (
    column1 INT PRIMARY KEY,
    column2 VARCHAR(50),
    column3 DECIMAL(10, 2)
);

| column1 | column2 |
|---------|---------|
|   10    |   A     |
|   15    |   B     |
|   20    |   C     |
|   25    |   D     |
|   30    |   E     |

 
 

1. ROW_NUMBER(): 각 행에 고유한 번호를 할당합니다. 주로 순위를 지정하는 데 사용됩니다.

 

-- ROW_NUMBER(): 각 행에 고유한 번호를 할당합니다. 
SELECT
    column1,
    column2,
    ROW_NUMBER() OVER (ORDER BY column1) AS row_num
FROM
    your_table;

| column1 | column2 | row_num |
|---------|---------|---------|
|   10    |   E     |    1    |
|   15    |   B     |    2    |
|   20    |   C     |    3    |
|   25    |   D     |    4    |
|   30    |   A     |    5    |

* ROW_NUMBER() OVER (ORDER BY column1)는 column1을 기준으로 오름차순으로 정렬하여 각 행에 고유한 번호 순서를 부여합니다. 결과에서 와 같이 첫번째 값이 10인  A는 1순위 이고 30인 E는 5번 순위를 가집니다. 
 

2. RANK(): 순서에 따라 각 행에 순위를 할당합니다. 동일한 값에 대해 동일한 순위가 부여됩니다.

 

-- RANK(): 순서에 따라 각 행에 순위를 할당합니다
SELECT
    column1,
    column2,
    RANK() OVER (ORDER BY column1) AS ranking
FROM
    your_table;

| column1 | column2 | ranking |
|---------|---------|---------|
|   10    |   A     |    1    |
|   15    |   B     |    3    |
|   10    |   C     |    1    |
|   20    |   D     |    4    |
|   15    |   E     |    3    |

* 1등이 2개 있으면 다음은 3등으로 시작합니다.
 

3. DENSE_RANK(): RANK()와 유사하지만 순위 사이에 빈 공간이 없습니다.

 

-- DENSE_RANK(): RANK()와 유사하지만 순위 사이에 빈 공간이 없습니다.
SELECT
    column1,
    column2,
    DENSE_RANK() OVER (ORDER BY column1) AS dense_rank
FROM
    your_table;

| column1 | column2 | dense_rank |
|---------|---------|------------|
|   10    |   A     |     1      |
|   15    |   B     |     2      |
|   10    |   C     |     1      |
|   20    |   D     |     3      |
|   15    |   E     |     2      |

* 1등이 2개 있으면 다음은 2등으로 시작합니다.
 

4. SUM(): 윈도우 내에서 특정 열의 누적 합을 계산합니다.

 

-- SUM(): 윈도우 내에서 특정 열의 누적 합을 계산
SELECT
    column1,
    column2,
    SUM(column1) OVER (ORDER BY column2) AS cumulative_sum
FROM
    your_table;

 
 

5. LEAD()와 LAG(): 각 행에 대해 다음 또는 이전 행의 값을 가져올 수 있습니다.

 

SELECT
    column1,
    column2,
    LEAD(column1) OVER (ORDER BY column2) AS next_value,
    LAG(column1) OVER (ORDER BY column2) AS prev_value
FROM
    your_table;

 

| column1 | column2 | next_value | prev_value |
|---------|---------|------------|------------|
|   10    |   A     |      15    |    NULL    |
|   15    |   B     |      20    |      10    |
|   20    |   C     |      25    |      15    |
|   25    |   D     |      30    |      20    |
|   30    |   E     |    NULL    |      25    |

 
* LEAD(column1) OVER (ORDER BY column2) AS next_value: 현재 행의 다음 행의 column1 값을 가져옵니다. 마지막 행은 다음 행이 없으므로 NULL이 됩니다.

* LAG(column1) OVER (ORDER BY column2) AS prev_value: 현재 행의 이전 행의 column1 값을 가져옵니다. 첫 번째 행은 이전 행이 없으므로 NULL이 됩니다.
 

6. FIRST_VALUE()와 LAST_VALUE(): 각 윈도우에서 첫 번째 및 마지막 값을 찾습니다.

 

| column1 | column2 | column3 |
|---------|---------|---------|
|   10    |   A     |    1    |
|   15    |   A     |    2    |
|   20    |   B     |    1    |
|   25    |   B     |    2    |
|   30    |   B     |    3    |

 

-- FIRST_VALUE()와 LAST_VALUE(): 각 윈도우에서 첫 번째 및 마지막 값
SELECT
    column1,
    column2,
    FIRST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3) AS first_val,
    LAST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3) AS last_val
FROM
    your_table;

 

| column1 | column2 | column3 | first_val | last_val |
|---------|---------|---------|-----------|----------|
|   10    |   A     |    1    |    10     |    15    |
|   15    |   A     |    2    |    10     |    15    |
|   20    |   B     |    1    |    20     |    30    |
|   25    |   B     |    2    |    20     |    30    |
|   30    |   B     |    3    |    20     |    30    |

 
* FIRST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3) AS first_val: 각 부서(column2)별로 정렬된 column3의 순서에 따라 첫 번째 값 가져옵니다.

* LAST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3) AS last_val: 각 부서(column2)별로 정렬된 column3의 순서에 따라 마지막 값 가져옵니다.