Source: thenewstack.io
Using Window Functions in SQL
Window functions allow us to perform calculations on a subset of rows in a table, rather than the entire table. A window function performs a calculation across a set of rows that are related to the current row, based on a specified window of observations.
The code below shows a moving average calculation that uses a window function.
A row clause that specifies a frame with one row preceding and following the row will result in a frame with three observations in it — the row preceding the current row, the current row and the row following the current row.
The partition by clause separates the rows based on the stock symbol, the ordering clause arranges the rows within each stock partition in descending order of time, and the row clause specifies a window frame with five observations before the current row.
The code below shows a moving average calculation that uses a window function.
A row clause that specifies a frame with one row preceding and following the row will result in a frame with three observations in it — the row preceding the current row, the current row and the row following the current row.
The partition by clause separates the rows based on the stock symbol, the ordering clause arranges the rows within each stock partition in descending order of time, and the row clause specifies a window frame with five observations before the current row.
Related Articles
Community Partners
DevOps Careers