Understanding OLAP evaluation

OLAP evaluation can be conceptualized as several phases of query execution that contribute to the final result. You can identify OLAP phases of execution by the relevant clause in the query. For example, if a SQL query specification contains window functions, the WHERE, JOIN, GROUP BY, and HAVING clauses are processed first. Partitions are created after the groups defined in the GROUP BY clause and before the evaluation of the final SELECT list in the query’s ORDER BY clause.

For the purpose of grouping, all NULL values are considered to be in the same group, even though NULL values are not equal to one another.

The HAVING clause acts as a filter, much like the WHERE clause, on the results of the GROUP BY clause.

Consider the semantics of a simple query specification involving the SQL statements and clauses, SELECT, FROM, WHERE, GROUP BY, and HAVING from the ANSI SQL standard:

1. The query produces a set of rows that satisfy the table expressions present in the FROM clause.

2. Predicates from the WHERE clause are applied to rows from the table. Rows that fail to satisfy the WHERE clause conditions (do not equal true) are rejected.

3. Except for aggregate functions, expressions from the SELECT list and in the list and GROUP BY clause are evaluated for every remaining row.

4. The resulting rows are grouped together based on distinct values of the expressions in the GROUP BY clause, treating NULL as a special value in each domain. The expressions in the GROUP BY clause serve as partition keys if a PARTITION BY clause is present.

5. For each partition, the aggregate functions present in the SELECT list or HAVING clause are evaluated. Once aggregated, individual table rows are no longer present in the intermediate result set. The new result set consists of the GROUP BY expressions and the values of the aggregate functions computed for each partition.

6. Conditions from the HAVING clause are applied to result groups. Groups are eliminated that do not satisfy the HAVING clause.

7. Results are partitioned on boundaries defined in the PARTITION BY clause. OLAP windows functions (rank and aggregates) are computed for result windows.