[SQL] count, groupby, subquery

행 개수 구하기 - COUNT

COUNT로 행 개수 구하기

SQL은 집합을 다루는 집계함수를 제공한다.

일반적인 함수는 인수로 하나의 값을 지정하는 데 비해 집계함수는 인수로 집합을 지정한다.

​

집계함수의 특징은 일반적인 함수의 경우 하나의 행에 대하여 하나의 값을 반환하는데,

복수의 값(집합)에서 하나의 값을 계산해낸다는 것이다.

이렇게 집합으로부터 하나ㅡ이 값을 계산하는 것을 '집계'라고 부른다.

따라서 집계함수를 SELECT 구에 쓰면 WHERE 구의 유무와 관계없이 결괏값으로 하나의 행을 반환한다.

​

COUNT 함수는 인수로 주어진 집합의 '개수'를 구해 반환한다.

 

SELECT COUNT(*) FROM employees;

 

SELECT 구는 * 가 '모든 열'을 의미하는데, COUNT 집계함수에서는 '모든 열 = 테이블 전체'의 의미이다.

​

COUNT 함수의 인수로 열 명을 지정할 수 있다.

열 명을 지정하면 그 열에 한해서 행의 개수를 구할 수 있다.

실제로 집계함수는 보통 그 같은 목적을 위해 많이 사용되며,

*를 인수로 사용할 수 있는 집계함수는 COUNT 함수뿐이다.

다른 집계함수에서는 열 이름이나 식을 인수로 지정한다.

​

집계함수는 또한 집합 안에 NULL 값이 있을 경우 이를 제외하고 처리한다.

다만 COUNT(*)의 경우 모든 열의 행수를 카운트하기 때문에 NULL 값이 있어도 해당 값이 무시되지 않는다.

​

 

DISTINCT로 중복 제거

DISTINCT 함수는 중복된 값을 제거하는 함수로,

SELECT 구에서 DISTINCT를 지정하면 중복된 데이터를 제외한 결과를 클라이언트로 반환한다.

중복 여부는 SELECT 구에 지정된 모든 열을 비교해 판단한다.

​

반대로 SELECT 구에 ALL을 사용할 경우 중복 유무와 관계없이 문자 그대로 모든 행을 반환한다.

만약 SELECT 구에 두 가지 모두 지정하지 않은 경우에는 중복된 값은 제거되지 않는다.

 

​

집계함수에서 DISTINCT

DISTINCT는 집계함수의 인수에 수식자로 지정할 수 있다.

즉, DISTINCT를 이용해 집합에서 중복을 제거한 뒤 COUNT로 개수를 구할 수 있는 것이다.

 

SELECT COUNT(DISTINCT name)
FROM employees;

 

여기서 SELECT DISTINCT COUNT(name)이라는 SELECT 명령은

COUNT 쪽이 먼저 계산되기 때문에 실행되지 않는다.

 

 

COUNT 이외의 집계함수

SUM으로 합계 구하기

SUM 집계함수를 사용하면 집합의 합계를 구할 수 있다.

SELECT SUM(quantity) # quantity는 수치형열 FROM sample51;

SUM 집계함수에서에 지정되는 집합은 수치형 뿐이다.

 

문자열형이나 날짜시간형의 집합에서 합계를 구할 수는 없다.

 

또한 SUM 집계함수도 COUNT와 마찬가지로 NULL 값을 무시하여 제거한 뒤 합계를 계산한다.

​

 

 

AVG로 평균 내기

AVG 집계함수를 통해 평균값을 계산할 수 있다.

AVG에 주어지는 집합은 SUM과 마찬가지로 수치 형만 가능하다.

AVG 집계함수도 NULL 값을 무시하여 제거한 뒤 평균값을 계산한다.

 

만약 NULL을 0으로 간주해서 평균을 내고 싶다면 CASE 명령어를 사용해 NULL을 0으로 변환한 뒤 계산하면 된다.

# NULL을 0으로 변환하여 AVG 평균값 계산
SELECT AVG (CASE WHEN quantity IS NULL THEN 0 ELSE quantity END) AS avgnull0
FROM employees;

 

 

MIN · MAX로 최솟값 · 최댓값 구하기

MIN 집계함수, MAX 집계함수를 사용해 집합에서 최솟값과 최댓값을 구할 수 있다.

