1. GROUP BY
GROUP BY 는 데이터를 특정 컬럼 기준으로 그룹화하여 각 그룹에 대해 집계(Aggregate) 함수를 적용할 때 사용하는 구문이다. 이를 통해 데이터를 그룹별로 요약하거나 통계를 계산할 수 있다.
USE sqlDB SELECT userID, amount FROM buyTb1 ORDER BY userID
위의 쿼리 사용자별로 구매한 결과를 볼 수 있다. 이 결과에서 사용자별로 전체 구매 횟수를 합산하려면 GROUP BY 를 사용해야 한다.
SELECT userID as "사용자 아이디", SUM(amount) as "총 구매 개수" FROM buyTb1 GROUP BY userID
GROUP BY 절에 사용한 컬럼은 SELECT 절에 사용할 수 있지만, 그 외의 컬럼은 사용할 수 없다. 왜냐면 GROUP BY 절은 특정 컬럼을 기준을 그룹화하여 조회하는데, 다른 컬럼은 그룹화되어있지 않아 그룹화된 데이터를 출력할 수 없기 때문이다.
따라서 GROUP BY절을 사용할 때 SELECT 절에 사용할 수 있는 컬럼은 GROUP BY 절에 사용한 컬럼, 집계함수로 계산한 값 등만 사용할 수 있다.
2. 집계 함수
집계 함수(Aggregate Function)는 SQL에서 여러 행을 하나의 결과값으로 요약하기 위해 사용되는 함수이다. 데이터의 집합에서 계산을 수행하여 총합, 평균, 개수, 최대값, 최소값 등을 반환하는 데 사용된다.
함수명 | 설명 |
SUM() | 합산을 구한다. |
AVG() | 평균을 구한다. |
MIN() | 최소값을 구한다. |
MAX() | 최대값을 구한다. |
COUNT() | 행의 개수를 센다. |
COUNT_BIG() | 행의 개수를 센다. 단 결과값은 bigint 형이다. |
STDEV() | 표준편차를 구한다. |
VAR) | 분산을 구한다. |
USE sqlDB SELECT AVG(amount) AS[평균구매개수] FROM buyTb1
계산결과 2가 나온다.
하지만 실제 계산은 2.91666….. 이 나온다.
이는 구매 개수(amount) 컬럼의 타입이 정수형이기 때문에 정수로 반환된 것이다.
이런 경우 정수에서 실수로 타입을 변환해야 한다.
SELECT AVG(CAST(amount AS DECIMAL(10,6))) AS [평균구매개수] FROM buyTb1 또는 SELECT AVG(CONVERT(DECIMAL(10,6), amount)) AS [평균구매개수] FROM buyTb1;
SELECT userID, AVG(CONVERT(DECIMAL(10,6), amount)) AS [평균구매개수] FROM buyTb1 GROUP BY userID
회원별 평균 구매개수 를 계산할 수 있다.
이번에는 가장 작은 키와 가장 큰 키를 가진 회원의 이름을 출력해보자.
SELECT name,MAX(height) as 최대값, MIN(height) as 최소값 FROM userTb1
이렇게 GROUP BY절 없이 집계함수를 사용하면 다른 컬럼과 함께 사용할 수 없다.
SELECT name,MAX(height) as 최대값, MIN(height) as 최소값 FROM userTb1 GROUP BY name
GROUP BY 절을 사용하면 결과는 조회되지만 회원별로 최대값, 최소값이 전부 같은 값으로 조회된다.
이런 경우는 서브쿼리를 사용해 데이터를 조회해야 한다.
SELECT name,height FROM userTb1 WHERE 1=1 AND height = (SELECT MAX(height) FROM userTb1) OR height = (SELECT MIN(height) FROM userTb1)
이렇게 서브쿼리에 집계함수를 사용해 원하는 값을 조회할 수 있다.
SELECt COUNT(mobile1) as "휴대폰이 있는 사용자" FROM userTb1
COUNT() 함수를 사용해 휴대폰이 있는 회원의 수를 계산했다.
userTb1 의 회원수는 10명이지만, 전화번호가 없는 회원은 2명이기 때문에 8명만 조회가 된다.
집계함수는 NULL값을 제외한 행을 연산한다.
3. HAVING 절
HAVING 절은 GROUP BY절과 함께 사용하며 , 그룹화된 컬럼의 조건을 설정할 때 사용한다.
SELECT userID AS "사용자" , SUM(price*amount) as "총 구매액" FROM buyTb1 GROUP BY userID
사용자 별 총 구매액을 계산했다. 여기서 총 구매액이 1000 이상인 회원에게 사은품을 증정하고 싶다면 어떻게 해야 할까?
SELECT userID AS "사용자" , SUM(price*amount) as "총 구매액" FROM buyTb1 WHERE SUM(price*amount) >1000 GROUP BY userID
만약 필터링 조건을 WHERE 절에 사용한다면 사진과 같은 오류가 발생한다.
집계함수에서 필터링을 할 때는 HAVING절에 사용해야 한다.
SELECT userID AS "사용자" , SUM(price*amount) as "총 구매액" FROM buyTb1 GROUP BY userID HAVING SUM(price*amount) >1000
HAVING 절을 통해 집계함수에 대한 조건을 넣을 수 있다.
4. ROLLUP() / GROUPING SETS() / CUBE()
만약 총합 또는 중간 합계가 필요하다면 GROUP BY절과 함께 ROLLUP() 는 CUBE() 를 사용하면 된다.
4.1 ROLLUP()
SELECT num, groupName, SUM(price*amount) as "비용" FROM buyTb1 GROUP BY ROLLUP(groupName,num)
ROLLUP() 은 시작 컬럼을 기준으로 소계를 낸다.
현재 ROLLUP(groupName,num) 이렇게 되어 있다면 계산을 할 때 시작 컬럼인
groupName을 기준으로 중간 합계, 그리고 마지막으로 전체 합계를 계산한다.
결과를 보면 먼저 groupName 가 NULL일 때, 서적일 때, 의류, 전자일 때를 기준으로 중간 합산을 한 후 마지막 둘다 NULL 값인 경우에 전체 합계를 계산한다.
즉 (groupName,num) , (groupName, 합산) , (합산, 합산) 의 형태로 나타난다.
여기서 합계는 결과에 NULL로 표시된 부분을 의미한다. (물론 원래 값이 NULL 인 값도 있지만)
4.2 CUBE()
SELECT num, groupName, SUM(price*amount) as "비용" FROM buyTb1 GROUP BY CUBE(groupName,num)
CUBE() 는 () 내부에 있는 모든 컬럼을 가지고 합계를 낸다.
CUBE를 사용하면 num 컬럼을 이용한 소합계, groupName을 활용한 소합계, 그리고 전체 합계를 나타낸다.
즉 (groupName,num), (groupName,합계), (합계 , num) (합계, 합계) 의 헝태로 결과가 나타난다.
4.3 GROUPING SETS()
GROUPING SETS() 는 ROLLUP() 이나 CUBE() 로 나타낼 수 없는 경우 사용한다.
SELECT num, groupName, SUM(price*amount) as "비용" FROM buyTb1 GROUP BY grouping sets(groupName,num)
GROUPING SETS 은 조회를 원하는 값을 () 에 넣어 표기할 수 있는데, GROUPING SETS 에 지정된 컬럼을 기준으로 나타낸다.
grouping sets(groupName,num) 이 경우는 각각 groupName 를 통한 소합계, num 를 통한 소합계만 출력한다.
SELECT num, groupName, SUM(price*amount) as "비용" FROM buyTb1 GROUP BY grouping sets((groupName,num))
반면 같은 컬럼에 괄호룰 추가해 grouping sets((groupName,num)) 이렇게 한다면 (groupName,num) 이 값을 하나의 컬럼으로 인식해 그룹화한다.
Share article