SQL 공부 일지 7일차 230820

## 공부정리

과목2 1장 SQL 기본 3편

7) GROUP BY, HAVING 절

8) ORDER BY 절

9) JOIN(조인)

 

 

 

#7 GROUP BY, HAVING 절

1 집계함수

• 여러 행들의 그룹이 모여서 그룹 당 단 하나의 결과를 돌려주는 다중행 함수
• GROUP BY 절은 행들을 소그룹
• SELECT 절, HAVING 절, ORDER BY 절에 사용
• 집계함수명 (ALL | Distinct 칼럼)
• 주로 숫자형에서 사용, MIN MAX COUNT는 문자 날짜도 적용가능

 

2 집계함수의 종류

이미지 출처 : https://openbetweensecret.tistory.com/entry/SQL-%EA%B8%B0%EB%B3%B8-%EB%B0%8F-%ED%99%9C%EC%9A%A9-%EC%A7%91%EA%B3%84%ED%95%A8%EC%88%98GROUP-BY-HAVING-ORDER-BY

 

 

3 GROUP BY 절

• FROM 절과 WHERE 절 뒤에 오며, 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별 통계 정보를 얻을 때 사용
• ROLLUP이나 CUBE에 의한 소계가 계산된 결과에는 GROUPING(EXPR)=1 이 표시
• 그 외 결과에는 GROUPING(EXPR)=0 이 표시

 

SQL>>

SELECT [DISTINCT]칼럼명[ALIAS명]FROM 테이블명[WHERE조건식]

[GROUP BY칼럼(Column)이나 표현식] [HAVING 그룹조건식];

 

"K-리그 선수들의 포지션별 평균키는 어떻게 되는가?"
SELECT POSITION 포지션, COUNT(*)인원수, COUNT(HEIGHT)키대상, MAX(HEIGHT) 최대키,
MIN(HEIGHT) 최소키, ROUND(AVG(HEIGHT),2) 평균키 FROM PLAYER GROUP BY POSITION;

결과>> 포지션 인원수 키대상 최대키 43 43 196 174 186.26 DF 172 142 190 170 180.21
	FW 100 100 194 168 179.91 MF 162 162 189 165 176.31

 

 

4 HAVING 절

- HAVING 절은 WHERE 절과 비슷하지만 그룹을 나타내는 결과 집합의 행에 조건이 적용

- WHERE 절에는 집계함수를 사용할 수 없다.

- GROUP BY 절보다 HAVING 절을 앞에 사용해도 같은 결과가 나오긴 하지만 논리적 순서를 지키는 것을 권고한다.

- HAVING 절은 SELECT 절에 사용되지 않은 칼럼이나 집계함수가 아니더라도, GROUP BY 절의 기준 항목이나 소그룹의 집계함수를 이용한 조건을 표시 가능

- WHERE 절 조건 변경은 대상 데이터 개수가 변경되므로 결과 데이터 값이 변경 가능성 있음

- HAVING 절 조건 변경은 결과 데이터 변경은 없고 출력되는 레코드 개수만 변경 가능성 있음

 

SQL>>
"HAVING 절을 이요해 평균키가 180 센터미터 이상인 정보만 표시"

SELECT POSITION 포지션, ROUND(AVG(HEIGHT),2) 평큔키 FROM PLAYER
GROUP BY POSITION HAVING AVG((HEIGHT) >= 180;

 

 

5 GROUP BY 절과 HAVING 절의 특성

• GROUP BY에 의한 소그룹별 만들어진 집계 데이터 중 HAVING 조건 만족하는 내용만 출력
• 가능하면 GROUP BY 하기 전에, WHERE 절로 계산 대상을 줄이는게 효과적
• WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 필요없는 조건을 미리 제거하는 역할
• HAVING 절은 GROUP BY로 만들어진 소그룹에 대해서만 조건임

 

 

6 CASE 표현을 활용한 월별 데이터 집계 -함수(CASE()) ~ GROUP BY

: 모델링의 제1정규화로 인해 반복되는 칼럼의 경우 구분 칼럼을 두고 여러 개의 레코드로 만들어진 집합을 정해진 칼럼 수만큼 확장해서 집계 보고서를 만드는 유용한 기법

 

 

7 집계함수와 NULL 처리

: 리포트 출력 때 NULL이 아닌 0을 표시하고 싶은 경우네는 NVL(SUM(SAL),0)이나, ISNULL(SUM)SAL),0) 처럼 전체 SUM의 결과가 NULL인 경우(대상 건수가 모두 NULL인 경우)에만 한 번 NVL/ISNULL 함수를 사용

 

 

 

