Aggregation by Time
SQL has analytic functions that provide powerful ways of declaratively expressing different types of data aggregation. These are especially suitable for analyzing ordered data such as time series data.
Grouping by Time is also referred to as resampling.
Aggregate
Aggregating for the entire period can be done by simply using the appropriate time component of a date (datetime) field.
Cumulative Aggregate
Cumulative aggregates can be computed by using analytic functions and specifying the PARTITION BY and ORDER BY clauses of the OVER clause. The partitioning indicates the set of rows which should be aggregated while the ordering indicates the direction in which to cumulate the aggregates. The example below partitions by Region and orders by Year of Order Date.
Moving / Sliding Window Aggregate
The moving (sliding) window aggregates are computed similar to the cumulative aggregates described above but within a given window. In the example below, after partitioning and ordering, the set of data is further clipped by specifying a window to be (-1,0) which is relative row position w.r.t to the current row. Hence, it computes the average using one previous row and the current row.