[펌]오라클 그룹 함수

5.그룹함수  Aggregate 함수
 5-1) AVG* 함수
 5-2) CORR* CORR* 함수
 5-3) COUNT* 함수
 5-4) COVAR_POP 함수
 5-5) COVAR_SAMP 함수
 5-6) CUME_DIST 함수
 5-7) DENSE_RANK 함수
 5-8) FIRST 함수
 5-9) GROUP_ID 함수
 5-10) Grouping 함수
 5-11) GROUPING_ID 함수
 5-12) LAST 함수
 5-13) MAX 함수
 5-14) MIN 함수
 5-15) PERCENTILE_CONT 함수
 5-16) PERCENTILE_DISC 함수
 5-17) PERCENT_RANK 함수
 5-18) RANK 함수
 5-19) REGR_(linear regression) function* 함수
 5-20) STDDEV 함수
 5-21) STDDEV_POP 함수
 5-22) STDDEV_SAMP 함수
 5-23) SUM 함수
 5-24) VAR_POP 함수
 5-25) VAR_SAMP 함수
 5-26) VARIANCE 함수
 5-27) Grouping sets 함수

 

5-1) AVG* 함수
--------------------------------------------------------------------------------

조건을 만족하는 행(row)의 평균을 값을 반환하며,
 aggregate 함수나 analytic 함수로 사용된다.

【형식】
AVG( [DISTINCT | ALL] 컬럼명)
   [ [OVER] (analytic 절)]

【예제】aggregate 예
SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

SQL> select avg(salary) from emp;

AVG(SALARY)
-----------
        240

【예제】analytic 예
SQL> select avg(distinct salary) over(partition by bonus)
  2  from emp;

AVG(DISTINCTSALARY)OVER(PARTITIONBYBONUS)
-----------------------------------------
                               236.666667
                               236.666667
                               236.666667
                                      250

SQL> select avg(salary) over(partition by bonus order by id
    2 rows between 1 preceding and 1 following) as avg  from emp;

       AVG
----------
       245
236.666667
       235
       250

5-2) CORR* CORR* 함수
--------------------------------------------------------------------------------

집합 쌍의 상관관계 계수를 반환한다.

【형식】
CORR( expr1, expr2 ) [ [OVER] (analytic 절)]

【예제】
SQL> select corr(avg(bonus),max(bonus))
  2  from employees
  3  group by dept_no;

CORR(AVG(BONUS),MAX(BONUS))
---------------------------
                          1

 

5-3) COUNT* 함수
--------------------------------------------------------------------------------

 쿼리한 행의 수를 반환한다.
【형식】
COUNT([*|DISTINCT|ALL] 컬럼명) [ [OVER] (analytic 절)]

【예제】
SQL> select count(*) from emp;

  COUNT(*)
----------
         4

SQL> select count (distinct dept_no) from employees;

COUNT(DISTINCTDEPT_NO)
----------------------
                     2

SQL> select count (all dept_no) from employees;

COUNT(ALLDEPT_NO)
-----------------
                4

SQL> select salary,count(*)
  2  over (order by salary)
  3  from emp;

    SALARY COUNT(*)OVER(ORDERBYSALARY)
---------- ---------------------------
       220                           1
       240                           2
       250                           4
       250                           4

5-4) COVAR_POP 함수
--------------------------------------------------------------------------------

이 함수는 number 쌍의 집합에 대한 population covariance를 반환한다.

【형식】
COVAR_POP(expr1, expr2 [ OVER (analytic 절)] )

【예제】
SQL> select covar_pop(bonus,salary) from emp;

COVAR_POP(BONUS,SALARY)
-----------------------
                   62.5

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

 

5-5) COVAR_SAMP 함수
--------------------------------------------------------------------------------

이 함수는 number 쌍의 집합에 대한 sample covariance를 반환한다.

【형식】
COVAR_SAMP(expr1, expr2 [ OVER (analytic 절)] )

【예제】
SQL> select covar_samp(bonus,salary) from emp;

COVAR_SAMP(BONUS,SALARY)
------------------------
              83.3333333

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

 

5-6) CUME_DIST 함수
--------------------------------------------------------------------------------

이 함수는 그룹 값 내에서 어떤 값의 cumulative distribution(누적분포)을 계산한다.

【형식】
CUME_DIST(expr,... WITHIN GROUP (ORDER BY
     expr [DESC | ASC] [NULLS {FIRST|LAST}],...)
또는
CUME_DIST() over ([query_partition_clause] order_by_clause)

【예제】
SQL> select cume_dist(230) within group
  2  (order by salary ) from emp;

CUME_DIST(230)WITHINGROUP(ORDERBYSALARY)
----------------------------------------
                                      .4

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

 

5-7) DENSE_RANK 함수
--------------------------------------------------------------------------------

