Functions Analytic

Introduction

You use analytic functions to determine values based on groups of values. For example, you can use this type of function to determine running totals, percentages, or the top result within a group.

Syntax

1. FIRST_VALUE ( scalar_expression ) OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )
2. LAST_VALUE ( scalar_expression ) OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )
3. LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )
4. LEAD ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] order_by_clause )
5. PERCENT_RANK( ) OVER ( [ partition_by_clause ] order_by_clause )
6. CUME_DIST( ) OVER ( [ partition_by_clause ] order_by_clause )
7. PERCENTILE_DISC ( numeric_literal ) WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] ) OVER ( [ <partition_by_clause> ] )
8. PERCENTILE_CONT ( numeric_literal ) WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] ) OVER ( [ <partition_by_clause> ] )