windowExpressions

Window Clause makes the function analyze only certain rows "around" the current row rather than all rows in the partition. For example: a moving average by specifying some number of preceding and following rows, or a running count or running total by specifying all rows up to the current position.

PARTITION BY clause acts much like the GROUP BY clause in the outermost block of a query. It divides the rows into groups containing identical values in one or more columns. These logical groups are known as partitions.

ORDER BY clause works much like the ORDER BY clause in the outermost block of a query. It defines the order in which rows are evaluated for the entire input set, or for each group produced by a PARTITION BY clause.

DENSE_RANK() Function Returns an ascending sequence of integers, starting with 1. The output sequence produces duplicate integers for duplicate values of the ORDER BY expressions. After generating duplicate output values for the "tied" input values, the function continues the sequence with the next higher integer. Therefore, the sequence contains duplicates but no gaps when the input contains duplicates.

FIRST_VALUE() Function Returns the expression value from the first row in the window. The return value is NULL if the input expression is NULL.

LAG() Function returns the value of an expression using column values from a preceding row. You specify an integer offset, which designates a row position some number of rows previous to the current row. Any column references in the expression argument refer to column values from that prior row. Typically, the table contains a time sequence or numeric sequence column that clearly distinguishes the ordering of the rows. Sometimes used an an alternative to doing a self-join.

Example:

select stock_symbol, closing_date, closing_price, lag(closing_price,1) over (partition by stock_symbol order by closing_date) as "yesterday closing" from stock_ticker order by closing_date;

select stock_symbol, closing_date, closing_price, cast( closing_price - lag(closing_price,1) over (partition by stock_symbol order by closing_date) as decimal(8,2) ) as "change from yesterday" from stock_ticker order by closing_date desc;

LAST_VALUE() Function Returns the expression value from the last row in the window. This same value is repeated for all result rows for the group.

LEAD() Function turns the value of an expression using column values from a following row. You specify an integer offset, which designates a row position some number of rows after to the current row. Any column references in the expression argument refer to column values from that later row. Typically, the table contains a time sequence or numeric sequence column that clearly distinguishes the ordering of the rows.

Example:

select stock_symbol, closing_date, closing_price, case (lead(closing_price,1) over (partition by stock_symbol order by closing_date)

    - closing_price) > 0
when true then "higher"
when false then "flat or lower"

end as "trending" from stock_ticker order by closing_date;

RANK() Function Returns an ascending sequence of integers, starting with 1. The output sequence produces duplicate integers for duplicate values of the ORDER BY expressions. After generating duplicate output values for the "tied" input values, the function increments the sequence by the number of tied values. Therefore, the sequence contains both duplicates and gaps when the input contains duplicates. Starts the sequence over for each group produced by the PARTITIONED BY clause.

ROW_NUMBER() Function Returns an ascending sequence of integers, starting with 1. Starts the sequence over for each group produced by the PARTITIONED BY clause. The output sequence includes different values for duplicate input values. Therefore, the sequence never contains any duplicates or gaps, regardless of duplicate input values.