그룹 내에서 순위를 반환한다.

【예제】
SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL> select dense_rank(230, .05) within group
  2  (order by salary, bonus) "Dense Rank"
  3  from employees;

Dense Rank
----------
         2

5-8) FIRST 함수
--------------------------------------------------------------------------------

first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.
【형식】
집합함수 KEEP (
 DENSE_RANK FIRST ORDER BY
    expr [DESC|ASC][NULL{FIRST|LAST}],...)
【예제】
SQL> select
  2  min(salary) keep (dense_rank first order by salary) "Worst",
  3  max(salary) keep (dense_rank last order by salary) "Best"
  4  from employees
  5  order by id;

     Worst       Best
---------- ----------
       220        250

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

5-9) GROUP_ID 함수
--------------------------------------------------------------------------------

GROUP() 함수는 group by로 분리되어 복제된 번호로 복제 횟수를 구분하도록 출력한다.
번호가 0부터 시작되므로 n번 복제되었으면 n-1의 번호가 출력된다.

【예제】
SQL> select dept_no, group_id() from employees
  2  group by dept_no;

   DEPT_NO GROUP_ID()
---------- ----------
        10          0
        20          0

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

5-10) Grouping 함수
--------------------------------------------------------------------------------

Grouping 함수는 Rollup이나 cube 연산자랑 함께 사용하여
 grouping 함수에서 기술된 컬럼이 그룹핑시 사용되었는지 보여주는 함수이다.
특별히 연산의 기능은 없으며,
  rollup이나 cube 연산 후 출력된 결과에 대한 사용자의 이해를 높이기 위해 사용된다.
  즉, grouping 함수를 이용할 경우 출력되는 결과값 중 null 값이 있다면,
      이 null 값이 rollup이나 cube 연산의 결과로 생성된 값인지,
      원래 테이블상에 null 값으로 저장된 것인지 확인할 수 있다.

. grouping 함수는 인수로 하나의 값만을 가진다.
. grouping 함수에 사용된 인수는 group by 절에 기술된 값 중에 하나와 반드시 일치해야 한다.
. grouping 함수의 결과값으로 0 또는 1을 반환한다.
    0은 해당인수로 쓰인 값이 rollup이나 cube 연산에 사용되었음을 나타나고,
    1은 사용되지 않았음을 의미한다.
【형식】
SELECT   컬럼명,그룹함수(컬럼명), GROUPING(컬럼명)
FROM  테이블명
WHERE  조건
GROUP BY [ROLLUP | CUBE] 그룹핑하고자하는 컬럼명,...
HAVING  그룹조건
ORDER BY 컬럼명 또는 위치번호

【예제】
SQL> select grade,deptno,sum(salary),GROUPING(deptno)
  2  from aaa
  3  group by rollup(grade,deptno);

     GRADE     DEPTNO SUM(SALARY) GROUPING(DEPTNO)
---------- ---------- ----------- ----------------
         1         10         100                0
         1         20         500                0
         1         30         300                0
         1                    900                1
         2         10         400                0
         2         20         200                0
         2         30         600                0
         2                   1200                1
                             2100                1

9 rows selected.

 

5-11) GROUPING_ID 함수
--------------------------------------------------------------------------------

GROUPING_ID(expr,...) 함수는 행과 관련되는 GROUPING 비트 벡터에 대응되는 수치를 반환한다.

【예제】
SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL> select sum(salary), grouping_id(dept_no)
  2  from employees
  3  group by dept_no;

SUM(SALARY) GROUPING_ID(DEPT_NO)
----------- --------------------
        500                    0
        460                    0

 

5-12) LAST 함수
--------------------------------------------------------------------------------

first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.
【형식】
집합함수 KEEP (
 DENSE_RANK LAST ORDER BY
    expr [DESC|ASC][NULL{FIRST|LAST}],...)
【예제】
SQL> select
  2  min(salary) keep (dense_rank first order by salary) "Worst",
  3  max(salary) keep (dense_rank last order by salary) "Best"
  4  from employees
  5  order by id;

     Worst       Best
---------- ----------
       220        250

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

5-13) MAX 함수
--------------------------------------------------------------------------------

이 함수는 최대 값을 반환한다.

【형식】
MAX ([{DISTINCT|ALL}] expr) [OVER(analytic_clause)]

