Knowledge Drop

Window Functions in Looker

  • 7 May 2021
  • 2 replies
  • 762 views

Userlevel 5
Badge
  • Looker Staff
  • 159 replies

Last tested: April 2021

 

How do I use window functions in Looker?

Window functions are a very powerful tool in SQL because they perform post-aggregation calculations. There are three main ways to implement window functions:

1: Use Table Calculations

Calculating after aggregations is exactly what table calculations excel at. Many common window functions in SQL can be replaced by table calcs like row, offset, or clever combinations thereof. Do keep in mind though, table calculations can only be performed on the data within the Data Tab of the Explore page!

2: Use a derived table

If table calculations don't provide what you need, you can always write a derived table with whatever SQL you want, including window functions.

 

 

This content is subject to limited support.                

 

 

 


2 replies

It would be great to add this functionality directly to lookml, it should be pretty feasible to use metadata to generate the window function clause and then not use it in the group by statement. 
the metadata could also force the inclusion of fields required for the partition statement and order by to make sense.

There are three main ways to implement window functions:

 

This is helpful. Thanks. But is there a third way? :grinning:

 

Reply