Database/SQL

[SQLD] SQL 활용 II

gangintheremark 2023. 9. 6. 13:55
728x90

윈도우 함수(Window Function)

  • Partition과 Group By 구문은 의미적으로 유사
  • Partition 구문이 없으면 전체 집합을 하나의 Partition으로 정의한 것과 동일
  • 윈도우 함수 적용 범위는 Partition을 넘을 수 없다

 

Rank 함수

Rank 함수는 ORDER BY를 포함한 쿼리문에서 특정 칼럼에 대한 순위를 구하는 함수이며 동일한 값에 대해서는 동일한 순위를 부여하며 중간 순위를 비워둔 데이터를 추출한다.

💡 1 1 ➜ 3 ➜ 4
-- 고객별 매출액과 매출순위
SELECT 고객번호, 매출액, RANK() OVER(ORDER BY 매출액 DESC) 순위
FROM ~
GROUP BY 고객번호

-- 활동점수가 높은 고객을 게임상품ID별로 선별
SELECT 게임상품ID, 순위 
FROM (SELECT RANK() OVER(PARTITION BY 게임상품ID ORDER BY 활동점수 DESC), 게임상품ID 
        FROM 고객활동)

DENSE_RANK

동일 순위를 부여하되 중간 순위를 비우지 않는다.

ROW_NUMBER

동일 값에 대해서도 유일한 순위를 부여한다.

 

RANGE PRECEDING FOLLOWING

SELECT 상품분류코드, AVG(상품가격)
        ,COUNT(*) OVER(ORDER BY AVG(상품가격) RANGE 
        BETWEEN 10000 PRECEDING AND 10000 FOLLOWING) AS 유사개수
FROM 상품
GROUP BY 상품분류코드;

위 코드의 유사개수 컬럼은 상품분류코드별 평균상품가격을 서로 비교하여 -10000 ~ +10000 사이에 존재하는 상품분류코드의 개수를 구한 것.

💡 SQLD에서는 이정도만 알아둘 것. 자세한건 SQLP 내용

 

LAG / LEAD

  • LAG() : 이전 행의 값을 리턴
  • LEAD() : 다음 행의 값을 리턴

 

GRANT / REVOKE

객체 권한 부여

GRANT [SELECT | UPDATE | INSERT | DELETE | ... ]
ON [객체명]
TO [유저명]
💡 A 유저의 테이블에서 WHERE절 작업을 수행할 수 있도록 B 유저에게 권한을 부여할 때 SELECT 권한을 부여

 

ROLE

ROLE은 많은 DBMS사용자에게 개별적으로 많은 권한을 부여하는 번거로움과 어려움을 해소하기 위해 다양한 권한을 하나의 그룹으로 묶어놓은 논리적인 권한의 그룹이다.

 

GROUPING SETS / ROLLUP | CUBE

  • CUBE 그룹 함수는 인자로 주어진 컬럼의 결합 가능한 모든 조합에 대해서 집계를 수행하므로 다른 그룹 함수에 비해 서비스에 대한 부하가 크다
  • CUBE GROUPING SETS ROLLUP 그룹 함수 모두 일반 그룹 함수로 동일한 결과 추출 가능
  • ROLLUP은 함수의 인자로 주어진 컬럼의 순서에 따라 다른 결과를 추출하므로 컬럼의 순서 중요.
  • ROLLUP ➜ oo별 합계+전체합계
  • CUBE GROUPING SETS ROLLUP 함수에 의해 집계된 레코드에서 집계 대상 컬럼 이외의 GROUP 대상 컬럼 값은 NULL반환

 

START WITH / CONNECT BY / PRIOR

처리순서 :START WITH - CONNECT BY- WHERE

 

NTILE

  • 특정한 컬럼의 값을 기준으로 N개의 등급으로 분류하여 주는 함수

 

PL/SQL

PL/SQL의 특징

  • Block구조로 되어있어 각 기능별로 모듈화 가능
  • 변수, 상수등을 선언하여 SQL문장 간 값 교환, WHERE절 조건으로 대입 가능
  • Procedure User Defined Function Trigger 객체 작성 가능
  • Procedure 내부에 작성된 절차적 코드는 PL/SQL 엔진이 처리하고 일반적은 SQL문장은 SQL실행기가 처리
  • Procedure User Defined Function Trigger 은 트랜잭션을 분할할 수 있으며 자율 트랜잭션 처리 가능
  • IF LOOP 등 절차형 언어를 사용하여 절차적인 프로그램 가능
  • DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용 가능
  • Oracle에 내장되어 있어 Oracle과 PL/SQL을 지원하는 어떤 서버로 프로그램 옮기기 가능
  • 프로그램 성능 향상
  • 여러 SQL문장을 Block으로 묶고 한 번에 전부를 서버로 보내기 때문에 통신량이 줄음
  • DECLARE와 BEGIN~END 문 필수 (EXCEPTION문은 선택사항)

 

EXECUTE IMMEDIATE

PL/SQL 에서 동적 SQL 또는 DDL 문장을 실행할 때 EXECUTE IMMEDIATE를 사용해야 한다.

 

저장 모듈(Stored Module)

  • SQL 문장을 데이터베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램
  • 독립적으로 실행되거나 다른 프로그램으로 부터 실행될 수 있는 완전한 실행 프로그램
  • Oracle의 저장 모듈에는 Procedure User Defined Function Trigger이 있다.

 

프로시저 (Procedure)

저장형 프로시져는 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합

사용자 정의 함수 (User Defined Function)

저장형 함수는 단독적으로 실행되기 보단 다른 SQL문을 통하여 호출되고 결과를 반환하는 SQL의 보조적인 역할

트리거(Trigger)

  • 트리거는 특정한 테이블에 DML문이 수행되었을 때 데이터베이스에서 자동으로 동작하도록 작성된 프로그램
  • 데이터의 무결성과 일관성을 위해 사용자정의함수를 사용
  • 데이터베이스에 로그인하는 작업에도 정의가능
프로시저(Procedure) 트리거 (Trigger)
CREATE Procedure CREATE Trigger
EXECUTE 명령어로 실행 생성 후 자동으로 실행
COMMIT, ROLLBACK 실행 가능 COMMIT, ROLLBACK 실행 불가

 

728x90