힘내보자

WINDOW FUNCTION

SELECT WINDOW_FUNCTION (ARGUMENTS)
OVER ( [PARTITION BY 컬럼] [ORDER BY 절] [WINDOWING 절] )
FROM 테이블 명

WINDOW_FUNCTION : 기존에 사용하던 함수(SUM, AVG 등등)도 있고, 새롭게 WINDOW 함수용으로 추가된 함수도 있음.

ARGUMENTS(인수) : 윈도우함수를 사용할 때 설정이 필요한 경우 옵션으로 사용 (함수에 따라 0 ~ N개의 인수가 지정될 수 있음)

PARTITION BY 절 : 컬럼 선택은 옵션으로 전체 조회된 행에 대해 소그룹을 지정한 컬럼으로 만들 수 있다.

ORDER BY 절 : 해당 컬럼에 대해서 정렬

WINDOWING 절 : 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있음.

    1)ROWS : 물리적인 결과 행의 수

    2)RANGE : 논리적인 값에 의한 범위

둘 중의 하나를 선택해서 사용가능

다만, WINDOWING 절은 SQL SERVER에서 지원X

윈도우 함수(WINDOW FUNCTION) 종류

1.그룹 내 순위(RANK) 관련 함수

2.그룹 내 집계(AGGREGATE) 관련 함수

3.그룹 내 행 순서 관련 함수

4.그룹 내 비율 관련 함수

5.선형 분석을 포함한 통계 분석 관련 함수

https://rise-up.tistory.com/724

WINDOW 절

ROWS |  RANGE BETWEEN
UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING
AND
UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING

ROWS : 물리적인 ROW 단위로 행 집합을 지정 ( 현재행을 기준으로 몇개의 행을 포함하는지 )

RANGE : 논리적인 상대번지로 행 집합을 지정 ( 현재행을 기준으로 어떤 값의 범위를 포함하는지 )

BETWEEN~ AND 절 : 윈도우의 시작과 끝 위치를 지정UNBOUNDED PRECEDING : PARTITION의 첫 번째 로우에서 윈도우가 시작

UNBOUNDED FOLLOWING : PARTITION의 마지막 로우에서 윈도우가 시작

CURRENT ROW : 윈도우의 시작이나 끝 위치가 현재 로우

자료출처

https://dataonair.or.kr/

30번

ORACLE과 SQL SERVER(MS SQL)의 COMMIT

오라클의 경우 DDL은 autocommit true false 보다 윗개념으로 쿼리문 실행되면 바로 커밋이 된다

auto commit false로 설정 영향x

Oracle 의 경우

기본 값이 auto commit off

ddl 이 일어날 경우 묵시적 commit이 됨 (설정 불가)

Sql Server 의 경우

기본 값이 auto commit on

false 가 될 경우 ddl 도 묵시적 commit 이 되지 않음

1. SQL 서버의 경우, AUTO COMMIT 꺼두면 UPDATE, CREATE 모두 취소되고 다시 테이블이 생성되지 않음
2. 오라클은 DDL의 AUTO COMMIT이 기본이기 때문에 CREATE 취소되지 않고, UPDATE도 취소 X

34번

테이블명 & 컬럼명 규칙

1)테이블명 네이밍

테이블 명은 다른 테이블의 이름과 중복x

반드시 문자로 시작해야한다

• A-Z , a-z , 0-9 , _ , $ , #만 사용 가능

2)컬럼명 네이명

테이블 내의 칼럼명은 중복x

각 컬럼들 , 로 구분되고 ; 로 끝난다.

칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다

반드시 문자로 시작해야한다

• A-Z , a-z , 0-9 , _ , $ , #만 사용 가능

37번

오더바이(ORDER BY) 특징

1)SELECT 구문에 사용되지 않은 컬럼도 OERDER BY 구문에서 사용할 수 있다

2) ORDER BY 1, COL1 과 같이 숫자와 컬럼을 혼용하여 사용할 수 있다

3) ORACLE 은 NULL 을 가장 큰 값으로 취급하여 ORDER BY 시 맨 뒤로 정렬되고 SQL SERVER 반대로 가장 앞으로 정렬한다

39번

Hash Join

Hash Join 은 정렬 작업이 없어 정렬이 부담되는 대량배치작업에 유리

Equal Join(동등 조인)에서만 가능

대용량 처리에 유리

각 테이블에 INDEX가 반드시 필요한 것은 아님

데이터 건수가 적은 테이블을 선행 테이블로 두는 것이 유리

정렬 작업이 없어 정렬이 부담되는 대량배치작업에 유리

40번

최상위 관리자가 나올려면 left outer join 이 되어야 함. 최상위 관리자는 manager_id 가 null 이거나 없기 때문.

41번

INTERSECT

결과의 교집합으로 중복된 행을 하나의 행으로 표시한다 

결과의 교집합으로 중복된 행을 모두 포함한다

42번

window function

Sum,max, min 등과 같은 집계 window function을 사용할 때 window 절과 함께 사용하면 집계의 대상이 되는 레코드 범위를 지정할 수 있다

