5. 집계와 서브쿼리

20. 행 갯수 구하기

 

집계: 집합으로 부터 하나의 값을 계산하는 과정

집계 함수: 하나의 행에 대해 하나의 값을 반환하는 일반적인 함수와 다르게 복수의 값에서 하나의 값을 계산

* SELECT 구문에서의 집계함수는 WHERE 구의 유무와 관계없이 결과값으로 하나의 행을 반환

 

집계함수 [집합] 형태로 사용하며 대표적인 집계함수는 다음과 같음

- COUNT, SUM, AVG, MIN, MAX...

 

COUNT

인수로 지정된 집합의 행 개수를 반환

mysql> SELECT COUNT(*) FROM t1; // 테이블 전체의 행 개수 반환
mysql> SELECT COUNT(*) FROM t1 WHERE name='A'; // WHERE구의 조건에 해당하는 행의 개수 반환

* SELECT보다 WHERE 구를 먼저 처리하므로 WHERE 조건으로 검색된 행을 COUNT

 

NULL 집계

COUNT 인수로 열명을 지정할 때 해당 열의 행의 개수를 구할 수 있음

단, 이때 집합 안에 NULL 값이 있는 경우 이를 제외하고 처리

* COUNT(*)의 경우 NULL 값이 있어도 무시되지 않으며 NULL 포함 테이블 내 총 행 수 반환

 

DISTINCT

SELECT 구에서 중복된 데이터를 제외한 결과를 클라이언트로 반환하는 함수

중복 여부는 SELECT 구문에서 지정된 모든 열을 배교해 판단

mysql> SELECT DISTINCT name FROM t1;
mysql> SELECT ALL name FROM t1; // 중복 유무와 관계없이 모든 행을 반환

* SELECT 구문에서 지정하는 ALL 혹은 DISTINCT가 중복값을 제거할 것인지 설정하며 생략하는 경우 ALL이 기본값

 

집계함수와 DISTINCT

mysql> SELECT DISTICT COUNT(name); // COUNT 먼저 계산
mysql> SELECT COUNT(DISTINCT(name)); // 집계함수 인수로 중복 제거 후 COUNT로 NULL 제외한 행 개수 반환

 

21. COUNT 이외의 집계함수

 

SUM

SUM [ALL | DISTINCT]: 집합의 합계를 구하는 함수로 SUM 집계함수에는 수치형만 지정 가능

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

* COUNT와 동일하게 NULL값을 무시하며 제거한 후 합계 반환

 

AVG

평균을 구하는 방법 1) SUM 집계함수로 합한 값을 COUNT 집계함수로 구한 개수로 나눠 평균값 반환

평균을 구하는 방법 2) AVG 집계함수를 통해 반환

mysql> SUM(quantity) / COUNT(quantity);
mysql> AVG(quantity);

 

NULL 값을 0으로 간주하고 싶은 경우 CASE를 사용해 NULL 값을 0으로 변환한 뒤 AVG 계산

mysql> SELECT AVG(quantity), AVG(CASE WHEN quantity IS NULL THEN 0 ELSE quantity END) AS avgnull0 FROM t1;

 

MIN, MAX

각각 최솟값, 최댓값을 구하는 함수로 문자열형과 날짜시간형에도 사용 가능하며 NULL 값은 무시

mysql> SELECT MIN(quantity), MAX(quantity), MIN(name), MAX(name) FORM t1;

 

22. 그룹화

 

GROUP BY

열을 지정해 그룹화하여 지정된 열의 값이 같은 행을 하나의 그룹으로 묶는 함수

이때, DISTINCT와 같이 중복 제거 가능

mysql> SELECT name FROM t1 GROUP BY name;

* GROUP BY 구를 이용해 name열에서 같은 값을 가진 행끼리 묶어 반환

mysql> SELECT name, COUNT(name), SUM(quantity) FROM t1 GROUP BY name;

* GROUP BY 구를 지정하기 위해서는 집계함수를 반드시 사용해야하며 그룹화된 각각의 그룹이 하나의 집합으로서 집계함수 인수로 넘어가도록 설정

 

