오라클 함수, SQL Functions(10g) - Aggregate Functions

 

*SQL Functions(10g) -  Aggregate Functions

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

 

1. AVG(N) - N의 평균을 구하여 반환한다. 단, NULL인 값은 제외하고 계산한다.

SQL> SELECT EMPNO, SAL, COMM FROM EMP;

SQL> SELECT AVG(COMM)         AS AVG1,  --  커미션이 있는 사원들의 평균 커미션
                 AVG(NVL(COMM, 0))  AS AVG2   --  사원 1인당 평균 커미션
         FROM EMP;

 

Analytic Example

SQL> SELECT manager_id, last_name, hire_date, salary,
   AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
   ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
   FROM employees;

 

 

2. COLLECT(N) -

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions024.htm#i1271564

 

  SQL> create or replace procedure proc1 (d EMP.DEPTNO%TYPE)
  is
    TYPE emp_sal_table_type IS TABLE OF emp.sal%type
      INDEX BY PLS_INTEGER;

    emp_sal_tab emp_sal_table_type;
  begin
    select sal BULK COLLECT INTO emp_sal_tab
    from emp
    where deptno = d
    order by sal desc;

    p.p(emp_sal_tab.first);
    p.p(emp_sal_tab.last);

    for i in emp_sal_tab.first .. emp_sal_tab.last loop
      p.p(emp_sal_tab(i));
    end loop;
  end;
  /

  SQL> show errors

  SQL> exec proc1(10)
  SQL> exec proc1(30)

3. CORR(M, N) -

 

CORR

returns the coefficient of correlation of a set of number pairs. You can use it as an aggregate or analytic function.

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions028.htm#i82637

 

Analytic Example

SQL> SELECT employee_id, job_id,
TO_CHAR((SYSDATE - hire_date) YEAR TO MONTH ) "Yrs-Mns", salary,
CORR(SYSDATE-hire_date, salary)
OVER(PARTITION BY job_id) AS "Correlation"
FROM employees
WHERE department_id in (50, 80)
ORDER BY job_id, employee_id;

4. COUNT(N) - N의 총 개수를 계산한다. 단, NULL은 제외한다.

SQL>  drop table t1 purge;

SQL> create table t1 (col1 number);
SQL>  insert into t1 values (1000);SQL>
SQL> insert into t1 values (1000);
SQL> insert into t1 values (2000);
SQL> insert into t1 values (2000);
SQL> insert into t1 values (null);
SQL> insert into t1 values (null);

 

SQL> commit;

 

SQL> select col1, col1, col1 from t1;

 

SQL> select count(*), count(col1), count(distinct col1) from t1;

 

 

 

5. COVAR_POP(P1, P2) - COVAR_POP returns the population covariance of a set of number pairs. You can use it as an aggregate or analytic function.

COVAR_POP(P1, P2) = (SUM(P2*P1)- SUM(P2) * SUM(P1) / N) /N

 

population : 집단

covariance : 공분산

 - http://terms.naver.com/entry.nhn?cid=1839&docId=75608&mobile&categoryId=1841

 - http://terms.naver.com/entry.nhn?cid=1839&docId=977687&mobile&categoryId=1841

 

SQL> SELECT job_id,
   COVAR_POP(SYSDATE-hire_date, salary) AS covar_pop,
   COVAR_SAMP(SYSDATE-hire_date, salary) AS covar_samp
   FROM employees
   WHERE department_id in (50, 80)
   GROUP BY job_id;

 

 

6. COVAR_SAMP(P1, P2) = (SUM(P1*P2) - SUM(P1) * SUM(P2) / N)/(N-1) 

COVAR_SAMP returns the sample covariance of a set of number pairs. You can use it as an aggregate or analytic function.

7. CUME_DEST - CUME_DIST calculates the cumulative distribution of a value in a group of values. The range of values returned by CUME_DIST is >0 to <=1. Tie values always evaluate to the same cumulative distribution value.

 

