신기한 연구소

안녕하세요. 신기한 연구소입니다.

이번 포스팅은 GROUP BY와 관련된 함수들을 살펴보겠습니다.

다양한 함수들이 있는데 제대로 이해해서 사용하는 게 중요합니다.

그룹관련 함수는 데이터를 그룹화해서 총 건수, 합계, 평균, 최대값, 최솟값, 표준편차, 분산 값을 사용할 수 있는데 그 사용법에 대해 살펴보겠습니다.

COUNT  건수 구하기

Salgrade 테이블의 데이터를 확인해봅니다.

총 9건의 데이터가 존재합니다.

테이블의 총 데이터 건수를 알고 싶으면 COUNT를 사용하면 됩니다.

특정 컬럼이 아닌 전체 건수를 찾기에 COUNT(*)로 *를 넣어 검색하면 됩니다.

전체 건수인 9가 결과에 나왔습니다.

특정 컬럼의 데이터 중 NULL값을 제외한 건수를 구하고 싶다면

COUNT 함수에 원하는 컬럼을 ARGUMENT로 입력하면 됩니다.

LOSAL을 보면 총 9건 중 2건이 NULL입니다.

COUNT(LOSAL)로 검색해보겠습니다.

전체를 조회하면 9건이 나오지만 LOSAL을 ARGUMENT로 넣으면

NULL을 제외한 7건만 표시됩니다.

SUM(ALL 또는 DISTINCT 표현식)

그룹 함수에서 총합을 구하는 함수입니다.

기본적으로 SUM(표현식)으로 사용하며 중복된 숫자는 빼고 합을 구하고자 할 때는

SUM(DISTINCT 표현식)을 사용하면 됩니다. ALL은 디폴트라 생략해도 됩니다.

SUM(CCC)와 SUM(ALL CCC)는 값이 같습니다. 같은 의미거든요.

하지만 SUM(DISTINCT CCC)는 중복된 30을 하나만 인식해서

총 합이 30이 적게 나옵니다.

또한 NULL이 있다고 NVL을 사용해서 0으로 변환하는 것은 의미가 없습니다.

괜히 쿼리 시간만 늘리게 되거든요.

어짜피 SUM 함수는 NULL 자체를 계산하지 않거든요.

AVG(ALL 또는 DISTINCT 표현식)

그룹 함수에서 평균을 구하는 함수입니다.

기본적으로 AVG(표현식)으로 사용하며 중복된 숫자는 빼고 평균을 구하고자 할 때는

AVG(DISTINCT 표현식)을 사용하면 됩니다. ALL은 디폴트라 생략해도 됩니다.

1, 2 라인은 같은 의미입니다. ALL은 생략 가능하거든요.

3라인은  소수점이 나와서 ROUND로 처리했는데 중복된 숫자 20을 하나만 적용되므로

평균 45보다 작은 숫자라 평균 수치가 53.33으로 올라갔습니다.

전체 건수는 5건이고 NULL을 제거한 건수는 4건입니다.

기본적으로 AVG는 NULL을 제거한 총합에 그 건수를 나눕니다.

그래서 45가 나옵니다.

하지만 NULL도 건수에 포함 시킨다면 5로 나누게 되므로 평균이 36으로 줄어듭니다.

평균을 구할 때 총 건수에 대해 처리를 해야 한다면 총건수로 할 건지 NULL이 아닌

실제 더한 값들의 수로 나눌 건지 잘 확인해야 합니다.

이런 경우 총 건수로 해야 한다면 NVL을 사용해서 NULL을 0으로 바꾸면 됩니다.

MAX(ALL 또는 DISTINCT 표현식)

MIN(ALL 또는 DISTINCT 표현식)

선택된 표현식의 값 중 가장 큰 값은 MAX를 가장 작은 값은 MIN을 사용합니다.

STDDEV(ALL 또는 DISTINCT 표현식)

VARIANCE (ALL 또는 DISTINCT 표현식)

사실 위 함수는 표준편차와 분산에 대한 값을 구하는데 실제로 사용했던 기억은 없습니다.

관련 업무를 하지 않았기에 그런 듯합니다.

그래도 관련 업무를 할 수도 있기에 잘 확인해 보시고 필요하면 검색해서 사용하면

된답니다.

마지막으로 GROUP BY 절을 사용할 때

앞에 WHERE 절에 필요한 데이터를 먼저 필터 한다면

GROUP BY의 부담이 많이 줄어든답니다.

그리고 그 값 중에 그룹 함수를 이용한 조건을 넣고 싶다면

HAVING 절을 활용하면 된답니다.

위 결과를 보면 ‘K001’과 ‘K002’에 대한 합계를 구한 결과입니다.