HAVING
SQL에서의 내부 처리 순서에 따라 WHERE 구에 집계함수를 설정할 수 없으므로 조건에 맞는 값을 선택하기 위해 사용하는 함수

GROUP BY 구문 뒤에 작성해 WHERE 구에 동일하게 조건식을 지정

mysql> SELECT name, COUNT(name) FROM t1 GROUP BY name; // 각 그룹의 행수를 반환
mysql> SELECT name, COUNT(name) FROM t1 GROUP BY name, HAVING COUNT(name) = 1; // 잡계한 결과에서 조건에 부합하는 값을 선택

 

복수열의 그룹화

GROUP BY에 지정하지 않은 열은 SELECT 구에도 지정할 수 없으며 그룹화하면 반환되는 결과를 그룹당 하나의 행으로 고정

mysql> SELECT MIN(no), name, SUM(quantity) FROM t1, GROUP BY name;
mysql> SELECT name, quantity FROM t1 GROUP BY name, quantity;

 

결과 정렬

GROUP BY를 사용하는 경우에도 실행결과 순서를 정렬할 수는 없으므로 ORDER BY를 사용해 정렬

mysql> SELECT name, COUNT(name), SUM(quantity) FROM t1 GROUP BY name ORDER by SUM(quantity) DESC;

 

23. 서브쿼리

 

서브쿼리

SELECT 명령에 의한 데이터 질의러, 상부가 아닌 하부의 부수적인 질의를 의미

SQL 명령문 안에 지정하는 하부 SELECT 명령으로 괄호로 묶어 지정하며 서브쿼리는 WHERE 구에서 사용

 

DELETE

조건에 해당하는 행을 삭제하는 함수

mysql> DELETE FROM t1 WEHRE a = (SELECT MIN(a) FROM t1);

 

단, MySQL에서는 해당 쿼리를 실행할 수 없으며 에러를 해결하기 위해서는 인라인 뷰로 임시 테이블을 제작 후 처리

mysql> DELETE FROM t1 WHERE a = (SELECT a FROM (SELECT MIN(a) AS a FROM t1) AS x);

 

스칼라

SELECT 명령이 하나의 값만 반환하는 것으로 SELECT 명령은 서브쿼리로 사용하기 용이

스칼라 서브쿼리: 스칼라 값을 반환하는 서브쿼리

 

SELECT 구에서 하나의 열을 지정한 후 GROUP BY를 지정하지 않은 채 집계함수를 사용하면 단일화한 값을 반환

WHERE 조건으로 하나의 행만 검색하는 SELECT 명령도 스칼라값 반환

 

- 연산자를 사용하는 경우 스칼라 값끼리 비교

- 스칼라 서브쿼리는 WHERE 구에 사용 가능하며 집계함수를 사용해 집계한 결과를 조건식으로 사용 가능

 

SELECT 구문 서브쿼리

mysql> SELECT (SELECT COUNT(*) FROM t1) as sq1, (SELECT COUNT(*) FROM t2) as sq2;

* MySQL 등에서는 위와 같이 FROM 구 생략이 가능하지만 Oracle 등 전통적인 DB 제품에서는 FROM 생략 불가, FROMDUAL로 지정 후 실행

 

SET 구문 서브쿼리

UPDATE의 SET 구문에서 서브쿼리 사용 가능

mysql> UPDATE t1 SET a = (SELECT MAX(a) FROM t1);
mysql> UPDATE t2 SET a = (SELECT a FROM (SELECT MAX(a) AS a FROM t2) AS x);

 

FROM 구문 서브쿼리

FROM 구에 기술하는 경우에는 반드시 스칼라 값을 반화할 필요는 없음

mysql> SELECT * FROM (SELECT * FROM t1) sq;
mysql> SELECT * FROM (SELECT * FROM (SELECT * FROM t1) sq1) sq2; // 3단계 중첩구조
mysql> SELECT * FROM (SELECT * FROM t1 ORDER BY a DESC) sq WHERE ROWNUM <= 2;

* nested 구조, 중첩구조, 내포구조

 

