SQL Function - Window Aggregate (Analytics function)

Table of Contents

About

Windowing functions (known also as analytics) allow to compute:

They are distinguished from ordinary SQL functions by the presence of an OVER clause.

With windowing aggregate functions, you can calculate moving and cumulative versions of SUM, AVERAGE, COUNT, MAX, MIN, and many more functions.

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window.

Analytic functions enable you to calculate:

Analytic functions can be used in the following areas where standard SQL fails to impress.

  • Ranking data within subsets of a data set.
  • Aggregating data within subsets of a data set.
  • Performing aggregations over moving windows.
  • Displaying and comparing aggregates to individual entries within a single query.
  • Comparing two or more rows within a given data set.
  • Fetching from the translate table the values based on EFFDT.

Partition in the clause statement define the subset.

The data property dense/parse is really important when using this functions.

Analytic functions allow to replace procedural by set based logic.

True streaming platform (not microbatch) will handles late arriving data. The window is never really closed and some late arriving data with a timestamp inside the window can arrive at any time.

The process

Query processing using analytic functions takes place in three stages :

  1. all joins, WHERE, GROUP BY and HAVING clauses are performed.
  2. the result set is made available to the analytic functions, and all their calculations take place.
  3. if the query has an ORDER BY clause at its end, the ORDER BY is processed to allow for precise output ordering.

Functions

List of analytic functions

Reference


Data (State)
Data (State)
DataBase
Data Processing
Data Quality
Data Structure
Data Type
Data Warehouse
Data Visualization
Data Partition
Data Persistence
Data Concurrency

Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Modeling
Process
Logical Data Modeling
Relational Modeling
Dimensional Modeling
Automata

Data Type
Number
Time
Text
Collection
Relation (Table)
Cube
Tree
Key/Value
Graph
Spatial
Color
Log

Measure Levels
Order
Nominal
Discrete
Distance
Ratio

Code
Compiler
Lexical Parser
Grammar
Function
Testing
Debugging
Shipping
Data Type
Versioning
Design Pattern

Infrastructure
Operating System
Cryptography
Security
File System
Network
Process (Thread)
Computer
PerfCounter
Infra As Code

Marketing
Advertising
Analytics
Email

Web
Html
Dom
Http
Url
Css
Javascript
Selector
Browser
Web Services
OAuth

Contact
[email protected]
Privacy Policy
Status

Powered by ComboStrap