[자격증 / SQLD] SQL 기본(3)_GROUP BY, ORDER BY, 조인(JOIN)

 

DATA ON-AIR에서 18번 GROUP BY, HAVING 절과 19번 ORDER BY 절, 20번 조인(JOIN)을 요약 정리한 내용이다.

 

 

 

---

 

 

 

집계 함수(Aggregate Function)

다중행 함수 중 집계 함수(Aggregate Function)는 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수이다.

1. 여러 행의 그룹에 대한 연산을 통해 하나의 결과를 돌려주는 함수이다.
2. GROUP BY 절을 통해 그룹핑 기준을 명시한다.
3. SELECT 절, HAVING 절, ORDER BY 절에 사용할 수 있다.

• 집계 함수명 ( [DISTINCT | ALL] 칼럼이나 표현식 )
  • ALL : Default, 옵션 생략 가능
  • DISTINCT : 같은 값을 갖는 여러 데이터를 하나의 데이터로 간주하는 옵션

 

다중 집계 함수 종류

다중 집게 함수	함수 설명
COUNT(*)	NULL 값을 포함한 행의 수
COUNT([DISTINCT | ALL] 표현식)	표현식의 값이 NULL 값인 것을 제외한 행의 수
SUM([DISTINCT | ALL] 표현식)	NULL 값을 제외한 합계
AVG([DISTINCT | ALL] 표현식)	NULL 값을 제외한 평균
MAX([DISTINCT | ALL] 표현식)	최대값 (문자, 날짜, 데이터 타입도 사용 가능)
MIN([DISTINCT | ALL] 표현식)	최소값 (문자, 날짜, 데이터 타입도 사용 가능)
STDDEV([DISTINCT | ALL] 표현식)	표현식의 표준 편차를 출력
VARIAN([DISTINCT | ALL] 표현식)	표현식의 분산을 출력
기타 통계 함수	벤더별로 다양한 통계식 제공

• 조건절에 해당하는 데이터가 없을 때 COUNT(*)의 결과 값은 0 (집계함수 통계정보 NULL값 가진 행 제외하고 수행)
• WHERE 절에 사용 불가

 

+ SUM(COL1) + SUM(COL2 + COL3) + SUM(COL4)의 값은?

COL1	COL2	COL3	COL4
NULL	NULL	50	30
30	20	10	30
NULL	10	NULL	NULL

정답 : 120

→ NULL 연산 결과 값도 NULL이다. 

 

 

 

GROUP BY

GROUP BY 절은 SQL 문에서 FROM 절과 WHERE 절 뒤에 오며, 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별로 통계 정보를 얻을 때 추가로 사용된다.

• GROUP BY는 테이블에서 행을 소규모 그룹화하여 합계, 평균, 최대, 최소 등을 계산할 수 있다.
• HAVING 절에 조건문을 사용한다.
• ORDER BY 절도 사용할 수 있다.

SELECT [DISTINCT] 칼럼명 [ALIAS명] FROM 테이블명 [WHERE 조건식] [GROUP BY 칼럼(Column)이나 표현식] [HAVING 그룹조건식] ;

 

+ GROUP BY 예시

-- 부서 아이디(DPT_ID) 별 직원들의 월급(SAL) 합계
SELECT DPT_ID, SUM(SAL) FROM EMP GROUP BY DPT_ID;

-- 부서 아이디가 10, 11, 12, 13인 직원들의 월급 합계 조회
SELECT DPT_ID, SUM(SAL) FROM EMP WHERE DPR_ID BETWEEN 10 AND 13 GROUP BY DPT_ID;

 

 

HAVING

• GROUP BY에 조건절을 사용하려면 HAVING 절을 사용해야 한다.
• 만약 WHERE 절에 조건문을 사용하게 된다면, GROUP BY 대상에서 제외된다.

SELECT DPT_ID, SUM(SAL) FROM EMP GROUP BY DPT_ID HAVING SUM(SAL) > 30000;

위의 예시는 부서 아이디(DPT_ID) 별 직원들의 월급(SAL) 합계를 출력하지만, HAVING문의 조건으로 인해 월급 합계가 30000을 넘는 행들만 조회한다.

 

 

GROUP BY 절과 HAVING 절의 특성

• GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.
• 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.
• GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없다.
• 집계 함수는 WHERE 절에는 올 수 없다. (집계 함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행됨)
• WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킨다.
• HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있다.
• GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다.
• HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치한다.

 

 

 

ORDER BY

ORDER BY 절은 SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 칼럼을 기준으로 정렬하여 출력하는데 사용한다.

• ORDER BY 절에서는 칼럼명, SELECT 절에서 사용한 ALIAS명, 칼럼순서 같이 혼용해서 사용 가능하다.
• 기본적으로 오름차순(A, B, C or 1, 2, 3)이 적용되며, DESC 옵션을 통해 내림차순으로 정렬 가능하다.
• SQL 문장의 제일 마지막에 위치한다.
• SELECT 절에서 정의하지 않은 칼럼 사용 가능하다.

Oracle - NULL을 가장 큰 값으로 간주
SQL Server - NULL을 가장 작은 값으로 간주

 

 

 

SELECT 문장 실행 순서

GROUP BY 절과 ORDER BY가 같이 사용될 때 SELECT 문장은 6개의 절로 구성된다.

