SQL 모르는 것 정리 : 집합 연산자 ~ 정규 표현식 (2회차)

이번에도 역시 기출문제에서 나온 개념을 위주로 정리한다.

2회차 내용들이 더 급해서 이것 먼저 정리했다😅

 

+ 5/28  업데이트 중

 

---

 

 

집합 연산자

• 두 집합의 컬럼수 일치 & 데이터 유형 일치
• 두 집합의 컬럼 사이즈가 달라도 연산 가능함
• 첫 집합(위)의 select 컬럼명이 전체 결과의 컬럼명이 됨

합집합 => 두 집합의 총합 => NULL 들어감	UNION	중복되는 애는 하나만 출력
	UNION ALL	중복되는 애도 전체 출력
교집합	INTERSECT
차집합	MINUS	(뒤 테이블 기준) 제외 ex) TAB1 MINUS TAB2;라고 치면 TAB2의 행들을 제외한 TAB1만 출력

=> 집합 연산자 사용 시 개별 쿼리에는 ORDER BY 사용 불가하므로, 여러 개 쿼리문 전부 다 마친 후에 ORDER BY 쓰면 됨

 

 

 

 

그룹함수

sum과 count는 (1회차)편 참고

VARIANCE	분산
STDDEV	표준편차 (분산의 루트값)

 

 

 

 

 

GROUP BY 함수

🏅 GROUPING SETS	( A, B, ()) => A, B, 전체총합	•  A와 B를 각각 group by 한 결과를 유니온(합친다) •  컬럼 나열 순서 안 중요하다 •  마지막에 ()나 NULL을 명시하면 전체 총합을 출력할 수 있다 => 기본 출력은 전체 출력이 안 된다는 말과도 같음
🏅  ROLLUP	(A, B) => A, (A,B), 전체	•  1별, (1,2)별, 전체 그룹 결과 출력 •  나열 순서 중요함 •  기본 출력도 전체 총계가 출력됨 => grouping sets와 정반대같네
CUBE	(A, B) => A, B, (A,B), 전체	•  모든 경우의 조합

 

이게 더 보기 편한가?

GROUPING SETS	ROLLUP
(컬럼명1, 컬럼명2, ())	(컬럼명1, 컬럼명2)
•  1과 2를 각각 group by 한 결과를 유니온(합친다) 해준다 •  컬럼 나열 순서 안 중요하다 •  마지막에 ()나 NULL을 명시하면 전체 총합을 출력할 수 있다 => 기본 출력은 전체 출력이 안 된다는 말과도 같음	•  1별, (1,2)별, 전체 그룹 결과 출력 •  나열 순서 중요함 •  기본 출력도 전체 총계가 출력됨 ==> grouping sets와 정반대같네
각각 그룹바이한 걸 다 합친 것	1별, (1,2)별, 전체 그룹 결과 출력

 

 

 

 

윈도우 함수 :

한 행을 액세스했을 때 얻을 수 없는 값은 서브쿼리나 조인 말고도 윈도우 함수로 간편히 해결 가능한 애

 

•  서로 다른 행의 비교/연산을 위한 함수

•  GROUP BY 사용 없이 그룹 연산 가능

•  파티션바이/그룹바이/로우즈/렌이지 비트윈 엔드 절이 있는데, 전달 순서가 중요하다

=> ORDER BY를 PARTITION BY 전에 사용 불가함

 

•  OVER절을 사용해서 기존 그룹함수랑 함께 사용할 수 있다

 

 

 

윈도우 함수 절

SELECT 윈도우함수(대상) OVER (PARTITION BY 컬럼)
                             (ORDER BY 컬럼 ASC | DESC)
                             (ROWS | RANGE BETWEEN a AND b)

PARTITION BY	RATIO_TO_REPORT(SALARY) OVER(PARTITION BY D_NO)	•  어떤 그룹내에서 궁금한건데? => ex) D_NO 그룹에서 •  출력할 총 데이터 수 변화없이 그룹연산 수행할 GROUP BY 컬럼 •   같은 그룹 내에서 행끼리 계산
ORDER BY
ROWS
RANGE BETWEEN a AND b

