SQL 활용 정리

목차 :
9. 집합연산자
10. 그룹함수
11. 윈도우 함수
12. TOP N 쿼리
13. 계층형 질의

9. 집합연산자

• 두개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회하는 방법
• UNION(합집합), UNION ALL(합집합,중복표시), INTERSECT(교집합), EXCEPT(차집합)

10. 그룹함수

• ROLLUP, CUBE, GROUPING SETS, GROUPING 함수가 있다.
• ROLLUP은 subtotal을 생성하기 위해 사용 된다. 계층 구조 이므로 인수 순서가 바뀌면 결과도 바뀐다.
• CUBE는 결합 가능한 모든 값에 대하여 다차원 집계를 생성.
• GROUPING SETS는 원하는 컬럼만 지정해서 소계를 구한다. UNION ALL과 결과가 같다.
• GROUPING 함수는 앞의 세개 함수와 같이 쓰이며 CASE에서 빠진 컬럼에 대해 1을 반환한다(NULL)

11. 윈도우 함수

• SELECT 결과에 대하여 행과 행 간의 관계를 파악하기 위해 쓰임. 행수는 그대로
• 윈도우함수() OVER (partition by 컬럼 order by 컬럼 asc or desc)
• 윈도우함수에는 순위함수 집계함수 행순서함수 비율함수가 있다. OVER는 윈도우 함수에서 꼭 들어가야 하며 OVER 내부에 partition by 절과 order by 절이 온다. partition by 절은 전체 집합을 어떤 기준에 따라 나눌지 결정하고 order by 절은 어떤 항목을 기준으로 순위를 정할지 결정하는 부분이다.
• 윈도우 함수의 종류 :
  1. 순위함수 : ROW_NUMBER(1,2,3,4,5), RANK(1,2,2,4,5), DENSE_RANK(1,2,2,3,4,5)
  2. 집계함수 : COUNT/SUM/MAX/MIN/AVG
  3. 행의 순서 함수 : LAG(이전값), LEAD(다음 값), FIRST_VALUE(가장 처음에 나온 값), LAST_VALUE(가장 나중에 나온 값)
  4. 비율 함수 : RATIO_TO_REPORT : 파티션 내의 주어진 컬럼 값의 합계에 대한 행별 백분율을 소수점으로 구한다, CUM_DIST : 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구한다., NTILE : 파티션별 전체 건수를 주어진 인자로 N등분한 결과를 반환한다., PERCENT_RANK : 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로 제일 늦게 나오는 것을 1로 하여 값이 아닌 행의 순서별 백분율을 구한다.
• 윈도우 함수의 WINDOWING 절 : 연산 대상이 되는 레코드의 범위를 정한다.
• 윈도우 함수() OVER (partition by 컬럼 order by 컬럼 RANGE BETWEEN A AND B OR ROWS BETWEEN A AND B)
• ROWS : 물리적인 행 단위, RANGE : 논리적인 행 집합, CURRENT ROW : 현재 행, UNBOUNDED PRECEDING : 현재행 기준 첫번째 행 까지 범위, UNBOUNDED FOLLOWING : 마지막 행, 숫자 PRECEDING : 숫자 이전행, 숫자 FOLLOWING : 숫자 다음행
• RANGE는 ORDER BY 절에 명시된 컬럼으로 논리적인 행 집합 구성. 집합으로 묶인 그룹이 1개의 행으로 인식된다. 반면 ROWS는 모든 행이 1개의 행으로 인식된다.

12. TOP N 쿼리

• ROWNUM : WHERE절에서 행의 갯수를 제한한다. WHERE ROWNUM < 4 이렇게 씀

13. 계층형 질의

• 계층형 구조 : 상하 수직관계의 트리형태의 구조이고 계층형 쿼리는 계층 구조가 적용된 데이터를 반환하는 질의어이다. 오라클만 계층형 쿼리 지원
• START WITH 구문 : 부모로 사용될 행을 지정한다. 여기서 지정되는 값이 LEVEL1. 서브쿼리 사용 가능
• CONNECT BY : 연결고리를 만든다. PRIOR 연산자로 계층구조 표현 가능. PRIOR 자식 = 부모 는 순방향 반대는 역방향. 서브쿼리 사용 불가
• LEVEL : 계층 구조 쿼리의결과 DEPTH를 표현