- SQL 활용 - 1(서브 쿼리, 그룹 함수)2022년 04월 14일 21시 42분 50초에 업로드 된 글입니다.작성자: sue24
서브 쿼리
서브 쿼리는 메인 쿼리의 칼럼을 활용할 수 있다.(반대는 불가)
메인 쿼리에서 서브 쿼리의 칼럼을 쓰고 싶다면조인,스칼라 서브 쿼리메인 쿼리의 결과가 서브 쿼리로 제공될 수도 있고,
서브 쿼리의 결과가 메인 쿼리로 제공될 수도 있다.조인은 집합간의 곱(product)의 관계다
- 1 : 1관계 테이블 조인 => 1레벨(1*1) 집합
- 1 : M관계 테이블 조인 => M레벨(1*M) 집합
- M : N관계 테이블 조인 => MN레벨(M*N) 집합
- 그러나 서브 쿼리는 서브 쿼리의 레벨과 무관하게 항상 메인 쿼리 레벨로 결과 집합이 생성된다.
서브 쿼리는 ()로 감싼다
서브 쿼리에서 비교 연산자 사용 가능
중첩 / 스칼라 서브 쿼리는 order by 사용 불가
서브 쿼리의 분류
- 동작하는 방식
- 비연관: 서브 쿼리가 메인 쿼리 칼럼을 가지지 않음. 주로 메인에 값 제공
- 연관
- 반환하는 데이터 형태
- 단일행 서브 쿼리: 서브 쿼리 실행 결과가 1건 이하.
단일행 비교 연산자와 함께 사용한다.(=, <, <=, >, >=, <>) - 다중행 서브 쿼리: 실행 결과가 여러 건.
다중행 비교 연산자 사용(IN, ALL, ANY, SOME, EXISTS) - 다중 칼럼 서브 쿼리: 실행 결과가 여러 칼럼.
메인 쿼리 조건절에 여러 칼럼 동시 비교 가능
서브 쿼리와 메인 쿼리에서 비교하고자 하는 칼럼 개수와 위치가 동일해야 한다
- 단일행 서브 쿼리: 서브 쿼리 실행 결과가 1건 이하.
- 동작하는 방식
단일행 서브 쿼리
결과가 반드시 1건 이하. 2건 이상이면 런타임 오류
단일행 비교 연산자와 함께 사용한다:
=, <, <=, >, >=, <>다중행 비교 연산자를 사용 할 수 있다.(다중행 서브 쿼리는 단일행 비교 연산자 사용 불가)
WHERE TEAM.ID = (SELECT TEAM.ID FROM PLAYER WHERE NAME='가나다'); WHERE HEIGHT >= (SELECT AVG(HEIGHT) FROM PLAYER);다중행 서브 쿼리
결과가 두 건 이상이면 반드시 다중행 비교 연산자 사용(아니면 에러가 난다)
- IN: multiple OR
IN / NOT IN은 메인 쿼리 각 행에 대해 서브 쿼리가 반환하는 모든 값과 비교해서 하나라도 일치하면 T / F
그러나 NOT IN을 쓸 때, 서브 쿼리 반환 결과 집합에 NULL이 있으면 일치여부를 알 수 없다(공집합 반환) - ALL: 서브 쿼리 결과의 모든 값을 만족한다. (>라면 최댓값보다 큰 것)
- ANY: 서브 쿼리 결과 중 어느 하나라도 만족한다.
SOME과 유사 (>라면 최솟값보다 큰 것) - EXISTS: 만족하는 값의 존재 여부. 여러 건이 만족하더라도 한 건만 찾으면 중지한다
EXISTS는 메인 쿼리 각 행에 대해 조인 성공 데이터가 있으면 T
NOT EXISTS는 메인 쿼리 각 행에 대해 조인 성공 데이터가 있으면 F
WHERE C1 NOT IN(1, NULL) => NOT (C1 = 1 OR C1 = NULL) => C1 <> 1 AND C1 <> NULL => C1 <> 1 AND NULL => NULL모든 조건이 NULL이므로 WHERE절을 만족하는 C1은 없다
SELECT A, B FROM T1 EXCEPT SELECT A, B FROM T2;EXCEPT는 NOT IN / NOT EXISTS로 대체 가능
SELECT T1.A, T2.B FROM T1 WHERE (T1.A, T1.B) NOT IN (SELECT T2.A, T2.B FROM T2); /* 위와 아래는 결과가 다르다 */ SELECT T1.A, T2.B FROM T1 WHERE T1.A NOT IN (SELECT T2.A FROM T2) AND T1.B NOT IN (SELECT T2.B FROM T2);T1 - (a, 1), (a, 2)
T2 - (b, 1), (a, 3)
이런 상황이면 a는 이미 T1.A NOT IN 조건절에 걸려서 공집합이 된다.
하나씩 떼서 비교하지 말고 한 쌍으로 묶어서 비교하기!
- IN: multiple OR
```sql SELECT T1.A, T2.B FROM T1 WHERE NOT EXISTS (SELECT 'X' FROM T2 WHERE T1.A = T2.A AND T1.B = T2.B); ```이용된 적 있는 서비스 추출하기
서비스 : 서비스 이용 = 1: M
SELECT * FROM (SELECT 서비스ID FROM 서비스 INTERSECT SELECT 서비스 ID FROM 서비스이용) A, 서비스 B WHERE A.서비스ID = B.서비스ID;서비스, 서비스 이용을 서비스ID로 이너 조인하고 GROUP BY까지!
그룹 바이를 안 하면 1:M관계라서 서비스이용건수만큼 결과가 나온다SELECT * FROM 서비스 X WHERE NOT EXISTS (SELECT 1 FROM (SELECT 서비스ID FROM 서비스 MINUS SELECT 서비스ID FROM 서비스이용) Y WHERE X.서비스ID = Y.서비스ID);서브 쿼리에서 전체 서비스에서 이용된 적 있는 서비스를 빼서 이용된 적 없는 서비스만 남긴다
그리고 전체에서 이 이용된 적 없는 서비스를 빼서 이용된 적 있는 서비스를 추출하는 것
SELECT * FROM 서비스 A LEFT OUTER JOIN 서비스이용 B ON (A.서비스ID = B.서비스ID) WHERE B.서비스ID IS NOT NULL GROUP BY B.서비스ID, A.서비스명, A.서비스URL;WHERE절에서 IS NULL 조건을 썼으면 사용된 적 없는 서비스를 추출한다
현재 부양하는 가족들이 없는 사원들의 이름을 구하라
가족의 부양사번 = 사원의 사번SELECT * FROM 사원 WHERE NOT EXISTS (SELECT * FROM 가족 WHERE 사번 = 부양사번);SELECT * FROM 사원 WHERE 사번 NOT IN (SELECT 부양사번 FROM 가족);SELECT * FROM 사원 LEFT OUTER JOIN 가족 ON (사번 = 부양사번) WHERE 부양사번 IS NULL;
SELECT A.ID, A.NAME, A.이메일 FROM 회원 A WHERE EXISTS(SELECT 'X' FROM 이벤트 B, 이메일발송 C WHERE B.시작일자 >= '2014.10.01' AND B.이벤트ID = C.이벤트ID AND A.회원ID = C.회원ID HAVING COUNG(*) < (SELECT COUNT(*) FROM 이벤트 WHERE 시작일자 >= '2014.10.01'));해빙 절 전까지의 서브쿼리는 회원별로 2014/10/01 이후 이벤트에 대해 발송된 메일을 구한다.
해빙 절은 2014/10/01 이후의 이벤트 수보다 2014/10/01 이후 발송된 이메일 수가 적은 회원을 구한다.
SELECT 1이나SELECT 'X'같은 경우에는 WHERE절에만 집중하면 된다WHERE절이 TRUE일 때만 본다
TA의 C1칼럼에 1, 2, 3이 있고
TB의 C1칼럼에 1, 2가 있다.
SELECT COUNT(*) FROM TA A WHERE EXISTS (SELECT 1 FROM TB X WHERE X.C1 = C1);상관 서브 쿼리 칼럼이 한정되어 있지 않다면 서브 쿼리 테이블의 칼럼을 우선 검색한다
그래서 이 경우엔
X.C1 = X.C1조건이 되는 것이는 항상 참이므로 TA의 전체 행을 반환한다.
답은 3이다
AND T F Null T T F N F F F F Null N F N OR T F Null T T T T F T F N Null T N N T F Null NOT F T N = <> > >= < <= AND OR NOT <> = <= < >= > OR AND
다중 칼럼 서브 쿼리
여러 개의 칼럼을 반환한다
SQL 서버는 지원하지 않는다
WHERE (ID, HEIGHT) IN (SELECT ID, MIN(HEIGHT) FROM PLAYER GROUP BY ID)연관 서브 쿼리
서브 쿼리 내에 메인 쿼리 칼럼이 사용된다
EXISTS는 항상 연관 서브 쿼리
WHERE EXISTS (SELECT 1 FROM SCHEDULE X WHERE X.ID = A.ID AND X.DATE BETWEEN '20220101' AND '20220401') 위 쿼리에서 A는 메인 쿼리의 FROM에 쓰이는 테이블이다
20220101~20220401에 경기가 있는 경기장을 조회하는 쿼리로
EXISTS는 만족하는 1건만 찾으면 추가 검색을 진행하지 않는다
연관 서브 쿼리는 WHERE 절에서 활용한다(인라인 뷰에서는 활용x)
그 밖의 위치에서 사용하는 서브 쿼리
SELECT절
스칼라 서브 쿼리 사용
- 스칼라 서브 쿼리:
한 행, 한 칼럼만을 반환하는 서브 쿼리로 두 건 이상 반환시 오류가 생긴다.
조인으로 변경 가능한다
칼럼을 쓸 수 있는 대부분의 곳에서 사용 가능하다
SELECT ROUND((SELECT AVG(X.HEIGHT) FROM PLAYER X WHERE X.ID = A.ID), 3) AS 평균키, A.NAME FROM PLAYER A;SELECT절의 스칼라 서브 쿼리 결과 건이 0건이더라도 메인 집합 건수와는 무관하다
이미 WHERE절로 필터링은 끝났고 출력할 칼럼만 스칼라 서브 쿼리로 보여주는 것
SELECT TA.A1, (SELECT SUM(TB.B3) FROM TB WHERE TB.B2 = TA.A1) FROM TA WHERE TA.A1 IN(3, 4);WHERE절을 만족하는 A1은 3과 4다.
그런데 서브 쿼리의 WHERE절의 B2에는 4가 없다.
서브 쿼리의 WHERE절 결과가 (A1 - B2 - B3순)
(3, 3, 5000), (3, 3, 9000), (4, NULL, NULL) 세 개라서
결국 서브 쿼리는 A1이 3일 때 14000, A1이 4일 때 NULL을 반환한다.
스칼라 서브 쿼리를 두 번 이상 쓰면 테이블 조회를 여러번 하게 되므로 인라인 뷰로 변경하는 것이 좋다.
메인 쿼리를 기준으로 인라인 뷰를 아우터 조인해야 스칼라 서브 쿼리를 쓸 때와 동일한 결과를 낸다
다만, 메인 쿼리 테이블과 서브 쿼리 테이블이 필수 관계라 모든 행이 조인시 한 건 이상 성공한다면 이너 조인도 같은 결과를 낸다
SELECT A.DEPTNO, A.DNAME, (SELECT MAX(X.SAL) FROM EMP X WHERE X.DEPTNO = A.DEPTNO), (SELECT MAX(X.COMM) FROM EMP X WHERE X.DEPTNO = A.DEPTNO) FROM DEPT A;이를 인라인 뷰로 바꾸면!
SELECT A.DEPTNO, A.DNAME, B.SAL, B.COMM FROM DEPT A, (SELECT DEPTNO, MAX(SAL) AS SAL, MAX(COMM) AS COMM FROM EMP GROUP BY DEPTNO) B WHERE B.DEPTNO(+) = A.DEPTNO;
- 스칼라 서브 쿼리:
- FROM절 인라인 뷰 / 다이나믹 뷰 뷰와 달리 쿼리 내에서 즉시 처리한다 서브 쿼리의 결과를 테이블처럼 사용할 수 있다 ORDER BY 사용 가능 - HAVING절 ```sql GROUP BY A.TEAM_ID, B.TEAM_NAME HAVING AVG(A.HEIGHT) < (SELECT AVG(X.HEIGHT) FROM PLAYER X WHERE X.TEAM_ID IN (SELECT TEAM_ID FROM TEAM WHERE TEAM_NAME = '삼성')); ```뷰(VIEW)
테이블과 달리 뷰는 실제 데이터를 갖고 있지 않다
(실제 데이터를 저장하고 있는 뷰를 생성할 수 있는 DBMS도 있다)뷰 정의만 가진다
쿼리에서 뷰가 사용되면 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성해 수행한다.
- 장점
- 독립성: 테이블 구조 변경 != 뷰를 사용하는 응용 프로그램 변경
- 편리성: 복잡한 질의가 뷰를 활용하면 단순하게 작성할 수 있다. 재사용성.
- 보안성: 뷰 생성시 보안 컬럼을 빼고 생성해서 정보를 감출 수 있다.
CREATE VIEW V_TEAM AS SELECT A.NAME, B.ID;FROM에서 다른 뷰를 참조할 수도 있다.
CREATE VIEW V_TEST AS SELECT * FROM V_TEAM;뷰 삭제는 DROP으로!
DROP VIEW V_TEST;- 장점
집합 연산자
조인을 쓰지 않고 두 개 이상의 테이블에서 연관 데이터 조회하는 방법
조인은 테이블의 행과 행을 연결하지만,
집합 연산자는 결과 집합 간의 연산을 통해 결합한다
서로 다른 테이블 혹은 동일 테이블 내에서 서로 다른 쿼리 수행 결과를 합치는 것
필요조건(충족 안 되면 오류)
- SELECT절의 칼럼 수 동일
- SELECT절의 동일 위치에 존재하는 칼럼의 데이터 타입 동일
SELECT ... FROM ... [WHERE / GROUP BY / HAVING] 집합연산자 SELECT ... FROM ... [WHERE / GROUP BY / HAVING] [ORDER BY];order by는 한 번만!
필요조건만 충족한다면 어떤 형태의 셀렉트 문이든 무관하다
집합 연산자 종류
UNION: 합집합. 중복된 행은 하나로 = SELECT DISTINCT
UNION ALL: 합집합. 중복된 행 그대로
UNION ALL 사용했는데 두 집합의 칼럼 ALIAS가 다르면 첫번째 것 기준으로 표시
INTERSECT: 교집합. 중복된 행 하나로 = DISTINCT + EXISTS 서브 쿼리
EXCEPT: 차집합. 중복된 행 하나로. 오라클은 MINUS = DISTINCT + NOT EXISTS / NOT IN 서브 쿼리
그룹 함수
데이터 분석을 위한 ANSI/ISO SQL 표준의 세가지 함수
Aggregate Function
그룹 함수의 일부라고 분류할 수도 있음
COUNT, SUM, AVG, MAX, MIN 등 집계 함수
Group Function
집계 함수를 제외하면 ROLLUP, CUBE, GROUPING SETS
Window function
분석 함수나 순위 함수라고 불리기도 함
데이터 웨어하우스에서 발전했다
ROLLUP
롤업에 지정된 그룹핑 칼럼 리스트는 subtotal 생성
그룹핑 칼럼 수가 N이면 N+1레벨의 subtotal 생성
롤업의 인수는 계층 구조 => 인수 순서가 바뀌면 수행 결과도 바뀐다
GROUP BY ROLLUP((C1, C2)) => (C1, C2), ()
GROUP BY ROLLUP((C2, C1)) => (C2, C1), ()
GROUP BY C1, ROLLUP(C2) => (C1, C2), (C1)
GROUP BY C2, ROLLUP(C1) => (C2, C1), (C2)
SELECT B.DNAME, A.JOB, COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM FROM EMP A, DEPT B WHERE B.DEPTNO = A.DEPTNO GROUP BY ROLLUP(B.DNAME, A.JOB);group by 시행시 level 1: D1-3, D2-3, D3-3 9개
rollup으로 생긴 추가 레벨 집계
level 2: DNAME별 모든 JOB subtotal 3개
levle 3: grand total 1개(맨 마지막)
롤업은 계층간 집계는 레벨별 순서(L1 => L2 => L3) 정렬,
계층 내 그룹 바이로 생성되는 표준 집계는 별도로 정렬하지 않는다.(order by 필요)
DNAME JOB EMP_ENT SAL_SUM D1 C 1 950 D1 M 1 2850 D1 S 4 5600 D1 6 9400 D2 A 2 6000 D2 C 2 1900 D2 M 1 2975 D2 5 10875 D3 C 1 1300 D3 M 1 2450 D3 P 1 5000 D3 3 8750 14 29025 GROUPING
ROLLUP,CUBE,GROUPING SETS등의 그룹 함수를 위해 쓴다ROLLUP, CUBE에 의해 소계가 계산된 결과에는 GROUPING(EXPR) = 1이 표시된다. 그 외의 결과에는 0!
CASE / DECODE를 이용해 소계 필드에 원하는 문자열 지정 가능
SELECT B.DNAME D, GROUPING(B.DNAME) AS DNAME_GRP, A.JOB, GROUPING(A.JOB) AS JOB_GRP, COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM FROM... WHERE... GROUP BY ROLLUP(B.DNAME, A.JOB) ORDER BY B.DNAME, A.JOB;D DNAME_GRP JOB JOB_GRP EMP_CNT SAL_SUM A 0 C 0 1 1300 A 0 M 0 1 2450 A 0 P 0 1 5000 A 0 1 3 8750 R 0 A 0 2 6000 R 0 C 0 2 1900 R 0 M 0 1 2975 R 0 1 5 10875 S 0 C 0 1 950 S 0 M 0 1 2850 S 0 S 0 4 5600 S 0 1 6 9400 1 1 14 29025 이걸 CASE를 써서..
SELECT CASE GROUPING(B.DNAME) WHEN 1 THEN 'ALL DEPARTMENTS' ELSE B.DNAME END AS DNAME, DNAME_GRP, CASE GROUPING(A.JOB) WHEN 1 THEN 'ALL JOBS' ELSE A.JOB END AS JOB, COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM FROM EMP A, DEPT B WHERE B.DEPTNO = A.DEPTNO GROUP BY ROLLUP(B.DNAME, A.JOB);D JOB EMP_CNT SAL_SUM A C 1 1300 A M 1 2450 A P 1 5000 A ALL JOBS 3 8750 R A 2 6000 R C 2 1900 R M 1 2975 R ALL JOBS 5 10875 S C 1 950 S M 1 2850 S S 4 5600 S ALL JOBS 6 9400 ALL DEPARTMENTS ALL JOBS 14 29025 GROUPING 함수는 표현식이 행 그룹에 포함되면 0, 아니면 1
SELECT C1, C2, SUM(C3), GROUPING(C2) GP FROM T1 GROUP BY ROLLUP(C1, C2);C1 C2 C3 GP A a 1 0 A b 1 0 A 2 1 B c 1 0 B d 1 0 B 2 1 C e 1 0 C f 1 0 C 2 1 6 1 GP열 1인 행을 보면 C1은 포함됐지만 C2는 포함되지 않았다.
ROLLUP 함수의 일부 사용
GROUP BY B.DNAME, ROLLUP(A.JOB)DNAME은 집계는 안 되도 그룹 바이 순서는 1번
맨 마지막 ALL DEPARTMENTS 행이 사라진다
ROLLUP이 JOB 칼럼에만 사용되어서 DNAME 집계가 필요없기 때문
ROLLUP 함수의 결합 칼럼 사용
GROUP BY ROLLUP(B.DNAME, (A.JOB, A.MGR))JOB과 MGR은 하나의 집합으로 간주하고 JOB & MGR에 대한 롤업 결과 출력
JOB과 MGR 따로 집계를 구하지 않는다.
CUBE
롤업은 가능한 subtotal만 출력하지만
큐브는 결합 가능한 모든 값에 대해 다차원 집계
롤업에 비해 시스템에 부담을 준다
표시된 인수들에 대한 계층별 집계
롤업과 달리 인수간 계층구조가 없는 평등한 관계
인수 순서가 바뀌면 행간 정렬 순서는 바뀌어도 데이터 결과는 같다
결과 정렬하려면 ORDER BY!
GROUP BY CUBE(DNAME, JOB)DNAME JOB EMP_CNT SAL_SUM A C 1 1300 A M 1 2450 A P 1 5000 A ALL 3 8750 R A 2 6000 R C 2 1900 R M 1 2975 R ALL 5 10875 S C 1 950 S M 1 2850 S S 4 5600 S ALL 6 9400 ALL A 2 6000 ALL C 4 4150 ALL M 3 8275 ALL P 1 5000 ALL S 4 5600 ALL ALL 14 29025 GROUPING COLUMNS가 N개면 subtotal은 2n레벨
UNION ALL과 결과 데이터가 같다(행들의 정렬은 다를 수 있지만)
UNION ALL을 쓰면 테이블을 반복 액세스해야 하는데
큐브는 한 번만 액세스하면 되므로
수행속도 및 자원 사용률이 개선되고 가독성이 상승한다.
(이 개선 효과는 롤업도 동일하다)
GROUPING SETS
GROUP BY를 여러번 반복하지 않아도 된다
인수들에 대한 개별 집계가 가능하다
표시된 인수는 롤업과 달리 평등하다
인수 순서가 바뀌어도 결과는 같다
정렬은 ORDER BY!
SELECT CASE GROUPING(B.DNAME) WHEN 1 THEN 'ALL DEPARTMENTS' ELSE B.DNAME END AS DNAME, DNAME_GRP, CASE GROUPING(A.JOB) WHEN 1 THEN 'ALL JOBS' ELSE A.JOB END AS JOB, COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM FROM EMP A, DEPT B WHERE B.DEPTNO = A.DEPTNO GROUP BY GROUPING SETS(B.DNAME, A.JOB) ORDER BY B.DNAME, A.JOB;DNAME JOB EMP_CNT SAL_SUM A ALL 3 8750 R ALL 5 10875 S ALL 6 9400 ALL A 2 6000 ALL C 4 4150 ALL M 3 8275 ALL P 1 5000 ALL S 4 5600
GROUPING SETS는 UNION ALL과 같은 결과(정렬 순서는 다를 수 있음)
괄호로 묶은 집합별로 집계를 구할 수 있다
3개의 인수를 활용한 GROUPING SETS
GROUP BY GROUPING SETS ( (B.DNAME, A.JOB, A.MGR), (B.DNAME, A.JOB), (A.JOB, A.MGR) )GROUP BY GROUPING SETS (B.REG_ID, TO_CHAR(A.DATE, 'YYYY, MM'))
계층구조 없이 지역에 대한 합계와 월별 합계를 각자 생성한다
'SQL' 카테고리의 다른 글
SQL활용-3(계층형 쿼리, PIVOT, 정규표현식) (0) 2022.05.20 SQL활용 - 2(윈도우 함수, TOP N 쿼리) (0) 2022.05.12 SQLD 합격! (0) 2022.04.14 SQL 기본 - 2(Group by, Order by, Join) (0) 2022.04.06 SQL 기본 - 1(관계형 데이터베이스, SELECT, 함수, WHERE) (0) 2022.03.24 다음글이 없습니다.이전글이 없습니다.댓글