【예제】
SQL> select max(salary) over (partition by dept_no)
  2  from employees;

MAX(SALARY)OVER(PARTITIONBYDEPT_NO)
-----------------------------------
                                250
                                250
                                240
                                240

SQL> select max(salary) from employees;

MAX(SALARY)
-----------
        250

 

5-14) MIN 함수
--------------------------------------------------------------------------------

이 함수는 최소 값을 반환한다.

【형식】
MIN ([{DISTINCT|ALL}] expr) [OVER(analytic_clause)]

【예제】
SQL> select min(salary) over (partition by dept_no)
  2  from employees;

MIN(SALARY)OVER(PARTITIONBYDEPT_NO)
-----------------------------------
                                250
                                250
                                220
                                220

SQL> select min(salary) from employees;

MIN(SALARY)
-----------
        220

 

5-15) PERCENTILE_CONT 함수
--------------------------------------------------------------------------------

이 함수는 연속 모델에 대한 inverse distribution function이다.

【형식】
PERCENTILE_CONT(expr) WITHIN GROUP (ORDER BY expr [{DESC|ASC}])
  [OVER (query_partition_cluause)]

【예제】
SQL> select dept_no,percentile_cont(0.5) within group
  2  (order by salary DESC)
  3  from employees GROUP BY dept_no;

   DEPT_NO PERCENTILE_CONT(0.5)WITHINGROUP(ORDERBYSALARYDESC)
---------- --------------------------------------------------
        10                                                250
        20                                                230

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

5-16) PERCENTILE_DISC 함수
--------------------------------------------------------------------------------

이 함수는 불연속 모델에 대한 inverse distribution function이다.

【형식】
PERCENTILE_DISC(expr) WITHIN GROUP (ORDER BY expr [{DESC|ASC}])
  [OVER (query_partition_cluause)]

【예제】
SQL> select dept_no,percentile_disc(0.5) within group
  2  (order by salary DESC)
  3  from employees GROUP BY  dept_no;

   DEPT_NO PERCENTILE_DISC(0.5)WITHINGROUP(ORDERBYSALARYDESC)
---------- --------------------------------------------------
        10                                                250
        20                                                240

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

 

5-17) PERCENT_RANK 함수
--------------------------------------------------------------------------------

이 함수는 CUME_DIST 함수와 유사하게 percent_rank 값을 반환한다.

【형식】
PERCENT_RANK(expr,...) WITHIN GROUP (ORDER BY expr [{DESC|ASC}]
  [NULLS {FIRST|LAST}],...)
또는
PERCENT_RANK() OVER( [query_partition_clause] order_by_clause)

【예제】
SQL> select percent_rank(230,0.05) within group
  2  (order by salary,bonus) from employees;

PERCENT_RANK(230,0.05)WITHINGROUP(ORDERBYSALARY,BONUS)
------------------------------------------------------
                                                   .25

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

 

5-18) RANK 함수
--------------------------------------------------------------------------------

이 함수는 그룹 내에서 위치를 반환한다.

【형식】
RANK(expr,...) WITHIN GROUP (ORDER BY expr [{DESC|ASC}]
  [NULLS {FIRST|LAST}],...)
또는
RANK() OVER( [query_partition_clause] order_by_clause)

【예제】
SQL> select rank(230,0.05) within group
  2 (order by salary,bonus) from employees;

RANK(230,0.05)WITHINGROUP(ORDERBYSALARY,BONUS)
----------------------------------------------
                                             2

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

 

5-19) REGR_(linear regression) function* 함수
--------------------------------------------------------------------------------

선형 회귀(linear regression) 함수 ordinary-least squares regression line을 찾도록 한다.
 사용되는 회귀함수는 자음 중 하나이다.
 REGR_SLOPE REGR_INTERCEPT REGR_COUNT
 REGR_R2 REGR_AVGX REGR_AVGY
 REGR_SXX REGR_SYY REGR_SXY

【형식】
REGR { REGR_SLOPE|REGR_INTERCEPT|REGR_COUNT|REGR_R2|REGR_AVGX|
       REGR_AVGY|REGR_SXX|REGR_SYY|REGR_SXY}
    (expr1,expr2) [OVER (analytic_clause)]

【예제】
SQL> select regr_slope(salary,bonus) from employees

REGR_SLOPE(SALARY,BONUS)
------------------------
              .533333333

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

5-20) STDDEV 함수
--------------------------------------------------------------------------------

이 함수는 standard deviation을 반환한다.

