오라클 집계함수
- 다음은 오라클 데이터베이스에서 사용하는 주요 집계 함수 입니다.
함수 | 설명 | 사용 예제 | 결과 예시 | 주의사항 |
---|---|---|---|---|
COUNT | 행의 개수를 세는 함수 | SELECT COUNT(*) FROM employees; |
107 | NULL 값도 포함하여 카운트 |
SUM | 숫자 값의 합계 계산 | SELECT SUM(salary) FROM employees; |
691400 | NULL 값은 무시됨 |
AVG | 숫자 값의 평균 계산 | SELECT AVG(salary) FROM employees; |
6461.68224 | NULL 값은 계산에서 제외 |
MAX | 그룹 내 최대값 찾기 | SELECT MAX(hire_date) FROM employees; |
2000-04-21 | 날짜, 문자, 숫자 모두 적용 가능 |
MIN | 그룹 내 최소값 찾기 | SELECT MIN(salary) FROM employees; |
2100 | MAX와 마찬가지로 다양한 타입 적용 가능 |
LISTAGG | 문자열을 연결하여 한 행으로 표시 | SELECT LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) FROM employees; |
"John, Mary, ..." | 4000바이트 제한 주의 |
STDDEV | 표준편차 계산 | SELECT STDDEV(salary) FROM employees; |
3909.57975 | 통계 분석에 사용 |
VARIANCE | 분산 계산 | SELECT VARIANCE(salary) FROM employees; |
15284811 | 데이터 분포 분석용 |
단일 집계 함수 사용
```sql -- 전체 직원 수 조회 SELECT COUNT(*) AS total_employees FROM employees;
-- 평균 급여 조회 (소수점 2자리까지) SELECT ROUND(AVG(salary), 2) AS avg_salary FROM employees; ```
GROUP BY와 함께 사용
```sql -- 부서별 평균 급여 조회 SELECT
department_id, COUNT(*) AS emp_count, ROUND(AVG(salary), 2) AS avg_salary, MAX(salary) AS max_salary, MIN(salary) AS min_salary
FROM
employees
GROUP BY
department_id
ORDER BY
department_id;
```
HAVING 절로 필터링
```sql -- 평균 급여가 5000 이상인 부서만 조회 SELECT
department_id, AVG(salary) AS avg_salary
FROM
employees
GROUP BY
department_id
HAVING
AVG(salary) >= 5000
ORDER BY
avg_salary DESC;
```
- 고급 기능 예제
LISTAGG로 문자열 합치기
```sql -- 부서별 직원 이름 목록 생성 SELECT
department_id, LISTAGG(first_name, ', ') WITHIN GROUP (ORDER BY first_name) AS employees
FROM
employees
GROUP BY
department_id;
```
ROLLUP으로 소계 생성
```sql -- 부서별 + 전체 급여 합계 SELECT
department_id, SUM(salary) AS total_salary
FROM
employees
GROUP BY
ROLLUP(department_id);
```
분석 함수와 함께 사용
```sql -- 부서별 급여 비율 계산 SELECT
first_name, department_id, salary, ROUND(salary/SUM(salary) OVER (PARTITION BY department_id)*100, 2) || '%' AS salary_percent
FROM
employees;
```
- 주의사항
- NULL 처리: 대부분의 집계 함수는 NULL 값을 무시합니다. (COUNT(*) 제외)
- GROUP BY: SELECT 절에 집계 함수가 아닌 컬럼은 반드시 GROUP BY에 포함해야 합니다.
- 성능 문제: 대량 데이터에서 복잡한 집계 연산은 성능 저하를 일으킬 수 있습니다.
- HAVING vs WHERE: WHERE는 그룹화 전 필터링, HAVING은 그룹화 후 필터링입니다.
오라클 19c에서 제공하는 집계함수
APPROX_COUNT ( ( * | expr ) [ , 'MAX_ERROR' ] ) APPROX_COUNT_DISTINCT(expr) APPROX_COUNT_DISTINCT_AGG( detail ) APPROX_COUNT_DISTINCT_DETAIL( expr ) APPROX_MEDIAN( expr [ DETERMINISTIC ] [, { 'ERROR_RATE' | 'CONFIDENCE' } ] ) APPROX_PERCENTILE( expr [ DETERMINISTIC ] [, { 'ERROR_RATE' | 'CONFIDENCE' } ] ) WITHIN GROUP ( ORDER BY expr [ DESC | ASC ] ) APPROX_PERCENTILE_AGG(expr) APPROX_PERCENTILE_DETAIL( expr [ DETERMINISTIC ] ) APPROX_RANK ( expr [ PARTITION BY partition_by_clause ] [ ORDER BY order_by_clause DESC] ) APPROX_SUM ( ( * | expr )[ , 'MAX_ERROR' ] ) AVG([ DISTINCT | ALL ] expr) [ OVER(analytic_clause) ] COLLECT( [ DISTINCT | UNIQUE ] column [ ORDER BY expr ] ) CORR(expr1, expr2) [ OVER (analytic_clause) ] CORR_K(expr1, expr2 [, { COEFFICIENT | ONE_SIDED_SIG | ONE_SIDED_SIG_POS | ONE_SIDED_SIG_NEG | TWO_SIDED_SIG } ] ) CORR_S(expr1, expr2 [, { COEFFICIENT | ONE_SIDED_SIG | ONE_SIDED_SIG_POS | ONE_SIDED_SIG_NEG | TWO_SIDED_SIG } ] ) COUNT({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ] COVAR_POP(expr1, expr2) [ OVER (analytic_clause) ] COVAR_SAMP(expr1, expr2) [ OVER (analytic_clause) ] CUME_DIST(expr[,expr ]...) WITHIN GROUP (ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [, expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] ]... ) DENSE_RANK(expr [, expr ]...) WITHIN GROUP (ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [,expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] ]... ) aggregate_function KEEP ( DENSE_RANK FIRST ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [, expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] ]... ) [ OVER ( [query_partition_clause] ) ] GROUP_ID( ) GROUPING(expr) GROUPING_ID(expr [, expr ]...) JSON_ARRAYAGG( expr [ FORMAT JSON ] [ order_by_clause ] [ JSON_on_null_clause ] [ JSON_agg_returning_clause ] [ STRICT ] ) JSON_OBJECTAGG( [ KEY ] key_expr VALUE val_expr [ JSON_on_null_clause ] [ JSON_agg_returning_clause ] [ STRICT ] [ WITH UNIQUE KEYS ] ) aggregate_function KEEP(DENSE_RANK LAST ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [, expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] ]... ) [ OVER ( [query_partition_clause] ) ] LISTAGG( [ ALL | DISTINCT ] measure_expr [, ' delimiter ' ] [ listagg_overflow_clause ] ) [ WITHIN GROUP ( order_by_clause ) ] [ OVER ( [ query_partition_clause ] ) ] MAX([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ] MEDIAN(expr) [ OVER ( [ query_partition_clause ] ) ] MIN([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ] PERCENT_RANK(expr [, expr ]...) WITHIN GROUP (ORDER BY expr [ DESC | ASC ] [NULLS { FIRST | LAST } ] [, expr [ DESC | ASC ] [NULLS { FIRST | LAST } ] ]... ) PERCENTILE_CONT(expr) WITHIN GROUP (ORDER BY expr [ DESC | ASC ]) [ OVER (query_partition_clause) ] PERCENTILE_DISC(expr) WITHIN GROUP (ORDER BY expr [ DESC | ASC ]) [ OVER (query_partition_clause) ] RANK(expr [, expr ]...) WITHIN GROUP (ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [, expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] ]... ) REGR_SLOPE(expr1 , expr2) [ OVER (analytic_clause) ] REGR_INTERCEPT(expr1 , expr2) [ OVER (analytic_clause) ] REGR_COUNT(expr1 , expr2) [ OVER (analytic_clause) ] REGR_R2(expr1 , expr2) [ OVER (analytic_clause) ] REGR_AVGX(expr1 , expr2) [ OVER (analytic_clause) ] REGR_AVGY(expr1 , expr2) [ OVER (analytic_clause) ] REGR_SXX(expr1 , expr2) [ OVER (analytic_clause) ] REGR_SYY(expr1 , expr2) [ OVER (analytic_clause) ] REGR_SXY(expr1 , expr2) [ OVER (analytic_clause) ] STATS_BINOMIAL_TEST(expr1, expr2, p [, { 'TWO_SIDED_PROB' | 'EXACT_PROB' | 'ONE_SIDED_PROB_OR_MORE' | 'ONE_SIDED_PROB_OR_LESS' } ] ) STATS_CROSSTAB(expr1, expr2 [, { 'CHISQ_OBS' | 'CHISQ_SIG' | 'CHISQ_DF' | 'PHI_COEFFICIENT' | 'CRAMERS_V' | 'CONT_COEFFICIENT' | 'COHENS_K' } ] ) STATS_F_TEST(expr1, expr2 [, { { 'STATISTIC' | 'DF_NUM' | 'DF_DEN' | 'ONE_SIDED_SIG' } , expr3 | 'TWO_SIDED_SIG' } ] ) STATS_KS_TEST(expr1, expr2 [, { 'STATISTIC' | 'SIG' } ] ) STATS_MODE(expr) STATS_MW_TEST(expr1, expr2 [, { 'STATISTIC' | 'U_STATISTIC' | 'ONE_SIDED_SIG' , expr3 | 'TWO_SIDED_SIG' } ] ) STATS_ONE_WAY_ANOVA(expr1, expr2 [, { 'SUM_SQUARES_BETWEEN' | 'SUM_SQUARES_WITHIN' | 'DF_BETWEEN' | 'DF_WITHIN' | 'MEAN_SQUARES_BETWEEN' | 'MEAN_SQUARES_WITHIN' | 'F_RATIO' | 'SIG' } ] ) STATS_T_TEST_* STATS_WSR_TEST(expr1, expr2 [, { 'STATISTIC' | 'ONE_SIDED_SIG' | 'TWO_SIDED_SIG' } ] ) STDDEV([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ] STDDEV_POP(expr) [ OVER (analytic_clause) ] STDDEV_SAMP(expr) [ OVER (analytic_clause) ] SUM([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ] SYS_OP_ZONE_ID( [ [ schema. ] table. | t_alias. ] rowid [, scale ] ) SYS_XMLAGG(expr [, fmt ]) TO_APPROX_COUNT_DISTINCT(detail) TO_APPROX_PERCENTILE(detail, expr, 'datatype' [, { 'DESC' | 'ASC' | 'ERROR_RATE' | 'CONFIDENCE' } ]) VAR_POP(expr) [ OVER (analytic_clause) ] VAR_SAMP(expr) [ OVER (analytic_clause) ] VARIANCE([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ] XMLAGG(XMLType_instance [ order_by_clause ])