Window functions:
1. When to use the window function? The window function is often used in conjunction with the aggregate function. Generally, the number of rows after the aggregate is less than the number of rows before the aggregate, but sometimes we want to display both the data before and after the aggregate, so we introduce the window function.
Such as: +——-+————-+——-+—————+–+ | name | orderdate | cost | sum_window_0 | +——-+————-+——-+—————+–+ | jack | 2017-01-01 | 10 | 205 | | jack | 2017-01-08 | 55 | 205 | | tony | 2017-01-07 | 50 | 205 | | jack | 2017-01-05 | 46 | 205 | | tony | 2017-01-04 | 29 | 205 | | tony | 2017-01-02 | 15 | 205 | | jack | 2017-02-03 | 23 | 23 | | mart | 2017-04-13 | 94 | 341 | | jack | 2017-04-06 | 42 | 341 | | mart | 2017-04-11 | 75 | 341 | | mart | 2017-04-09 | 68 | 341 | | mart | 2017-04-08 | 62 | 341 | | neil | 2017-05-10 | 12 | 12 | | neil | 2017-06-12 | 80 | 80 | +——-+————-+——-+—————+–
2. Window function syntax:
UDAF() over (PARTITION By col1, col2 order By col3) And..) AS column alias
Order By (PARTITION By, order By, order By, order By, order By, order By, order By, order By, order By, order By, order By)
Over () determines the aggregation scope of the aggregate function. By default, the aggregate function aggregates data in the entire window. The aggregate function is called once for each piece of data.
For example: Select name, orderDate, cost, sum(cost) over() from business;
4. Partition by clause: Partiton by clause is used to partition data, and paritition by can be used to aggregate data in an area.
For example, select name, orderDate, cost, sum(cost) over(partition by name) from business;
5. Order by
(1) Sort the data in the partition; (2) Determine which rows to aggregate (default is to aggregate from start to current row)
For example, select name, orderDate, cost, sum(cost) over(partition by name order by orderDate) from business;
Between the CURRENT ROW and the FOLLOWING data, we have UNBOUNDED data. Between the UNBOUNDED PRECEDING and the UNBOUNDED FOLLOWING
The data is partitioned by using the partition by clause. If you want finer dynamic partitioning of Windows, introduce window clauses.
Such as: select name, orderdate,cost, sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW) from business;
7. A few notes:
(1) Order by must be followed by partition by. (2) “Rows” must follow the Order by clause. (3) (partition by.. Distribute by) can be replaced by (distribute by.. Sort by..)