Window Function

Window function in SQL server

·       Aggregates function :- Avg,sum, count, min , max

·       Ranking function :- rank,dense rank,row number

·       Analytic function:- Lead, Lag, First value, last value

These function are called window Function and use with over clause. Over clause partioning and ordering the rows.

over clause accept following three function to define a window for these function to operate on

• order by
• partition by

CUME_DIST The CUME_DIST()function calculates the cumulative distribution of a value within a group of values.

Simply put, it calculates the relative position of a value in a group of values

As shown in the output, 50% of the sales staff have net sales greater than 285K.

NTILE() function: Ntile() is a window function that distributes rows of an ordered partition into a specified number of approximately equal groups, or buckets. It assigns each group a bucket number starting from one.

NTILE(buckets) OVER ( [PARTITION BY partition_expression, ... ] ORDER BY sort_expression [ASC | DESC], ... )

bucket= any number

PERCENT_RANK(): The PERCENT_RANK() function is similar to the CUME_DIST() function. The PERCENT_RANK() function evaluates the relative standing of a value within a partition of a result set.

PERCENT_RANK() OVER (

    [PARTITION BY partition_expression, ... ]

    ORDER BY sort_expression [ASC | DESC], ...

)

First value, last value

seSelect last_value(name)over(partition by dept order by salary asc RANGE BETWEEN  UNBOUNDED PRECEDING AND 

            UNBOUNDED FOLLOWING) as value_f,

*

from employee

NtNtile(): 

select ntile(3)over(partition by dept order by salary asc ) as value_f,* 

from employee