꿈꾸는개발자

※ Top-N 쿼리

        1. ROWNUM - 슈도 컬럼(Pseudo Column)으로 존재하지 않는 컬럼을 의미한다. 엑셀의 자동 순번처럼 ROWNUM도 순번이 1씩 증가하기 때문에 ROWNUM 조건식에 <나 <=만 사용 가능하다.

※ 셀프 조인(Self Join)

           1. 셀프 조인이란? - 나 자신과의 조인하므로 혼동을 막기 위해 FROM절에 테이블명마다 별칭(ALIAS)을 표기해야 한다. 

※ 계층 쿼리

         1. 계층 쿼리란? - 테이블에 계층 구조를 이루는 컬럼이 존재할 경우 계층 쿼리를 이용해서 데이터를 출력할 수 있다. ORDER SIBLINGS BY절을 써서 같은 레벨에서 정렬한다.

              • LEVEL - 현재의 DEPTH를 반환하며 루트 노드는 1이 된다.

              • SYS_CONNECT_BY_PATH(컬럼, 구분자) - 루트 노드부터 현재 노드까지의 경로를 출력해주는 함수

              • START WITH - 경로가 시작되는 루트 노드를 생성해주는 절

              • CONNECT BY - 루트로부터 자식 노드를 생성해주느 절으로 조건에 만족하는 데이터가 없을 때까지 노드를 생성한다.

              • CONNECT_BY_ROOT 컬럼 - 루트 노드의 주어진 컬럼값을 반환한다.

              • CONNECT_BY_ITSELF - 가장 하위 노드인 경우 1을 반환하고 그 외에는 0을 반환한다.

              • PRIOR - 바로 앞에 있는 부모 노드의 값을 반환한다. 즉 하위 데이터를 지정

※ 윈도우 함수

        1. 윈도우 함수란? - OVER 키워드와 함께 사용되며 다음과 같이 나뉜다.

             1-1. 순위 함수

                    ① RANK - 순위를 매기면서 같은 순위가 존재하면 존재하는 수만큼 반복하고 다음 순위를 건너뛴다.

                    ② DENSE_RANK - 순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매긴다. 

                    ③ ROW_NUMBER - 순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여한다. 

             1-2. 집계 함수

                    ① SUM - 데이터의 합계를 구하는 함수로 인자값으로는 숫자형만 올 수 있다. SUM하는 컬럼을 OVER절에서 ORDER BY절에 명시해주면 RANGE UNBOUNDED PRECEDING 구문 없어도 누적합이 집계된다.

                    ② MAX - 데이터의 최댓값을 구하는 함수

                    ③ MIN - 데이터의 최솟값을 구하는 함수 

                    ④ AVG - 데이터의 평균값을 구하는 함수

                    ⑤ COUNT - 데이터의 건수를 구하는 함수

WINDOW절 default RANGE UNBOUNDED PRECEDING

             1-3. 행 순서 함수(SQL Server에서 사용 불가능)

                    ① FIRST_VALUE - 파티션 별 가장 선두에 위치한 데이터를 구하는 함수다.

                    ② LAST_VALUE - 파티션 별 가장 끝에 위치한 데이터를 구하는 함수다.

                    ③ LAG - 파티션 별로 특정 수만큼 앞선 데이터를 구하는 함수로 LAG함수의 두 번째 인자값을 생략하면 default값인 1을 인자값으로 사용한다.

                    ④ LEAD - 파티션 별 특정 수만큼 뒤에 있는 데이터를 구하는 함수로 LEAD함수의 두 번째 인자값을 생략하면 default값인 1을 인자값으로 사용한다.

             1-4. 비율 함수(SQL Server에서 사용 불가능)

                    ① RATIO_TO_REPORT - 파티션 별 합계에서 차지하는 비율을 구하는 함수다.

                    ② PERCENT_RANK - 해당 파티션의 맨 위 행을 0, 맨 아래 행을 1로 놓고 현재 행이 위치하는 백분위 순위값을 구하는 함수다.

                    ③ CUME_DIST - 해당 파티션에서의 누적 백분율을 구하는 함수로 결괏값은 0보다 크고 1보다 작거나 같은 값을 가진다. 

                    ④ NTILE - 주어진 수만큼 행들을 n등분한 후 현재 행에 해당하는 순번을 구하는 함수다. 순번을 구하고도 순번을 할당할 행이 남아 있을경우 맨 앞의 그룹부터 하나씩 더 채워진다. 

