TIL 2/16 sqld 2과목 제 2장 SQL 활용

서브 쿼리

조인은 집합간의 곱 관계라, M:N 관계의 테이블을 조인하면 MN 레벨의 집합이 결과로 생성된다.
서브 쿼리는 서브 쿼리 레벨과는 상관없이 항상 메인 쿼리 레벨로 결과 집합이 생성된다.
결과 집합을 잘보고 서브 쿼리를 써야 할 때 조인을 쓰지 않도록 하자 (DISTINCT로 결과를 원하는대로 낼 수 있지만 비효율적

종류

• 동작 방식 :비연관(un-correlated) 서브쿼리, 연관(correlated) 서브 쿼리
• 반환데이터 형태 : Single Row 서브 쿼리, Multi Row 서브 쿼리, Multi Column 서브 쿼리
• 위치에 따라 : 조건절, Select 절 (스칼라 서브 쿼리), From절  (인라인 뷰), Having 절

서브쿼리의 논리적 실행 순서는 항생 메인 쿼리에서 데이터 읽음 -> 서브 쿼리에서 조건 체크
그러나 실제 실행 순서는 다를 수 있다.

스칼라 서브 쿼리는 메인 쿼리의 결과 건수만큼 반복수행된다.

중첩 서브 쿼리 및 스칼라 서브 쿼리에는 Order by를 사용하지 못한다. (인라인뷰에서는 사용 가능)

단일행 비교 연산자 : =, >, <, <>

다중행 비교 연산자 :IN, All, ANY, EXISTS
EXISTS 에 있는 서브쿼리는 무조건 연관서브쿼리이다.

뷰

뷰의 장점 : 독립성, 편리성, 보안성
성능 문제를 방지하기 위해 SQL 수행 원리를 알고 쓰자

집합 연산자

1. 유사한 형태의 결과를 하나의 결과로 합치고자 할 때
2. 동일 테이블에서 서로 다른 질의를 합치고자 할 때
3. 튜닝 관점에서 실행 계획을 분리하고자 할 때

따라서 SELECT 절의 칼럼 수가 동일하고, 같은 위치의 데이터 타입이 동일해야 한다.

UNION : 합집합.  같은 테이블이라면 조건문으로 대체 가능 (DISTINCT를 사용해야한다. 그런데 이게 더 성능이 좋을 수도 있다.)
UNION ALL : 단순하게 결과를 concat한다.
INTERSECT : 교집합.   EXISTS나 IN 서브쿼리로 대체 가능.  같은 테이블이라면 조건문으로 대체 가능
EXCEPT/MINUS : 차집합. NOT EXISTS나 NOT IN 서브쿼리로 대체 가능, 같은 테이블이라면 조건문으로 대체 가능

UNION ALL 을 제외한 다른 집합 연산자는 해당 집합 연산을 수행한 후 결과에서 중복된 건을 배제하는 작업을 수행한다.

집합 연산자의 Order by는 가장 마지막에 한 번만 기술한다.

서로 다른 테이블을 합칠 때,  HEADING은 첫번째 SQL의 ALIAS가 적용된다.

그룹 함수

• Aggreagate Function
  • COUNT, SUM, AVG, MAX, MIN
• Group Function
  • ROLL UP, CUBE, GROUPING SETS
• Window Functon
  • 분석 함수
  • 순위 함수

Grouping : 해당 열이 소계인지 아닌지를 1 또는 0으로 나타낼 수 있게 해준다.

• ROLL UP : 중간 집계를 한 방면에 대해 내준다.  GROUP BY 에서 ROLL UP을 partial로 걸 수도 있고,  결합 칼럼을 집계 대상으로 할 수도 있다.   N+1 개의 서브토탈 레벨 생성 (N은 칼럼내 값의 종류의 수)
• CUBE : 다 차원의 집계를 모두 내준다. 연산을 많이 하니 꼭 필요한지 확인하고 쓴다.
  • UNION ALL로 대체 가능하다. (mariadb에서는 큐브를 지원하지 않아 이렇게 할 수 밖에 없다.) 테이블에 반복 접근해 비효율적이다.
  • 2^N 개의 서브토탈 레벨 생성 (N은 Grouping 칼럼의 수)
• GROUPING SETS: Group By와 UNION ALL의 사용을 대체해준다.
  • GROUPING SETS 내에서는 인수의 순서를 바꿔도 결과는 같다.

윈도우 함수

SELECT 윈도우함수(인수) OVER ([PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절])

• 그룹 내 순위 함수 : RANK, DENSE_RANK(동일 순위 skip 안함), ROW_NUMBER(동일 순위 고유 번호)
• 그룹 내 집계 함수  : SUM, MAX, MIN, AVG, COUNT
  • ORDER BY를 쓰면 '누적 합계' 를 낼 수 있다.
• 그룹 내 행 순서 함수 : FIRST_VALUE, LAST_VALUE, LAG(과거), LEAD(미래)
• 그룹 내 비율 함수 : CUME_DIST(나를 포함한 상위 몇 프로), PERCENT_RANK(상위 몇 프로), NTILE, RATIO_TO_REPORT(전체 중의 비율)
• 선형 분석을 포함한 통계 분석 함수 : CORR, ....

윈도윙 절에는 ROWS 나 RANGE 를 사용한다.  범위는 UNBOUNDED PRCECEDING/FOLLOWING, CURRENT ROW, 표현 PRECEDING/FOLLOWING 중 하나를 쓴다.

Top N 쿼리

Oracle은 ROWNUMS를 이용해,  SQL Server는 TOP()을 이용해 TOP N 을 하지만, Order by와 결합될 때 의도된대로 나오지 않는다. (데이터가 추출된 이후 Order)

ROW LIMITING 을 이용하면 Order by가 의도대로 작동한다.

SELECT a, b, [OFFSET 5 ROW] [FETCH FIRST|NEXT 3 [PERCENT] ROW ONLY|WITH TIES]

with ties는 마지막 행에 동순위 포함해 반환
first 와 next는 대부분 같은 의미

계층형 질의 , 셀프 조인

• 순방향 전개 : 부모에서 자식으로 내려감
• 역방향 전개 : 부모로 올라감

계층형 질의는 계속 써내려나가야 하는 셀프 조인 구문을 편하게 실행할 수 있도록 해준다.

Oracle 계층형 질의

SELECT...
START WITH 조건
CONNECT BY [NOCYCLE] MGR = PRIOR EMPNO;

PRIOR가 현재 노드고, PRIOR가 없는게 다음이 self join할 노드다.

가상 칼럼

• LEVEL: 루트면 1, 이후 레벨 올라갈 때까지 1씩 증가
• CONNECT_BY_ISLEAF : 리프인지 아닌지
• CONNECT_BY_ISCYCLE : 사이클이 있는지 없는지

함수

• SYS_CONNECT_BY_PATH : 루트 데이터 ~ 전개할 데이터까지의 경로 표시
• CONNECT_BY_ROOT : 루트 데이터 표시 (모두 같게 나옴)

SQL 서버 계층형 질의

with 내에 Union all을 활용한 재귀적 선언으로 해결한다.

WITH EMP_ANCHOR AS(
SELECT , ... 0 AS LEVEL
	FROM EMP
	WHERE PARENT IS NULL
UNION ALL
SELECT R. ..., A.LEVEL + 1
	FROM EMP_ANCHOR A, EMP R
    WHERE R.Parent = A.id)
SELECT LEVEL, ...
	FROM EMP_ANCHOR

이 때, 조직도와 같은 모습으로 출력하려면, CONVERT 등을 활용해 path를 만들고, 이를 기준으로 정렬한다.

CONVERT(VARCHAR(1000), A.SORT + ',', + R.EMP) AS SORT

PIVOT, UNPIVOT

PIVOT은 행을 열로 전환,  UNPIVOT은 열을 행으로 전환

SELECT *
 FROM (인라인 뷰)
 PIVOT( SUM (SAL) FOR DEPTNO IN(10, 20,30))

Pivot 된 후 누적 대상이 되는 건, 인라인 뷰에 있는 칼럼들 중 FOR 문에 속하지 않은 칼럼들이다.
FOR문에 다중 열을 기술할 수도 있다. 그러면 IN절에도 다중 열을 사용해야 한다.

Unpivot의 FOR 문에는 pivot 이전의 칼럼명을,  IN 문에는 pivot이후의 칼럼명에 as로 unpivot 이후의 별칭을 일일히 부여한다.

Pivot 은 집계함수와 CASE 표현식으로 대체할 수 있다.
UNPIVIOT 은 카더시안 곱과 CASE 표현식으로 대체할 수 있다.

정규 표현식

일반적인 정규표현식이랑 거의 비슷하다.

역참조 ( \n) 일치한 서브 표현식을 다시 참조할 수 있다.

수량사는 패턴을 최대로 일치시키는 탐욕적 방식
수량사 뒤에 ? 가 붙는 PERL 수량사는 패턴을 최소로 일치시키는 비탐욕적 방식으로 동작한다.

함수

REGEXP_LIKE, REGEXP_REPLACE, REGEXP_INSTR, REGEXP_COUNT

---

알게 된 점

내가 실무에서 자주 썼던 inline 서브 쿼리는 서브 쿼리에서 특수한 형태였다.

api용으로 뷰를 만들어서 편하게 쿼리하는 게 좋을 것 같다.

페이지네이션 이전의 총 합계를 반환하기 위해, 같은 쿼리를 페이지네이션용으로 한 번, 총 합계용으로 한번씩 돌리는 코드가 있었는데, 윈도우 함수를 이용해서 더 줄일 수 있을 것 같다.

집합연산자는 JOIN과 아예 다른 맥락이다. 

Union은 hash를 활용한다!

느낀 점

취업 이전에 따려다가, 너무 쉬운 시험이라 의미가 없을 거라 생각하고 안 땄던 sqld 시험을 준비중이다. sqlp를 따기 위한 자격조건 때문에 시작했는데, 역시 시험 공부는 도움이 되는 것 같다. 몰랐던 내용도 있지만 알던 내용도 다시 보면서 어? 이거 기존 코드에 적용해볼까? 하는게 꽤 된다.

Oracle이나 SQL Server의 문법을 주로 다루는데, 내가 실무에서 써 온 건 mariadb라 약간의 괴리가 있다. mariadb에서 쓰는 LIMIT 같은 키워드가 SQL 스탠다드가 아니라는데 놀랐다. 시험 공부를 하면서 다른 비표준 sql을 사용하는 dbms는 어떤 문법을 쓰는지도 병행해 공부하고 있다.

ROLL UP, CUBE, PIVOT같은 중간 집계 함수는 보고서 작성 앱이 아닌 이상 별로 쓸 일은 없을 것 같지만, 그래서 이렇게 공부를 해놓는게 좋은 것 같다. 필요할 때 구현 고민이 줄어드니까!

계층형 질의는 Oracle에서 잘 지원해주는데, 아쉽게도 비표준 sql DBMS에는 그런게 없다. 대신 RECURSIVE가 있는데, SQL SERVER에서 CTE를 쓰는 방법과 비슷해서 배워두면 활용하기 쉬울 것 같다.

UNPIVOT 절을 활용하는 데는 월별 칼럼이 있는 테이블에 저장된 경우 정도가 있을 건데, 보통 DBMS에는 이런식으로 데이터를 저장하지 않아 SQL보다는 다른 도구(python, R 등)를 활용해 전처리를 한다고 한다.

궁금한 점과 해결

REGEXP_SUBSTR('abababab', '(.*)\1+')

위 표현식이 ab를 캡쳐할지, abab를 캡쳐할지가 찾아가는 과정이 궁금했다.
정답은 abab를 캡쳐한다.

이유: (.*)이 탐욕적 방법으로 먼저 'abababab'를 캡쳐하는데, 이 때 역참조 \1 에 대한 수량자 +가 하나나 그 이상을 얻길 원하는데, 0개가 나오므로 캡쳐는 'abababa' 로 줄여 다시 실행된다. 이렇게 'abab' 까지 줄이면 비로소 수량자가 1개를 확보할 수 있게 되어 종료된다.

수량자 + 도 탐욕적 방식을 쓰는데, 'ab'를 캡쳐했을 때 그 수가 3으로 더 크다 .   (.*)와 + 처럼 둘다 탐욕적인 경우에는, 그 우선순위가 먼저 등장한 친구에게 돌아간다고 한다.