집계함수란?
집계함수란 데이터를 집계(계산) 처리하는 함수다. 보통 GROUP BY와 함께 사용되며, 그룹별 데이터 값을 집계하기 위해 사용한다. 대표적인 집계함수를 살펴보면 다음과 같다.
•
SUM: 그룹별로 합계를 구하는 집계함수
•
MIN/MAX: 그룹별로 최소, 최대값을 구하는 집계함수
•
COUNT: 그룹별로 건수를 카운트하는 집계함수
•
AVG: 그룹별로 평균을 구하는 집계함수
이외에도 다양한 집계함수가 있으나, 이 정도만 알아도 SQL을 활용하고 데이터의 기본적인 분석을 수행하는데 전혀 어려움이 없다. 일반적으로 GROUP BY를 사용하는 이유는 그룹별로 데이터를 집계하기 위해서다. 그러므로 GROUP BY와 집계함수는 바늘과 실처럼 자주 붙어 다닌다. (물론 따로 사용되기도 한다.)
COUNT
GROUP BY로 만들어진 그룹별로 데이터 건수를 카운트한다.
-- [SQL-5-2-1] Item 데이터 조회
-- GROUP BY 및 집계 처리전 SQL
SELECT T1.ItemId ,T1.ItemNm ,T1.ItemCat ,T1.HotColdCd
FROM startdb.Item T1
WHERE T1.ItemSizeCd = 'REG'
AND T1.LaunchDt = STR_TO_DATE('20190101','%Y%m%d')
ORDER BY T1.ItemCat;
ItemId ItemNm ItemCat HotColdCd
------ ------------------- ------- ---------
HCHR Hot Chocolate(R) BEV HOT
LEMR Lemonade(R) BEV COLD
BGLR Bagel(R) BKR HOT
BMFR Blueberry Muffin(R) BKR COLD
CMFR Chocolate Muffin(R) BKR COLD
AMR Americano(R) COF HOT
CLR Cafe Latte(R) COF HOT
IAMR Iced Americano(R) COF COLD
ICLR Iced Cafe Latte(R) COF COLD
-- [SQL-5-2-2] [SQL-5-2-1]을 ItemCat별 집계(카운트) 처리
SELECT T1.ItemCat
,COUNT(*) CNT
FROM startdb.Item T1
WHERE T1.ItemSizeCd = 'REG'
AND T1.LaunchDt = STR_TO_DATE('20190101','%Y%m%d')
GROUP BY T1.ItemCat
ORDER BY T1.ItemCat;
ItemCat CNT
------- ---
BEV 2
BKR 3
COF 4
SQL
복사
GROUP BY와 함께 COUNT 집계함수를 사용하면, 그룹별로 그룹을 구성한 데이터의 건수가 카운트된다. 결과를 보면, 상품카테고리(ItemCat)가 BEV(음료)인 상품이 두 건, BKR(베이커리) 상품이 세 건, COF(커피)인 데이터가 네 건 있는 것을 알 수 있다.
아래 SQL은 HotColdCd별로 GROUP BY하고 카운트를 하는 SQL이다.
-- [SQL-5-2-3] [SQL-5-2-1]을 HotColdCd별 집계(카운트) 처리
SELECT T1.HotColdCd
,COUNT(*) CNT
FROM startdb.Item T1
WHERE T1.ItemSizeCd = 'REG'
AND T1.LaunchDt = STR_TO_DATE('20190101','%Y%m%d')
GROUP BY T1.HotColdCd
ORDER BY T1.HotColdCd;
-- 결과
HotColdCd CNT
--------- ---
COLD 5
HOT 4
SQL
복사
MIN / MAX
그룹별로 최소값(MIN)과 최대값(MAX)을 구하기 위해 사용하는 집계함수다.
-- [SQL-5-2-4] Ord 데이터 조회
SELECT T1.OrdNo ,T1.ShopId, T1.OrdDtm ,T1.OrdAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20210501','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20210601','%Y%m%d')
AND T1.ShopId IN ('S190','S244')
ORDER BY T1.ShopId ,T1.OrdNo;
-- GROUP BY와 집계함수(MIN/MAX) 처리전 결과
OrdNo ShopId OrdDtm OrdAmt
----- ------ ------------------- --------
12791 S190 2021-05-02 10:00:00 4000.000
12805 S190 2021-05-02 10:00:00 8500.000
12821 S190 2021-05-02 11:00:00 4500.000
13766 S190 2021-05-30 10:00:00 4500.000
13782 S190 2021-05-30 10:00:00 6500.000
13805 S190 2021-05-30 11:00:00 4000.000
13566 S244 2021-05-23 12:30:00 4000.000
13733 S244 2021-05-29 12:30:00 3500.000
-- [SQL-5-2-5] [SQL-5-2-4]를 ShopId 별로 MIN/MAX 처리
SELECT T1.ShopId
,MIN(T1.OrdDtm) `5월첫번째주문일자`
,MAX(T1.OrdDtm) `5월마지막주문일자`
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20210501','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20210601','%Y%m%d')
AND T1.ShopId IN ('S190','S244')
GROUP BY T1.ShopId
ORDER BY T1.ShopId;
-- GROUP BY와 집계함수(MIN/MAX) 처리후 결과
ShopId 5월첫번째주문일자 5월마지막주문일자
------ ------------------- -------------------
S190 2021-05-02 10:00:00 2021-05-30 11:00:00
S244 2021-05-23 12:30:00 2021-05-29 12:30:00
SQL
복사
SUM / AVG
그룹별로 합계(SUM)와 평균(AVG)을 구하기 위해 사용하는 집계함수다.
-- [SQL-5-2-6] Ord 데이터 조회
SELECT T1.OrdNo ,T1.ShopId, T1.OrdDtm ,T1.OrdAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20210501','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20210601','%Y%m%d')
AND T1.ShopId IN ('S190','S244')
ORDER BY T1.ShopId ,T1.OrdNo;
-- GROUP BY와 집계함수(MIN/MAX) 처리전 결과
OrdNo ShopId OrdDtm OrdAmt
----- ------ ------------------- --------
12791 S190 2021-05-02 10:00:00 4000.000
12805 S190 2021-05-02 10:00:00 8500.000
12821 S190 2021-05-02 11:00:00 4500.000
13766 S190 2021-05-30 10:00:00 4500.000
13782 S190 2021-05-30 10:00:00 6500.000
13805 S190 2021-05-30 11:00:00 4000.000
13566 S244 2021-05-23 12:30:00 4000.000
13733 S244 2021-05-29 12:30:00 3500.000
-- [SQL-5-2-7] [SQL-5-2-6]을 ShopId별로 집계 처리
SELECT T1.ShopId
,ROUND(SUM(T1.OrdAmt),1) `5월주문금액합계`
,ROUND(AVG(T1.OrdAmt),1) `5월주문금액평균`
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20210501','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20210601','%Y%m%d')
AND T1.ShopId IN ('S190','S244')
GROUP BY T1.ShopId
ORDER BY T1.ShopId;
-- GROUP BY와 집계함수(MIN/MAX) 처리 결과
ShopId 5월주문금액합계 5월주문금액평균
------ --------------- ---------------
S190 32000.0 5333.3
S244 7500.0 3750.0
SQL
복사
GROUP BY를 생략한 집계함수
GROUP BY를 생략하고 집계함수만 사용할 수 있다. 이 경우 전체 데이터 집합에 대해 집계함수가 수행된다. (WHERE 절이 있는 SQL이라면 WHERE 절이 처리된 결과에 집계함수가 수행된다.)
-- [SQL-5-2-8]
-- Item의 전체 건수를 카운트
SELECT COUNT(*) ItemCnt
FROM startdb.Item T1;
ItemCnt
-------
21
-- [SQL-5-2-9]
-- 특정 조건의 Item만 카운트
SELECT COUNT(*) ColdItemCnt
FROM startdb.Item T1
WHERE T1.HotColdCd = 'COLD';
ColdItemCnt
-----------
11
-- [SQL-5-2-10]
-- 특정 조건의 Ord에 대해 SUM과 COUNT 처리
SELECT SUM(T1.OrdAmt) OrdAmt ,COUNT(*) OrdCnt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20220102','%Y%m%d');
OrdAmt OrdCnt
--------- ------
15000.000 3
SQL
복사
NULL과 SUM 집계함수
•
NULL + 1과 같이 NULL에 산술 연산을 하면 결과는 NULL이다.
•
반면에 SUM 집계함수는 NULL을 아예 제외하고 집계 처리한다.
◦
단, SUM 처리할 대상이 모두 NULL이면, SUM 결과는 NULL이다.
•
아래 SQL을 실행해 NULL에 대한 SUM 결과를 살펴보자.
-- [SQL-5-2-11]
SELECT NULL + 1 C1 FROM DUAL;
C1
----
NULL
-- [SQL-5-2-12]
SELECT SUM(T1.C1) SUM_C1
FROM (
SELECT NULL C1 FROM DUAL
UNION ALL
SELECT 1 C2 FROM DUAL
) T1;
SUM_C1
------
1
-- [SQL-5-2-13]
SELECT SUM(T1.C1) SUM_C1
FROM (
SELECT NULL C1 FROM DUAL
UNION ALL
SELECT NULL C1 FROM DUAL
) T1;
SUM_C1
------
NULL
SQL
복사
Tip. UNION ALL
UNION ALL은 위 아래의 데이터 집합을 하나로 결합하는 구문. 나중에 별도로 설명한다.
Tip. INLINE-VIEW
FROM 절에 괄호를 사용해 괄호 안에 또 다른 SELECT SQL을 안에 사용할 수 있다. 이와 같은 방법을 인라인-뷰라고 하며 이 부분도 나중에 별도로 다룬다.
NULL과 COUNT 집계함수
•
COUNT는 괄호 안에 컬럼명이나 ‘*’ 를 사용할 수도 있다.
•
COUNT(*)는 데이터 값과 상관 없이 레코드 자체를 카운트한다.
•
COUNT(컬럼명)은 컬럼의 값이 NULL 이면 0으로 카운트한다.
◦
COUNT는 SUM처럼 집계 처리할 대상이 모두 NULL이라고 NULL로 카운트하지 않는다.
•
일반적으로 COUNT(*)을 사용한다.
◦
나중에 OUTER JOIN에서는 COUNT(컬럼명)을 반드시 사용해야 할 때가 있다.
•
아래 SQL을 실행해 NULL과 COUNT에 대해 살펴보자.
-- [SQL-5-2-14]
-- LeaveDtm이 NULL인 데이터만 조회했습니다.
SELECT COUNT(*) CNT_ALL
,COUNT(T1.JoinDtm) CNT_JoinDtm
,COUNT(T1.LeaveDtm) CNT_LeaveDtm
FROM startdb.Member T1
WHERE T1.JoinDtm = STR_TO_DATE('20200516','%Y%m%d')
AND T1.LeaveDtm IS NULL
ORDER BY T1.MemberId;
CNT_ALL CNT_JoinDtm CNT_LeaveDtm
------- ----------- ------------
6 6 0
-- [SQL-5-2-15]
-- 데이터 전체가 NULL, 컬럼도 NULL인 경우입니다.
SELECT COUNT(*) CNT_ALL, COUNT(T1.C1) CNT_C1
FROM (
SELECT NULL C1 FROM DUAL
) T1;
CNT_ALL CNT_C1
------- ------
1 0
SQL
복사
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 5-2-1
(SQL-1)과 (결과-1)을 참고해, (SQL-2)를 실행하면 나올 (결과-2)를 채우시오.
•
SQL을 실행하지 않고 (결과-2)를 예측해서 채우시오.
•
(SQL-2)는 (SQL-1)을 GROUP BY 처리한 SQL이다.
-- (SQL-1)
SELECT T1.ShopId, T1.MemberId ,T1.OrdDtm ,T1.OrdAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20200101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20200201','%Y%m%d')
AND T1.ShopId IN ('S015','S016','S023')
ORDER BY T1.ShopId ,T1.OrdNo;
-- (결과-1)
ShopId MemberId OrdDtm OrdAmt
------ -------- ------------------- --------
S015 M1278 2020-01-02 13:00:00 8000.000
S015 M1278 2020-01-30 13:00:00 4500.000
S016 M0292 2020-01-02 11:00:00 4000.000
S016 M0292 2020-01-30 11:00:00 3000.000
S023 M1293 2020-01-11 10:30:00 7500.000
S023 M1293 2020-01-23 10:30:00 4000.000
S023 M1293 2020-01-29 10:30:00 4000.000
SQL
복사
-- (SQL-2)
SELECT T1.ShopId ,COUNT(*) CNT, SUM(T1.OrdAmt) SUM_AMT
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20200101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20200201','%Y%m%d')
AND T1.ShopId IN ('S015','S016','S023')
GROUP BY T1.ShopId
ORDER BY T1.ShopId;
-- (결과-2)
SQL
복사
BOOSTER QUIZ-5-2-2
(SQL-1)과 (결과-1)을 참고해, (SQL-2)를 실행하면 나올 (결과-2)를 채우시오.
•
SQL을 실행하지 않고 (결과-2)를 예측해서 채우시오.
•
(SQL-2)는 (SQL-1)을 GROUP BY 처리한 SQL이다.
-- (SQL-1)
SELECT T1.ShopId, T1.MemberId ,T1.OrdDtm ,T1.OrdAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20200101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20200201','%Y%m%d')
AND T1.ShopId IN ('S015','S016','S023')
ORDER BY T1.ShopId ,T1.OrdNo;
-- (결과-1)
ShopId MemberId OrdDtm OrdAmt
------ -------- ------------------- --------
S015 M1278 2020-01-02 13:00:00 8000.000
S015 M1278 2020-01-30 13:00:00 4500.000
S016 M0292 2020-01-02 11:00:00 4000.000
S016 M0292 2020-01-30 11:00:00 3000.000
S023 M1293 2020-01-11 10:30:00 7500.000
S023 M1293 2020-01-23 10:30:00 4000.000
S023 M1293 2020-01-29 10:30:00 4000.000
SQL
복사
-- (SQL-2)
SELECT T1.MemberId ,SUM(T1.OrdAmt) SUM_AMT ,MIN(T1.OrdDtm) 최초주문일
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20200101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20200201','%Y%m%d')
AND T1.ShopId IN ('S015','S016','S023')
GROUP BY T1.MemberId
ORDER BY T1.MemberId;
-- (결과-2)
SQL
복사
BOOSTER QUIZ 5-2-3
커피 카테고리 메뉴(상품)가 사이즈별로 몇 개인지 알려주세요. 단순히 메뉴 개수를 알고 싶어요.
•
대상 테이블: 상품(Item)
•
조회 조건: ItemCat가 COF(커피)인 데이터
•
조회 컬럼: ItemSizeCd, ItemCnt
•
추가 조건
◦
ItemSizeCd 별로 GROUP BY 처리하시오.
◦
ItemCnt는 ItemSize별 상품 건수
•
정렬 기준: ItemSizeCd로 오름차순 정렬하시오.
ItemSizeCd ItemCnt
---------- -------
BIG 4
REG 8
SQL
복사
BOOSTER QUIZ 5-2-4
2022년 1월 주문에 대해 회원ID 별로 주문금액 합계와 주문 건수를 뽑아주세요.
•
대상 테이블: 주문(Ord)
•
조회 조건: OrdDtm이 2022년 1월인 주문 데이터
•
조회 컬럼: MemberId, OrdAmt_Sum, OrdCnt
•
추가 조건
◦
MemberId 별로 GROUP BY 처리하시오.
◦
OrdAmt_Sum은 MemberId별 OrdAmt를 SUM 집계한 값
◦
OrdCnt는 MemberId별 데이터 건수
•
정렬 기준: OrdAmt_Sum으로 내림차순 정렬하시오.
MemberId OrdAmt_Sum OrdCnt
-------- ---------- ------
M2001 177000.000 31
M0001 163000.000 31
M1001 154000.000 31
M1249 39500.000 4
M1201 37500.000 4
M0207 34000.000 4
… 생략 …
SQL
복사