이들은 문자열형과 날짜시간형에도 사용할 수 있다.

또한 NULL 값을 무시하는 기본 규칙은 다른 집계함수와 같다.

 

 

그룹화 - GROUP BY

GROUP BY로 그룹화

GROUP BY 구를 사용해 행끼리 한데 묶어 그룹화한 집합을 집계함수로 넘겨줄 수 있다.

GROUP BY 구에 열을 지정하여 그룹화하면 지정된 열의 값이 같은 행이 하나의 그룹으로 묶인다.

GROUP BY를 지정해 그룹화하면 DISTINCT와 같이 중복을 제거하는 효과가 있다.

GROUP BY 구를 지정하는 경우에는 집계함수와 함께 사용하지 않으면 별 의미가 없다.

​

 

HAVING 구로 조건 지정

SQL의 내부 처리 순서는 WHERE 구 -> GROUP BY 구 -> SELECT 구 -> ORDER BY 구의 순이다.

즉, WHERE 구가 GROUP BY 구보다 먼저 실행이 되기 때문에,

그룹화가 필요한 집계함수는 WEHRE 구에서 사용할 수 없다.

​

따라서 집계함수를 사용한 결과에서 조건에 맞는 값을 따로 걸러내기 위해선 HAVING 구를 사용해야 한다.

HAVING 구는 GROUP BY 구의 뒤에 기술하며 WHERE 구와 동일하게 조건식을 지정할 수 있다.

 

 

결과적으로 WHERE 구와 HAVING 구에 지정된 조건으로 검색하는 2단 구조가 된다.

 

① WHERE로 검색 -> ② 검색한 뒤 그룹화 -> ③ HAVING 구로 조건식 반영

 

# HAVING을 사용해 검색
SELECT name, COUNT(name)
FROM employees
GROUP BY name
HAVING COUNT(name) = 1;

 

name	COUNT(name)
B	1
C	1

 

 

HAVING 구가 포함된 내부 처리 순서는 다음과 같다.

WHERE 구 -> GROUP BY 구 -> HAVING 구 -> SELECT 구 -> ORDER BY 구

​

 

 

 

복수열의 그룹화

GROUP BY를 사용할 때 GROUP BY에 지정한 열 이외의 열은

집계함수를 사용하지 않은 채 SELECT 구에 기술해서는 안 된다.

예를 들면 다음과 같다.

 

# GROUP BY로 지정한 name 열 이외의 열 존재(no,quantity)
SELECT no, name, quantity 
FROM employees
GROUP BY name;

 

만약 GROUP BY에 지정한 열 이외의 열을 SELECT 구에 지정하려면 집계함수를 같이 써야 한다.

 

SELECT MIN(no), name, SUM(quantity)
FROM employees
GROUP BY name;

 

 

결괏값 정렬

GROUP BY로 그룹화해도 실행 결과 순서를 정렬할 수는 없다.

결과를 정렬하려면 GROUP BY 구로 그룹화한 경우에도 ORDER BY 구를 사용해 정렬할 수 있다.

 

 

SELECT name, COUNT(name), SUM(quantity)
FROM employees
GROUP BY name
ORDER BY SUM(quantity) DESC;

name	COUNT(name)	SUM(quantity)
B	1	10
C	1	3
A	2	3
NULL	0	NULL

 

 

 

서브쿼리

DELETE의 WHERE 구에서 서브쿼리 사용하기

 

서브쿼리는 SQL 명령문 안에 하부 SELECT 명령으로 괄호로 묶어 지정한다.

서브쿼리는 SELECT 구, FROM 구, WHERE 구 등 SELECT 명령의 각 구를 기술할 수 있다.

# 최솟값을 가지는 행 삭제하기 DELETE FROM sample54 WHERE a = (SELECT MIN(a) FROM sample54);

 

스칼라 값

일반적인 서브쿼리 패턴은 다음과 같다.

① 하나의 값을 반환하는 패턴 ② 복수의 행이 반환되지만 열은 하나인 패턴

③ 하나의 행이 반환되지만 열이 복수인 패턴 ④ 복수의 행, 복수의 열이 반환되는 패턴

첫 번째 패턴은 '단일 값'으로도 통용되지만 데이터베이스 업계에서는 '스칼라 값'이라 불리는 경우가 많다.

