4. 그룹함수 Cube, Roll up, Grouping set, Window function, CTE, Partition by, SUBQUERY 와 JOIN 의 차이

📌 그룹함수의 필요성

그룹 함수(GROUP FUNCTION)의 필요성

결산 개념의 업무를 가지는 원가나 판매 시스템의 경우는 소계, 중계, 합계, 총 합계 등 여러 레벨의 결산 보고서를 만드는 것이 중요 업무 중의 하나입니다. 개발자들이 이런 보고서를 작성하기 위해서는 레벨별 집계를 위한 여러 단계의 SQL을 UNION, UNION ALL로 묶은 후 하나의 테이블을 여러 번 읽어 다시 재정렬하는 복잡한 단계를 거쳐야만 합니다.

하지만, 그룹 함수(GROUP FUNCTION)을 사용한다면, 하나의 SQL로 테이블을 한 번만 읽어서 빠르게 원하는 리포트를 작성할 수 있습니다. 또한, GROUPING 함수와 CASE 함수를 이용하면 쉽게 원하는 포맷의 보고서 작성도 가능합니다.

📌 요약

• ROLLUP 함수 : 소그룹 간의 소계를 계산함
• CUBE 함수 : GROUP BY 항목들간 다차원적인 소계를 계산할 수 있음 (모든 그룹케이스를 계산함)
• GROUPING SETS 함수 : 특정 항목에 대한 소계를 계산할 수 있음 (GROUPING SETS 은 특정항목을 그룹한 다수의 SQL들을 UNION한 것과 같음)

📌GROUPING SETS절

