The functions consultations are functions that can only be invoked as part of a SELECT statement and only in some contexts. There are two different types of them, which are classified according to the way each one of them operates. The two types of query functions used in SQL are grouping functions , which can only appear in the SELECT element list, in HAVING, WINDOW or ORDER BY clauses , and OLAP functions , which can only appear in the list of elements in the SELECT, or in the ORDER BY.
Summary
[ hide ]
- 1 Types of functions
- 1 Grouping functions:
- 2 OLAP functions:
- 2 Use of windows
- 1 Syntax
- 2 Example
- 2.1 Example 1: Average sales over the last three months.
- 3 Clauses
- 3 See also
- 4 Bibliography
Types of functions
There are two types of reporting functions:
- Grouping functions, which computes a grouping operation or an aggregate function of a group from a grouped table.
- OLAP functions, which computes a range, a number of rows or a windowed aggregate function.
Grouping functions:
There are two types of grouping functions , the grouping operation and the aggregation functions. The only grouping operation that SQL uses is GROUPING (<column>), which returns a value of 1 to indicate an added row above the column referenced during the execution of a grouped query containing CUBE and ROLLUP, and returns 0 otherwise. The aggregation functions, meanwhile, are the already known COUNT (), SUM (), AVG (), MIN (), MAX (), EVERY (), ANY (), SOME ().
OLAP functions:
There are three types of OLAP functions: range functions, row number function, and windowed aggregation functions. The range functions compute the ordinal range of a row R within the partition of R as defined in the window descriptor, according to the order of those rows specified within the window descriptor. There are two variants of these range functions: RANK and DESERANK.
- RANKfunction : It is the rank or hierarchy that a tuple R occupies taking into account the tuples that precede it. The range if it finds two tuples in a tied position, assigns them the same number, for example 1, and the next one it finds assigns the number 3.
- DENSERANKfunction : The range of the tuple R is defined as the number of rows that precede and including the tuples that do not accept the order. Unlike the range, the DENSERANK (dense range) assigns position 1 for example to two tuples that are tied in the first position, and number 2 to the next one it finds.
- ROWNUMBERfunction : computes a sequential row number starting with 1 in the first tuple in each partition and according to the order of the tuples in the partition.
- Windowed aggregationfunctions : Compute an aggregate value (COUNT, SUM, AVG, etc.) in the same way as the aggregation function except that it computes beyond a group or grouped table.
Use of windows
A key element to understanding working with OLAP functions is the definition of windows in the Select statement. A window is a temporary data structure, it is used to define partitions and aggregation groups that are later used in OLAP functions .
It has two ways of defining itself:
- explicitly using a window definition or
- implicitly via an inline window specification.
Syntax
SELECT elements FROM tables WHERE conditions GROUP BY elements HAVING conditions WINDOW definition ORDER BY elements LIMIT nro OFFSET pos;
As you can see, the definition of windows is located after the Having in the Select statement and before the order by. In this part you can define as many windows as necessary separated by commas, each one with a name and its corresponding definition. A window definition can specify three components, each of
optional way:
- Partitioned.
- Ordering.
- Aggregation groups.
Example
Example 1: Average sales over the last three months.
SELECT d.territorial, d.month, d.sales, AVG (d.sales) OVER W1 as Average FROM Historical_Sales d WINDOW W1 AS (PARTITION BY d. Territory ORDER BY d.mes ASC ROWS 2 PRECEDING);
In the part “AVG (sales d.) OVER W1” as there is an aggregation function followed by the word OVER and the name of a window, it is the way to recognize that it is an OLAP function. The W1 window specification simply specifies the three elements that a window can contain. In this case the Partition clause indicates that the tuples delivered by the FROM clause must be assigned to partitions based on their territories. The Order clause indicates that the tuples are organized in ascending order by the month in each partition. The final line of the window specification defines a grouping group. In this example the grouping group consists of the current tuple and the two preceding ones in the partition, according to the ordering in the partition.
Clauses
WINDOW window_name AS (PARTITION BY columns ORDER BY columns ASC / DESC ROWS … / RANGE …);
- The PARTITION BYclause is written similar to GROUP BY, however this does not mean that one statement is the same as another. The difference is that the partitioner does not group all the elements found in a single tuple, but rather associates the tuple with a specific partitioner.
- The ORDER BYclause is similar to the ORDER BY clause that appears in the Select statement outside of the window specification. However, there are differences because the order by specified in the window does not visibly order the tuples, but rather sorts them to define the value of the OLAP function used by this window; Another difference is that the ordering within the window applies to each partitioning separately and not to all tuples like the other order by.
