What is ROWS UNBOUNDED PRECEDING used for in Teradata?

Practical use-cases for ROWS UNBOUNDED PRECEDING

This clause isn't just a convenient tool for running totals - it forms the backbone of many other analytical scenarios that call for sequential row processing. Here are a few not-so-obvious situations where it's indispensable:

You've got ranked!

When it comes to assigning rankings or computing dense ranks, nothing works as seamlessly with the RANK() and DENSE_RANK() functions as this clause does. No competitors were harmed in the making of this leaderboard:

RANK() OVER (ORDER BY score DESC ROWS UNBOUNDED PRECEDING) -- 'Who's on first?'


Rolling with the changes

This clause is the go-to choice for rolling or moving average calculations. It can be used to establish the starting point of a calculation without constraints on the terminal position. This central role in dynamic calculation borders is what makes Santa's naughty-or-nice list possible:

AVG(sales) OVER (PARTITION BY store_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -- 'Toys were hotter than coal in June!'


Group therapy for counts

For cases where you need to reset counts at each partition (like during sessionization events), ROWS UNBOUNDED PRECEDING ensures no session gets shortchanged:

COUNT(event_id) OVER (PARTITION BY user_id ORDER BY event_time ROWS UNBOUNDED PRECEDING) -- 'No cookie left behind!'


Pro tips and advanced usage

If you think you've seen all there is to ROWS UNBOUNDED PRECEDING, hold your seats. Here's some bonus level game-play:

Bang for your byte

When dealing with hefty data volumes, be wary of performance overheads related to this clause. Proactively counter this with strategic partitioning and indexing.

Master of all bounds

ROWS UNBOUNDED PRECEDING isn't a lone wolf. It can pair well with the ROWS BETWEEN clause to create frames that have a mix of bound types:

SUM(revenue) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) -- 'Month two richer by month one'


Behind the scenes with Teradata

Given Teradata's unique database architecture, it spins ROWS UNBOUNDED PRECEDING differently internally, leading to noticeable performance efficiency.

From OLAP with love

Bundle the clause with other OLAP functions like LAST_VALUE() or FIRST_VALUE() to grab the pole position or the caboose from the window frames:

LAST_VALUE(sales) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) -- 'Looking backwards to move forward'