1. Partition by 와 Group by 는 파티션 분할한다는 점에서 유사
2. 집계 Window Function(sum, max, min)을 Window절과 함께 사용하면 레코드 범위(집계대상) 지정가능
3. Window Function 으로 결과 건수 줄지 않음
4. group by, Window Function 병행 불가

44번

ORDER SIBLINGS BY

같은 레벨 내에서 정렬

Start with SUPER_ID IS NULL

• (1, NULL, A) 선택(루트 LEVEL1)

CONNECT BY PRIOR ID = SUPER_ID

• ID가 자식 SUPER_ID 가 부모 / ID=2,3은 SUPER_ID=1 의 자식 / ID=4는 SUPER_ID=2의 자식

ORDER SIBLINGS BY 는 같은 레벨 내에서 정렬

• ID=2,3은 둘다 LEVEL=1 이므로 정렬, CODE DESC 코드 뒷순서 부터 정렬

46번

INITCAP

첫 번째 문자만 대문자로 변환

48번

AND OR 우선순위

AND가 OR보다 우선 연산이므로 AND부터 먼저 해주면

SALARY > 200 조건만 만족해서 3임

NOT - AND - OR 순으로 연산

49번

NTILE

전체 건수를 인수값으로 N등분함

https://goldswan.tistory.com/25

NTILE(4) OVER (ORDER BY COL1) AS VAL 
FROM SQLD_34_X7

50번

LAG & LEAD

이전 N번째 행을 가져옴

(해당 쿼리를 보면 두번째 이전의 salary 값을 가져오므로 답은 2 가 된다.)

DEPARTMENT_ID 그룹, SALARY 오름차순 정렬

https://gent.tistory.com/339

LAG 함수 : 이전 행의 값을 리턴

LEAD 함수 : 다음 행의 값을 리턴

**LAG**(expr [,offset] [,default]) **OVER**([partition_by_clause] order_by_clause)
**LEAD**(expr [,offset] [,default]) **OVER**([partition_by_clause] order_by_clause)

expr : 대상 컬럼명

offset : 값을 가져올 행의 위치 기본값은 1, 생략가능

default : 값이 없을 경우 기본값, 생략가능

partition_by_clause : 그룹 컬럼명, 생략가능

order_by_clause : 정렬 컬럼명, 필수

연관 링크

• 기출 39회 26-50번 https://rise-up.tistory.com/775
• 기출 39회 1-25번 https://rise-up.tistory.com/774
• 기출 38회 1-50번 https://rise-up.tistory.com/764
• 기출 35회 31-50번 https://rise-up.tistory.com/763
• 기출 35회 1-30번 https://rise-up.tistory.com/762
• 기출 34회 30-50번 https://rise-up.tistory.com/761
• 기출 34회 1-29번 https://rise-up.tistory.com/760
• 기출 30회 1-24번 https://rise-up.tistory.com/759
• 기출 30회 25-50번 https://rise-up.tistory.com/758

자료 출처
[SQLD] 34회 기출 문제 ( 50문제 / 정답,해설포함 ) (tistory.com)

yurimac님 pdf 정리 자료

윈도우 함수(WINDOW FUNCTION) 종류

WINDOW FUNCTION 탄생 배경

PL/SQL, SQL/PL, T-SQL, PRO*C 같은 절차형 프로그램을 작성하거나, INLINE VIEW를 이용해 복잡한 SQL 문을 작성해야 하던 것을 부분적이나마 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수가 WINDOW FUNCTION

윈도우 함수를 활용하면 복잡한 프로그램을 하나의 SQL 문장으로 쉽게 해결 가능

벤더별로 지원하는 함수에는 차이가 있다.

1.그룹 내 순위(RANK) 관련 함수

RANK, DENSE_RANK, ROW_NUMBER 함수

ANSI/ISO SQL 표준과 Oracle, SQL Server 등 대부분의 DBMS에서 지원

2.그룹 내 집계(AGGREGATE) 관련 함수

UM, MAX, MIN, AVG, COUNT 함수

ANSI/ISO SQL 표준과 Oracle, SQL Server 등 대부분의 DBMS에서 지원

SQL Server의 경우, 집계 함수는 뒤에서 설명할 OVER 절 내의 ORDER BY 구문을 지원X

3.그룹 내 행 순서 관련 함수

FIRST_VALUE, LAST_VALUE, LAG, LEAD 함수

Oracle ONLY 지원

1. FIRST_VALUE, LAST_VALUE 함수
   • MAX, MIN 함수와 비슷한 결과를 얻을 수 있음
2. LAG, LEAD 함수는 DW에서 유용하게 사용되는 기능

4.그룹 내 비율 관련 함수

CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT 함수

1. CUME_DIST, PERCENT_RANK 함수
   • ANSI/ISO SQL 표준과 Oracle DBMS에서 지원
2. NTILE 함수
   • ANSI/ISO SQL 표준에는 없지만, Oracle, SQL Server에서 지원
3. RATIO_TO_REPORT 함수
   • Oracle ONLY 지원
   • 현업에서 유용한 기능

5.선형 분석을 포함한 통계 분석 관련 함수

Oracle의 통계 관련 함수

CORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, REGR_(LINEAR REGRESSION), REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY

자료출처

https://dataonair.or.kr/db-tech-reference/d-guide/sql/?pageid=3&mod=document&uid=351