본문 바로가기

SQL

 (31)
31. PIVOT, UNPIVOT , Top n , Fetch [ pivot / unpivot ] 1. pivot  - long data -> wide data** 문법 select   from 테이블명 또는 서브쿼리 pivot(value값 for unstack컬럼명 in (값1, 값2, 값3, ...); ** 특징 - value값에는 집계함수 형태가 들어가야 함(sum, count, avg, ...) - from절에는 unstack컬럼, stack컬럼, value컬럼 외 정의되면 안됨   -> from절에 존재하는 컬럼 중 unstack컬럼과 value컬럼을 제외한 모든 컬럼이 stack 처리 되므로!!!!  예제) 각 부서내 job별로 인원수 파악 select *   from emp                                       -- 잘못된..
30. 계층형 질의, 그룹 함수 [ 계층형 질의 ]   - 한 테이블 내 각 행끼리 상하 관계를 갖는 경우 그 depth(level)를 표현하는 방법  ex) 부서에 대한 상하관계 존재할 경우 부서명 들여쓰기 ** 문법 select    from   where  start with 시작조건 connect by 연결조건; 예제) 사장실부터 시작해서 하위부서 연결 정상) select d.*, level    from dept2 d  start with PDEPT is null connect by pdept = prior dcode; 잘못된 쿼리) select d.*   from dept2 d  start with PDEPT is null connect by  prior pdept =dcode;   예제) department 테이블 내 있는..
29. 윈도우함수(2) [ 참고 - 누적합 연산 범위 ] 누적합 연산 시 범위를 생략하면  range between unbounded preceding and current row 연산범위가 default이다. 즉, 처음부터 현재행까지를 연산하고 위아래 같은 값이 있다면 하나의 range로 묶어서 동시에 누적합을 계산함 ** 누적합 연산 범위(서로 다른 범위)  select e.sal, e.empno,        sum(sal) over(order by sal) as 누적합1,        sum(sal) over(order by sal, empno) as 누적합2,        sum(sal) over(order by sal rows between unbounded preceding and current row) as 누적..
28. 윈도우함수(1) [ 윈도우 함수 ] - 테이블 추가 접근(조인, 서브쿼리)없이 다른행과의 비교, 연산을 가능하게 하는 문법 - select절에서만 사용 가능(where절 사용 불가) ** 종류 1. 집계함수 형태 sum(*), min, max, count, avg ... over(...) ex) 누적합, 누적count 2. 순위(*) - rank over() - dense_rank over() - row_number over() ex) 성적이 높은순 순위 3. 이전/이후 - lag over() - lead over() ex) 이전 연도에 비해 매출 감소한 연도 확인 4. 최대/최소 - first_value over() - last_value over() 5. 비율 - ratio_to_report over() - cume..
27. 정규식표현(2) 지난 시간에 다룬 내용에 가벼운 정리와 함께 나머지 표현도 함께 보자. [ 정규식 표현(Regular Expression) ] 1. regexp_substr  ** 문법 regexp_substr(대상, 패턴, 시작위치, 발견횟수, 옵션, 서브그룹번호) ** 발견횟수(default:1)    패턴에 만족하는 여러 문자열 중 가장 처음 발견되는 문자열을 추출함    2. regexp_replace ** 문법 regexp_replace(대상, 찾을문자열, 바꿀문자열, 시작위치, 발견횟수, 옵션) ** 발견횟수(default:0)    패턴에 만족하는 모든 문자열 치환  -------------------------------------------------------------------------------..
26. 정규식표현(1) [ 정규식표현(regexp) ] 문자열 패턴을 간소화하여 공통 패턴을 가진 문자열을 추출,삭제,조회하기 위한 표현식 ** 정규식표현식 1) 문자/숫자/특수기호 표현 \d : 숫자 \w : 글자(숫자,문자) + _ \s : 공백 \t : 탭 \n : 엔터 [[:digit:]] : 숫자 [[:alpha:]] : 문자 [[:alnum:]] : 숫자+문자 [[:punct:]] : 특수기호 [[:blank:]] : 공백 2) 횟수 표현 * : 0회 이상(포함되어있지 않거나 1회이상 연속적으로 포함된 경우)     ex) abc\d* : abc, abc1, abc1000처럼 abc 뒤에 숫자가 없거나 있다면 여러개 나열되는 형태의 문자열을 의미                  abc1000ab 문자열의 경우 abc1..
25. 기타 오브젝트(3) [ WITH GRANT/ADMIN OPTION ] - 중간관리자에게 일부 권한을 일임하여 중간관리자가 제3자에게 권한을 부여하게 할 수 있는 옵션 - GRANT로 부여 시 옵션 전달 - WITH GRANT OPTION은 오브젝트 권한에 대해, WITH ADMIN OPTION은 시스템권한 및 롤에 대해 작업 시 사용 ** 정리) with grant option(테이블권한) : 직접회수 불가, 동시회수 가능 with admin option(시스템권한) : 직접회수 가능, 동시회수 불가 [ 실습 - 중간관리자를 통한 권한 관리 ] DBA : SYSTEM 중간관리자 : ITWILL 업무계정 : HONG 0. HONG 계정 생성(SYSTEM 계정에서 수행) create user hong identified by ..
24. 기타 오브젝트(2) 1. SYNONYM- 공식적으로 부여하는 테이블 별칭 - 보통 스키마.테이블명으로 조회하는 대신 테이블명만으로 조회되도록 설정하기 위해 사용                                       1) 생성 create [or replace] [public] synonym 별칭 for 원본이름; ** public : 시노님 생성자(소유자) 외에도 공용으로 사용가능한 별칭             public 생략 시에는 시노님 소유자만 별칭 사용 가능  2) 삭제  drop [public] synonym 시노님명; *주의)public synonym은 반드시 drop public synonym 명령어로 삭제!! [ 실습 - 시노님을 통한 테이블 접근 ] scott 계정에 dba 권한 부여) sys..

티스토리툴바