※ 그룹 함수

1. 그룹 함수란? - 그룹화된 데이터를 사용하여 집계나 총계를 계산하는 함수다.

2. 그룹 함수 종류

    ① 집계 함수 - COUNT, SUM, AVG, MAX, MIN

    ② 소계(총계) 함수 - ROLLUP, CUBE, GROUPING SETS

         ㉮ ROLLUP - 소그룹 간의 소계 및 총계를 계산하는 함수로 인수의 순서에 따라 결과가 달라진다. 

              • ROLLUP(A) - A로 그룹핑 → 총합계

              • ROLLUP(A, B) - A, B로 그룹핑 → A로 그룹핑 → 총합계

              • ROLLUP(A, B, C) - A, B, C로 그룹핑 → A, B로 그룹핑 → A로 그룹핑 → 총합계

         ㉯ CUBE - 소그룹 간의 소계 및 총계를 다차원적으로 계산할 수 있는 함수로 조합할 수 있는 모든 그룹에 대한 소계를 계산한다. 인수의 순서에 상관없이 같은 결과를 출력한다.

              • CUBE(A) - A로 그룹핑 → 총합계

              • CUBE(A, B) - A, B로 그룹핑 → A로 그룹핑 → B로 그룹핑 → 총합계

              • CUBE(A, B, C) - A, B, C로 그룹핑 → A, B로 그룹핑 → A, C로 그룹핑 → B, C로 그룹핑 → A로 그룹핑 → B로 그룹핑 → C로 그룹핑 → 총합계

         ㉰ GROUPING SETS - 특정 항목에 대한 소계를 계산하는 함수다. 인자값으로 ROLLUP이나 CUBE를 사용할 수 있고 인수의 순서에 상관없이 같은 결과를 출력한다.

              • GROUPING SETS(A, B) - A로 그룹핑 → B로 그룹핑 

              • GROUPING SETS(A, B, ( )) - A로 그룹핑 → B로 그룹핑 → 총합계

              • GROUPING SETS(A, ROLLUP(B)) - A로 그룹핑 → B로 그룹핑 → 총합계

              • GROUPING SETS(A, ROLLUP(B, C)) - A로 그룹핑 → B, C로 그룹핑 → B로 크룹핑 → 총합계

                  • GROUPING SETS(A, B, ROLLUP(C)) - A로 그룹핑 → B로 그룹핑 → C로 크룹핑 → 총합계

         ㉱ GROUPING - ROLLUP, CUBE, GROUPING SETS와 함께 쓰이며 소계를 나타내는 Row의 값에 1을 부여하고 나머지 Row의 값에 0을 부여해 구분한다. 

※ 집합 연산자

1. 집합 연산자란? - 각 SELECT문의 결과을 가지고 집한 연산을 수행하며 두 SELECT문의 컬럼 수와 데이터 타입이 동일해야 하고 

2. 집합 연산자 종류

① UNION ALL - 각 쿼리의 결과를 합하면서 중복된 행도 그대로 출력하지만 정렬은 하지 않는다.

② UNION - 각 쿼리의 결과를 합하면서 중복을 제거하고 정렬도 한다. 

③ INTERSECT - 각 쿼리의 결과에서 공통된 부분만 중복을 제거하고 출력한다. 

④ MINUS/EXCEPT - 첫 번째 쿼리 결과에서 두 번째 쿼리 결과를 제외한 부분만 출력한다.