For a true calendar-day window, aggregate to one row per department/day and supply missing dates, or use a date-bounded query. With duplicate dates, add a meaningful tie-breaker for a ROWS frame. Adding an ORDER BY or PARTITION BY column can change the result and must preserve the intended business semantics.
Part of the Teradata SQL Tuning and Query Rewriting guide.
Teradata employs two distinct approaches in Ordered Analytic Functions for preparing the data layout necessary for processing. This article explains both approaches and their respective advantages and disadvantages.
Teradata Ordered Analytical Functions
Teradata Analytic Functions are versatile tools that allow for a wide range of applications. The ability to retrieve previous and subsequent rows is critical in many queries.
The following query sums the current row and the two preceding rows within each department. It represents three calendar days only when there is exactly one row per department per date and there are no missing dates.
- The rows have to be sorted ascending by date
- The summation is carried out:
- The value of the current row is added to the sum of the previous row
- The row that falls outside the three-row frame is excluded. A date gap does not create an extra row.
All of these steps can be executed using a single SQL statement.
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 bookSELECT SUM(Amount) OVER (PARTITION BY Department_Id ORDER BY Sales_Date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS RunningSum
FROM Sales;
Teradata Ordered Analytical Functions & Performance Considerations
- Index eligibility for ordered analytics depends on the function, index definition and release. Use EXPLAIN to establish the access path rather than assuming every join or hash index is excluded.
- Distribution can use partition-key hashing or range-based processing, depending on the plan. Inspect EXPLAIN for the actual strategy; skew can increase spool use. The window PARTITION BY clause is distinct from physical row partitioning.
- Skew depends on the chosen distribution and the number and size of window groups.
The optimizer can choose different distribution strategies; neither guarantees even work across AMPs.
- Partition-key hashing is one possible strategy when there are enough groups. Group sizes and skew also matter; the number of groups relative to AMPs is not a universal plan-selection rule.
- Range-based processing can distribute work across partition and ordering keys. Confirm it in the plan rather than treating a low distinct-group count as a guaranteed trigger.
A Simple Trick to Reduce the Skewing
We can expand the PARTITION BY or ORDER BY clause by adding columns with more diverse values. However, the additional column should not alter the query’s meaning.
The historical example adds an ordering column. It may affect distribution, but it can also alter window results for tied dates. Compare the complete result set and total resource use; a smaller difference between minimum and maximum AMP I/O alone does not prove an improvement.
Original Query (Heavily Skewed)
SELECT SUM(Amount) OVER (PARTITION BY Customer_Id ORDER BY Due_Date ASC ) AS TotalSum
FROM Ranges;
MinAMPIO = 102
MaxAMPIO = 237
Optimized Query (Less Skewed)
SELECT SUM(Amount) OVER (PARTITION BY Customer_Id ORDER BY Due_Date ASC, QuiteUniqueColumn ) AS TotalSum
FROM Ranges;
MinAMPIO = 191
MaxAMPIO = 231
How to determine how the Rows are distributed?
If range distribution is utilized, the execution plan will display the statement “redistributed by value to all AMPs.”
Explain SELECT SUM(Amount) OVER (PARTITION BY Customer_Id ORDER BY Due_Date ASC, QuiteUniqueColumn ) AS TotalSum
FROM Ranges;
1) First, we lock DWHPRO.Ranges in TD_MAP1 for read on a reserved
RowHash to prevent global deadlock.
2) Next, we lock DWHPRO.Ranges in TD_MAP1 for read.
3) We do an all-AMPs STAT FUNCTION step in TD_MAP1 from DWHPRO.Ranges
by way of an all-rows scan with no residual conditions into Spool
5 (Last Use), which is assumed to be redistributed by value to all
AMPs in TD_Map1. The result rows are put into Spool 1
(group_amps), which is built locally on the AMPs. The size is
estimated with low confidence to be 293,656 rows (12,039,896
bytes). The estimated time for this step is 0.57 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.57 seconds.
Need help with query performance or platform costs?
Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.
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