• Grouping sets은 왜 필요한가?

  • 만약 subject 별 평균과 name 별 평균, All 평균 정보를 한번에 보고 싶다면...

  • ⇒ 하나씩 Group by 한 결과를 Union All 해서 출력 가능

    • 근데 너무 쿼리문이 길어진다.

  • Union all 대신에 쓸 수 있는게 있다면 !?

    ```sql

  • • 1. 전체 평균 계산
         SELECT NULL,
         NULL,
         Avg(score)
         FROM scorelist
  • • 2. 과목, 이름별 평균 계산
         UNION ALL
         SELECT subject,
         name,
         Avg(score)
         FROM scorelist
         GROUP BY subject,

         name

  • • 3. 과목별 평균 계산
         UNION ALL
         SELECT subject,
         NULL,
         Avg(score)
         FROM scorelist
         GROUP BY subject
  • • 4. 이름별 평균 계산
         UNION ALL
         SELECT NULL,
         name,
         Avg(score)
         FROM scorelist
         GROUP BY name;

  • 이렇게 긴 구문을

  • 아래와 같이 변환시킨다.

    SELECT subject,
         NAME,
         Avg(score)
    FROM   scorelist
    GROUP  BY grouping sets( ( subject, NAME ), -- 2. 과목, 이름별 평균 계산
                                                                       subject, -- 3. 과목별 평균 계산
                                                                              NAME, -- 4. 이름별 평균 계산
                                                                              ( ) ) -- 1. 전체 평균 계산

  • 장점은 수행 속도도 더 빠르고 간단하게 보이는 점

📌 Roll up

• ROLL UP이 뭔가?

  • 지정된 GROUPING 컬럼의 소계를 생성함
  • 소그룹 간의 소계를 계산

• 인자로 들어온 컬럼을 오른쪽(→)부터 하나씩 빼면서 Group 생성

    Rollup(월,부서)
    -- 부서 빼고 월별로 그룹화한 agg 함수값과 
    -- 마지막에는 둘다 빼고 총합으로 그룹화한 값이 나온다.

📌 Cube

• ROLL UP과의 차이
  • ROLL UP은 오른쪽부터 순서대로 하나씩 뺌
  • CUBE는 방향 상관없이 하나씩 빼며 모든 가능한 조합의 Group 생성

    | CUBE(C1, C2) | ROLLUP(C1,C2) |
    | --- | --- |
    | C1, C2 | C1, C2 |
    | C1 | C1 |
    | C2 | X |
    | () | () |

GROUP BY CUBE((SUBJECT, NAME))

CUBE에 SUBJECT, NAME이 괄호안에 하나의 그룹이고, 제외 되면 아무 것도 없으므로 전체 평균 출력

📌 WINDOW FUNCTION 개요 및 설명

윈도우 함수 (WINDOW FUNCTION)

• 행과 행 간의 관계를 쉽게 정의하기 위해 만든 함수가 윈도우 함수다.

  ⇒ 분석 함수나 순위 함수랑 동의어

WINDOW FUNCTION 문법

SELECT **WINDOW_FUNCTION** (**ARGUMENTS**) OVER 
( [PARTITION BY **컬럼**] [ORDER BY **컬럼**] [WINDOWING 절] )
FROM 테이블명 ;

• OVER 문구가 키워드로 필수 포함
• WINDOW_FUNCTION : 윈도우 함수
• ARGUMENTS(= 인수) : 함수에 따라 0 ~ N개 인수가 지정될 수 있다.
• PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
• ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 order by 절을 기술한다.

📌 WINDOW FUNCTION 종류

WINDOW FUNCTION 는 크게 5가지 그룹으로 분류할 수 있다. (벤더별로 지원하는 함수 차이가 있음)

1. 📌 그룹 내 순위(RANK) 관련 함수: RANK, DENSE_RANK, ROW_NUMBER
2. 📌 그룹 내 집계(AGGREGATE) 관련 함수 : SUM, MAX, MIN, AVG, COUNT
3. 📌 그룹 내 행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD
4. 📌 그룹 내 비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT
5. 선형 분석을 포함한 통계 분석 함수

📌 Rank 관련함수

1. 특정 항목과 파티션에 대해 순위를 계산할 수 있는 함수

   1. 📌 RANK — 순위를 계산합니다. 동일한 순위는 동일한 값이 부여됩니다.

   2. 📌 DENSE_RANK — 동일한 순위를 하나의 건수로 계산합니다

      • DENSE = 밀집한
        • RANK()는 같은 순위가 있으면(2위) 그 다음 순위(3위)가 없어집니다.
        • DENSE_RANK()는 같은 순위가 있어도 건수로 계산하기 때문에 3위가 있습니다.
          • 빽빽하게, 밀접하게 다 채운다고 생각!

   3. 📌 ROW_NUMBER — 동일한 순위에 대해 고유의 순위를 부여합니다.

      • ROW_NUMBER는 동일한 순위가 있더라도 고유의 순위를 부여합니다.

      • RANK, DENSE_RANK 가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해,

        ROW_NUMBER 는 동일한 값이라도 고유한 순위를 부여

📌 AGGREGATE 집계 관련함수

• 집계 함수(AGGREGATE Function)
  • SUM, MAX, MIN, AVG, COUNT 등등
  • Group by 때 써주었던 기본 집계함수

📌 그룹 내 행 순서 관련 함수

• 행 순서 관련 함수는 상위 행의 값을 하위에 출력하거나 하위 행의 값을 상위 행에 출력하게 할 수 있습니다.

• 또는 특정 위치의 행을 갖고와 출력 할 수 있습니다.

• FIRST_VALUE 함수

  • 파티션에서 가장 처음에 나오는 값
  • MIN 함수를 사용한것과 같은 결과

• LAST_VALUE 함수

  • 파티션에서 가장 마지막에 나오는 값
  • MIN 함수를 사용한것과 같은 결과

• LAG 함수

  • 이전 행(값)을 가져옵니다.
  • Lag(Sum(amount))

               OVER (ORDER BY **Extract(month FROM Date(p.payment_date))**) as pre_amt,

• LEAD 함수

  • 지정된 행(값)을 가져옵니다. 기본값은 1입니다.
  • LEAD(sal, 1, 0) 1을 주었기 때문에 바로 다음 행값 가져옴
    • 2를 주면 Lead 열의 첫번째 값에 sal의 세번째 값을 가져옴
    • 세번째 파라미터인 0은 null값을 처리하는 파라미터

📌 그룹 내 행 비율 관련 함수

• 비율 관련 함수는 누적 백분율, 순서별 백분율, 파티션을 N등분으로 분할한 결과 등을 조회

  1. CUME_DIST

     • CUME_DIST -- 파티션 전체에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회합니다.
     • 누적 분포상 위치는 0~1 사이에 위치
     • 즉 그냥 누적 백분율이 얼마인지 조회하는것

  2. PERCENT_RANK

     • 파티션에서 제일 먼저 나온 것을 0, 제일 늦게 나온 것을 1로 하여 값이 아닌 행의 순서별 백분율을 조회
     • 파티션을 나누어 Partition by 가장 먼저 나온 것을 0 마지막을 1로하여 등수의 퍼센트를 구합니다.

     

  3. NTILE

     • -- 파티션별로 전체 건수를 ARGUMENT 값으로 N등분한 결과
     • Ntile(4) over (partition by ~~) → 4등분한 것

       SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC ) AS QUAR_TILE 
       FROM EMP ;

       

  4. RATIO_TO_REPORT

     • -- 파티션 내 전체 SUM(컬럼)에 대한 행 별 컬럼 값의 백분율을 소수점까지 조회합니다.
       ⇒ (점유율 조회)
     • 파티션을 나누어 각 sal이 각 deptno에서 어느정도 점유율을 갖고 있는지 조회

     )출처

📌 선형 분석을 포함한 통계 분석 함수

• 선형분석을 포함한 통계분석 함수
• CORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, REGR_(LINEAR REGRESSION), REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY

CTE (Common Table Expressions)

• 단일 문의 범위 내에 존재
  • 서브쿼리로 쓰이는 파생테이블(derived table)과 비슷한 개념
  • 복잡한 쿼리문에서 코드의 가독성과 재사용성을 위해 파생테이블 대신 사용하기에 유용
  • 즉, 서브쿼리보다 깔끔할수도 ?
• 해당 문 내에서 여러 번 참조할 수 있는 명명된 임시 결과 집합 = 테이블
• 📌 즉, 계속해서 사용할 수 있는 가상의 테이블

CTE 만들기

• 예시
  

- `WITH`로 시작하여 *`CTE Body`*까지가 CTE입니다.
- `AS()` 안의 쿼리에 따라 `engineers`라는 이름의 결과 집합이 일시적으로 생성됩니다.
- CTE로 만들어진 결과 `engineers`는 *CTE Usage* 부분의 `FROM` 절 등에서 사용 가능합니다.
- CTE의 결과는 데이터베이스에 저장되지 않고, **쿼리가 수행되는 동안에만 존재**합니다.

• 단일 테이블

WITH 가상테이블명 
    AS ( SELECT 쿼리 
                UNION ALL 
                SELECT 쿼리 )

-- 예시

WITH cte_film
     AS (SELECT film_id,
                title,
                ( CASE
                    WHEN length < 30 THEN 'Short'
                    WHEN length < 90 THEN 'Medium'
                    ELSE 'Long'
                  END ) length
         FROM   film)
SELECT film_id,
       title,
       length
FROM   cte_film
WHERE  length = 'Long'
ORDER  BY title;

• 2개 이상의 가상 테이블
• , 로 추가

WITH 
        가상1 AS ( 서브쿼리문 ), 
        가상2 AS ( 서브쿼리문 )

-- 실제 사용 

SELECT 컬럼, [컬럼, ...] FROM 가상1, 가상2

서브쿼리 쓰면 안되나 ? 굳이 with 써야하나 ?

• 굳이 가상 테이블을 With문으로 만들 필요는 없음
• 하지만 With문을 활용하였을 때 해당 SQL 환경에서 임시로 재사용이 가능하다는 장점
  • 즉 CREATE TABLE을 하는게 아니라
  • 임시로 테이블을 사용할때 유용함

VIEW vs CTE

• VIEW는 만들기 위해 권한이 필요하고 사전에 정의를 해야한다.
• 반면, CTE는 권한이 필요 없고 하나의 쿼리문이 끝날때까지만 지속되는 일회성 테이블

주의할점

• MySQL 8과 PostgreSQL에서는 CTE를 Materializing한다
• CTE를 무분별하게 사용할 경우, Query performance가 오히려 더 떨어질 수 있다
• Query Optimizer가 실행력을 계산할 때 CTE의 최적화를 고려하지 않는다
  • 예로, 설정한 index가 CTE에서는 적용되지 않게 된다

window func의 PARTITION BY 복습

분석함수([칼럼]) OVER(PARTITION BY 칼럼1, 칼럼2... [ORDER BY 절] [WINDOWING 절])

• OVER 절 내부에 PATITION BY 절을 사용하지 않으면 쿼리 결과 전체를 집계
• PARTITION BY 절을 사용하면 쿼리 결과에서 해당 칼럼을 그룹으로 묶어서 결과를 표시
  • 즉, 구문 마다 GROUP BY 해서 컬럼에 값을 담는다.

⛔ 분석함수 관련 정리

SUM(SAL)

분석함수 SUM을 사용했고 SAL 칼럼에 대한 행들이 행 그룹입니다.

OVER

분석절이라고 합니다. 분석함수에 대한 조절을 OVER절 안에서 합니다.

PARTITION BY

GROUP BY와 동일하게 그룹지어 결과를 출력 합니다.

ORDER BY

PARTITION BY로 정의된 WINDOW 내에서 행들의 정렬순서를 정의해줍니다.

📌 JOIN 의 종류

1. 묵시적 조인

   SELECT 속성명

   FROM 테이블1, 테이블2

   WHERE 테이블1.속성1 = 테이블2.속성1

1. 명시적 조인

   SELECT 속성명

   FROM 테이블1 JOIN 테이블2

   ON 테이블1.속성1 = 테이블2.속성1

📌 SUBQUERY 와 JOIN 의 차이

서브 쿼리는 복잡한 SQL 쿼리문에 많이 사용됩니다.

반면에, 조인은 여러 개의 쿼리를 필요로 하지 않습니다.

조인은 쿼리문이 복잡해지더라도 서브 쿼리에 비해 읽어내기 수월합니다.

• 서브 쿼리를 조인으로 대체할 수 있는 경우

  • 내부 쿼리가 단일한 값을 반환한다면 이는 조인으로도 충분히 구현할 수 있다
    • 스칼라 서브쿼리
  • IN 연산자 안에 서브 쿼리가 있다면 해당 서브 쿼리를 조인으로 바꿔 쓸 수 있습니다. 이 경우는 내부 쿼리, 즉, 서브 쿼리가 여러 개의 값을 반환합니다.
    • NOT IN 연산자 안에 있는 서브 쿼리포함
  • EXISTS와 NOT EXISTS 연산자 안에 있는 서브 쿼리도 조인으로 대체

• 하위 쿼리를 JOIN으로 대체할 수 없는 경우

  • GROUP BY가 있는 FROM의 하위 쿼리

      SELECT city,
             sum_price
      FROM   (SELECT city,
                     Sum(price) AS sum_price
              FROM   sale
              GROUP  BY city) AS s
      WHERE  sum_price < 2100;

  • WHERE 절에서 집계 값을 반환하는 하위 쿼리

      SELECT name FROM product
      WHERE cost<(SELECT AVG(price) from sale);

  • ALL 절의 하위 쿼리

    📌 결론

  • 웬만하면 JOIN 사용하자

이미지 출처 : https://seohc.tistory.com/87
| 구분 | 분석함수 |
| --- | --- |
| 집계 | COUNT, MAX, MIN, SUM, AVG |
| 순위 | ROW_NUMBER, RANK, DENSE_RANK |
| 순서 | FIRST_VALUE, LAST_VALUE, LAG, LEAD |
| 통계 | STDDEV, VARIANCE |
| 비율 | RATIO_TO_REPORT, CUME_DIST, PERCENT_RANK, NTITLE |

📌 JOIN 의 종류

1. 묵시적 조인

   SELECT 속성명

   FROM 테이블1, 테이블2

   WHERE 테이블1.속성1 = 테이블2.속성1

1. 명시적 조인

   SELECT 속성명

   FROM 테이블1 JOIN 테이블2

   ON 테이블1.속성1 = 테이블2.속성1

📌 SUBQUERY 와 JOIN 의 차이

서브 쿼리는 복잡한 SQL 쿼리문에 많이 사용됩니다.

반면에, 조인은 여러 개의 쿼리를 필요로 하지 않습니다.

조인은 쿼리문이 복잡해지더라도 서브 쿼리에 비해 읽어내기 수월합니다.

• 서브 쿼리를 조인으로 대체할 수 있는 경우

  • 내부 쿼리가 단일한 값을 반환한다면 이는 조인으로도 충분히 구현할 수 있다
    • 스칼라 서브쿼리
  • IN 연산자 안에 서브 쿼리가 있다면 해당 서브 쿼리를 조인으로 바꿔 쓸 수 있습니다. 이 경우는 내부 쿼리, 즉, 서브 쿼리가 여러 개의 값을 반환합니다.
    • NOT IN 연산자 안에 있는 서브 쿼리포함
  • EXISTS와 NOT EXISTS 연산자 안에 있는 서브 쿼리도 조인으로 대체

• 하위 쿼리를 JOIN으로 대체할 수 없는 경우

  • GROUP BY가 있는 FROM의 하위 쿼리

      SELECT city,
             sum_price
      FROM   (SELECT city,
                     Sum(price) AS sum_price
              FROM   sale
              GROUP  BY city) AS s
      WHERE  sum_price < 2100;

  • WHERE 절에서 집계 값을 반환하는 하위 쿼리

      SELECT name FROM product
      WHERE cost<(SELECT AVG(price) from sale);

  • ALL 절의 하위 쿼리

    📌 결론

  • 웬만하면 JOIN 사용하자

이미지 출처 : https://seohc.tistory.com/87