[SQL] 5. 집계와 서브쿼리

1. 행 개수 구하기 - COUNT

1) COUNT로 행 개수 구하기

-집계함수란, 인수로 집합을 지정하면 특정 방법으로 계산 후 그 결과흘 반환하는 함수임.

   → 대표적인 집계함수

• COUNT(집계함수)
• SUM(집계함수)
• AVG(집계함수)
• MIN(집계함수)
• MAX(집계함수)

   → 집계함수는 복수의 값(집합)에서 하나의 값을 계산해내므로 결과로 하나의 행을 반환함.

-COUNT 집계함수는 인수로 지정된 집합의 개수를 계산하는 함수임.(=행의 개수를 계산)

-집계함수는 복수의 값(집합)에서 하나의 값을 계산해내므로 결과로 하나의 행을 반환함.

SELECT COUNT(*) FROM sample51 WHERE name = 'A';

   

→ WHERE 구가 SELECT 구보다 먼저 처리됨. 따라서 WHERE 구로 조건을 지정하면 테이블 전체가 아닌 검색된 행이 COUNT로 넘겨짐.

 

2) 집계함수와 NULL 값

-COUNT의 인수로 *나 열명을 지정할 수 있음.(다른 집계함수는 인수로 * 지정 불가능) 열명을 지정하면 그 열에 한해서 행의 개수를 구할 수 있음.

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

   → ex)

no	name	quantity
1	A	1
2	A	2
3	B	5
4	C	22
5	NULL	9

SELECT COUNT(no), COUNT(name) FROM sample51;

 

   → NULL을 무시하므로 COUNT(no)의 결과값은 5, COUNT(name)의 결과값은 4가 나옴.

 

3) DISTINCT로 중복 제거

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

-ALL을 지정하면 중복 유무와 관계없이 문자 그대로 모든 행을 반환함.

-어느 것도 지정하지 않은 경우엔 중복된 값이 제거되지 않음. 즉 생략할 경우 ALL로 간주함.

SELECT ALL name FROM sample51;
SELECT DISTINCT name FROM sample51;

 

 

4) 집계함수에서 DISTINCT

-집계함수의 인수로 DISTINCT을 사용한 수식을 지정하면 집합에서 중복을 먼저 제거한뒤 COUNT로 개수를 구할 수 있음.

SELECT COUNT(ALL name), COUNT(DISTINCT name) FROM sample51;

 

2. COUNT 이외의 집계함수

1) SUM으로 합계 구하기

-SUM 집계함수를 사용해 집합의 합계를 구할 수 있음.

SELECT SUM(quantity) FROM sample51;

 

-SUM 집계함수에 지정되는 집합은 수치형만 가능함.

 

2) AVG로 평균내기

-AVG 집계함수를 통해 평균값을 간단하게 구할 수 있음.

SELECT AVG(quantity) FROM sample51;

 

-AVG 집계함수에 주어지는 집합은 수치형만 가능함.

 

3)MIN/MAX로 최솟값/최댓값 구하기

-MIN/MAX 집계함수를 사용해 집합에서 최솟값 최댓값을 구할 수 있음.

SELECT MIN(quantity), MAX(quantity), MIN(name), MAX(name) FROM sample51;

 

-이들 함수는 문자열형과 날짜시간형에도 사용할 수 있음.

 

3. 그룹화 - GROUP BY

1) GROUP BY로 그룹화

-SYNTAX

SELECT * FROM 테이블명 GROUP BY 열1, 열2...

 

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

   → DISTINCT 예약어를 지정한 것과 같이 중복을 제거하는 효과가 있음.

 

2) HAVING 구로 조건 지정

-내부처리 순서로 인해 집계함수는 WHERE 구의 조건식에서는 사용할 수 없음.

SELECT name, COUNT(name) FROM sample51 WHERE COUNT(name) = 1 GROUP BY name;

 

   → 에러 발생하여 실행 불가능

   →  내부처리 순서: WHERE 구 -> GROUP BY 구 -> SELECT 구 -> ORDER BY 구

 

-HAVING 구를 사용하면 집계함수를 사용하여 조건식 지정 가능함.

SELECT name, COUNT(name) FROM sample51 GROUP BY name HAVING COUNT(name)=1;

 

   →  내부처리 순서: WHERE 구 -> GROUP BY 구 -> HAVING 구 -> SELECT 구 -> ORDER BY 구

 

3) 복수열의 그룹화

-GROUP BY를 사용할 때, GROUP BY 구에 지정한 열 이외의 열은 집계함수를 사용하지 않은 채 SELECT 구에 기술해서는 안됨.

SELECT no,quantity FROM sample51 GROUP BY no, quantity;

 

4) 결괏값 정렬

-GROUP BY로 그룹화해도 실행결과 순서를 정렬할 수 없고, ORDER BY 구를 사용해 결과를 정렬할 수 있음.

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

 

4. 서브쿼리

