윈도우 함수() OVER PARTITION BY 컬럼 ORDER BY 컬럼 ASC or DESC (Rows BETWEEN A AND B)
or
윈도우 함수() OVER (PARTITION BY 컬럼) ORDER BY 컬럼 ASC or DESC (RANGE BETWEEN A AND B)
행 분할
윈도우 함수: 순위, 집계, 행순서, 비율(함수)
OVER: 필수! OVER 내부에 파티션 & ORDER BY 들어감.
PARTITION BY(행 수 안 바뀜) ≒ GROUP BY(행 수 줄어듬) : 전체 집합을 어떤 컬럼에 따라 나눌지
ORDER BY 컬럼: 어떤 컬럼을 기준으로 순위를 정할지...
Rows BETWEEN A AND B: 대상 행 지정
DEFAULT 세팅: RANGE BETWEEN UPBOUNDED PRECEDING AND CURRENT ROW
현재 컬럼의 값 기준, 연산에 참여 할 행 선택
- UPBOUNDED PRECEDING : 첫 행
- CURRENT ROW : 현재 row 값
- UPBOUNDED FOLLOWING : 끝 행
윈도우 함수 : 행과 행간의 관계를 정의하거나 행과 행간을 비교, 연산하는 함수 - "GROUP BY" 병행 불가
SELECT 결과에 윈도우 함수써도 결과 행 수는 변하지 않음.
[순위 관련 함수]
- RANK : 동일한 값에 대해서는 동일한 순위를 부여 (1,2,2,4)
(동점자 처리 후 그만큼 점프) - DENSE_RANK : 동일한 순위를 하나의 등수로 간주(1,2,2,3)
(동점자 처루 후 그 다음 순위) - ROW_NUMBER : 동일한 값이라도 고유한 순위 부여(1,2,3,4)
(동점자 처리 안함)
[집계 관련 함수]
- SUM : 파티션별 윈도우의 합 구할 수 있다.
ex) 같은 매니저를 두고 있는 사원들의 월급 합 - MAX, MIN : 파티션별 윈도우의 최대,최소 값을 구할 수 있다.
ex) 같은 매니저를 두고 있는 사원들 중 최대 값 - AVG : 원하는 조건에 맞는 데이터에 대한 통계 값
ex) 같은 매니저 내에서 앞의 사번과 뒤의 사번의 평균
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
(현재 행을 기준으로 파티션 내에서 앞의 1건, 현재행, 뒤의 1건을 범위로 지정) - COUNT : 조건에 맞는 데이터에 대한 통계 값
ex)본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받는 인원수
[행 순서 관련 함수] - SQL Server 지원X
- FIRST_VALUE : 파티션별 윈도우의 처음 값
- LAST_VALUE : 파티션별 윈도우의 마지막 값
- LAG : 파티션별 윈도우에서 이전 몇 번째 행의 값
- LEAD : 파티션별 윈도우에서 이후 몇 번째 행의 값
[비율 관련 함수]
- RATIO_TO_REPORT : 파티션 내 전체 SUM에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다. >0, <=1
- PERCENT_RANK : 파티션별 윈도우에서 처음 값을 0, 마지막 값을 1로 하여 행의 순서별 백분율을 구한다. 0>=,<=1
- CUME_DIST : 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구한다. >0, <=1
- NTILE : 파티션별 전체 건수를 인수 값으로 N등분한 결과를 구할 수 있다.
[DCL] : 유저 생성하고 권한을 제어할 수 있는 명령어
Oracle과 SQL Server의 사용자 아키텍처 차이
Oracle : 유저를 통해 DB에 접속을 하는 형태, ID와 PW 방식으로 인스턴스에 접속을 하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여받게 됨
SQL Server : 인스턴스에 접속하기 위해 로그인이라는 것을 생성하게 되며, 인스턴스 내에 존재하는 다수의 DB에 연결하여 작업하기 위해 유저를 생성한 후 로그인과 유저를 매핑해 주어야 한다. Windows 인증 방식과 혼합 모드 방식이 존재함
시스템 권한 : 사용자가 SQL 문을 실행하기 위해 필요한 적절한 권한
- GRANT : 권한 부여
- REVOKE : 권한 취소
GRANT CREATE USER TO SCOTT;
CONN SCOTT/TIGER(ID/PW)
CREATE USER PJS IDENTIFIED BY KOREA7;
GRANT CREATE SESSION TO PJS;
GRANT CREATE TABLE TO PJS;
REVOKE CREATE TABLE FROM PJS;
GRANT SELECT ON SCHEMA:: A_USER TO 유저명;
→ 유저에게 스키마 A(테이블 A)에 대한 SELECT 권한 부여
REVOKE SELECT ON SCHEMA::A_USER FROM 유저명; = DENY SELECT ON SCHEMA::A_USER TO 유저명;
모든 유저는 각각 자신이 생성한 테이블 외에 다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받아야 한다.
DENY:유저에게 개체에 대한 권한 차단
ROLE : 유저에게 알맞은 권한들을 한 번에 부여하기 위해 사용하는 것
CREATE ROLE LOGIN_TABLE;
GRANT CREATE TABLE TO LOGIN_TABLE;
DROP USER PJS CASCADE;
CASCADE : 하위 오브젝트까지 삭제
절차형 SQL : SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성할 수 있다, Procedure, User Defined Function, Trigger 등이 있음
저장 모듈 : PL/SQL 문장을 DB 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램, 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램
PL/SQL 특징
- Block 구조로 되어있어 각 기능별로 모듈화 가능
- 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환
- IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.
- DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
- PL/SQL은 Oracle에 내장되어 있으므로 호환성 좋음
- 6. 응용 프로그램의 성능을 향상시킨다.
- Block 단위로 처리 -> 통신량을 줄일 수 있다.
DECLARE : BEGIN~END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입 선언부
BEGIN~END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용 필요한 로직 처리
EXCEPTION : BEGIN~END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할지 정의하는 예외 처리부
T-SQL : 근본적으로 SQL Server를 제어하는 언어
CREATE Procedure schema_NAME.Procedure_name
Trigger : 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때, DB에서 자동으로 동작하도록 작성된 프로그램, 사용자 호출이 아닌 DB 자동 수행 - TCL(COMMIT, ROLLBACK, SAVEPOINT) 불가능!
프로시저와 트리거의 차이점
프로시저 : BEGIN~END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어 사용가능.
EXECUTE 명령어로 실행 → EXECUTE IMMEDIATE TRUNCATE TABLE DEPT;
트리거 : BEGIN~END 절 내에 사용 불가. 생성 후 자동 실행. TCL 불가능!
'SQLD > 2과목' 카테고리의 다른 글
| 2과목 개념 (윈도우 함수 ~ 프로시저 & 트리거) (0) | 2023.08.26 |
|---|---|
| 2과목 개념 (계층형 질의 ~ GROUPING 함수) (0) | 2023.08.06 |
| 2과목 개념 (JOIN) (0) | 2023.08.05 |
| 2과목 개념 (연산자 종류 ~ 집계 함수) (0) | 2023.08.05 |
| 2과목 개념 (SQL 기본 ~ 트랜잭션) (0) | 2023.08.04 |