Aggregate Functions
집계 함수
- 집계 함수는 쿼리 대상 Table 혹은 View 의 모든 Row들 혹은 Group Row들에 대해 수행한 연산의 결과를
하나의 Row 로 반환한다.
(즉, n개 Row에 대한 단일 행 함수의 결과값의 개수는 1개 이다.)
- 집계 함수는 SELECT 쿼리에서 아래 Clause 들에 위치할 수 있다:
- SELECT LIST
- ORDER BY Clause
- HAVING Clause
- Windowing Clause
Aggregate Functions (집계 함수)
Summary
- 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 ])
Reference: Oracle, SQL Language Reference : 7 Functions, URL, 2023년 5월 25일 검색.
