Database Reference
In-Depth Information
GROUPING_ID(expression [, expression ... ])
. Finds a GROUP
BY level for a particular row.
11.2.2
Enhancing Grouping Functions for Analysis
Analysis is used to calculate cumulative, moving, centered, and reporting
summary aggregate values often used in data warehouse environments.
Unlike aggregate functions, analytic functions return multiple rows for each
group. Each group of rows is called a
window
and is effectively a variable
group, consisting of a range of rows. The number of rows in a window can
be based on a specified row count or an interval such as a period of time.
Apart from the ORDER BY clause, analytic functions are always executed
at the end of a query statement.
The following functions allow analysis and thus analytics using tools
such as the windowing clause:
COUNT, SUM, AVG, MIN, and MAX.
FIRST_VALUE and LAST_VALUE.
STDDEV, VARIANCE, and CORR.
STDDEV_POP, VAR_POP, and COVAR_POP.
STDDEV_SAMP, VAR_SAMP, and COVAR_SAMP.
Let's examine syntax and demonstrate what Oracle means by analytics.
We will use a SUM function. In short, the SUM function adds things up,
and everyone knows what that means. We could use something like a STD-
DEV or VARIANCE function, but not everyone knows what those are. For
some, who cares? In Chapter 1, we built some data warehouse-type fact
and dimension tables. The SALES table is a fact table because it contains
facts about sales (a history of sales transactions). Thus the SALES table is
appropriate for some analysis of this nature.
Using the SUM function, let's examine total sales as shown in Figure
11.3.
COLUMN SALES FORMAT $999,990.00
SELECT SUM(SALE_PRICE) AS SALES FROM SALES;