여기서 만약 합계가 120 이내인 데이터를 검색하거나

또는 평균 40을 초과하는 데이터를 표현하고 싶다면

다음과 같이 작성하면 됩니다.

지금까지 그룹 함수에 대해 살펴봤습니다.

실전에서도 자주 사용하는 함수들이니 잘 이해하시고

특히 합계나 통계를 잘못 설정해서 엉뚱한 값이 나오기도 하니

꼭 대사를 하면서 사용하길 권장합니다.

잘못된 내용이나 수정이 필요하면 댓글 주세요.

아래 하트(공감) 버튼을 눌러서 더 다양한 글을 쓸 수 있게 응원 부탁드립니다. 감사합니다.

안녕하세요. 신기한 연구소입니다.

SQL 작업을 하다 보면 NULL 처리가 필요한 경우가 많은데요.

SOL에서 NULL 처리를 하는 방법에 대해 알아봅니다.

오라클 기준으로 같이 살펴보겠습니다.

우선 NULL에 대해 알아볼게요.

NULL은 아직 정의가 안되었다, 또는 배정이 안 된 상태를 말합니다.

빈 값(공백)이나 0과는 다른 값이므로 주의해야 합니다. ASCII값도 다르거든요.

NULL의 특징 중 하나는 해당 값과 연산(+,-,*,/)을 해도 무조건 NULL이 나온다는 겁니다.

NULL에 대한 함수를 살펴보겠습니다.

우선 우리가 SELECT 문을 통해 데이터를 조회했는데 특정 컬럼의 값이 NULL이 나오는 경우가 있습니다.

이런 경우 대체할 값을 넣으면 좋은데요.

NVL이라는 함수를 사용하면 됩니다.

NVL(표현식_1, 표현식_2)

표현식_1의 값이 NULL이면 표현식 2의 값을 보여주고 NULL이 아니면 표현식_1의 값을 그대로 보여주는 함수입니다.

먼저 dept테이블의 loc 컬럼에 NULL값을 넣고 입력합니다.

조회하면 NULL로 표시됩니다.

아직 값이 할당 안되었다는 의미입니다.

사용자 화면에 NULL로 표시되면 일반 사용자들은 무슨 의미인지 모르겠지요?

Loc가 NULL인 경우는 ‘미설정’으로 변경해서 사용자에게 보여줄 수 있습니다.

5번째 라인에 NULL값이 '미설정'으로 변경된 것을 확인할 수 있습니다.

다음으로 NULLIF()에 대해 살펴봅니다.

NULLIF(표현식_1, 표현식_2)

표현식_1의 값과 표현식_2의 값이 같으면 NULL을 반환하는 함수입니다.

5번째 loc값과 NULL이 같기에 결과가 NULL로 나왔습니다.

3번째 dname값이 'SALES'면 NULL을 반환한다는 표현이기에 NULL로 변경된 것을 확인할 수 있습니다.

마지막으로 하나 더 살펴볼 함수는 COALESCE입니다.

COALESCE(표현식_1, 표현식_2, …)

표현식_1의 컬럼 중 NULL이 아닌 첫 번째 값을 보여줍니다.

표현식_2도 같은 원리이며 원하는 만큼 컬럼을 지정하면 됩니다.

coalesce(loc, dname)으로 표현식을 만들었는데 5번째 loc값이 NULL이어서 그다음 dname값인 IT가 나온 것을

확인할 수 있습니다.

AAA, BBB, CCC 컬럼값을 확인해 보면

ANO가 2인 경우 AAA는 NULL,

ANO가 3인 경우 AAA, BBB는 NULL입니다.

COALESCE를 사용하면 1행은 AAA가 값이 있으니 해당 값인 1

2행은 AAA가 NULL이기에 패스~ BBB는 값이 있으니 해당 값인 2

3행은 AAA, BBB가 NULL이기에 패스~ CCC는 값이 있으니 해당 값인 3

이 표출됩니다.

마지막으로 실전에서도 자주 사용하는 공집합에 대해 알아봅니다.

deptno가 60인 행은 없습니다.

그럼 데이터 자체가 존재하지 않기에 첫 번째 쿼리처럼 nvl함수를 사용해도 값이 나오지 않습니다.

그래서 max() 또는 min() 함수를 사용해서 NULL을 리턴 받은 다음 nvl을 사용하면

'no data'가 표출됨을 확인할 수 있습니다. 

이렇게 NULL에 대한 함수를 알아봤습니다.

수정이 필요하거나 잘못된 내용이 있으면 댓글 주세요.

아래 하트(공감) 버튼을 눌러서 더 다양한 글을 쓸 수 있게 응원 부탁드립니다. 감사합니다.