Partition, Sort Over and Range
Window functions extend the regular SELECT query with a new OVER clause per selected expression, with three new different areas, handled by the following special builder bars:
- Partition – offers an alternative way of grouping data. Your field may aggregate data through a Group function, and you may select different GROUP BY fields in Partition, or “(all)”.
- Sort Over – offers an alternative way of sorting grouped data. Like Partition, your field may aggregate data through a Group function, and you may select different ORDER BY fields in Sort Over.
- Range – refines the Sort Over clause with from-to pointers.
Following query aggregates data with COUNT(*), but using different hidden GROUP BY columns, defined through the Partition fields:
Ranking functions are supported in Oracle (used here), SQL Server, DB2, PostgreSQL and Amazon Redshift. There are global functions that generate sequence numbers for your rows.
- RowNumber – similar to a running sum: it starts with 1 for the first row, 2 for second etc.
- Rank – similar for first row in a group with the same value, but keeps that value for all other rows within the same group.
- DenseRank – somehow similar, but it increments its previous value by 1 when detecting a new group.
- NTile(n=10) –
Switch to design mode the Ranking Functions demo query, that uses data from FruitsU demo query (a ten-rows dynamic virtual table with fruit names and quantity per fruit):
Analytic functions are supported in Oracle (used here), SQL Server, DB2, PostgreSQL and Amazon Redshift. Applied to a field, they may return the value of another row within the same data set, by relative or absolute position.
- Lag(offset=1) – returns the value that was in that column offset number of rows before (or Null if none). Lag(1) returns the value in the previous results row, Lag(3) three rows before.
- Lead(offset=1) – returns the value in that column offset number of rows after. Lead(1) returns the value in the next results row, Lag(3) three rows after.
- FirstValue – returns the value in the first row.
- LastValue – returns the value in the last row.
Switch to design mode the Analytic Functions demo query, that uses data from FruitsU demo query: