GROUP BY

<쿼리 교도소 탈출하기> - 2.GROUP BY  | Programming

2. GROUP BY SQL문의 GROUP BY에 대해서 살펴보도록 하겠습니다. GROUP BY란 간단히 말해서 특정 컬럼에 대해 동일한 값끼리 묶어버린다고 생각하시면 됩니다. 그리고 GROUP BY와 함께 사용할 수 있는 것이 바로 집계 함수입니다. 그룹바이의 사용은 다음과 같습니다. SELECT select_list FROM table_source WHERE search_codition GROUP BY select_list WHERE절 다음에 GROUP BY가 오고, GROUP BY에는 컬럼들을 ,로 구분해서 써주면 됩니다. 단, 여기서는 SELECT의 select_list처럼 *를 사용할 수는 없습니다. 한가지 항상 명심해야 할 것은 GROUP BY에 온 컬럼들과 SELECT에 오는 컬럼들은 같아야 한다는 겁니다. 단!! 집계 함수를 사용하는 컬럼은 제외됩니다. 몇가지 간단한 문제를 보면서 GROUP BY와 집계함수에 대해 설명을 해보겠습니다. 문제2-1 Northwind의 제품(Products)들중에서 어떤 카테고리(CategoryID)들이 들어가 있는지 보여주세요 쿼리2-1 SELECT  CategoryID FROM    Products GROUP   BY CategoryID 설명드렸듯이 GROUP BY를 사용하게 되면 해당 컬럼들끼리 그룹을 지어버립니다. 그러므로 위와 같은 쿼리를 날리면, 제품 테이블에서 CategoryID가 1인 제품이 몇 개가 있든 항상 1개만 결과가 나오게 됩니다. 위와 같은 결과를 똑같이 얻기 위해서 사용할 수 있는 것이 SELECT절에 DISTINCT를 추가하는 겁니다. 쿼리2-2 SELECT  DISTINCT CategoryID FROM    Products DISTINCT 다음에 컬럼들을 쭉 적어주면, 해당 컬럼들에 동일한 값들을 하나로 묶어서 하나만 결과값으로 돌려줍니다. 이런 DISTINCT 란 절이 있는데 무엇 때문에 GROUP BY를 사용할까요? GROUP BY의 가장 큰 장점은 집계함수를 같이 사용할 수 있다는 겁니다. 집계 함수란 여러 값들을 여러가지 방법으로 집계해서 한가지 값만을 돌려주는 겁니다. 예를 들어 설명을 하자면 어떤 학생의 점수가 80, 99, 30 일경우 이 학생의 점수중 최하 점수를 알고 싶을 때, SQL에서는 MIN이란 집계함수를 사용하는 겁니다. 예를들면 MIN(점수) 하면 되는 겁니다. MIN 집계 함수를 사용해서 GROUP BY를 계속 설명해 보도록 하겠습니다. 문제2-3 Northwind 제품들중에 가장 가격이 낮은게 얼마인지 보여주세요. 쿼리2-3 SELECT  MIN(UnitPrice) FROM    Products 위의 문제에서 원하는게 가장 낮은게 얼마!! 즉,, 얼마인지를 물어보았습니다. 그러므로 가격만 보여줘도 되는것이므로 MIN(UnitPirce)를 사용해서 가장 낮은 가격을 보여주는 겁니다. 하지만, 대부분 가장 낮은걸 보여달라는건, 제품도 같이 보여줘야되는게 기본이죠..^^ 이거에 대한 처리는 뒤에서 설명을 하겠습니다. 문제2-4 Northwind 제품들의 카테고리별로 가장 낮은 가격을 보여주세요 쿼리2-4 SELECT  CategoryID, MIN(UnitPrice) FROM    Products GROUP BY CategoryID 이 쿼리는 단지 세줄이지만, 이 쿼리를 정확하게 이해하셔야만 GROUP BY를 정확히 이해하실 수 있습니다. 우선 데이터를 보면서 설명을 드립겠습니다. <Northwind의 Products테이블의 일부> ProductID ProductName SupplierID CategoryID ,,, UnitPirce 1 Chai… 1 1   18.0000 2 Chang… 1 1   19.0000 3 Aniseed Syrup… 1 2   10.0000 4 Chef Anton's… 2 2   22.0000 5 Chef Anton's… 2 2   21.3500 쿼리2-4를 실행하시고 결과를 확인하셨을리라 믿고 설명을 드리겠습니다. 위의 테이블은 실제 데이터의 내용을 보여준겁니다. 가장 먼저 GROUP BY CategoryID라고 쿼리를 했기 때문에 현재 1이 두개 2가 3개 있지만, 결과에는 1이 한 건, 2가 한 건 무조건 결과가 하나씩 나오게 됩니다. 위쿼 쿼리 2-4에서 MIN(UnitPrice)를 제외하고 실행하면 CategoryID별로 한건씩 결과가 나옵니다. 여기서 각 CategoryID에는 각 CategoryID에 해당하는 모든 데이터를 가지고 있다고 할 수 있습니다. 말로 설명하니까 어렵군요. CategoryID로 GROUP BY를 했다면 데이터는 다음과 같다고 할 수 있습니다. ProductID ProductName SupplierID CategoryID ,,, UnitPirce 1 Chai… 1 1   18.0000 2 Chang… 1   19.0000 3 Aniseed Syrup 1 2   10.0000 4 Chef Anton's… 2   22.0000 5 Chef Anton's… 2   21.3500 보시면 하늘색의 데이터는 CategoryID로 GROUP BY를 했을 때, 나타낼 수 없는 데이터들입니다. 왜냐면 GROUP BY CategoryID를 했기 때문에 데이터가 출력될 때 CategoryID는 CategoryID별로 무조건 한건씩 나오는데, ProductID나, ProductName은 해당 CategoryID에 여러가지 경우가 있기 때문에 어떤 값을 출력할 것인가를 데이터베이스 마음대로 정할 수 없기 때문입니다. 그러므로 우리는 집계함수를 사용해서 어떤 데이터를 보여달라고 요청할 수가 있는 겁니다. 여기서 MIN(UnitPirce)를 했으므로 CategoryID가 1일 때 현재 테이블에서 UnitPrice의 최소값은 18이므로 18이 출력되고 CategoryID가 2일때는 10이므로 10이 출력되는 겁니다. 이번에는 최대값을 구하는 MAX를 사용해보도록 하겠습니다. 문제2-5 Northwind 제품들의 카테고리별로 가장 낮은 가격과 가장 높은 가격을 보여주세요 쿼리2-5 SELECT  CategoryID, MIN(UnitPrice), MAX(UnitPrice) FROM    Products GROUP BY CategoryID 위의 쿼리를 수행하면 다음과 같이 데이터들을 그려볼 수 있죠(만약 다음의 데이터들만 가지고 있다면) CategoryID UnitPirce   1 18.0000 CategoryID가 1인 데이터의 최소값 19.0000 CategoryID가 1인 데이터의 최대값   2 10.0000 CategoryID가 2인 데이터의 최소값 22.0000   21.3500 CategoryID가 2인 데이터의 최대값 그러므로 결과는 다음과 같습니다. CategoryID                                              ----------- --------------------- --------------------- 1           4.5000                263.5000 2           10.0000               43.9000 결과가 틀린건.. 실제 테이블에는 더 많은 데이터가 있기 때문입니다.!^^ 여기서 주목할 만한 내용은 UnitPrice는 컬럼상으로 하나인데 두개의 컬럼으로 결과를 가져왔다는 겁니다. 이런 방법이 바로 세로로 떨어져 있는 데이터를 가로로 펼쳐내는 기술입니다. 기술이라고 할 것 까진 없지만 프로그램 센스가 있으신 분들은 이 방법을 응용할 수 있는 쿼리문이 굉장히 많을것이라고 느꼈을 겁니다. 지금까지는 GROUP BY에 하나의 컬럼만을 썼을때를 보았습니다. 만약에 여러 컬럼을 쓴다면 어떻게 될까요? 이것또한 별반 다르지 않습니다. 똑 같은 방법으로 해당 컬럼들로 그룹을 짓고 해당 컬럼들내에 최소값이든 최대값등을 가져올 수 있습니다. 문제2-6 학생들중에서 학과별 남녀별로 최고의 점수와 최고의 점수를 맞은 사람의 이름을 보여주세요 쿼리2-6(잘못된 쿼리입니다. 예를 들기 위한 겁니다) --예를 들기 위해서 STUDENT 테이블을 임의로 만듭니다. IF (OBJECT_ID('STUDENT') IS NOT NULL)         DROP TABLE STUDENT   CREATE TABLE STUDENT (         sno char(3) PRIMARY KEY,                     --학번         gwaCode char(2) NOT NULL DEFAULT '',         --학과코드         sname varchar(20) NOT NULL DEFAULT '',               --이름         sexGubun char(1) NOT NULL DEFAULT 'M',               --성별구분 : M은 남성, F는 여성         score tinyint NOT NULL DEFAULT 0             --점수 ) --테이블에 임의의 데이터를 입력합니다. INSERT  STUDENT VALUES('001','01','류일환','M',70) INSERT  STUDENT VALUES('002','01','김대웅','M',90) INSERT  STUDENT VALUES('003','01','조은경','F',99) INSERT  STUDENT VALUES('004','01','백지영','F',80)   INSERT  STUDENT VALUES('005','02','오상민','M',80) INSERT  STUDENT VALUES('006','02','남동현','M',100) INSERT  STUDENT VALUES('007','02','최정주','F',99) INSERT  STUDENT VALUES('008','02','조희랑','F',90)   --과별, 남녀별로 최고 점수와 최고 점수자의 이름을 뽑아오는 쿼리 SELECT  gwaCode, sexGubun, MAX(sname), MAX(score) FROM    STUDENT GROUP BY gwaCode, sexGubun 위와 쿼리문의 결과를 실행하면 다음과 같은 결과가 나오는데 예상한 것과 다르다는 것을 알 수 있을 겁니다. gwaCode sexGubun 열이름 없음 열이름 없음 01 F 조은경 99 02 F 최정주 99 01 M 류일환 90 02 M 오상민 100 실제 데이터를 SELECT * FROM STUDENT해서 확인해 보면 데이터는 다음과 같습니다. gwaCode sexGubun sname Score 01 M 류일환 70 김대웅 90 01 F 조은경 99 백지영 80 02 M 오상민 80 남동현 100 02 F 최정주 99 조희랑 90 위에서 같은 색으로 된 것은 같은 그룹이라는 겁니다. 문제에서 는 최고점수자의 이름입니다. 그러면 학과1번의 남자중에서 최고점수는 90점입니다. 그리고 최고점수를 받은 사람은 김대웅입니다. 근데, 결과를 보시면 01과의 남자중에서 최고점수는 90점 최고점수를 받는 사람은 류일환이라고 나왔습니다. 이런게 아주 미묘한 실수인데, MAX(컬럼)은 그룹내의 해당컬럼중에서만 최대값을 구합니다. 그러므로 01학과의 남자중에서 최대값은 김대웅이란 이름보다 류일환이란 이름이 문자정렬상 최대값이므로 류일환이라는 잘못된 값이 나온겁니다. 이걸 잘못된 값이라고 할 수는 없죠 쿼리문을 그렇게 했으니까 데이터베이스는 정확한 값을 내보낸거죠. 그럼… 우리가 원하는 90점에 김대웅이란 값을 얻을려면 어떻게 해야 할까요? 위와 같은 방법으로는 구해낼 수 없죠.. 우리가 원하는 값을 얻기 위해서는 서브쿼리나 ORDER BY같은 여러가지 방법들이 쓰여질 수 있습니다. 이러한 방법에 대해서는 서브쿼리와 ORDER BY에 대한 절에서 설명하겠습니다. GROUP BY의 그림자처럼 같이 다니는 집계함수에는 어떤 것들이 있는지 한번 살펴보겠습니다. SUM 지정한 열에 해당하는 행들의 합을 구합니다. 문제2-7 각 과별로 총 점수를 구해주세요 쿼리2-7 SELECT  gwaCode, SUM(score) FROM    STUDENT GROUP BY gwaCode 좀더 세밀하게 SUM이 어떻게 작용할지 한번 살펴보도록 하겠습니다. 문제2-8 여학생들을 대상으로 각 과별로 총 점수를 구해주세요 쿼리2-8 SELECT  gwaCode, SUM(score) FROM    STUDENT WHERE   sexGubun = 'F' GROUP BY gwaCode 위와 같은 쿼리를 했을 때 어떤 방식으로 처리하는지 한번 생각해보도록 하죠. 우선 WHERE sexGubun = ‘F’부터 처리를 합니다. 테이블 내에서 여자인데이터들만 골라냅니다. 그리고, GROUP BY gwaCode이므로 gwaCode별로 그룹을 만듭니다.(여기서 그룹을 만든다는건, 과별순으로 정렬을 한다는 말과 동일하다 할 수 있습니다. 그다음, SELECT 목록에 있는 과코드를 보여주고, 해당 과코드내의 점수를 합해서 결과를 보여줍니다. 직접 그림으로 보겠습니다. WHERE = ‘F’ 적용 sno gwaCode sname sexGubun Score 003 01 조은경 F 99 004 01 백지영 F 80 007 02 최정주 F 99 008 02 조희랑 F 90 WHERE = ‘F’가 적용되어서 여학생들 데이터만 추려냅니다. GROUP BY gwaCode 적용 WHERE = ‘F’가 적용된 상태에서 gwaCode별로 그룹을 만듭니다.(정렬을 시킵니다.) 현재 gwaCode별로 정렬이 되어 있으므로 위와 동일합니다. SELECT될 내용들만 추려냅니다. SELECT될 내용에는 gwaCode와 SUM(score)이므로, gwaCode와 score컬럼만이 필요합니다. gwaCode Score   01 99 80 02 99 90 위와 같이 데이터가 걸러지게 됩니다.(색별로 한줄의 데이터만 나옵니다) gwaCode별로 SUM을 수행합니다. 과코드별로 SUM을 수행한 결과값을 보여줍니다. MIN과 MAX MIN과 MAX는 위 예제에서 계속 사용했으므로 생략하겠습니다. COUNT(*) 그룹화된 항목의 개수를 구하는 겁니다. 문제2-9 학과별로 인원이 몇 명인지 알려주세요 쿼리2-9 SELECT  gwaCode, COUNT(*) FROM    STUDENT GROUP BY gwaCode  AVG 항목의 평균을 구합니다.   간단하게 집계함수를 살펴보았습니다. 이 외에도 여러 집계함수가 있지만, 자주 사용하는 건 이정도이므로 이정도만 알아도 되실겁니다. 집계함수를 살펴 봤는데 여기서 두가지 생각해보도록 하겠습니다. 우선, NULL에 대한 겁니다. NULL이란건 알 수 없는 값입니다. 0도 아니고 음수값도 아닌 그냥 알 수 없는 값입니다. 만약에 MIN, MAX, SUM, AVG 집계 함수를 사용했을 때, 값이 없는 이 연산을 수행하라고 했을 경우는 NULL값이 리턴됩니다. NULL값은 예기치 않은 오류를 발생시킬 수 있으므로 이에 대한 처리를 정확히 해두는 것이 좋습니다. MAX(score)를 했을 때, 우선 score가 숫자형이라는걸 알고 있다면 ISNULL(MAX(score),0)으로 처리를 해서 널값이 나오는 것을 예방하는 것이 좋습니다. 물론, 데이터에 어떤 값이 있는지 정확히 알고, 어떤 결과가 일어날지 정확하다면 궂이 ISNULL을 해서 손가락을 피고하게 하거나 데이터베이스를 피곤하게 할 필요는 없죠. 그리고 COUNT란 집계함수는 널을 반환하지 없습니다. 데이터가 없을 경우는 0이란 건수를 돌려줍니다. COUNT는 널이 없이 0이다, MIN, MAX, SUM, AVG는 널을 반환할 수 있다는 것을 항상 유심하시길 바랍니다. 두번째로 생각해볼 문제는 GROUP BY없이 집계 함수를 사용한다면 어떻게 될까요? 입니다. 그렇다면 조건에 맞는 모든 데이터들 중에서 집계된 내용을 돌려준다는 겁니다. WHERE절로 걸러진 모든 데이터를 대상으로 MIN, MAX, SUM, AVG, COUNT등을 구한다는 겁니다.