DWHPro Insights

Teradata OLAP Functions: A Systematic Approach to Understanding Window Aggregates

By Roland Wenzlofsky ·

Teradata OLAP Functions: A Systematic Approach to Understanding Window Aggregates

Are you familiar with the Teradata OLAP Functions in your data warehouse applications? Have you encountered a request to utilize them? You may have experienced difficulty comprehending their functionality or applicability to business issues they aim to address.

Have you ever questioned why your company’s official training material, which costs a significant amount of money, requires multiple printed pages to cover a topic you still don’t understand after completing it?

If you have encountered any of these situations, I can present you with a methodical approach to comprehending and handling these groups. Every window aggregate function we have encountered involves only five design choices.

Here they are:

  1. Over what population of data (the “where”-decision)?
  2. For which aggregation levels (the “partition”-decision)?
  3. Over what lag and what lead (the “preceding-following” decision)?
  4. What kind of aggregation with what intent (the “function” decision)?
  5. Over which attribute(s) (the “grouping” decision)?

The Five Decisions Behind Every Window Aggregate

WHERE filters input rows before window calculations. QUALIFY filters the resulting rows after the windows are evaluated. These are distinct stages; choose both deliberately.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Next, determine the appropriate levels of aggregation by utilizing the “partition by” option within the “over()” portion.

A window frame selects rows contributing to the calculation, not output columns. Define ORDER BY when order matters and use a deterministic tie-breaker for row-based frames. The SELECT list controls output columns.

The aggregate you select determines the outcome. It is important to note that the function you opt for is a technical solution to the semantics of the business problem, which may not correspond exactly to the implementation you ultimately choose.

Next, determine which attributes to exhibit in the result set. While your ability to create logical result sets is restricted, you can still opt to display or conceal columns, whether they have been transformed or not.

Each decision is interdependent, but there are varying levels of flexibility.

Testing the Approach on Sample Data

To validate my method and experiment with different decisions, generate and complete the table below with fictitious data. Then, modify a single decision each time and execute the subsequent window aggregation.

CREATE SET TABLE TABLE_A_S3  (A INTEGER, B INTEGER, C INTEGER, D INTEGER ) PRIMARY INDEX (A) ;
INSERT INTO TABLE_A_S3 VALUES (1, 70000, 0, 1);
INSERT INTO TABLE_A_S3 VALUES (2, 70000, 0, 2);
INSERT INTO TABLE_A_S3 VALUES (3, 70000, 0, 3);
INSERT INTO TABLE_A_S3 VALUES (4, 70000, 0, 4);
INSERT INTO TABLE_A_S3 VALUES (5, 70000, 0, 5);
INSERT INTO TABLE_A_S3 VALUES (6, 70000, 0, 6);
INSERT INTO TABLE_A_S3 VALUES (7, 70000, 0, 7);
INSERT INTO TABLE_A_S3 VALUES (8, 70000, 0, 8);
INSERT INTO TABLE_A_S3 VALUES (9, 70000, 0, 9);
INSERT INTO TABLE_A_S3 VALUES (10, 70000, 1, 1);
INSERT INTO TABLE_A_S3 VALUES (11, 70000, 1, 2);
INSERT INTO TABLE_A_S3 VALUES (12, 70000, 1, 3);
INSERT INTO TABLE_A_S3 VALUES (13, 70000, 1, 4);
INSERT INTO TABLE_A_S3 VALUES (14, 70000, 1, 5);
INSERT INTO TABLE_A_S3 VALUES (15, 70000, 1, 6);
INSERT INTO TABLE_A_S3 VALUES (16, 70000, 1, 7);
INSERT INTO TABLE_A_S3 VALUES (17, 70000, 1, 8);
INSERT INTO TABLE_A_S3 VALUES (18, 70000, 1, 9);

— Your basic Window aggregate

— Decision 1: where B BETWEEN 70000 AND 70001, second level: QUALIFY D <AVG(D) OVER (PARTITION BY B)

— Decision 2: PARTITION BY B, C / PARTITION BY B in QUALIFY

— Decision 3: unlimited, i.e. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Decision 4: AVG over a decimal expression when fractional averages are required; the audit observed integer AVG 4 versus decimal AVG 4.5000.

— Decision 5: display A,B,C,D

SELECT A,B,C,D,
 AVG(CAST(D AS DECIMAL(18,4))) OVER (PARTITION BY B,C
 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS mean_d
FROM TABLE_A_S3
WHERE B BETWEEN 70000 AND 70001
QUALIFY D < AVG(CAST(D AS DECIMAL(18,4))) OVER (PARTITION BY B);

;

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam