Oracle tenants of OceanBase support some complex analytic functions which are generally used for summarizing the calculation results, moving data, centering data, and aggregating reports. The following table lists the analytic functions supported by Oracle tenants of OceanBase:
| Function | Description |
|---|---|
| AVG | Returns the average value. |
| SUM | Returns the sum of values provided. |
| MAX | Returns the largest value from the values provided. |
| MIN | Returns the smallest value from the values provided. |
| COUNT | Returns the count of values. |
| CUME_DIST | Returns the cumulative distribution of a specified value in a set. |
| RANK | Returns the non-consecutive ranking for the values. |
| DENSE_RANK | Returns the dense rank of a row among a group of rows. |
| PEERCENT_RANK | Returns the cumulative distribution of a value in a set of values in percentage. |
| FIRST_VALUE | Returns the first value in an ordered set of values. |
| LAST_VALUE | Returns the last value in an ordered set of values. |
| LAG | |
| LEAD | Operates on a group of rows to return a list of values. |
| LISTAGG | Concatenates values of a column into one string |
| NTH_VALUE | Returns the value of an expression from the Nth row of the window. |
| NTILE | Divides a result set into a specified number of groups. |
| RATIO_TO_REPORT | Returns the ratio of a specified value to the sum of values in the set. |
| ROW_NUMBER | Assigns a unique number to each row. |
| STDDEV | Returns the cumulative standard deviation. |
| STDDEV_POP | Returns the population standard deviation. |
| STDDEV_SAMP | Returns the sample standard deviation. |
| VARIANCE | Returns the variance from the selected parameter columns. |
Basic syntax for the complex analytic functions:
analytic_function ( [ arguments ] )
OVER
(
[
PARTITION BY { expr [, expr] ... }
[
ORDER [ SIBLINGS ] BY
{ expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
[, { expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
] ...
[
{ ROWS | RANGE }
{ BETWEEN
{ UNBOUNDED PRECEDING | CURRENT ROW | value_expr
{ PRECEDING | FOLLOWING }
} AND
{ UNBOUNDED FOLLOWING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING }
}
|
{ UNBOUNDED PRECEDING | CURRENT ROW| value_expr PRECEDING
}
}
]
]
)
Example: Using analytic functions to query data
SELECT * FROM (
SELECT o_w_id, o_d_id, o_ol_cnt, o_entry_d, rank() OVER (PARTITION BY o_w_id order BY o_entry_d) entry_order
FROM ordr
ORDER BY o_w_id , entry_order
) WHERE entry_order <=5;
SELECT * FROM (
SELECT o_w_id, o_d_id, o_ol_cnt, o_entry_d, dense_rank() OVER (PARTITION BY o_w_id order BY o_entry_d) entry_order
FROM ordr
ORDER BY o_w_id , entry_order
) WHERE entry_order <=5;
SELECT o_w_id, o_d_id, o_ol_cnt, o_entry_d, first_value(o_c_id) OVER (PARTITION BY o_w_id order BY o_entry_d) first_cid
FROM ordr
ORDER BY o_w_id ;
