Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 9.0.0 | 

Overview

Aggregate Function
Description

Aggregate function returns one result based on the group of rows. When the GROUP BY clause is included, a one-row aggregate result per group is returned. When the GROUP BY clause is omitted, a one-row aggregate result for all rows is returned. The HAVING clause is used to add a condition to the query which contains the GROUP BY clause.

Most aggregate functions can use DISTINCT. For the GROUP BY ... HAVING clause, see GROUP BY ... HAVING clause.

Analytic Function
Description

Analytic function calculates the aggregate value based on the result of rows. The analytic function is different from the aggregate function since it can return one or more rows based on the groups specified by the query_partition_clause after the OVER clause (when this clause is omitted, all rows are regarded as a group).

The analytic function is used along with a new analytic clause, OVER, for the existing aggregate functions to allow a variety of statistics for a group of specific rows.

Syntax

function_name ( [argument_list ] ) OVER (<analytic_clause>)

 

<analytic_clause>::=

     [ <query_partition_clause> ] [ <order_by_clause> ]

    

<query_partition_clause>::=

    PARTITION BY value_expr [, value_expr ]...

 

<order_by_clause>::=

    ORDER BY { expr | position | column_alias } [ ASC | DESC ]

        [, { expr | position | column_alias } [ ASC | DESC ] ] ...

  • <query_partition_clause>: Groups based on one or more value_expr. It uses the PARTITION BY clause to partition the query result.
  • <order_by_clause>: defines the data sorting method in the partition made by <query_partition_clause>. The result can be sorted with several keys. <When query_partition_clause> is omitted, the data is sorted within the overall result sets. Based on the sorting order, the function is applied to the column values of accumulated records, including the previous values.
Remark

Among the aggregate functions, the following analytic functions support the OVER analytic clause. For more description on examples of analytic functions, see the description of the corresponding function.