* 그룹바이와 차이

 

 

순위 관련 함수

RANK	RANK WITHIN
	RANK( ) OVER( )	•  우리가 일반적으로 매기는 순위 ex)  1 / 2 / 2 / 4 / 5 •  ORDER BY 필수 => 순위 필요 대상컬럼을 ORDER BY에 => 순위 대상컬럼은 여러 개 써도 됨	•  동일 순위 인정⭕
DENSE		•  연속적인 순위 ex) 1 / 2 / 2 / 3 / 4 => 누적순위라는데 연속적인(이라는 말은 row_number 개념에 써진 말이지만)이 더 생각날 것 같음...	•  동일 순위 인정⭕
ROW_NUMBER		•  동일 순위없이 순차적임 ex) 1 / 2 / 3 / 4 / 5	•  동일 순위 비인정
LAG LEAD	이전값 이후값	(컬럼명, 몇번째행 이전값, 가져올값없으면 이 값으로 치환) ex) LAG(SAL, 2, 0) => SAL컬럼 2번째행 이전값, 값 없으면 0으로 치환	값 없으면 =이전/후 행이 없으면
FIRST_VALUE LAST_VALUE	첫번째값 마지막값	사용에 따라 min / max 로 사용 가능하고 역할이 서로 바뀔 수 있음	LAST_VALUE는 정렬에 따라 MIN이 될 수도 있음
NTILE	그룹으로 나눔	NTILE(2) => 2 그룹으로 나눠라 * 홀수의 경우 앞그룹 인원이 더 많아짐 ex) NTILE(2)일 때 1그룹행이 3개, 2그룹 행이 5개일 경우 1그룹: 2,1 2그룹: 3,2

 

이것도 다시 정리하면

RANK OVER	DENSE	ROW_NUMBER
동순위를 반영하여 후순위 계산 1,2,2,4,5	연속적 1,2,2,3,4	동순위 비인정 1,2,3,4,5	후순위 대처
ORDER BY 필수

 

 

 

🥇 비율 관련 함수

RATIO_TO_REPORT	전체 비율 중 차지 비율 (컬럼명) => 뭐의 비율이 궁금한 건데? 컬럼명	•   전체 총합 대비 무언가의 비율을 출력하는 윈도우 함수 •  ORDER BY 못 씀
CUME_DIST	누적 비율 = 행위치 비율 자기값 이하 행수 / 전체 행수 ex) 1 / 4 = 0.25
PERCENT_RANK	0 ~ 1 사이 비율 rank-1 / 전체행수 - 1 🏅 첫 행 0  끝 행 1	•  각 행의 상대적인 위치를 알려줘서 값 자체의 크기를 리턴하는 애가 아님 => 집합 내에서 상대적으로 어디에 위치하는지의 비율을 나타냄 •  0~1사이 값으로 표현 •  ORDER BY 필수 •  ORDER BY로 정렬이 된 후의 행의 위치로 rank를 판단하면 됨 => 그래서 첫 행이 0 끝 행이 1

 

 

 

 

 

TOP N 쿼리

TOP N 쿼리:

• 전체 결과에서 특정 N개 추출
• 페이징처리에 필요한 쿼리

rownum: 가상의 번호(= 특정행 지정 불가, 연산불가), 출력된 데이터 기준으로 행 번호 부여

rank

fetch: offset

top n

