You& Data_Science & Life

SQL_9_GroupBy

30 Jun 2023 SQL

1. 데이터의 그룹화

SELECT group_col_1, group_col_2, agg_func
FROM table_nm 
WHERE conditional
GROUP BY col_1, col_2;

-- EX.
-- 원본 데이블
-- SELECT * FROM ENG_SCORE;
-- +-----+----------+-----------+-----------+
-- | ID  | ENG | MATH  | GENDER  |
-- +-----+----------+-----------+-----------+
-- | 김필호 | 28     |  49    | 남자
-- | 이치훈 | 68     |  98    | 남자
-- | 김영숙 | 87     |  87    | 여자
-- | 성은영 | 65     |  78    | 여자
-- +-----+----------+-----------+-----------+

-- 성별 기준 그룹핑
SELECT GENDER, 
    AVG(ENG) AS ENG_SCORE,
    AVG(MATH) AS MATH_SCORE
FROM ENG_SCORE
GROUP BY GENDER;
-- +-----+----------+-----------+
-- | GENDER    | ENG_SCORE  | MATH_SCORE  
-- +-----+----------+-----------+
-- | ENG       | 48         |  73.5   
-- | MATH      | 76         |  82.5  
-- +-----+----------+-----------+

TIP

  • SELECT문의 출력 대상이 되는 열은 “꼭” GROUP BY 절에 있어야함.
  • GROUP BY절에 포함되는 열이 1개씩 증가할 때마다, 차원(Dimension)이 한개씩 증가하고 좀 더 세부적으로 분석 가능하다.
  • 순서: WHERE > GROUP BY > ORDER BY
  • 그룹화될 열에 NULL값이 존재할 경우, NULL값도 그룹화 됨.
  • GROUP BY & HAVING절에선, AS로 설정한 별칭을 활용하여 그룹핑을 할 수 있음.


2. 그룹화된 데이터의 필터링

1) HAVING

: HAVING은 WHERE 조건절과 마찬가지로 조건을 주는 역할을 하는데, 차이는 HAVING은 그룹화된 변수에 대해서만 대해 조건을 줌.

  • WHERE절은 개별 레코드에 대한 조건을 검사
  • HAVING절은 그룹화된 결과 집합에 조건을 검사
SELECT group_col_1, agg_func
FROM table_nm 
WHERE conditional
GROUP BY col_1
HAVING conditional_of_agg_func_output;

-- EX)
-- 1) 부서별 구성원이, 5명 초과인 부서 추출
SELECT SEG, 
    COUNT(*) AS PART_CNT
FROM PERF_MAST_201312
GROUP BY SEG
HAVING COUNT(*) > 5;
-- +------+----------+
-- | SEG  | PART_CNT |
-- +------+----------+
-- | MASS |        6 |
-- +------+----------+


-- 2) 부서별 직전달 구매금액 총액이, 500,000원 이상인 부서 추출
SELECT SEG, 
    SUM(TOT_AMT_1) AS TOT_AMT_1_SUM
FROM PERF_MAST_201312
GROUP BY SEG
HAVING SUM(TOT_AMT_1)>= 500000;
-- +-----+---------------+
-- | SEG | TOT_AMT_1_SUM |
-- +-----+---------------+
-- | PB  |      11534253 |
-- +-----+---------------+


-- 3) 부서별 직전달 인당 평균 금액이, 200000원 이하인 부서 추출
SELECT SEG, 
    AVG(TOT_AMT_1) AS TOT_AMT_1_AVG 
FROM PERF_MAST_201312
GROUP BY SEG
HAVING AVG(TOT_AMT_1) <= 200000;
-- +------+---------------+
-- | SEG  | TOT_AMT_1_SUM |
-- +------+---------------+
-- | MASS |   108726.2500 |
-- +------+---------------+


-- 3) 직전달 금액이 50000인 이상인 부서원에 한하여, 부서별 직전달 인당 평균 금액이, 200000원 이하인 부서 추출
SELECT SEG, 
    AVG(TOT_AMT_1) AS TOT_AMT_1_SUM
FROM PERF_MAST_201312
WHERE TOT_AMT_1 >= 50000
GROUP BY SEG
HAVING AVG(TOT_AMT_1) <= 200000;
-- +------+---------------+
-- | SEG  | TOT_AMT_1_SUM |
-- +------+---------------+
-- | MASS |   144788.3333 |
-- +------+---------------+

