oracle
group by 사용해보자, null처리 nvl,roll up ,cube, zipcode파일 테이블
말없는채플린씨
2009. 10. 8. 12:25
SQL> SELECT AVG(SAL),SUM(SAL),COUNT(SAL),MIN(SAL),MAX(SAL)
2 FROM EMP;
결과물
AVG(SAL) SUM(SAL) COUNT(SAL) MIN(SAL) MAX(SAL)
---------- ---------- ---------- ---------- ----------
2073.21429 29025 14 800 5000
SELECT를 이용하여 평균값을 구하거나 합을 구할때
//////////////////////////////////////////////////////////////////////////
SQL> SELECT COUNT(COMM) FROM EMP;
결과물
COUNT(COMM)
-----------
4
COUNT(COMM)을 사용하면 COMM을 카운트하여 총 몇개를 카운트했는지 보여준다
//////////////////////////////////////////////////////////////////////////
SQL> select count(*) from emp;
결과물
COUNT(*)
----------
14
count(*)을 이용하여 테이블의 컬럼수를 카운트 해주는 것이다
/////////////////////////////////////////////////////////////////////////
SQL> select avg(comm) from emp;
AVG(COMM)
----------
550
comm이 있는것만 평균을 내어준다 avg가 평균을 구하는것
////////////////////////////////////////////////////////////////////////
SQL> select avg(nvl(comm,0)) from emp;
AVG(NVL(COMM,0))
----------------
157.142857
nvl(a,b) nvl은 해당값이 null일때 b를 실행하고 null이 아닐때 comm을 실행
따라서 comm과 0들의 값에 평균을 구한 것이다
///////////////////////////////////////////////////////////////////////
1 SELECT JOB,TO_CHAR(AVG(SAL),'$9,999') AVG,SUM(SAL),COUNT(SAL) FROM EMP
2 GROUP BY (JOB)
3* HAVING AVG(SAL)>=(SELECT AVG(SAL) FROM EMP WHERE JOB IN('SALESMAN','MANAGER'))
SQL> /
JOB AVG SUM(SAL) COUNT(SAL)
--------- ------- ---------- ----------
PRESIDENT $5,000 5000 1
MANAGER $2,758 8275 3
ANALYST $3,000 6000 2
직업당 연봉을 평균냈을때 SALESMAN,MANAGER보다 높은 평균을 구하고 카운트를 넣어서
순위를 정하는것
//////////////////////////////////////////////////////////////////
SQL> SELECT DEPTNO,JOB,AVG(SAL) FROM EMP GROUP BY(DEPTNO,JOB);
DEPTNO JOB AVG(SAL)
---------- --------- ----------
20 CLERK 950
30 SALESMAN 1400
20 MANAGER 2975
30 CLERK 950
10 PRESIDENT 5000
30 MANAGER 2850
10 CLERK 1300
10 MANAGER 2450
20 ANALYST 3000
GROUP BY로 DEPTNO,JOB을 그룹되어 평균 연봉을 구한다
//////////////////////////////////////////////////////////////////////
SQL> SELECT DEPTNO,JOB,AVG(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO,JOB);
DEPTNO JOB AVG(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 2916.66667
20 CLERK 950
20 ANALYST 3000
20 MANAGER 2975
20 2175
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1400
30 1566.66667
2073.21429
ROLLUP는 구룹을 지어진 상태에서 다시 각 DEPTNO,JOB을 구분지어 그 값을 평균내 준다
구룹을 평균내어준다
////////////////////////////////////////////////////////////////////////////////
1* SELECT JOB,DEPTNO,COUNT(SAL) FROM EMP GROUP BY ROLLUP(JOB,DEPTNO)
SQL> /
JOB DEPTNO COUNT(SAL)
--------- ---------- ----------
CLERK 10 1
CLERK 20 2
CLERK 30 1
CLERK 4
ANALYST 20 2
ANALYST 2
MANAGER 10 1
MANAGER 20 1
MANAGER 30 1
MANAGER 3
SALESMAN 30 4
SALESMAN 4
PRESIDENT 10 1
PRESIDENT 1
14
이름별로 구룹을 지어주는듯해.. 위에꺼랑 순서만 바뀌었는데
결과값이 완전히 바뀐거 주의하면서 풀어봐
아마도 ROLLUP(A,B)라는게 A별로 B를 출력하라는것 같기도 해
////////////////////////////////////////////////////////////
SQL> SELECT JOB,DEPTNO,COUNT(SAL)
2 FROM EMP
3 GROUP BY CUBE(JOB,DEPTNO);
JOB DEPTNO COUNT(SAL)
--------- ---------- ----------
14
10 3
20 5
30 6
CLERK 4
CLERK 10 1
CLERK 20 2
CLERK 30 1
ANALYST 2
ANALYST 20 2
MANAGER 3
MANAGER 10 1
MANAGER 20 1
MANAGER 30 1
SALESMAN 4
SALESMAN 30 4
PRESIDENT 1
PRESIDENT 10 1
CUBE와 ROLLUP은 말로 표현하기가 그래
책 212참조하길바래
참고로 CUBE는 기준이 되는 값의 해당 값들을 표현해주고 그것을 구룹화 시키는듯해
////////////////////////////////////////////////////////////////////////////
CREATE TABLE ZIPCODE(
ZIPCODE VARCHAR2(7),
SIDO VARCHAR2(4),
GUGUN VARCHAR2(15),
DONG VARCHAR2(52),
BUNJI VARCHAR2(17),
SEQ NUMBER(5));
////////////////////////////////////////////////////////////// 1.CTL 파일///
LOAD DATA INFILE 'C:\\1.CSV'
INTO TABLE ZIPCODE
FIELDS TERMINATED BY ',' // 필드를 , 으로 구분한다
(ZIPCODE, SIDO,GUGUN,DONG,BUNJI,SEQ)
////////////////////////////////////////////////////////////// 파일을 table에 입력 cmd 모드에서 사용
CMD 모드에서 SQLLDR USERID=SCOTT/TIGER CONTROL='C:\\1.CTL' 입력하면 파일이 TABLE에 입력 된다.