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:

partition-by2

 

Ranking Functions

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):

ranking-functions2

Analytic Functions

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:

analytic-functions2

Leave a Reply