집계함수 (aggregate function)

오라클 집계함수

menu_book 집계함수란 무엇인가?

• 집계함수는 여러 행의 데이터를 한 개의 결과값으로 요약해주는 함수입니다.
• 마치 엑셀에서 합계나 평균을 구하는 것과 비슷한 개념입니다.

• 다음은 오라클 데이터베이스에서 사용하는 주요 집계 함수 입니다.

함수	설명	사용 예제	결과 예시	주의사항
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	데이터 분포 분석용

단일 집계 함수 사용

-- 전체 직원 수 조회
SELECT COUNT(*) AS total_employees 
FROM employees;

-- 평균 급여 조회 (소수점 2자리까지)
SELECT ROUND(AVG(salary), 2) AS avg_salary 
FROM employees;

GROUP BY와 함께 사용

-- 부서별 평균 급여 조회
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 절로 필터링

-- 평균 급여가 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로 문자열 합치기

-- 부서별 직원 이름 목록 생성
SELECT 
    department_id,
    LISTAGG(first_name, ', ') WITHIN GROUP (ORDER BY first_name) AS employees
FROM 
    employees
GROUP BY 
    department_id;

ROLLUP으로 소계 생성

-- 부서별 + 전체 급여 합계
SELECT 
    department_id,
    SUM(salary) AS total_salary
FROM 
    employees
GROUP BY 
    ROLLUP(department_id);

분석 함수와 함께 사용

-- 부서별 급여 비율 계산
SELECT 
    first_name,
    department_id,
    salary,
    ROUND(salary/SUM(salary) OVER (PARTITION BY department_id)*100, 2) || '%' AS salary_percent
FROM 
    employees;

1. 1. 주의사항

• NULL 처리: 대부분의 집계 함수는 NULL 값을 무시합니다. (COUNT(*) 제외)
• GROUP BY: SELECT 절에 집계 함수가 아닌 컬럼은 반드시 GROUP BY에 포함해야 합니다.
• 성능 문제: 대량 데이터에서 복잡한 집계 연산은 성능 저하를 일으킬 수 있습니다.
• HAVING vs WHERE: WHERE는 그룹화 전 필터링, HAVING은 그룹화 후 필터링입니다.

고급 집계 함수

• 오라클 19c 고급 집계 함수 가이드

함수	설명	사용 예제	결과 예시	적용 버전
APPROX_COUNT_DISTINCT	고유 값의 근사치 계산 (대용량 데이터에 최적화)	`SELECT APPROX_COUNT_DISTINCT(product_id) FROM sales;`	12578	12cR1 이상
APPROX_PERCENTILE	근사 백분위수 계산	`SELECT APPROX_PERCENTILE(0.5) WITHIN GROUP (ORDER BY salary) FROM employees;`	6500	12cR2 이상
JSON_ARRAYAGG	결과를 JSON 배열로 집계	`SELECT JSON_ARRAYAGG(employee_name) FROM employees;`	`["John","Mary",...]`	12c 이상
JSON_OBJECTAGG	결과를 JSON 객체로 집계	`SELECT JSON_OBJECTAGG(employee_id:salary) FROM employees;`	`{"100":24000,...}`	12c 이상
COVAR_POP, COVAR_SAMP	공분산 계산 (모집단/표본)	`SELECT COVAR_POP(salary, commission_pct) FROM emp;`	1250.25	10g 이상
CORR	상관계수 계산	`SELECT CORR(sales, profit) FROM biz_data;`	0.85	10g 이상
REGR_*	선형 회귀 함수군	`SELECT REGR_SLOPE(y, x) FROM points;`	1.25	10g 이상
STATS_MODE	최빈값 반환	`SELECT STATS_MODE(department_id) FROM employees;`	50	10g 이상
LISTAGG (향상된 기능)	중복 제거 옵션 추가	`SELECT LISTAGG(DISTINCT city, ', ') FROM locations;`	"Seattle, London,..."	19c 이상
COLLECT	중첩 테이블로 집계	`SELECT CAST(COLLECT(phone_number) AS phone_list_t) FROM employees;`	중첩 테이블 객체	10g 이상

• 고급 분석 함수 예제

패턴 매칭 분석 (MATCH_RECOGNIZE)

-- 주가 데이터에서 V자 패턴 찾기
SELECT *
FROM stock_prices
MATCH_RECOGNIZE (
  ORDER BY trade_date
  MEASURES
    STRT.trade_date AS start_date,
    DOWN.trade_date AS bottom_date,
    UP.trade_date AS end_date
  PATTERN (STRT DOWN+ UP+)
  DEFINE
    DOWN AS price < PREV(price),
    UP AS price > PREV(price)
);

다차원 집계 (CUBE)

-- 부서별, 직급별, 지역별 다차원 집계
SELECT 
    department_id, 
    job_id, 
    location_id,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM 
    employees
GROUP BY 
    CUBE(department_id, job_id, location_id);

롤링 윈도우 계산

-- 3개월 이동 평균 매출 계산
SELECT 
    month,
    sales,
    AVG(sales) OVER (ORDER BY month RANGE BETWEEN INTERVAL '1' MONTH PRECEDING AND INTERVAL '1' MONTH FOLLOWING) AS moving_avg
FROM 
    monthly_sales;

1. 1. JSON 처리 함수 예제

JSON 데이터 집계

-- JSON 배열로 결과 생성
SELECT 
    department_id,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id' VALUE employee_id,
            'name' VALUE first_name || ' ' || last_name,
            'salary' VALUE salary
        )
    ) AS employees
FROM 
    employees
GROUP BY 
    department_id;

1. 1. 머신러닝 함수 예제

선형 회귀 분석

-- 매출과 광고비의 관계 분석
SELECT 
    REGR_SLOPE(sales, ad_cost) AS slope,
    REGR_INTERCEPT(sales, ad_cost) AS intercept,
    REGR_R2(sales, ad_cost) AS r_squared
FROM 
    marketing_data;

1. 1. 성능 최적화 팁

1. **APPROX 함수** 사용: 정확한 값 대신 근사치가 필요한 대용량 데이터 분석 시 2. **MATERIALIZE 힌트**: 복잡한 집계 쿼리 성능 향상

   SELECT /*+ MATERIALIZE */ AVG(salary) FROM large_table;

3. **파티션 활용**: 대량 데이터는 파티션 단위로 집계

   SELECT month, SUM(sales)
   FROM partitioned_sales
   GROUP BY month;

오라클 19c는 기존 집계 함수의 성능을 개선하고, 빅데이터 분석을 위한 새로운 함수들을 도입했습니다. 실제 적용 시 실행 계획(EXPLAIN PLAN)을 확인하여 최적의 접근 방식을 선택하는 것이 중요합니다.

---

오라클 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 ])