Alternative for RANGE window framing (instead of ROWS)

Database
Enthusiast

Alternative for RANGE window framing (instead of ROWS)

Hello,

Many SQL dialects support RANGE in addition to ROWS in the window framing (where the limited are defined in terms of a value-based or range-based set of rows, rather than specific number/sequence of rows).

And I understand that Teradata does not currently support RANGE style of window framing... My question is about an alternative or work-around. Does anyone know of a way to do such framing, without RANGE keyword.

In our specific need, we would like to sum transactions by a rolling 10-minute window.

For example:

seconds     Transact_dttm  TransAmt  10min_Sum

3594886248  12/11 11:30:48       50         50

3594981063  12/12 13:51:03      100        100

3595072103  12/13 15:08:23      100        100

3595072186  12/13 15:09:47      100        200

3595505333  12/18 15:28:54      100        100

3595505335  12/18 15:28:56       50        150

3595505338  12/18 15:28:58       50        200

3595671565  12/20 13:39:25       25         25

3595671590  12/20 13:39:51       25         50

3595821367  12/22 07:16:08       50         50

3595821477  12/22 07:17:58       50        100

3595821563  12/22 07:19:23       50        150

3595821565  12/22 07:19:25       50        200

 

This was previously coded as:

    SELECT Trans_dttm,

    TransAmt,

    SUM(TransAmt)

    OVER (ORDER BY Seconds

    RANGE BETWEEN 600 and PRECEDING AND CURRENT ROW) as 10min_Sum;

If it were a date range or week range, I may attempt a "tally table" or EXPAND approach. But the number of seconds between the various transactions precludes this, I believe.

Any help anyone can provide would be EXTREMELY APPRECIATED!!

Thanks, Bk