SQL> SELECT CUME_DIST(15500, .05) WITHIN GROUP
   (ORDER BY salary, commission_pct) "Cume-Dist of 15500"
   FROM employees;

 

 

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions035.htm#i82886

8. DENSE_RANK(M, N) - DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank. This function is useful for top-N and bottom-N reporting.

This function accepts as arguments any numeric datatype and returns NUMBER.

 

1) RANK() VS DENSE_RANK()

SQL> SELECT EMPNO, SAL,
         RANK() OVER(ORDER BY SAL DESC) RANK_T,
         DENSE_RANK() OVER(ORDER BY SAL DESC) DENSE_RANK_T
         FROM EMP;

 

        -- RANK() 함수는 2등이 2명이면 다음 등수는 4등이 됩니다.

        -- DENSE_RANK() 함수는 2등이 2명이면 다음 등수는 3등이 됩니다.

        -- RANK() 함수는 중간에 빠지는 등수가 있지만 DENSE_RANK() 함수는 중간에 빠지는 등수가 없습니다.

 

 

 

2) 상위 5명 추출

SQL> SELECT * FROM
        (SELECT EMPNO, ENAME, SAL, RANK() OVER(ORDER BY SAL DESC) RANK FROM EMP)
        WHERE RANK < 6;

 

SQL> SELECT * FROM
        (SELECT EMPNO, ENAME, SAL, RANK() OVER(ORDER BY SAL DESC) RANK FROM EMP)
        WHERE ROWNUM < 6;

 

 

3)

SQL> SELECT DENSE_RANK(15500, .05) WITHIN GROUP
   (ORDER BY salary DESC, commission_pct) "Dense Rank"
   FROM employees;

 

 

 

9. FIRST & LAST - FIRST와 LAST 함수는 SORTING된 집합에서 첫번째 혹은 마지막 결과를 얻는데 사용된다.

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions056.htm#i1000901

 

SQL> SELECT department_id,
         MAX(salary) KEEP (DENSE_RANK LAST ORDER BY SALARY) "최대값",
         MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY SALARY) "최소값"
         FROM employees
         GROUP BY department_id;

 

SQL> SELECT DEPARTMENT_ID, 
         MAX(EMPLOYEE_ID||' '||LAST_NAME) KEEP (DENSE_RANK FIRST ORDER BY SALARY DESC) 최대급여,
         MAX(SALARY) 최대값,
         MIN(EMPLOYEE_ID||' ' ||LAST_NAME) KEEP (DENSE_RANK LAST ORDER BY SALARY DESC) 최소급여,
         MIN(SALARY) 최소값
         FROM EMPLOYEES
         GROUP BY DEPARTMENT_ID;

 

 

 

10. GROUP_ID - GROUP_ID 함수는 지정된 GROUP BY 결과로 부터 중복된 그룹을 구별한다. 질의 결과에서 중복된 GROUPING을 필터링 하는 것에 유용하다. 유일한 중복 그룹을 식별하기 위해 ORACLE NUMBER를 반환한다. GROUP_ID는 GROUP BY clause를  포함한 SELECT statement에 적용 가능하다. 만약 N이 특정 GROUPING에서 중복이 존재하면, GROUP_ID는 0~(N-1) 범위의 값을 반환한다.

 

 

1) http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions061.htm#i1002035

 

2) http://www.oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets.php

 

- ROLLUP, CUBE, GROUPING Functions and GROUPING SETS, GROUP_ID

 

3) http://etutorials.org/SQL/Mastering+Oracle+SQL/Chapter+13.+Advanced+Group+Operations/13.3+The+GROUPING_ID+and+GROUP_ID+Functions/

 

- GROUP BY CUBE, GROUP_ID

 

4) http://blog.naver.com/PostView.nhn?blogId=agoodman_jjk&logNo=130092652199&categoryNo=25&viewDate=&currentPage=1&listtype=0

 

5) http://neucareer.tistory.com/50

 

6) http://radiocom.kunsan.ac.kr/lecture/oracle/function/GROUP_ID.html

 