SELECT 칼럼명 [별칭] FROM 테이블명 WHERE 조건식 GROUP BY 컬럼/표현식 HAVING 그룹조건식 ORDER BY 칼럼/표현식;

 

+ SELECT 문장 실행 순서

FROM → WHERE  → GROUP BY  → HAVING  → SELECT  → ORDER BY

1. 발췌 대상 테이블을 참조한다. (FROM)
2. 발췌 대상 데이터가 아닌 것은 제거한다. (WHERE)
3. 행들을 소그룹화 한다. (GROUP BY)
4. 그룹핑된 값의 조건에 맞는 것만을 출력한다. (HAVING)
5. 데이터 값을 출력/계산한다. (SELECT)
6. 데이터를 정렬한다. (ORDER BY)

 

+ SQL Server의 WITH TIES

SELECT TOP(2) WITH TIES ENAME, SAL FROM EMP ORDER BY SAL DESC;

> 급여가 높은 2명을 내림차순으로 출력하되, 같은 급여를 받는 사원은 같이 출력한다.

 

 

 

Top N 쿼리

ROWNUM (ORACLE)

WHERE 절에서 행의 개수를 제한하는 목적으로 사용

• 한 행만 가져오고 싶을 때
  WHERE ROWNUM = 1; WHERE ROWNUM <= 1; WHERE ROWNUM < 2;
• 두 건 이상의 N행을 가져오고 싶을 때
  WHERE ROWNUM = N; (X)
  WHERE ROWNUM <= N; WHERE ROWNUM < N+1; (O)

✔ SQL Server
SELECT TOP(2) WITH TIES ENAME, SAL
> 1위 한 명, 공동 2위가 2명 있을 때, WITH TIES 조건 추가하면 결과가 3건 출력된다. (WITH TIES 없으면 2건 출력)

 

 

 

조인(JOIN) 개요

두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것을 조인(JOIN)이라고 한다.

• 일반적인 경우 행들은 PRIMARY KEY(PK)나 FOREIGN KEY(FK) 값의 연관에 의해 JOIN이 성립된다.
• 하지만 어떤 경우에는 이러한 PK, FK의 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립 가능하다.

✔ 한 가지 주의할 점은 FROM 절에 여러 테이블이 나열되더라도 SQL에서 데이터를 처리할 때는 단 두 개의 집합 간에만 조인이 일어난다는 것이다.

> 예를 들어, 5가지의 테이블을 JOIN 하기 위해서는 최소 4번의 JOIN 과정이 필요하다. (N-1개)

 

 

 

등가 조인(EQUI JOIN)

• 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용되는 방법이다.
• 대부분 PK ↔ FK의 관계를 기반으로 한다. (테이블 설계 시에 나타난 PK ↔ FK의 관계)
• JOIN의 조건은 WHERE 절에 기술하게 되는데 “=” 연산자를 사용해서 표현한다.

-- WHERE 절에 JOIN 조건을 넣는다.
SELECT
	테이블1.칼럼명,
	테이블2.칼럼명,
	...
FROM
	테이블1,
	테이블2
WHERE
	테이블1.칼럼명1 = 테이블2.칼럼명2;

 

+ INNER JOIN

• 위와 같은 내용을 ANSI/ISO 표준 방식으로 표현하면 아래와 같다.
• INNER JOIN은 'ON 구'를 사용해 테이블을 연결한다.

-- ON 절에 JOIN 조건을 넣는다.
SELECT
	테이블1.칼럼명,
	테이블2.칼럼명,
	...
FROM
	테이블1
INNER JOIN 테이블2 ON
	테이블1.칼럼명1 = 테이블2.칼럼명2;

 

 

 

비등가 조인(Non EQUI JOIN)

• 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용된다.
• “=” 연산자가 아닌 다른(Between, >, >=, <, <= 등) 연산자들을 사용하여 JOIN을 수행한다.

SELECT
	테이블1.칼럼명,
	테이블2.칼럼명,
	...
FROM
	테이블1,
	테이블2
WHERE
	테이블1.칼럼명1 BETWEEN 테이블2.칼럼명1 AND 테이블2.칼럼명2;

 

 

 

CROSS JOIN

CROSS JOIN은 조인 조건 구 없이 2개의 테이블을 하나로 조인한다.

• 조인구가 없어서 카테시안 곱이 발생한다.
• 만약 행이 14개 있는 테이블과 행이 4개 있는 테이블을 조인하면 총 56개의 행이 조회된다.
• FROM절에 'CROSS JOIN' 구를 사용하면 안 된다.

SELECT * FROM 테이블1 CROSS JOIN 테이블2;

 

 

 

---

[관련 글 보러가기]

• [자격증 / SQLD] SQL 기본(1)_TABLE과 명령어(DML, DDL, DCL, TCL)
• [자격증 / SQLD] SQL 기본(2)_WHERE 조건절(연산자)과 함수
• [자격증 / SQLD] SQL 활용(1)_표준 조인과 집합 연산자
• [자격증 / SQLD] SQL 활용(2)_계층형 질의, 서브쿼리
• [자격증 / SQLD] SQL 활용(3)_그룹 함수와 윈도우 함수
• [자격증 / SQLD] SQL 활용(4)_절차형 SQL

 

---

 

[참고자료]

 

https://dataonair.or.kr/db-tech-reference/d-guide/sql/?pageid=4&mod=list

SQL – DATA ON-AIR