즉, SELECT 명령이 하나의 값만 반환하는 것을 '스칼라 값을 반환한다'라고 한다.

​

스칼라 값을 반환하는 SELECT 명령을 특별 취급하는 이유는 서브 쿼리로써 사용하기 쉽기 때문이다.

통상적으로 특정한 두 가지가 서로 동일한지 여부를 비교할 때 서로 단일한 값으로 비교한다.

즉, WHERE 구에서 스칼라 값을 반환하는 서브쿼리는 = 연산자로 비교할 수 있다.

DELETE FROM sample54 WHERE a = (SELECT MIN(a) FROM sample54);

여기서 서브쿼리 부분은 스칼라 값을 반환하는 SELECT 명령으로 되어 있으므로

= 연산자를 사용해 열 a의 값과 비교할 수 있다.

​

스칼라 값을 반환하는 서브쿼리를 특별히 '스칼라 서브쿼리'라고 부른다.

'스칼라 서브쿼리'라면 WHERE 구에 사용할 수 있으므로

집계함수를 사용해 집계한 결과를 조건식으로 사용할 수 있다.

​

 

 

SELECT 구에서 서브쿼리 사용하기

SELECT 구에서 서브쿼리를 지정할 때는 스칼라 서브쿼리가 필요하다.

 

SELECT (SELECT COUNT(*) FROM sample51) AS sq1,

(SELECT COUNT(*) FROM sample54) AS sq2;

sq1	sq2
5	3

sample51 테이블의 행 개수와 sample54 테이블의 행 개수를 각 서브쿼리로 구한다.

여기서 주의할 점은 서브쿼리가 아닌 상부의 SELECT 명령에는 FROM 구가 없다는 것이다.

​

FROM 구에서 서브쿼리 사용하기

FROM 구에서도 서브쿼리를 사용할 수 있는데, 여기선 스칼라 서브쿼리가 아니어도 상관이 없다.

# FROM 구에서 서브쿼리 사용하기 SELECT * FROM (SELECT * FROM sample54) AS sq;

SELECT 명령 안에 SELECT 명령이 들어있는 듯 보이는 이런 구조를

'네스티드(nested) 구조' 또는 '중첩 구조', '내포 구조'라 부른다.

FROM 구에서는 'AS' 키워드를 통해 테이블이나 서브쿼리에 별명을 붙일 수 있다.

​

INSERT 명령과 서브쿼리

INSERT 명령에는 VALUES 구의 일부로 서브쿼리를 사용하는 경우와,

VALUES 구 대신 SELECT 명령을 사용하는 두 가지 방법이 있다.

INSERT INTO sample541 VALUES( (SELECT COUNT(*) FROM sample51), (SELECT COUNT(*) FROM sample54) ); SELECT * FROM sample541;

a	b
5	3

VALUES 구의 값으로 서브쿼리를 사용하는 경우에는 스칼라 서브쿼리여야하며, 자료형도 일치해야 한다.\

INSERT INTO sample541 SELECT 1,2; SELECT * FROM sample541;

a	b
5	3

다음은 흔히 'INSERT SELECT'라 불리는 명령으로 INSERT와 SELECT를 합친 것 같은 명령이다.

INSERT SELECT 명령은 SELECT 명령의 결과를 INSERT INTO로 지정한 테이블에 전부 추가한다.

이때 SELECT 명령이 반환하는 값이 꼭 스칼라 값일 필요는 없으며,

SELECT가 반환하는 열 수와 자료형이 INSERT할 테이블과 일치하기만 하면 된다.

이는 데이터의 복사나 이동을 할 때 자주 사용하는 명령이다.

 

상관 서브쿼리

EXISTS

EXISTS 술어를 사용하면 서브쿼리가 반환하는 결괏값이 있는지를 조사할 수 있다.

다음과 같은 두 개의 샘플 테이블이 있다.

no	a
1	NULL
2	NULL
3	NULL
4	NULL
5	NULL

- sample551

no 2

3

5

- sample552

​

이 두 테이블에서 sample552에 no 열의 값과 같은 행이 있으면 '있음'으로,

행이 없으면 '없음'이라는 값으로 갱신하도록 하겠다.