-서브쿼리: SELECT 명령에 의한 데이터 질의로, 상부가 아닌 하부의 부수적인 질의를 의미

   →  SYNTAX: ( SELECT 명령 )

-특히 서브쿼리는 SQL 명령의 WHERE 구에서 주로 사용됨. WHERE 구는 SELECT, DELETE, UPDATE 구에서 사용할 수 있는데 이들 중 어떤 명령에서든 서브쿼리 사용 가능함.

 

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

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

 

   →  괄호로 둘러싼 서브쿼리 부분을 먼저 실행한 후 DELETE 명령을 실행함. 

 

2) 스칼라 값

-일반적인 서브쿼리 패턴

• 하나의 값을 반환하는 패턴

SELECT MIN(a) FROM sample54;

• 복수의 행이 반환되지만 열은 하나인 패턴

SELECT no FROM sample54;

• 하나의 행이 반환되지만 열이 복수인 패턴

SELECT MIN(a), MAX(no) FROM sample54;

• 복수의 행, 복수의 열이 반환되는 패턴

SELECT no, a FROM sample54;

 

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

   →  스칼라 값을 반환하는 SELECT 명령은 서브쿼리로서 사용하기 쉬움. WHERE 구에서 스칼라 값을 반환하는 서브쿼리는 = 연산자로 비교할 수 있음.

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

 

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

-SELECT 구에서 서브쿼리를 지정할 때는 스칼러 서브쿼리가 필요함.

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

 

   →  상부의 SELECT 명령에는 FROM 구가 없다는 것에 주의. MySQL 등에서는 FROM 구를 생략 가능함.

 

4) SET 구에서 서브쿼리 사용하기

-UPDATE의 SET 구에서도 서브쿼리를 사용할 수 있음. 

UPDATE sample54 SET a = (SELECT MAX(a) FROM sample54);

 

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

-SELECT 구나 SET 구에서는 스칼라 서브쿼리를 지정해야 하지만 FROM 구에 기술할 경우에는 스칼라 값을 반환하지 않아도 상관없음. 

SELECT * FROM (SELECT * FROM sample54) sq;

 

 

   →  SELECT 명령 안에 SELECT 명령이 들어있는 것과 같은 구조를 네스티드 구조(=중첩구조, 내포구조)라고 함.

 

6) INSERT 명령과 서브쿼리

-INSERT 명령과 서브쿼리를 조합해 사용하는 방법은 크게 두가지로 나뉜다.

   →  VALUES 구의 일부로 서브쿼리를 사용하는 경우: 서브쿼리를 스칼라 서브쿼리로 지정해야 하며, 자료형이 일치해야 함. 

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

 

  →  VALUES 구 대신 SELECT 명령을 사용하는 경우: SELECT가 반환하는 값이 꼭 스칼라 값일 필요는 없으며, SELECT가 반환하는 열 수와 자료형이 INSERT할 테이블과 일치하기만 하면 됨.

INSERT INTO sample541 SELECT 1,2;

 

5. 상관 서브쿼리

1) EXISTS

-서브쿼리를 사용해 검색할 떄 데이터가 존재하는지 아닌지 판별하기 위해 EXISTS 술어를 지정하여 조사할 수 있음.

-EXIST는 서브쿼리가 행을 반환할 경우 참을 반환하고, 반환되는 행이 없으면 거짓을 반환함.

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

 

2) NOT EXISTS 

-NOT EXISTS는 행이 존재하지 않는 경우에 참을 반환함.

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

 

3) 상관 서브쿼리

-부모 명령과 자식인 서브쿼리가 특정 관계를 맺는 것을 상관 서브쿼리라고 부름

  →  ex)

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

 

=> UPDATE 명령(부모)에서 WHERE 구에 괄호로 묶은 부분이 서브쿼리(자식)이 됨. 부모 명령에서는 sample551을 갱신하고 자식인 서브쿼리에서는 sample552 테이블의 no2열 값이 부모의 no 열 값과 일치하는 행을 검색함.

 

-테이블 명 붙이기: 다른 테이블의 같은 데이터의 열명이 같다면 그것을 이용하여 명령을 작성할 때 열이 어느 테이블의 것인지 명시적으로 나타낼 필요가 있음.

  →  테이블명.열명과 같이 명시화함.

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

 

4) IN

-IN을 사용하면 집합 안의 값이 존재하는지를 조사할 수 있음.

-SYNTAX: 열명 IN(집합)

SELECT * FROM sample551 WHERE no = 3 or no = 5;
SELECT * FROM sample551 WHERE no IN (3,5);
SELECT * FROM sample551 WHERE no = IN
 (SELECT no2 FROM sample552);

 

  →  세 명령어 모두 똑같은 결과를 반환.

-IN 에서는 집합 안에 NULL 값이 있어도 무시하지는 않지만, NULL=NULL을 제대로 계산할 수 없으므로 IN 을 사용해도 NULL 값은 비교할 수 없음.(NULL 값의 비교는 IS NULL을 이용해야 함.)