ROWNUM		출력된 데이터 기준 : 행 번호 부여 => 하지만 가상 번호이므로 특정 행 지정/연산 불가 연산을 할거면 1을 포함해야 함 ex) ROWNUM > 1이나 = 4 (❌) ex) ROWNUM <= 5 (⭕) => 1~5라서 => 1보다 크거나 같다는 가능하지만  >  연산은 안 됨 •  페이징처리에 필요한 쿼리 ex) 상위 다섯명의 SAL을 뽑으려면 파싱 순서를 생각해보아야 함(where이 order by보다 먼저 읽히니까) => FROM에서 서브쿼리 이용해서 정렬 먼저 하고      ROWNUM <= 5 (인라인뷰 서브쿼리) •  rownum 자체로는 between 1 안 포함된 값을 지정 못하지만, select에서 rownum 후 알리야스 붙이면 알리야스로는 between 1 안 포함된 값을 지정할 수 있다. ex) (❌) rownum between 4 and 6  ( ⭕ ) 별칭 between 4 and 6  ROWNUM 자체로는 BETWEEN 4 AND 6이 안 되고, 서브쿼리에서 ROWNUM을 별칭(Alias) 으로 만든 다음에는 그 별칭을 가지고 BETWEEN 4 AND 6이 됨
RANK
FETCH	ORDER BY  OFFSET N { ROW / ROWS } FETCH { FIRST / NEXT } N { ROW / ROWS } ONLY	•  🥇 OFFSET: 건너뛸 행 수 •  N: 출력할 행의 수 •  FIRST:    offset 비사용시 사용    1~N행까지 => 디폴트 •  NEXT:    offset 사용시 사용 (but 크게 의미는 없음)    => N행 제외한 다음 행부터
TOP N	ex) SELECT TOP 3 ENAME, SAL FROM EMPYOLEE ORDER BY SAL DESC; => 상위 급여자 3명 출력 ex) SELECT TOP 3 WITH TIES ENAME, SAL FROM EMPYOLEE ORDER BY SAL DESC; => 동순위일 경우, 상위 급여자 4명까지 출력할 수도 있음	•  TOP N: 상위 n개 행 추출 •  WITH TIES: 동순위까지 함께 출력 & 앞에 꺼 스킵 안 됨

 

 

 

 

계층형 질의

🏅 읽는/파싱 순서: START WITH -> CONNECT BY -> WHERE

START WITH	가장 먼저 읽을 거, 시작위치 START WITH 사원번호 IN (1006,1001)	1006과 1001을 시작점으로 가져감 => 이때 둘은 level 1 그리고...
CONNECT BY
	PRIOR: 먼저 어디 컬럼부터 읽을거냐	왼쪽/오른쪽에 붙었는지 중요함 => 찾아가는 방향이 완전 다름
	각종 문구
	CONNECT_BY_ROOT/ISLEAF	최상위/하위 계층 값
	SYS_CONNECT_BY_PATH	◎ - ◎ - ◎              =>     이어지는 경로 출력
	ORDER_BY_SIBILINGS BY	정렬, 형제자매 => 같은 계층들 정렬 수행

 

* CONNECT BY가 START WITH를 생략시킬 순 없다

* WHERE는 CONNECT BY보다 늦게 파싱돼서 START WITH를 생략시킬 수 있다

=> where에 의해 start with값이 출력이 안 될 수 있다

 

 

 

 

피벗 언 피벗

PIVOT
UNPIVOT	value의 컬럼명 FOR stack 컬럼명 IN(stack column1, stack column 2) => IN은 쌓을 대상 지정하는 구문 ==> 여기에 써있는 애들만 쌓이는거야	•  stack 처리한다 => 오라클: 언피벗 wide 데이터를 --(stack 처리한다)---> long 데이터로

PIVOT	UNPIVOT
Long -> Wide	Wide -> Long
Value의 컬럼명 FOR wide로 펼칠 컬럼명	Value의 컬럼명 FOR stack컬럼명	집계함수만 가능 맘대로: 펼쳐놓은 걸 함축시키는 느낌 실 컬럼명
sum(ENO) FOR dno IN (10,20,30)	sum_value FOR 부서들 IN (10,20,30)

 

 

정규 표현식

 

.REGEXP_REPLACE	(컬럼명, 찾을 데이터)	•  지움 => 해당 컬럼명에 가서 찾을 데이터 찾아서 지움
.REGEXP_SUBSTR		•  하나만 뽑아냄  => 만족하는 첫번째것만 뽑아냄