집계 함수
집계 함수#
여러 행에 대해 처리하여 단일 값을 반환하는 함수이다. 집계 함수는 SELECT 목록이나 ORDER BY 또는 HAVING 절 안에 나타날 수 있다.
SELECT 문에 GROUP BY 절이 포함되었다면 상수, 집계 함수(aggregate functions), GROUP BY 절에 명시된 표현식과 이들을 조합한 표현식만 SELECT 목록에 올 수 있다.
AVG#
구문#
AVG ( [ALL | DISTINCT] expression)
설명#
입력된 expression의 평균값을 구하는 함수이다. NULL은 계산에서 제외된다. 이 함수는 FLOAT 타입의 값을 반환한다.
예제#
<질의> 상품 테이블에서 평균 가격을 계산하여 출력하라.
SELECT AVG(price) FROM goods;
AVG(PRICE)
--------------
30406.173
1 row selected.
CORR#
구문#
CORR (expr1, expr2) OVER {...}
설명#
입력된 expr1와 expr2의 상관 계수(coefficient of correlation)를 구한다. 결과는 -1보다 크거나 같거나 1보다 작거나 같다. 0에 가까울수록 상관관계가 약하며 1에 가까울수록 상관 관계가 깊다. 반환 값의 타입은 DOUBLE이며 인자를 DOUBLE로 변환하여 계산한다.
CORR 함수는 집계 함수와 분석 함수로 사용할 수 있다.
예제#
<질의> 사원번호와 급여의 상관계수를 구한다.
SELECT CORR(ENO,SALARY) FROM employees;
CORR(ENO,SALARY)
-------------------------
-0.02180715597157
1 row selected.
COUNT#
구문#
COUNT ( [ * | [ALL | DISTINCT] expression ] )
설명#
질의에 의해 검색되는 행의 수를 반환하는 함수이다. 칼럼의 값이 널인 행은 계산 되지 않는다.
예제#
<질의> 사원 테이블의 전체 레코드의 개수를 출력하라.
SELECT COUNT(*) rec_count
FROM employees;
REC_COUNT
-----------------------
20
1 row selected.
<질의> 사원 테이블 생일 자료의 개수를 출력하라.
SELECT COUNT(birth) rec_count
FROM employees;
REC_COUNT
-----------------------
13
1 row selected.
COVAR_SAMP#
구문#
COVAR_SAMP (expr1, expr2) OVER {...}
설명#
입력된 expr1와 expr2의 표본 공분산(sample covariance)을 구한다. 한 개의 입력 값이 상승할 때 다른 값이 상승한다면 결과는 양수이며, 입력 값이 상승할 때 다른 값이 하락하면 결과는 음수이다. 반환 값의 타입은 DOUBLE이며, 인자를 DOUBLE로 변환하여 계산한다.
COVAR_SAMP 함수는 집계 함수와 분석 함수로 사용할 수 있다.
예제#
<질의> 사원번호와 급여의 표본 공분산을 구한다.
SELECT COVAR_SAMP(ENO,SALARY) FROM employees;
COVAR_SAMP(ENO,SALARY)
-------------------------
-95.0698529411784
1 row selected.
COVAR_POP#
구문#
COVAR_POP (expr1, expr2) OVER {...}
설명#
입력된 expr1와 expr2의 모공 분산(population covariance)을 구한다. 한 개의 입력 값이 상승할 때 다른 값도 상승한다면 결과는 양수이며, 입력 값이 상승할 때 다른 값이 하락하면 결과는 음수이다. 반환 값의 타입은 DOUBLE이며, 인자를 DOUBLE로 변환하여 계산한다.
COVAR_POP 함수는 집계 함수와 분석 함수로 사용할 수 있다.
예제#
<질의> 사원번호와 급여의 모공 분산을 구한다.
SELECT COVAR_POP(ENO,SALARY) FROM employees;
COVAR_POP(ENO,SALARY)
-------------------------
-89.4775086505208
1 row selected.
CUME_DIST#
구문#
설명#
CUME_DIST 함수는 결과 집합(또는 파티션)의 특정 멤버를 기준으로 정렬된 그룹의 누적분포도를 계산한다. 반환 값은 0보다 크고 1보다 작거나 같다.
주의: CUME_DIST의 인자 개수와 WITHIN GROUP절의 인자 개수는 일치해야 하며, 인자의 타입은 자동으로 호환되지 않는다. CUME_DIST인자의 데이터타입은 제한되지 않으나 상수사용을 권장한다.
예제#
<질의> CUME_DIST 함수를 사용하여 급여(SALARY)의 누적분포 값을 확인한다.
SELECT SALARY
FROM EMPLOYEES
ORDER BY 1;
SALARY
--------------
500
980
1000
1200
1400
1500
1700
1800
1800
1890
1900
2000
2003
2300
2500
2750
4000
...
20 rows selected.
SELECT CUME_DIST(1500) WITHIN GROUP (ORDER BY salary )
FROM employees;
CUME_DIST(1500) within group (order by SAL
---------------------------------------------
0.333333333333333
1 row selected.
FIRST#
구문#
설명#
ORDER BY에 의해 정렬된 데이터에 대해 첫 번째 부분만 aggregation하는 함수이다. aggregation_function에 사용할 수 있는 함수는 MIN, MAX, SUM, AVG, COUNT, VARIANCE, STDDEV 7가지이다.
예제#
<질의> 사원 테이블에서 부서별로 연봉을 가장 많이 받는 사원의 사원번호와 연봉을 구하라.
SELECT dno
, MAX(eno) KEEP(DENSE_RANK FIRST ORDER BY salary DESC) AS empno
, MAX(salary) AS max_sal
FROM employees
GROUP BY dno;
DNO EMPNO MAX_SAL
----------------------------------------
1001 16 2300
1002 6 1700
1003 10 4000
2001 17 1400
3001 4 1800
3002 1 2500
4001 8 1900
4002 20 1890
2 1500
9 row selected.
GROUP_CONCAT#
구문#
GROUP_CONCAT (expr1 [, arg1])
설명#
이 함수는 각 그룹에서 NULL이 아닌 expr1을 연결한 문자열을 반환한다.
arg1: 구분자 문자. 지정하지 않으면 문자열에 구분자가 삽입되지 않는다.
예제#
<질의> employees 테이블에서 각 부서별로 직원들의 성을 연결한 문자열을 반환하라.
SELECT dno
, CAST(GROUP_CONCAT(e_lastname, '|') AS VARCHAR (100)) AS names
FROM employees
GROUP BY dno;
DNO NAMES
-------------------------------------------------------------------------------------------------
1001 Kobain |Chen
1002 Momoi |Jones
1003 Bae |Liu |Miura |Davenport
2001 Fubuki
3001 Foster
3002 Moon |Ghorbani
4001 Wang |Diaz |Huxley
4002 Fleischer |Hammond |Marquez |Blake
Davenport
9 rows selected.
LAST#
구문#
설명#
ORDER BY에 의해 정렬된 데이터에 대해 마지막 부분만 aggregation하는 함수이다. aggregation_function에 사용할 수 있는 함수는 MIN, MAX, SUM, AVG, COUNT, VARIANCE, STDDEV이다.
예제#
<질의> 사원 테이블에서 부서별로 연봉을 가장 적게 받는 사원의 사원번호와 연봉을 구하라.
SELECT dno
, MIN(eno) KEEP(DENSE_RANK LAST ORDER BY salary DESC) AS empno
, MIN(salary) AS min_sal
FROM employees
GROUP BY dno;
DNO EMPNO MIN_SAL
-----------------------------------------
1001 3 2300
1002 13 1700
1003 15 4000
2001 17 1400
3001 4 1800
3002 5 2500
4001 9 1900
4002 7 1090
2 1500
9 row selected.
MAX#
구문#
MAX ([ALL | DISTINCT] expression)
설명#
입력된 expression 중에서 최대값을 구하는 함수이다.
예제#
<질의> 상품 테이블에서 가장 비싼 가격을 출력하라.
SELECT MAX(price) FROM goods;
MAX(PRICE)
--------------
100000
1 row selected.
MEDIAN#
구문#
MEDIAN ([ALL | DISTINCT] expression)
설명#
입력된 expression 중에서 중간 값(middle value; interpolated value)을 구하는 함수이다.
예제#
<질의> 상품 테이블의 중간 가격을 출력하라.
SELECT MEDIAN(price) FROM goods;
MEDIAN(PRICE)
----------------
9916.49
1 row selected.
MIN#
구문#
MIN ([ALL | DISTINCT] expression)
설명#
입력된 expression 중에서 최소값을 구하는 함수이다.
예제#
<질의> 상품 테이블의 가장 싼 가격을 출력하라.
SELECT MIN(price) FROM goods;
MIN(PRICE)
--------------
966.99
1 row selected.
PERCENT_RANK#
구문#
설명#
PERCENT_RANK 함수는 결과 집합(또는 파티션)의 특정 멤버를 기준으로 백분율순위를 매긴다. 반환 값은 0에서 1까지의 백분율 순위이다.
주의: PERCENT_RANK의 인자 개수와 WITHIN GROUP절의 인자 개수는 일치해야 하며, 인자의 타입은 자동으로 호환되지 않는다. PERCENT_RANK인자의 데이터타입은 제한되지 않으나 상수사용을 권장한다.
예제#
<질의> 부서번호(DNO)가 1003이고 급여(SALARY)가 1000인 직원의 백분율순위를 출력한다.
SELECT dno
, salary
FROM employees
ORDER BY 1, 2;
DNO SALARY
---------------------------
1001 2000
1001 2300
1002 980
1002 1700
1003 1000
1003 2003
1003 2750
1003 4000
2001 1400
3001 1800
3002 2500
3002
4001 1200
4001 1900
4001
4002 500
4002 1800
4002 1890
4002
1500
20 rows selected.
SELECT PERCENT_RANK(1003, 1000) WITHIN GROUP (ORDER BY dno, salary )
FROM EMPLOYEES;
RNK
-------------------------
0.2
1 row selected.
STATS_ONE_WAY_ANOVA#
구문#
설명#
STATS_ONE_WAY_ANOVA 함수는 일원 분산 분석(One-way ANOVA) 함수로써, 3번째 인자에 대한 값만 float 타입으로 반환한다.
이 함수는 3개의 인자를 가지며, 3번째 인자가 생략되면 SIG로 동작한 값이 반환된다. expr1은 expr2를 그룹화하는 칼럼이며, expr2는 expr1에 속하는 수치 데이터이다.
3번째 인자에 대한 설명은 아래의 표와 같다.
| 반환값 | 설명 |
|---|---|
| SIG | Significance |
| F_RATIO | 그룹간의 평균 제곱에 대하여 그룹내의 평균 제곱의 비율 |
| MEAN_SQUARES_WITHIN(MSW) | 그룹내의 평균 제곱 |
| MEAN_SQUARES_BETWEEN(MSB) | 그룹간의 평균 제곱 |
| DF_WITHIN(DFW) | 그룹 내 자유도 |
| DF_BETWEEN(DFB) | 그룹간의 자유도 |
| SUM_SQUARES_WITHIN(SSW) | 그룹 내 제곱의 합 |
| SUM_SQUARES_BETWEEN(SSB) | 그룹 사이의 제곱의 합 |
예제#
SELECT * FROM t3;
ID VALUE
---------- ----------
1 1
1 2
1 3
2 3
2 4
SELECT STATS_ONE_WAY_ANOVA(id, value, 'SUM_SQUARES_BETWEEN') SSB
, STATS_ONE_WAY_ANOVA(id, value, 'SUM_SQUARES_WITHIN') SSW
, STATS_ONE_WAY_ANOVA(id, value, 'DF_BETWEEN') DFB
, STATS_ONE_WAY_ANOVA(id, value, 'DF_WITHIN') DFW
, STATS_ONE_WAY_ANOVA(id, value, 'MEAN_SQUARES_BETWEEN') MSB
, stats_one_way_anova(id, value, 'MEAN_SQUARES_WITHIN') MSW
, STATS_ONE_WAY_ANOVA(id, value, 'F_RATIO') F
, STATS_ONE_WAY_ANOVA(id, value, 'SIG') P_VALUE
FROM t3;
SSB SSW DFB DFW MSB MSW F P_VALUE
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
2.7 2.5 1 3 2.7 .833333333 3.24 .169679927
STDDEV#
구문#
STDDEV ([ALL | DISTINCT] expression)
설명#
STDDEV는 입력된 expression 들의 표준편차를 반환한다.
시스템 호출에 의한 반복적인 실수 연산의 오차 누적에 따라 반환 값의 오차가 발생할 수 있다.
예제#
<질의> 직원 테이블에서 급여의 표준편차를 구하라.
SELECT STDDEV(salary) standard_deviation
FROM employees;
STANDARD_DEVIATION
-------------------------
797.706786762566
1 row selected.
STDDEV_POP#
구문#
STDDEV_POP (expression)
설명#
입력된 expression 의 NULL이 아닌 모든 행에 대하여 표준편차를 반환한다.
NULL이 아닌 행의 개수가 1보다 작으면 NULL을 반환한다. 시스템 호출에 의한 반복적인 실수 연산의 오차 누적에 따라 반환 값의 오차가 발생할 수 있다.
예제#
<질의> 직원 테이블에서 급여의 표준편차를 구하라.
SELECT STDDEV_POP(salary) FROM employees;
STDDEV_POP(SALARY)
-------------------------
773.889256492736
1 row selected.
STDDEV_SAMP#
구문#
STDDEV_SAMP ([ALL | DISTINCT] expression)
설명#
입력된 expression의 NULL이 아닌 모든 행에 대하여 표본 표준편차를 반환한다. NULL이 아닌 행의 개수가 2보다 작으면 NULL을 반환한다.
시스템 호출에 의한 반복적인 실수 연산의 오차 누적에 따라 반환 값의 오차가 발생할 수 있다.
예제#
<질의> 직원 테이블에서 급여의 표본 표준편차를 구하라.
SELECT STDDEV_SAMP(salary) FROM employees;
STDDEV_SAMP(SALARY)
-------------------------
797.706786762566
1 row selected.
SUM#
구문#
SUM ([ALL | DISTINCT] expression)
설명#
입력된 expression의 합을 구하는 함수이다.
예제#
<질의> 상품 테이블에서 모든 보관 수량의 합을 구하라.
SELECT SUM(stock) FROM goods;
SUM(STOCK)
-----------------------
379420
1 row selected.
VARIANCE#
구문#
VARIANCE ([ALL | DISTINCT] expression)
설명#
VARIANCE는 입력된 expression들의 변동량을 반환한다.
시스템 호출에 의한 반복적인 실수 연산의 오차 누적에 따라 반환 값의 오차가 발생할 수 있다.
예제#
<질의> 직원 테이블에서 급여의 변동량을 구하라.
SELECT VARIANCE(salary) variance
FROM employees;
VARIANCE
-------------------------
636336.117647059
VAR_POP#
구문#
VAR_POP (expression)
설명#
입력된 expression의 NULL이 아닌 모든 행에 대하여 모집단 분산(population variance)을 반환한다.
NULL이 아닌 행의 개수가 1보다 작으면, NULL을 반환한다.
시스템 호출에 의한 반복적인 실수 연산의 오차 누적에 따라 반환 값의 오차가 발생할 수 있다.
예제#
<질의> 직원 테이블에서 급여의 모분산을 구하라.
SELECT VAR_POP(salary) FROM employees;
VAR_POP(SALARY)
-------------------------
598904.581314879
1 row selected.
VAR_SAMP#
구문#
VAR_SAMP (expression)
설명#
입력된 expression의 NULL이 아닌 모든 행에 대하여 표본 분산값을 반환한다.
NULL이 아닌 행의 개수가 2보다 작으면 NULL을 반환한다.
시스템 호출에 의한 반복적인 실수 연산의 오차 누적에 따라 반환 값의 오차가 발생할 수 있다.
예제#
<질의> 직원 테이블에서 급여의 표본 분산을 구하라.
SELECT VAR_SAMP(salary) FROM employees;
VAR_SAMP(SALARY)
-------------------------
636336.117647059
row selected.