标签
PostgreSQL , Oracle , PL/SQL , 聚合函数 , 自定义聚合函数
背景
Oracle的自定义聚合函数的定义方法,在创建函数是,使用AGGREGATE USING Clause关键词。
AGGREGATE USING Clause
Specify AGGREGATE USING to identify this function as an aggregate function, or one that evaluates a group of rows and returns a single row. You can specify aggregate functions in the select list, HAVING clause, and ORDER BY clause.
When you specify a user-defined aggregate function in a query, you can treat it as an analytic function (one that operates on a query result set). To do so, use the OVER analytic_clause syntax available for built-in analytic functions. See "Analytic Functions" for syntax and semantics.
In the USING clause, specify the name of the implementation type of the function. The implementation type must be an object type containing the implementation of the ODCIAggregate routines. If you do not specify schema, Oracle Database assumes that the implementation type is in your own schema.
Restriction on Creating Aggregate Functions
If you specify this clause, you can specify only one input argument for the function.
自定义的聚合函数,与普通聚合函数一样,可以用于聚合、KEEP等操作SQL中。