Analytic Functions
Analytic Functions let you apply grouping function to subset of record, you can also order the result set using specified values.
Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.
Analytic functions can appear only in the select list or ORDER BY clause.
OVER()
In the OVER clause you have to put the field-fields on such you apply the function (sum,count,ecc.)
If no arguments are specified , the set considered will be the same of the origin table.
Ex.
SELECT SUM(misura) OVER () … same as SELECT SUM(misura) …
You can also use the ORDER BY clause like an OVER function argument, to obtain an aggregated and ordinated set of data.
In the following example the result set will contain the sum of the field “misura”,ordered by the values of the fields dimensione_1, dimensione_2.
SELECT SUM(misura) OVER (ORDER BY dimensione_1, dimensione_2) …
The following example will return the sum of the field “venduto”, listed in order of the values of the fields anno, mese.
SELECT SUM(venduto) OVER (ORDER BY anno, mese) sc_venduto FROM vendite;
If you want to partition the query result set into groups based on one or more value, you have to use the PARTITION BY clause.
If you omit this clause, then the function treats all rows of the query result set as a single group.
The example will return the sum of the values of “venduto”, grouped by the values of “cd_negozio”
SELECT SUM(venduto) OVER(PARTITION BY cd_negozio) FROM vendite;
If you use both: ORDER BY and PARTITION BY, you can obtain an ordered and partitioned result set.
SELECT SUM(venduto) OVER(
PARTITION BY cd_negozio
ORDER BY anno, mese
) sc_venduto
FROM vendite;