Databases Reference
In-Depth Information
Ranking functions
Used to compute a record's rank with respect to other records. Functions include
RANK, DENSE_RANK, CUME_DIST, PERCENT_RANK, NTILE, and
ROW_NUMBER. Hypothetical ranking is also supported.
Windowing and Reporting aggregate functions
Used to compute cumulative and moving averages. Functions include SUM, AVG,
MIN, MAX, COUNT, VARIANCE, STDDEV, FIRST_VALUE, LAST_VALUE, and
RATIO_TO_REPORT.
LAG/LEAD functions
Often used to compare values from similar time periods, such as the first quarter
of 2013 and the first quarter of 2012.
Linear regression functions
Include REGR_COUNT, REGR_AVGX and REGR_AVGY, REGR_SLOPE,
REGR_INTERCEPT, REGR_R2, and other functions used in regression line fitting
for a set of numbers in pairs (e.g., having X and Y values).
Also supported in Oracle are pivoting operations, histograms (using WIDTH_BUCK‐
ET), CASE expressions, filling gaps in data, and time-series calculations.
The database includes a statistics package, DBMS_STATS_FUNCS. Functions in the
statistics package support linear algebra, frequent itemsets, descriptive statistics, hy‐
pothesis testing (T-test, F-test, Binomial test, Wilcoxon Signed Ranks test, One-Way
ANOVA, Chi-square, Mann Whitney, Kolmogorov-Smirnov), crosstab statistics (%
statistics, Chi-squared, phi coefficient, Cramer's V, contingency coefficient, and Cohen's
kappa), and nonparametric correlation (Pearson's correlation coefficients, and Spear‐
man's and Kendall's).
Other SQL Extensions
The SQL MODEL clause first appeared in Oracle Database 10
g
as an extension to the
SELECT statement. This clause enables relational data to be treated as multidimensional
arrays (much like spreadsheets) and is also used to define formulas for the arrays,
avoiding multiple joins and UNION clauses.
MODEL supports analytical queries that include prior-year comparisons and mathe‐
matical business rules and it is particularly useful in budgeting, forecasting, and other
statistical applications. Example MODEL usages include calculating sales differences in
two geographies, calculating percentage change, and calculating net present value. The
SQL MODEL clause can also use simultaneous equations and regression in calculations.
Oracle Database 12
c
introduces SQL pattern matching used in finding patterns in data
across multiple rows. It is particularly useful when looking for repeating sequences in
data, such as for when you might be trying to identify unusual behavior or are