#8 ORDER BY 절

1 ORDER BY 정렬

• SQL 문장으로 조회된 데이터들을 목적에 맞게 특정 칼럼을 기준으로 정렬하여 출력
• ORDER BY 절에 칼럼명 대신 SELECT 절에서 사용한 ALIAS 명, 칼럼 순서를 나타내는 정수도 사용이 가능
• 기본적으로 오름차순이며 SQL 문장이 제일 마지막에 위치
• 숫자형 타입은 오름차순시 작은 값 / 날짜형 타입은 오름차순시 빠른 날부터 출력
• ORDER BY에는 GROUP BY 칼럼이나 SELECT의 칼럼만 올 수 있다
• Oracle에선 null을 가장 큰 값으로 SQL Server에선 null을 가장 작은 값으로 간주

 

SQL>>
"선수 테이블에서 선수들의 이름, 포지션, 백넘버를 출력하는데 사람 이름을 내림차순(DESC)으로 정렬하여 출력, 키가 NULL인 데이터는 제외"

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버 FROM PLAYER
WHERE BACK_NO IS NOT NULL ORDER BY PLAYER_NAME DESC;

 

 

2 SELECT 문장의 실행 순서

1. 발췌 대상 테이블을 참조한다 - FROM

2. 발췌 대상 데이터가 아닌 것은 제거한다. - WHERE

3. 행들을 소그룹화 한다. - GROUP BY

4. 그룹핑된 값의 조건에 맞는 것만을 출력한다. - HAVING

5. 데이터 값을 출력/계산한다. - SELECT

 

이미지 출처 : https://wikidocs.net/133046

• WHERE절에서는 컬럼의 별칭을 사용할 수 없다.
  • 컬럼의 별칭은 SELECT절에서 정의되는데, WHERE절은 SELECT절보다 먼저 처리되기 때문이다.
• GROUP BY가 포함된 SQL에서는,
  • GROUP BY에 정의한 내용(컬럼 또는 변형한 컬럼)만 SELECT절에서 사용할 수 있다.
  • GROUP BY에 정의하지 않은 내용은 SELECT절에서 집계함수 처리해야 한다.
• ORDER BY절에서는 컬럼의 별칭을 사용할 수 있다.
  • SELECT절이 ORDER BY절보다 먼저 처리되기 때문이다.
• ORDER BY절에는,
  • 테이블의 컬럼명을 바로 사용할 수 있다.
  • SELECT절에서 사용한 별칭도 사용할 수 있다.
  • SELECT절에 적은 컬럼의 순서에 따른 번호(왼쪽부터 1번)를 사용할 수도 있다.

 

 

3 TOP N 쿼리

① ROWNUM

- Oracle에서 순위가 높은 N개의 로우를 추출하기 위해 ORDER BY 절과 WHERE 절의 ROWNUM 조건을 같이 사용하는 경우, 이 두 조건으로는 원하는 결과를 얻을 수 없다.

- Oracle의 경우 정렬이 완료된 후 데이터의 일부가 출력되는 것이 아니라 데이터의 일부가 먼저 추출된 후(ORDER BY 절은 결과 집합을 결정하는데 관여하지 않음) 데이터에 대한 정렬작업 수행

 

② TOP()

- SQL 은 TOP 조건을 사용하게 되면 별도 처리 없이 관련 ORDER BY 절의 데이터 정렬 후 원하는 일부 데이터만 쉽게 출력

- TOP 절을 사용하여 결과 집합으로 반환되는 행의 수를 제한

SQL>>
"사원 테이블에서 급여가 높은 2명을 내림차순으로 출력하는데 같은 급여를 받는 사원이 있으면 같이 출력한다."

