warehouse for me: 8월 2012

/****************************************************************
작성자 : 삼이
작성일 : 2004-09-30
제 목 : Grouping(), ROLLUP, CUBE Study 정리
****************************************************************/

* ROLLUP 연산자
- GROUP BY절에 있는 컬럼들을 오른쪽에서 왼쪽의 차례로 그룹들을 생성하고,
   각 그룹에 계산함수를 적용한다.
- GROUP BY절의 결과는 누적 계산 결과이다.

* CUBE 연산자
- GROUP BY절에 있는 모든 컬럼들에 대한 가능한 모든 조합을 그룹으로 생성한다.

* GROUPING 함수 - 각 결과 행이 CUBE, ROLLUP 연산자들에 의해 계산된 것인지를 알기 위해 사용된다.
- 해당컬럼에 대해 계산되었다면 0, 그렇지 않다면(컬럼값이 NULL) 1을 반환한다.
- GROUP BY절에 나타나는 컬럼에 적용된다.

사용 예)
-- table생성(사원이름,급여,부서,직위,입사년도) CREATE TABLE roll_test (
name VARCHAR2(10),
sal    NUMBER,
dept VARCHAR2(10),
duty VARCHAR2(10),
entYear NUMBER(4)
);
INSERT INTO roll_Test VALUES('kim' , 1000, 'AA', '00', 2004);
INSERT INTO roll_Test VALUES('no' , 1500, 'AA', '00', 2004);
INSERT INTO roll_Test VALUES('choi', 2000, 'BB', '02', 2003);
INSERT INTO roll_Test VALUES('park', 2000, 'BB', '02', 2003);
INSERT INTO roll_Test VALUES('lee' , 3000, 'CC', '03', 2002);
INSERT INTO roll_Test VALUES('cho' , 4000, 'AA', '04', 2001);
INSERT INTO roll_Test VALUES('lyu' , 4000, 'DD', '04', 2001);
INSERT INTO roll_Test VALUES('ham' , 4000, 'AA', '04', 2001);
INSERT INTO roll_Test VALUES('kang', 7000, 'DD', '05', 2001);
COMMIT;
SELECT * FROM roll_Test;

1. 각 부서에 대한 급여 소계를 구하고, 총계를 구하라
   (하나의 Column Grouping)
-----일반-----------
SELECT dept, SUM(sal)
FROM roll_Test
GROUP BY dept;
---------------------
----- ROLLUP ---------
SELECT dept, SUM(sal), GROUPING(dept)
FROM roll_Test
GROUP BY ROLLUP(dept);
-----------------------
----- CUBE ----------
SELECT dept, SUM(sal), GROUPING(dept)
FROM roll_Test
GROUP BY CUBE(dept);
-----------------------
ㆍ 일반적인 GROUP BY를 사용할 경우 급여 소계만 나오고, 총계는 따로 구해야 함
ㆍ ROLLUP과 CUBE 차이점 없음

2. 각 부서별, 직위별 급여 소계를 구하고, 총계를 구하라
   (두개의Column Grouping)
-----NORMAL-----------
SELECT dept, duty, SUM(sal)
FROM roll_Test
GROUP BY dept, duty;
-----------------------
----- ROLLUP ----------
SELECT dept, duty, SUM(sal), GROUPING(dept), GROUPING(duty)
FROM roll_Test
GROUP BY ROLLUP(dept, duty);
-----------------------
----- CUBE ----------
SELECT dept, duty, SUM(sal), GROUPING(dept), GROUPING(duty)
FROM roll_Test
GROUP BY CUBE(dept, duty);
-----------------------
ㆍ ROLLUP은 부서에 대한 소계 / 부서에 대한 직위별 소계만 볼 수 있고,
ㆍ CUBE는 부서에 대한 소계 / 부서에 대한 직위별 소계 / 직위별 소계를 볼 수 있음
ㆍ GROUP BY 내의 왼쪽 컬럼부터 자동으로 오름차순 정렬 됨

3. 각 부서별, 직위별, 입사년도별 급여 소계를 구하고   , 총계를 구하라
(세개의 Column Grouping)
-----NORMAL-----------
SELECT dept, duty, entYear, SUM(sal)
FROM roll_Test
GROUP BY dept, duty, entYear;
-----------------------
----- ROLLUP ----------
SELECT dept, duty, entYear, SUM(sal), GROUPING(dept), GROUPING(duty), GROUPING(entYear)
FROM roll_Test
GROUP BY ROLLUP(dept, duty, entYear);
-----------------------
----- CUBE ----------
SELECT dept, duty, entYear, SUM(sal), GROUPING(dept), GROUPING(duty), GROUPING(entYear)
FROM roll_Test
GROUP BY CUBE(dept, duty, entYear);
-----------------------

ㆍ ROLLUP 사용시 3개의 소계와 1개의 총계를 구할 수 있음
(부서별, 부서*직위별, 부서*직위*입사년도별, 총계)
※ GROUP BY 내의 가장 왼쪽 컬럼을 기준으로 하여 순차적으로 하위 그룹 생성
ㆍ CUBE 사용시 7개의 소계와 1개의 총계를 구할 수 있음.
(부서별, 직위별, 입사년도별, 부서*직위별, 부서*입사년도별, 직위*입사년도별, 부서*직위*입사년도별, 총계)
※ 생성 가능한 모든 경우를 그룹 생성

사용 예)
ㆍ 부서에 대한 소계를 보고 싶을 때
HAVING GROUPING(dept) = 0 AND GROUPING(duty) = 1
AND GROUPING(entYear) = 1;
ㆍ 각 부서에 대한 직위별 소계를 보고 싶을 때
HAVING GROUPING(dept) = 0 AND GROUPING(duty) = 0
AND GROUPING(entYear) = 1;

원문 URL : http://fly32.net/252

ORDER BY의 DEFAULT는 ASC!
오라클 예제는 SCOTT/TIGER와 함께..

1. DECODE 사용하기
-- 정렬순서 : 부서명
-- 1          : OPERATION
-- 2          : SALES
-- 3          : RESEARCH
-- 4          : ACCOUNTING
SELECT DNAME
FROM DEPT
ORDER BY DECODE(DNAME, 'OPERATIONS', 1, 'SALES', 2, 'RESEARCH', 3, 'ACCOUNTING', 4);

2. NULL값 사용해서 정렬하기 (ORDER BY 저
ASC   정렬일 때 : LAST 값
DESC 정렬일 때 : FIRST 값

//COLUMN에 NULL값이 있는 게 아닐 때
-- RESEARCH를 가장 마지막값으로 정렬할 때
SELECT DNAME
FROM DEPT
ORDER BY CASE WHEN (DNAME='RESEARCH') THEN NULL
                        ELSE DNAME END;
(ORDER BY 절에 조건식 사용이 가능!)

//COLUMN에 NULL값을 가지고 있을 때
-- ASC일 때 NULL값 FIRST 값으로 정렬
ORDER BY NVL(COLUMN명, '')
--DESC일 때 NULL값 LAST 값으로 정렬
ORDER BY COLUMN명 NULLS LAST

warehouse for me

2012년 8월 8일 수요일

[oracle] grouping(), rollup, cube (그룹별 소계 구할 때 사용)

[oracle] sql에서 연속된 숫자 발생시키는 쿼리

[orcle] order by 조건 사용하기