TIP

  • WHERE 조건절의 조건은 데이터가 그룹화 되기 ‘전에’ 필터링을 진행
  • HAVING 조건절의 조건은 데이터가 그룹화 된 ‘후에’ 필터링을 진행
    • 따라서 WHERE조건절에서 제외된 행은, GROUP BY 및 HAVING 조건절 모두에서 제외됨.


+ 연습문제

  • PPC_MAST_201312 테이블 
    +---------------+---------+---------+------+-------------+
| SSN           | ACCT_NO | ACCT_CD | PRFT | BALANCE_AMT |
+---------------+---------+---------+------+-------------+
| 7802221111111 | 22033   |     130 |  504 |       56746 |
| 8307153333444 | 54412   |     110 |  585 |       23540 |
| 5605099999222 | 65433   |     340 |  213 |      987800 |
| 8012301111333 | 58721   |     320 |  780 |      310000 |
| 6711032222111 | 23422   |     120 | 5679 |           3 |
| 8910103333222 | 89811   |     310 |  240 |       40011 |
| 7802221111111 | 78022   |     100 |  899 |     4565000 |
| 6711032222111 | 35714   |     300 | 3780 |     2545640 |
| 8910103333222 | 68740   |     310 |  233 |      522312 |
| 5605099999222 | 96870   |     330 | 7000 |        2158 |
| 7802221111111 | 89770   |     140 | 1000 |      566600 |
| 6711032222111 | 33270   |     130 | 5600 |       68770 |
| 7802221111111 | 87890   |     340 | 1270 |     5500000 |
+---------------+---------+---------+------+-------------+

-- 1-1) 자산 & 부채 테이블 생성
-- 수신(부채) 코드 : 100, 110, 120, 130, 140
-- 여신(자산) 코드 : 300, 310, 320, 330, 340
SELECT 
    CASE
        WHEN ACCT_CD IN (100, 110, 120, 130, 140) THEN 'LIABILITY'
        WHEN ACCT_CD IN (300, 310, 320, 330, 340) THEN 'ASSET'
        ELSE ACCT_CD END AS BALANCE_SHEET,
    sum(BALANCE_AMT) AS TOTAL_BALANCE_AMT
FROM PPC_MAST_201312
GROUP BY BALANCE_SHEET
ORDER BY BALANCE_SHEET ;
-- +---------------+-------------------+
-- | BALANCE_SHEET | TOTAL_BALANCE_AMT |
-- +---------------+-------------------+
-- | ASSET         |           9907921 |
-- | LIABILITY     |           5280659 |
-- +---------------+-------------------+

-- 2) 고객별 상품 갯수 테이블 생성
SELECT
    SSN, 
    COUNT(ACCT_NO) AS PPC
FROM PPC_MAST_201312
GROUP BY SSN
ORDER BY PPC DESC;
-- +---------------+-----+
-- | SSN           | PPC |
-- +---------------+-----+
-- | 7802221111111 |   4 |
-- | 6711032222111 |   3 |
-- | 5605099999222 |   2 |
-- | 8910103333222 |   2 |
-- | 8307153333444 |   1 |
-- | 8012301111333 |   1 |
-- +---------------+-----+

-- 3) 고객별 상품 갯수가 3개 이상인 고객만 추출
SELECT
    SSN, 
    COUNT(ACCT_NO) AS PPC
FROM PPC_MAST_201312
GROUP BY SSN
HAVING PPC >= 3
ORDER BY PPC DESC;
-- +---------------+-----+
-- | SSN           | PPC |
-- +---------------+-----+
-- | 7802221111111 |   4 |
-- | 6711032222111 |   3 |
-- +---------------+-----+

-- 4) 고객별 총수익과 PPC 테이블
SELECT SSN,
    COUNT(ACCT_NO) AS PPC,
    SUM(PRFT) AS PRFT_TOTAL
FROM PPC_MAST_201312
GROUP BY SSN
ORDER BY PPC DESC;
-- +---------------+-----+------------+
-- | SSN           | PPC | PRFT_TOTAL |
-- +---------------+-----+------------+
-- | 7802221111111 |   4 |       3673 |
-- | 6711032222111 |   3 |      15059 |
-- | 5605099999222 |   2 |       7213 |
-- | 8910103333222 |   2 |        473 |
-- | 8307153333444 |   1 |        585 |
-- | 8012301111333 |   1 |        780 |
-- +---------------+-----+------------+

Reference

  • 칼퇴족 김대리는 알고 나만 모르는 SQL - 책밥

Related posts