SELECT TOP(2) WITH TIES ENAME, SAL FROM EMP ORDER BY SAL DESC;
결과>>> KING 5000 SCOTT 3000 FROD 3000

 

 

 

#9 JOIN(조인)

1 JOIN - 두 개 이상의 테이블들을 연결 / 결합하여 데이터를 출력

• JOIN은 관계형 데이터베이스의 가장 큰 장점이면서 대표적인 핵심 기능
• 일반적인 경우 행들은 PK나 FK 값의 연관에 의해 JOIN이 성립된다
• 어떤 경우에는 PK, FK 관계가 없어도 논리적인 값들의 연관만으로 JOIN 성립이 가능
• 하나의 SQL 문장에서 여러 테이블을 조인해서 사용 할 수도 있다.
• FROM 절에 여러 테이블이 나열되더라도 SQL에서 데이터를 처리할 때는 두 개의 집합간에만 JOIN이 일어난다.
• FROM 절에 A, B, C 3개의 테이블이 나열되었더라도 특정 2개의 테이블만 먼저 조인되고, 그 조인된 새로운 결과 집합과 남은 한 개의 테이블이 다음 차례로 조인

 

 

2 EQUI JOIN (등가 조인)

- 두 테이블의 칼럼 갑시 정확하게 일치하는 경우, 대부분 PK ↔ FK 관계 기반

- JOIN 조건은 WHERE 절에 기술

 

* 조인 시 주의사항

- 조건 절에 테이블에 대한 ALIAS명을 적용하여 SQL 문장을 작성했을 경우, WHERE 절과 SELECT 절에는 테이블명이 아닌 ALIAS를 사용

 

SELECT 테이블1.칼럼명, 테이블2.칼럼명,

FROM 테이블1, 테이블2

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

 

SELECT 테이블1.칼럼명, 테이블2.칼럼명,

FROM 테이블1 INNER JOIN 테이블2 ON 테이블1.칼럼명1 = 테이블2.칼럼명2 -> ON 절에 JOIN을 넣는다.

 

SQL>>
"선수 테이블과 팀 테이블에서 선수 이름과 소속된 팀의 이름을 출력하시오."

SELECT PLAYER.PLAYER_NAME 선수명, TEAM.TEAM_NAME 소속팀명 FROM PLAYER_TEAM
WHERE PLAYER.TEAM_ID = TEAM.TEAM_ID;

(같은코드)

SELECT PLAYER.PLAYER_NAME 선수명, TEAM.TEAM_NAME 소속팀명 FROM PLAYER
INNER JOIN TEAM ON PLAYER.TEAM_ID = TEAM.TEAM_ID;

 

 

3 Non EQUI JOIN (비등가 조인)

- 두 테이블의 칼럼 값이 정확하게 일치하지 않는 경우

- Non EQUI JOIN 경우에는 "=" 연산자가 아닌 다른 (Between, >, >=, <, <= 등) 연산자들을 사용하여 JOIN을 수행

 

SELECT 테이블1.칼럼명, 테이블2.칼럼명,

FROM 테이블1, 테이블2

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

 

SQL>>
"선수들 별로 홈그라운드 경기장이 어디인지를 출력하고 싶다고 했을 때,
선수 테이블과 운동장 테이블이 서로 관계가 없으므로 중간에 팀 테이블이라는 서로 연관관계가 있는 테이블을
추가해서 새 개의 테이블을 JOIN 해야만 원하는 테이블을 얻을 수 있디."

SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션, T.REGION_NAME 연고지, T.TEAM_NAME 팀명,
S.STADIUM_NAME 구장명 FROM PLAYER P, TEAM T, STADIUM S
WHERE P.TEAM_ID = T.TEAM_ID AND T.STADIUM_ID = S.STADIUM_ID
ORDER BY 선수명;

(같은 코드)

SELECT PLAYER.PLAYER_
SELECT P.PLATER_NAME 선수명, P.POSITION 포지션, T.REGION_NAME 연고지, T.TEAM_NAME 팀명,
S.STADIUM_NAME 구장명 FROM PLAYER P INNER JOIN TEAM T ON P.TEAM_ID = T.TEAM_ID
INNER JOIN STADIUM S ON T.STADIUM_ID = S.STADIUM_ID ORDER BY 선수명;