Search notes:
Oracle: Use V$SQLFN_METADATA to query aggregate functions
It seems that
v$sqlfn_metadata can be used to query Oracle's aggregate functions.
select
-- fun.func_id,
fun.name,
case when fun.descr <> fun.name then fun.descr end descr,
fun.analytic,
a_1.datatype dt_1,
a_2.datatype dt_2,
a_3.datatype dt_3,
a_4.datatype dt_4,
fun.minargs, fun.maxargs,
count(*) cnt -- why are some functions entered twice?
from
v$sqlfn_metadata fun left join
v$sqlfn_arg_metadata a_1 on fun.func_id = a_1.func_id and a_1.argnum = 1 left join
v$sqlfn_arg_metadata a_2 on fun.func_id = a_2.func_id and a_2.argnum = 2 left join
v$sqlfn_arg_metadata a_3 on fun.func_id = a_3.func_id and a_3.argnum = 3 left join
v$sqlfn_arg_metadata a_4 on fun.func_id = a_4.func_id and a_4.argnum = 4
where
fun.aggregate = 'YES'
group by
-- fun.func_id,
fun.name,
case when fun.descr <> fun.name then fun.descr end,
fun.analytic,
a_1.datatype,
a_2.datatype,
a_3.datatype,
a_4.datatype,
fun.minargs, fun.maxargs
order by
fun.name;
On 19c, the query returns:
ANY_VALUE NO EXPR 1 1 1 APPROX_COUNT NO EXPR STRING 1 2 1 APPROX_COUNT_DISTINCT NO EXPR 1 1 1 APPROX_COUNT_DISTINCT_AGG NO BINARY 1 1 1 APPROX_COUNT_DISTINCT_DETAIL NO EXPR 1 1 1 APPROX_MEDIAN NO NUMERIC STRING 1 2 2 APPROX_PERCENTILE NO NUMERIC STRING 1 2 2 APPROX_PERCENTILE_AGG NO BINARY 1 1 2 APPROX_PERCENTILE_DETAIL NO EXPR 1 1 2 APPROX_RANK NO EXPR 1 1 1 APPROX_SUM NO EXPR STRING 1 2 2 APPROX_SUM NO EXPR 1 2 1 APPROX_SUM NO STRING 1 2 1 AVG NO NUMERIC 1 1 3 BITMAP_CONSTRUCT_AGG NO EXPR EXPR 1 2 1 BITMAP_OR_AGG NO EXPR 1 1 1 CORR YES EXPR EXPR 2 2 1 CORR_K NO EXPR EXPR STRING 3 3 1 CORR_S NO EXPR EXPR STRING 3 3 1 COUNT NO EXPR 1 1 1 COVAR_POP YES EXPR EXPR 2 2 3 COVAR_SAMP YES EXPR EXPR 2 2 3 CUME_DIST NO EXPR 1 0 1 CUME_DISTM NO 1 0 1 DENSE_RANK NO EXPR 1 0 1 DENSE_RANKM NO EXPR 1 0 1 FIRSTM NO 1 0 1 JSON_ARRAYAGG NO 4 0 1 JSON_OBJECTAGG NO 4 0 1 LISTAGG NO EXPR EXPR 1 2 1 MAX NO EXPR 1 1 1 MEDIAN NO NUMERIC 1 1 1 MIN NO EXPR 1 1 1 OPTANOVAONED STATS_ONE_WAY_ANOVA NO EXPR EXPR STRING 3 3 1 OPTCOVARP_OLD COVAR_POP YES EXPR EXPR 2 2 1 OPTCOVARS_OLD COVAR_SAMP YES EXPR EXPR 2 2 1 OPTFDCOVARP_OLD COVAR_POP YES EXPR EXPR 2 2 1 OPTFDCOVARS_OLD COVAR_SAMP YES EXPR EXPR 2 2 1 OPTFDSDV_OLD STDDEV NO NUMERIC 1 1 1 OPTFDVAR_OLD VARIANCE NO NUMERIC 1 1 1 OPTFFCOVARP_OLD COVAR_POP YES EXPR EXPR 2 2 1 OPTFFCOVARS_OLD COVAR_SAMP YES EXPR EXPR 2 2 1 OPTFFSDV_OLD STDDEV NO NUMERIC 1 1 1 OPTFFVAR_OLD VARIANCE NO NUMERIC 1 1 1 OPTFTESTD STATS_F_TEST NO 4 0 1 OPTSDV_OLD STDDEV NO NUMERIC 1 1 1 OPTTTESTIPD STATS_T_TEST_INDEP NO 4 0 1 OPTTTESTIUD STATS_T_TEST_INDEPU NO 4 0 1 OPTTTESTOD STATS_T_TEST_ONE NO 3 0 1 OPTTTESTPD STATS_T_TEST_PAIRED NO 3 0 1 OPTUAG User-defined Aggregate NO 1 0 1 OPTVAR_OLD VARIANCE NO NUMERIC 1 1 1 OPTWSRTESTD STATS_WSR_TEST NO EXPR EXPR STRING 3 3 1 PERCENTILE_CONT NO EXPR 1 1 1 PERCENTILE_DISC NO EXPR 1 1 1 PERCENT_RANK NO EXPR 1 0 1 PERCENT_RANKM NO EXPR 1 0 1 RANK NO EXPR 1 0 1 RANKM NO 1 0 1 REGR_AVGX YES EXPR EXPR 2 2 1 REGR_AVGY YES EXPR EXPR 2 2 1 REGR_COUNT YES EXPR EXPR 2 2 1 REGR_INTERCEPT YES EXPR EXPR 2 2 1 REGR_R2 YES EXPR EXPR 2 2 1 REGR_SLOPE YES EXPR EXPR 2 2 1 REGR_SXX YES EXPR EXPR 2 2 1 REGR_SXY YES EXPR EXPR 2 2 1 REGR_SYY YES EXPR EXPR 2 2 1 STATS_BINOMIAL_TEST NO EXPR EXPR NUMERIC STRING 4 4 1 STATS_CROSSTAB NO EXPR EXPR STRING 3 3 1 STATS_F_TEST NO EXPR EXPR STRING EXPR 4 4 1 STATS_KS_TEST NO EXPR EXPR STRING 3 3 1 STATS_MODE NO EXPR 1 1 1 STATS_MW_TEST NO EXPR EXPR STRING EXPR 4 4 1 STATS_ONE_WAY_ANOVA NO EXPR EXPR STRING 3 3 1 STATS_T_TEST_INDEP NO EXPR EXPR STRING EXPR 4 4 1 STATS_T_TEST_INDEPU NO EXPR EXPR STRING EXPR 4 4 1 STATS_T_TEST_ONE NO EXPR EXPR STRING EXPR 3 4 1 STATS_T_TEST_PAIRED NO EXPR EXPR STRING EXPR 3 4 1 STATS_WSR_TEST NO EXPR EXPR STRING 3 3 1 STDDEV NO NUMERIC 1 1 3 STDDEV_POP YES EXPR 1 1 1 STDDEV_SAMP YES EXPR 1 1 1 SUM SUM(DOUBLE) (Match_Recognize) NO NUMERIC 1 1 1 SUM SUM(FLOAT) (Match_Recognize) NO NUMERIC 1 1 1 SUM NO NUMERIC 1 1 4 SYS_OP_COUNTCHG NO 2 0 1 VARIANCE NO NUMERIC 1 1 3 VAR_POP YES EXPR 1 1 1 VAR_SAMP YES EXPR 1 1 1