# EXISTS를 사용해 '있음'으로 갱신하기 UPDATE sample551 SET a = '있음' WHERE EXISTS(SELECT * FROM sample552 WHERE no2 = no); SELECT * FROM sample551;

no	a
1	NULL
2	NULL
3	있음
4	NULL
5	있음

EXISTS 술어에 서브쿼리를 지정하면 서브쿼리가 행을 반환할 경우에 참을 돌려준다.

결과가 한 줄이어도 그 이상이어도 참이 된다.

반면 반환되는 행이 없을 경우에는 거짓이 된다.

​

NOT EXISTS

반대로, 행이 존재하지 않는 경우 참이 되어 '없음'이 되는 NOT EXISTS 술어도 있다.

UPDATE sample551 SET a = '없음' WHERE NOT EXISTS(SELECT * FROM sample552 WHERE no2 = no); SELECT * FROm sample551;

no	a
1	없음
2	없음
3	있음
4	없음
5	있음

이렇듯 서브쿼리를 이용해 다른 테이블의 상황을 판단하고 UPDATE로 갱신할 수 있다.

UPDATE뿐만 아니라, SELECT 명령이나 DELETE 명령으로도 서브쿼리를 사용할 수 있다.

​

상관 서브쿼리

상관 서브쿼리란 부모 명령과 자식인 서브쿼리가 특정 관계를 맺는 것을 말한다.

이전에 본 UPDATE 명령에 EXISTS 술어를 통해 서브쿼리를 활용한 코드를 보면,

UPDATE sample551 SET a = '있음' WHERE EXISTS(SELECT * FROM sample552 WHERE no2 = no);

UPDATE 명령이 부모가 되고, WHERE 구에 괄호로 묶은 부분 서브쿼리가 자식이 된다.

상관 서브쿼리에서는 부모 명령과 연관되어 처리되기 때문에

서브쿼리 부분만을 따로 떼어내서 실행시킬 수 없다.

DELETE FROM sample54 WHEN a = (SELECT MIN(a) FROM sample54);

다음과 같은 DELETE의 경우에는 상관 서브쿼리가 아니며, 단독 쿼리로 서브쿼리를 실행할 수 있다.

​

테이블명 붙이기

만약 앞서 살펴본 no 열과 no 2열과는 다르게 두 테이블에 같은 열이 있을 경우에는

no=no와 같은 코드를 실행하게 되면 에러가 발생한다.

이런 경우에는 열이 어느 테이블의 것인지 명시적으로 나타낼 필요가 있으며,

이는 열명 앞에 '테이블명.'을 붙이면 된다.

UPDATE sample551 SET a = '있음' WHERE EXISTS(SELECT * FROM sample552 WHERE sample552.no2 = sample551.no); SELECT * FROM sample551;

IN

스칼라 값끼리 비교할 때는 = 연산자를 사용하는데, 집합을 비교할 때는 사용할 수 없다.

여기선 IN을 사용하면 집합 안의 값이 존재하는지를 조사할 수 있다.

또한 특정 열의 값이 '무엇 또는(OR) 무엇'이라는 조건식을 지정하는 경우

IN을 사용하면 간단하게 지정할 수 있다.

​

IN에서는 오른쪽에 집합을 지정한다.

왼쪽에 지정된 값과 같은 값이 집합 안에 존재하면 참을 반환한다.

집합은 상수 리스트를 괄호로 묶어 기술하며, 서브쿼리로도 지정할 수 있다.

이 같은 경우 서브쿼리는 스칼라 서브쿼리가 될 필요는 없다.

# IN을 사용해 조건식 기술 SELECT * FROM sample551 WHERE no IN (3,5);

# IN의 오른쪽을 서브쿼리로 지정 SELECT * FROM sample551 WEHER no IN (SELECT no2 FROM sample552);

IN은 집합 안에 값이 포함되어 있으면 참이 되며, NOT IN은 집합에 값이 포함되어 있지 않을 경우 참이 된다.

​

IN에서는 집합 안에 NULL 값이 있어도 무시하지는 않지만 반드시 IS NULL을 사용해야 한다.

또한 NOT IN의 경우, 집합 안에 NULL 값이 있으면 설령 왼쪽 값이 집합 안에 포함되어 있지 않아도

참을 반환하지 않으며 이때 결과는 불명(UNKNOWN)이 된다.