요약과 집계 각종 요약과 집계 기법을 이해한다. 각종 집계 함수를 이해한다.

Presentation on theme: "요약과 집계 각종 요약과 집계 기법을 이해한다. 각종 집계 함수를 이해한다."— Presentation transcript:

1 요약과 집계 각종 요약과 집계 기법을 이해한다. 각종 집계 함수를 이해한다.
구식 요약 방법인 COMPUTE와 COMPUTE BY 절을 이해한다. GROUP BY 절, HAVING 절, ROLLUP 및CUBE 연산자, GROUPING 함수 등을 이해한다.

2 요약과 집계 집계 함수 GROUP BY 절 3. 집계 연산자

3 1. 집계 함수 COUNT 함수 집계(aggregate) 함수: 값 집합에 대한 계산을 수행한 후 단일 값을
돌려주는 함수이며, GROUP BY 또는 COMPUTE (BY) 절이 포함된 SELECT 문에서 많이 사용된다. COUNT 함수는 행의 개수를 돌려준다. COUNT 함수의 구문

4 1. 집계 함수 COUNT 함수 구문 설명 *는 행 전체의 개수를 돌려준다(열에 NULL을 포함한 행들도 모두
포함). 일반적으로 *를 가장 많이 사용한다. expression은 text, ntext, image를 제외한 형식의 식으로, 집계함수와 하위 질의는 허용되지 않는다(대부분의 경우 열 이름이 옴). ALL은 NULL이 아닌 값을 가지는 모든 expression의 개수를 돌려 주고, DISTINCT는 NULL이 아닌 고유한 값을 가지는 expression의 개수를 돌려준다(생략할 때는 ALL 적용). WHERE 절을 만족하는 행이 없거나 전체적으로 행이 하나도 없을 경우, COUNT 함수는 유일하게 0을 돌려준다(다른 모든 집계 함수는 이 경우에 NULL을 돌려줌).

5 1. 집계 함수 COUNT 함수 COUNT 함수를 사용하는 예를 세 가지 살펴보자. 1 USE AdventureWorksLT; 2 GO 3 SELECT CustomerID, MiddleName 4 FROM SalesLT.Customer; 5 SELECT COUNT(*) AS 'COUNT(*)', 6 COUNT(ALL MiddleName) AS 'COUNT(ALL MiddleName)', 7 COUNT(DISTINCT MiddleName) AS 'COUNT(DISTINCT MiddleName)' 8 FROM SalesLT.Customer; 예제 1

6 1. 집계 함수 COUNT 함수

7 1. 집계 함수 SUM / AVG 함수 SUM, AVG : 열 값들의 합계 및 평균값을 돌려주는 함수이다. 구문

8 1. 집계 함수 SUM / AVG 함수 구문 설명 expression은 bit를 제외한 숫자 형식의 식으로, 집계 함수와 하위
질의는 허용되지 않는다(대부분의 경우 열 이름이 옴). ALL은 NULL이 아닌 값을 가지는 모든 expression의 합계 또는 평균 값을 돌려주고, DISTINCT는 NULL이 아닌 고유한 값을 가지는 expression의 합계 또는 평균값을 돌려준다(생략할 때는 ALL 적용). expression이 속한 데이터 형식 범주 중 가장 정확한 형(예: int, bigint, decimal(38, s), money 또는 float 형)으로 값을 돌려준다 (예: expression이 tinyint 형이라면 int 형으로 값을 돌려줌).

9 1. 집계 함수 SUM / AVG 함수 SUM/AVG 함수에 ALL/DISTINCT 적용 결과를 나타내보자. 예제 2
1 SELECT SUM(ALL ListPrice) AS 'SUM(ALL)', 2 SUM(DISTINCT ListPrice) AS 'SUM(DISTINCT)', 3 AVG(ALL ListPrice) AS 'AVG(ALL)', 4 AVG(DISTINCT ListPrice) AS 'AVG(DISTINCT)' 5 FROM SalesLT.Product;

10 1. 집계 함수 MIN / MAX 함수 MIN, MAX: 열 값들 중에서 최솟값 또는 최댓값을 돌려주는 함수 구문 구문 설명
expression은 bit를 제외한 숫자형, char, varchar 또는 datetime 형식의 식으로, 집계 함수와 하위 질의는 허용되지 않는다. ALL은 NULL이 아닌 값을 가지는 모든 expression 중 최솟값 또는 최댓값을 돌려준다. MIN과 MAX 함수에서 DISTINCT 키워드는 의미가 없으며, 오직 ISO 호환을 위해서만 존재한다. expression과 같은 형의 값을 돌려준다.

11 1. 집계 함수 MIN / MAX 함수 MIN과 MAX 함수를 사용해보자. 예제 3
1 SELECT MIN(ListPrice) AS MIN, MAX(ListPrice) AS MAX 2 FROM SalesLT.Product;