INSERT 명령 서브쿼리

mysql> INSERT INTO t1 VALUES ((SELECT COUNT(*) FROM t1), (SELECT COUNT(*) FROM t2)); // VALUES 구의 일부로 서브쿼리 사용
mysql> INSERT INTO T1 SELECT 1,2; // VALUES 구문 대신 SELECT 사용

 

INSERT SELECT

INSERT와 SELECT를 합친 명령으로 결과값을 INSERT INTO로 지정한 테이블에 전부 추가

단, SELECT 명령 반환 값이 꼭 스칼라일 필요는 없으며 SELECT의 반환 자료형이 INSERT 테이블과 일치하면 가능

이때, 열 구성이 동일한 테이블 사이에는 다음과 같은 INSERT SELECT 명령으로 행 복사 가능

mysql> INSERT INTP t1 SELECT * FROM t2;

 

24. 상관 서브쿼리

 

EXISTS

서브쿼리를 사용해 검색하는 경우에 데이터가 존재하는지 아닌지 판별하기 위해 조건을 지정하는 술어

mysql> UPDATE t2 SET a = 'yes' WHERE EXISTS (SELECT * FROM t2 WHERE no2 = no); // t1의 no2열의 값과 t2 no 열의 값에 같은 행이 있다면 해당 행 a열 값을 yes로 UPDATE

* EXISTS 술어에 서브쿼리를 지정해 서브쿼리가 행을 하나 이상 반환할 경우 참, 반환되는 행이 없는 경우 거짓

 

NOT EXISTS

EXISTS와 반대로 행이 존재하지 않을 경우 참

mysql> UPDATE t1 SET a = 'no' WHERE NOT EXISTS (SELECT * FROM t2 WHERE no2 = no)

* UPDATE 명령 외 SELECT, DELETE 명려에도 동일하게 사용 가능

 

상관 서브쿼리

UPDATE 명령에서는 WHERE 구에 괄호로 묶은 부분이 서브쿼리

mysql> UPDATE t1 SET a = 'yes' WHERE EXISTS (SELECT * FROM t2 WHERE no2 = no); // t2 no2열의 값과 t1 no열 값에 같은 행이 있지 않다면 a열 값을 'no'로 UPDATE

 

단순 서브쿼리

서브쿼리 부분만 따로 떼어내어 실행 가능

mysql> DELETE FORM t1 WHERE a = (SELECT MIN(a) FORM t1);
mysql> SELECT MIN(a) FROM t1;

 

상관 서브쿼리

서브쿼리 부분만 따로 떼어내어 실행 불가

mysql> UPDATE t1 SET a = 'yes' WHERE EXISTS (SELECT * FROM t2 WHERE no2 = no);
mysql> SELECT * FROM t2 WHERE no2 = no;

 

t1과 t2의 두 열의 이름이 'no'로 같은 경우 각 열이 어느 테이블의 것인지 명시적으로 나타내야하며 열명 앞에 테이블명을 붙여 테이블 지정

mysql> UPDATE t1 SET a = 'yes' WHERE EXISTS (SELECT * FROM t2 WHERE t2.no = t1.no);

 

IN

스칼라 값끼리 비교할 때 = 연산자를 사용하지만 집합을 비교할 때는 사용할 수 없으므로 IN을 사용해 집합안의 값이 존재하는지 조사

mysql> SELECT * FROM t1 WHERE no IN (3,5);
mysql> SELECT * FROM t1 WHERE no IN (SELECT no2 FROM t2);

* 특정 열의 값이 OR 조건식을 지정하는 경우 IN을 사용하며 반대로 NOT IN을 지정하면 집합에 값이 포함되지 않은 경우 참

 

IN에서의 NULL
IN을 사용하는 경우 NULL 값을 비교할 수 없으므로 NULL 비교 시 IS NULL 사용 필수

mysql> SELECT * FROM t1 WHERE no2 IN (null);

* NOT IN의 경우 집합 안에 NULL 값이 있는 경우 왼쪽 값이 집합 안에 포함되어 있지 않아도 참을 반환하지 않으며 결과는 UNKNOWN