11. GROUPING(Expr) - Expr의 Value가 NULL이면 1을 NOT NULL이면 0을 반환한다.

                    SELECT 한 ROW에서 NULL 값을 유무를 확인 할 때 사용한다.

 

http://www.statwith.pe.kr/ORACLE/functions054.htm

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions062.htm#i77498

 

SQL> SELECT DEPTNO, 
                        JOB, 
                        SUM(SAL), 
                        GROUPING(DEPTNO) AS DEPT_G,
                        GROUPING(JOB) AS JOB_G
          FROM EMP
          GROUP BY ROLLUP(DEPTNO, JOB)
          ;

 

 

SQL> SELECT DECODE(GROUPING(department_name), 1, 'All Departments',
   department_name) AS department,
   DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job,
   COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal"
   FROM employees e, departments d
   WHERE d.department_id = e.department_id
   GROUP BY ROLLUP (department_name, job_id);

 

 

 

 

12. GROUPING_ID(Expr) - Expr의 GROUPING 비트 벡터에 대응하는 수치를 반환한다. GROUPING_ID는 ROLLUP, CUBE, GROUPING 함수와 같은 GROUP BY 확장 기능을 포함한 SELECT 문장에서 적용할 수 있다. 많은 GROUP BY 표현을 가지는 쿼리에서, 많은 GROUPING 함수를 필요로 하는 특정한 행의 GROUP BY 레벨을 지정하기 위해서는 복잡한 쿼리가 요구된다. GROUPING_ID는 이러한 경우 유용한다.

 GROUPING_ID 함수는 다수의 GROUPING 함수의 결과와 비트 벡터(0과 1의 문자열)를 연결하는 것과 동일핟. GROUPING_ID를 이용하여서 다수의 GROUPING 함수를 사용하지 않고서, 표한하기 위한 행의 필터 조건이 간단해 진다. 행 필터링은 원하는 행잉 GROUPING_ID=N의 단일 조건으로 정의 될 수 있어서 더 쉬워진다. 단일 테이블에서 다수의 집계의 레벨을 저장할 때 유용하다.

 

http://www.statwith.pe.kr/ORACLE/functions055.htm

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions063.htm#i1001964

 

SQL> SELECT  DEPTNO,
    DECODE(GROUPING_ID(DEPTNO,JOB),3,'총합계',1,'부서합계',JOB) AS JOB,
    DECODE(GROUPING_ID(DEPTNO,JOB,EMPNO),1,'직급소계',EMPNO) AS EMPNO,
    DECODE(GROUPING_ID(DEPTNO,JOB,EMPNO),0,ENAME,COUNT(*)||'명') AS ENAME,
    MGR,
    SUM(SAL) AS SAL,
    SUM(COMM) AS COMM
FROM EMP
GROUP BY ROLLUP (DEPTNO,JOB,(EMPNO,ENAME,MGR))

;

 

 

13. VARIANCE(Expr) - Exprt의 분산(VARIANCE) 값을 반환한다.

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions212.htm#i89144

 

1) Aggregate Example

SQL> SELECT VARIANCE(SALARY)  AS "Variance" FROM EMPLOYEES;

 

2) Analytic Example

SQL> SELECT LAST_NAME, SALARY, VARIANCE(SALARY) OVER (ORDER BY HIRE_DATE)  AS "Variance" FROM EMPLOYEES;

 

 

 

14. MAX(N) - N의 최대값을 반환한다.

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions085.htm#i89072

 

1) Aggregate Example
SQL> SELECT MAX(salary) AS "최대" FROM employees;

 

2) Analytic Example
SQL> SELECT manager_id, last_name, salary,
   MAX(salary) OVER (PARTITION BY manager_id) AS "메니져별 최대"
   FROM employees;

 

 

 

 

15. MIN(N) -  N의 최소값을 반환한다.

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions087.htm#i1280029

 

SQL> SELECT MIN(hire_date) AS  "Earliest" FROM employees;