12 2. GROUP BY 절 사전 준비 GROUP BY 절: 데이터를 그룹화해서 값을 집계할 필요가 있는 경우,
예를 들어 학생들의 나이별, 출신 지역별, 성적별로 인원 수나 합계, 평균값 등을 구하는 경우에 필요하다. GROUP BY 절의 테스트를 위해 3장에서 사용했던 Test1DB의 orders 테이블에 데이터를 추가한다.

13 2. GROUP BY 절 사전 준비 예제 4 1 USE Test1DB;
2 --삽입 열: buyer_id, product_id, qty 3 INSERT INTO orders VALUES (1,1,20); 4 INSERT INTO orders VALUES (1,1,15); 5 INSERT INTO orders VALUES (1,2,150); 6 INSERT INTO orders VALUES (1,3,300); 7 INSERT INTO orders VALUES (1,3,100); 8 INSERT INTO orders VALUES (2,1,30); 9 INSERT INTO orders VALUES (2,2,100); 10 INSERT INTO orders VALUES (2,2,120); 11 INSERT INTO orders VALUES (2,3,150); 12 INSERT INTO orders VALUES (2,3,50); 13 SELECT * 14 FROM orders 15 ORDER BY buyer_id, product_id; 예제 4

14 2. GROUP BY 절 사전 준비

15 2. GROUP BY 절 GROUP BY 절 GROUP BY 절은 특정 열(들)을 그룹화하고 집계 및 요약하기 위해
SELECT 문에 포함하여 사용함 [그림 4-1]은 GROUP BY 절의 개념도이다. 왼쪽의 orders 테이블에 대해 buyer_id와 product_id 순으로 두 단계로 그룹화하고 각 그룹에 대해 SUM(qty)로 집계한 것이 오른쪽 테이블임 오른쪽 테이블에는 각 그룹이 하나의 행으로 요약되고, 각 그룹 내 qty 열 값들의 합계가 SUM(qty) 열에 나타남

16 2. GROUP BY 절 GROUP BY 절

17 2. GROUP BY 절 GROUP BY 절 ISO와 호환되는 단순 GROUP BY 절의 구문
simple_group_by_item은 그룹화할 열 또는 열을 참조하는 연산식 SELECT 문의 열 목록에 있는 집계식이 아닌 모든 열은 simple_ group_by_item에 반드시 나와야 한다. 대부분의 경우, SELECT 문의 열 목록에 집계 함수가 포함된다. ORDER BY 절을 이용하여 그룹화할 열(들)을 정렬해야 한다. 이것을 생략하면 그룹 자체가 정렬되지 않을 수 있다.

18 2. GROUP BY 절 GROUP BY 절 [그림 4-1]의 개념도를 구현해보자. - 8행의 ORDER BY 절을 생략하면 그룹 자체의 순서가 뒤죽박죽된다 (생략하고 실행해 보면 확인할 수 있다). 1 USE Test1DB; 2 SELECT buyer_id, product_id, qty 3 FROM orders 4 ORDER BY buyer_id, product_id; 5 SELECT buyer_id, product_id, SUM(qty) AS 'SUM(qty)' 6 FROM orders 7 GROUP BY buyer_id, product_id 8 ORDER BY buyer_id, product_id; 예제 5

19 2. GROUP BY 절 GROUP BY 절

20 2. GROUP BY 절 GROUP BY 절

21 2. GROUP BY 절 HAVING 절 HAVING 절: GROUP BY 절에 의해 만들어지는 행을 제약할 때 쓰임
WHERE 절이 SELECT 문에 의해 만들어지는 행을 제약하는 것과 비슷한 관계다. 구문 대부분의 경우, HAVING 절은 GROUP BY 절과 함께 쓰인다. search_condition은 WHERE 절의 검색 조건과 비슷하다. SELECT 문의 열 목록에 나오는 집계식을 포함할 수 있다는 점이WHERE 절과 다르다.

22 2. GROUP BY 절 HAVING 절 [예제 5]에서 만들어진 요약 행을 HAVING 절로 제약해보자. - 이에 의해 원래의 총 6개 행 중 3개 행만 결과 집합에 포함된다. 1 SELECT buyer_id, product_id, SUM(qty) AS 'SUM(qty)' 2 FROM orders 3 GROUP BY buyer_id, product_id 4 HAVING SUM(qty) >= ORDER BY buyer_id, product_id; 예제 6

23 3. 집계 연산자 ROLLUP 연산자 정교하게 집계를 하려면 GROUP BY 절만으로는 부족한 경우가 있다.
이때 사용할 수 있는 것이 집계 연산자다. ROLLUP 연산자: GROUP BY 절의 일부로 사용되어, 집계 값을 요약하는 데 사용

