We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


window_frame_clause and Max — Vertica Forum

window_frame_clause and Max

Going by the definition of Max function in vertica doc 

Syntax
expression ) OVER (   ... [ window_partition_clause ]   ... [ window_order_clause ]  ... [ window_frame_clause ] ) we need to also specify window_frame_clause       But if we look at the example :    SELECT employee_state, annual_salary,  MAX ( [ DISTINCT ] 
     MAX(annual_salary)          OVER(PARTITION BY employee_state ORDER BY employee_key) max,            annual_salary- MAX(annual_salary)          OVER(PARTITION BY employee_state ORDER BY employee_key) diff      FROM employee_dimension      WHERE employee_state = 'MA';    employee_state | annual_salary |  max   |  diff  
----------------+---------------+--------+---------   MA             |          1918 | 995533 | -993615   MA             |          2058 | 995533 | -993475   MA             |          2586 | 995533 | -992947   MA             |          2500 | 995533 | -993033   MA             |          1318 | 995533 | -994215   MA             |          2072 | 995533 | -993461   MA             |          2656 | 995533 | -992877   MA             |          2148 | 995533 | -993385   MA             |          2366 | 995533 | -993167   MA             |          2664 | 995533 | -992869  (10 rows)      Now as per document:     If you omit the window_frame_clause, the default window isRANGE UNBOUNDED PRECEDING AND CURRENT ROW.    In the above example shouldnt it be the case we  need to specify RANGE UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file