AdSense

Friday, 12 July 2013

List of Analytical functions in Oracle SQL

List of Analytical functions in Oracle SQL

AVG
CORR
COVAR_POP
COVAR_SAMP
COUNT
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE
LAG
LAST
LAST_VALUE
LEAD
MAX
MIN
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT

REGR_ (Linear Regression) Functions
ROW_NUMBER
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE

I do believe that whatever an analytic function does can be done by native SQL, with join and sub-queries. However, the same when done by analytic function is never slower, if not faster, when compared to native SQL.
Yes offcourse, I am not considering the amount of time that spent in coding the native SQLs, testing, debugging and tuning them. That is just an additional work.