SQL> SELECT manager_id, last_name, hire_date, salary,
   MIN(salary) OVER(PARTITION BY manager_id ORDER BY hire_date
   RANGE UNBOUNDED PRECEDING) AS p_cmin
   FROM employees;

 

16. SUM(N)- N의 합계을 반환한다.

1) Aggregate Example
SQL> SELECT SUM(salary) "Total"
         FROM employees;

2) Analytic Example

SQL> SELECT manager_id, last_name, salary,
         SUM(salary) OVER (PARTITION BY manager_id ORDER BY salary
         RANGE UNBOUNDED PRECEDING) l_csum
         FROM employees;

17. MEDIAN(Expr) - Expr의 중앙값을 반환한다. 데이터를 크기 순서로 나열할 때 가장 중앙에 위치하게 되는 데이터 값. 자료의 개수 n이 홀수이면 (n+1)/2번째 값이 중앙값이고, 짝수이면 n/2번째 값과 (n/2)+1번째 값의 평균값이 중앙값이 된다.(네이버 IT용어사진)

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions086.htm#i1279886

 

http://terms.naver.com/entry.nhn?cid=80&docId=512281&mobile&categoryId=80

 

http://terms.naver.com/entry.nhn?cid=2906&docId=1619660&mobile&categoryId=2906

 

SQL> SELECT department_id, MEDIAN(salary)
         FROM employees
         GROUP BY department_id;

 

SQL> SELECT manager_id, employee_id, salary,
         MEDIAN(salary) OVER (PARTITION BY manager_id) "Median by Mgr"
         FROM employees;

 

 

 

18. RANK(Expr) - Expr의 순위를 반환한다.

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions123.htm#i1269223

 

1) Aggregate Example
SQL> SELECT RANK(15500, 0.5) WITHIN GROUP
            (ORDER BY SALARY, COMMISSION_PCT) "Rank"
            FROM EMPLOYEES

 

SQL> SELECT RANK(15500) WITHIN GROUP
            (ORDER BY SALARY DESC) "Rank of 155500"
            FROM EMPLOYEES

 

2) Analytic Example

SQL> SELECT department_id, last_name, salary, commission_pct,
           RANK() OVER (PARTITION BY department_id
           ORDER BY salary DESC, commission_pct) "Rank"
           FROM employees WHERE department_id = 90;

 

 

19. PERCNET_RANK(Expr) - PERCENT_RANK 함수는 CUME_DIST(cumulative distribution) 함수와 비슷하다. PERCENT_RANK의 반환값은 0과 1사이다. 첫번째 집합의 PERCENT_RANK의 값은 0이다.

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions109.htm#i1043951

 

1) Aggregate Example
SQL> SELECT PERCENT_RANK(15000, .05) WITHIN GROUP
   (ORDER BY salary, commission_pct) "Percent-Rank"
   FROM employees;

 

2) Analytic Example

SQL> SELECT department_id, last_name, salary,
   PERCENT_RANK()
      OVER (PARTITION BY department_id ORDER BY salary DESC) AS pr
   FROM employees
   ORDER BY pr, salary;

 

 

 

20. STDDEV(Expr) - Exprt의 표본표준편차(the sample standard deviation)을 반환한다. STTDEV는 1건의 데이타를 갖고 있으면 0을 반환하고 STDDEV_SAMP은 NULL을 반환한다.

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions159.htm#i89108

 

1) Aggregate Example
SQL> SELECT STDDEV(salary) "Deviation"
   FROM employees;

 

 

2) Analytic Example

SELECT last_name, salary,
   STDDEV(salary) OVER (ORDER BY hire_date) "StdDev"
   FROM employees 
   WHERE department_id = 30;

 

 

-----------------------------------------------------------------------------------------------------------

 

21. GROUPING SETS

 

http://radiocom.kunsan.ac.kr/lecture/oracle/function/GROUPING_SETS.html

 

22. GROUPING(), ROLLUP, CUBE Study 정리

 

http://fly32.net/252

*********************************
ANALYTIC FUNCTION 정리해야 함
*********************************