24 3. 집계 연산자 ROLLUP 연산자 구문 simple_group_by_item들에 대해 단순 GROUP BY 집계 행뿐만
아니라, 오른쪽에서 왼쪽 순으로 집계 값들을 요약한 추가적인 행을 만들어준다. ROLLUP으로 요약된 행의 해당 열 값은 NULL이 된다. simple_group_by_item들의 순서는 ROLLUP 결과와 결과 집합의 행 수에 영향을 미친다.

25 3. 집계 연산자 ROLLUP 연산자 [그림 4-2]의 개념도를 구현해보자. 예제 7
1 SELECT buyer_id, product_id, SUM(qty) AS 'SUM(qty)' 2 FROM orders 3 GROUP BY ROLLUP(buyer_id, product_id); 예제 7

26 3. 집계 연산자 CUBE 연산자 CUBE 연산자: ROLLUP 연산자와 비슷하지만 더 완전하게 요약해줌
group_by_expression들의 모든 가능한 조합에 대해 집계 값들을 요약한 추가적인 행을 만들어준다. 구문 simple_group_by_item들에 대해 단순 GROUP BY 집계 행과 ROLLUP 요약 행 외에 교차 집계 행을 만들어준다. CUBE로 요약된 행의 해당 열 값은NULL이 된다.

27 3. 집계 연산자 CUBE 연산자

28 3. 집계 연산자 CUBE 연산자 [그림 4-3]의 개념도를 구현해보자. 1 SELECT buyer_id, product_id, SUM(qty) AS 'SUM(qty)' 2 FROM orders 3 GROUP BY CUBE(buyer_id, product_id); 예제 8

29 3. 집계 연산자 CUBE 연산자

30 3. 집계 연산자 GROUPING SETS 연산자 GROUPING SETS 연산자: ROLLUP과 CUBE 연산자가 고정된
방식으로 요약을 하는 것과 달리 좀 더 융통성 있게 요약을 해준다. 구문 ( )는 총합계를 생성한다. simple_group_by_item은 ROLLUP 또는 CUBE 연산자의 것과 같다. grouping_set_item_list는 하나 또는 그 이상의 ( ) 또는 simple_group_by_item이다.

31 3. 집계 연산자 GROUPING SETS 연산자
GROUPING SETS 연산자를 사용해보자. 1 SELECT buyer_id, product_id, SUM(qty) AS 'SUM(qty)' 2 FROM orders 3 GROUP BY GROUPING SETS(buyer_id, product_id, ()); 예제 9

32 3. 집계 연산자 GROUPING SETS 연산자 GROUPING SETS 연산자의 복잡한 예를 들어보자. 예제 10
1 USE AdventureWorks; 2 SELECT T.[Group] AS N'지역', T.CountryRegionCode AS N'국가' 3 ,DATEPART(yyyy,OrderDate) AS '년' 4 ,DATEPART(mm,OrderDate) AS '월' 5 ,SUM(TotalDue) AS N'매출액 합계' 6 FROM Sales.Customer C 7 INNER JOIN Sales.Store S ON C.CustomerID = S.CustomerID 9 INNER JOIN Sales.SalesTerritory T ON C.TerritoryID = T.TerritoryID INNER JOIN Sales.SalesOrderHeader H ON S.CustomerID = H.CustomerID 13 WHERE DATEPART(yyyy,OrderDate) = '2003' 14 GROUP BY GROUPING SETS( (T.[Group], T.CountryRegionCode), (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)), ()) 16 ORDER BY T.[Group], T.CountryRegionCode ,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate); 예제 10

33 3. 집계 연산자

34 3. 집계 연산자 GROUPING 함수 ROLLUP, CUBE 또는 GROUPING SETS 연산자가 돌려주는
추가적인 요약 행에는 열이 요약되었음을 나타내는 NULL(들)이 포함되어 있다. 위 NULL은 집계열의 NULL과 혼동될 소지가 크다. SELECT 문의 열 목록에서 GROUPING 함수를 사용하면 된다. 구문

35 3. 집계 연산자 GROUPING 함수 구문 설명 column_expression은 요약 여부를 확인하고자 하는 열 또는
열을 포함하는 연산식이다. GROUPING 함수는 해당 열이 요약되었을 경우에는 1을 돌려주고, 요약되지 않았을 경우에는 0을 돌려준다. GROUPING 함수는 SELECT 문의 열 목록에서 필요에 따라 여러 번 사용될 수 있다.

36 3. 집계 연산자 GROUPING 함수 GROUPING 함수를 사용해보자. 1 USE Test1DB; 2 SELECT buyer_id, GROUPING(buyer_id) AS 'GROUPING(buyer_id)', 3 product_id, GROUPING(product_id) AS 'GROUPING(product_id)', 4 SUM(qty) AS 'SUM(qty)' 5 FROM orders 6 GROUP BY CUBE(buyer_id, product_id); 예제 11

37 3. 집계 연산자 GROUPING 함수