Analytic features compute an combination worth on a gaggle of rows. Unlike combination features, analytic features return all of the rows in every group. Analytic features ignores NULL Values of their computation.The vital analytic features that are used continuously are listed under:
- Sum Analytic Function: The sum analytic operate calculates the sum of values in every group and returns the sum worth for all of the rows in that group. The sum analytic operate will also be used to seek out the cumulative sum of a price. The sum analytic operate ignores null values in its computation.
- Count Analytic Function: The rely operate is used to seek out the variety of rows or variety of values of a column in every group. If you specify a column or expression to the rely operate, then it ignores null values in any other case it wont ignore null values.
- Min & Max Analytic Functions: The min and max analytic features are used to seek out the minimal and most values of a column in every group.
- Lead Analytic Function: The lead analytic operate supplies entry to rows that are adopted by the the present row.
- Lag Analytic Function:The lag analytic operate supplies entry to rows that are preceded by the the present row.
- First Value Analytic Function: The first worth analytic operate is used to get the primary worth of a column in a gaggle. You should specify the type standards to decide the primary worth.
- Last Value Analytic Function: The final worth analytic operate is used to get the final worth of a column in a gaggle. You should specify the type standards to decide the final worth.
- Rank, Row Number and Dense Rank Analytic Functions: Used to rank the values of a column in every group.
The normal syntax of analytic features is
Analytic_function([column|expression])
OVER (PARTITION BY Group columns ORDER BY kind columns)