【형식】
STDDEV [{DISTINCT|ALL}] (expr) [OVER (analytic_clause)]

【예제】
SQL> select stddev(salary) from emp;

STDDEV(SALARY)
--------------
    14.1421356

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

 

5-21) STDDEV_POP 함수
--------------------------------------------------------------------------------

이 함수는 population standard deviation을 반환한다.

【형식】
STDDEV_POP (expr) [OVER (analytic_clause)]

【예제】
SQL> select stddev_pop(salary) from emp;

STDDEV_POP(SALARY)
------------------
        12.2474487

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

 

5-22) STDDEV_SAMP 함수
--------------------------------------------------------------------------------

이 함수는 cumulative sample standard deviation을 반환한다.

【형식】
STDDEV_SAMP (expr) [OVER (analytic_clause)]

【예제】
SQL> select stddev_samp (salary) from emp;

STDDEV_SAMP(SALARY)
-------------------
         14.1421356

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

5-23) SUM 함수
--------------------------------------------------------------------------------

이 함수는 합계를 반환한다.

【형식】
SUM ([{DISTINCT|ALL}] expr) [OVER (analytic_clause)]

【예제】
SQL> select sum(salary) from emp;

SUM(SALARY)
-----------
        960

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

5-24) VAR_POP 함수
--------------------------------------------------------------------------------

이 함수는 population variance를 반환한다.

【형식】
VAR_POP (expr) [OVER (analytic_clause)]

【예제】
SQL> select var_pop(salary) from emp;

VAR_POP(SALARY)
---------------
            150

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

 

5-25) VAR_SAMP 함수
--------------------------------------------------------------------------------

이 함수는 sample variance를 반환한다.

【형식】
VAR_SAMP (expr) [OVER (analytic_clause)]

【예제】
SQL> select var_samp(salary) from emp;

VAR_SAMP(SALARY)
----------------
             200

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

5-26) VARIANCE 함수
--------------------------------------------------------------------------------

이 함수는 variance를 반환한다.

【형식】
VARIANCE ([{DISTINCT|ALL}] expr) [OVER (analytic_clause)]

【예제】
SQL> select variance(salary) from emp;

VARIANCE(SALARY)
----------------
             200

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

5-27) Grouping sets 함수
--------------------------------------------------------------------------------

Grouping sets 함수는 Group by의 확장된 형태로 하나의 쿼리문에서 원하는 그룹핑 조건을 여러 개 기술할 수 있으며,
 grouping sets 함수 사용이 불가능한 이전 버전에서
 복잡한 union all 연산자를 사용하여 기술하던 것을 간단하게
 한 문장 안에서 해결할 수 있어 검색시 효율성이 증대 된다.
 다시 말해서, grouping sets 함수를 사용하면,
              group by ... union all을 사용한 것보다
              SQL 문이 간단해지고 또한 실행시 성능이 빨라진다.
【형식】
SELECT   컬럼명,그룹함수(컬럼명), GROUPING(컬럼명)
FROM  테이블명
WHERE  조건
GROUP BY [ROLLUP | CUBE] 그룹핑하고자하는 컬럼명, ...
  [GROUPING SETS (컬럼명,컬럼명, ...), ...]
HAVING  그룹조건
ORDER BY 컬럼명 또는 위치번호

【예제】
SQL> select grade,deptno,sum(salary)
  2  from aaa
  3  group by grouping sets(grade,deptno);

     GRADE     DEPTNO SUM(SALARY)
---------- ---------- -----------
         1                    900
         2                   1200
                   10         500
                   20         700
                   30         900

SQL> select grade,deptno,sum(salary)
  2  from aaa
  3  group by grouping sets((grade,name),(deptno,name));

     GRADE     DEPTNO SUM(SALARY)
---------- ---------- -----------
         1                    100
         1                    300
         1                    500
         2                    200
         2                    400
         2                    600
                   10         100
                   20         200
                   30         300
                   10         400
                   20         500
                   30         600

12 rows selected.

【예제】Union all을 사용한 경우
SQL> select grade,deptno,sum(salary)
  2  from aaa
  3  group by grade,deptno
  4  union all
  5  select grade,deptno,sum(salary)
  6  from aaa
  7  group by grade,deptno;

     GRADE     DEPTNO SUM(SALARY)
---------- ---------- -----------
         1         10         100
         1         20         500
         1         30         300
         2         10         400
         2         20         200
         2         30         600
         1         10         100
         1         20         500
         1         30         300
         2         10         400
         2         20         200
         2         30         600

12 rows selected.

[출처]http://huikyun.tistory.com