Example: Cumulative Maximum

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

The following SQL query might yield the results that follow it, where the cumulative maximum value for sales is returned for each partition defined by city and kind.

   SELECT city, kind, sales, week, 
   MAX(sales) OVER (PARTITION BY city, kind 
                    ORDER BY week ROWS UNBOUNDED PRECEDING)
   FROM activity_month;
   
   city     kind      sales  week  Cumulative Max(sales)
   -------  --------  -----  ----  ---------------------
   LA       Canvas      263    16                    263
   LA       Canvas      294    17                    294
   LA       Canvas      321    18                    321
   LA       Canvas      274    20                    321
   LA       Leather     144    16                    144
   LA       Leather     826    17                    826
   LA       Leather     489    20                    826
   LA       Leather     555    21                    826
   Seattle  Canvas      100    16                    100
   Seattle  Canvas      182    17                    182
   Seattle  Canvas       94    18                    182
   Seattle  Leather     933    16                    933
   Seattle  Leather     840    17                    933
   Seattle  Leather     899    18                    933
   Seattle  Leather     915    19                    933
   Seattle  Leather     462    20                    933