Knowledge Drop

Window Functions in Looker

  • 7 May 2021
  • 3 replies
  • 2071 views

Userlevel 5
Badge
  • New Member
  • 168 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 two main ways to implement window functions in Looker:

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.                

 

 

 


3 replies

Userlevel 1

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:

 

Userlevel 5
Badge

@gregono2 good catch! :sweat_smile: 

 

There is a third way….which is to write the window function into a non-aggregate measure type (most often number or string type). This method isn’t very reliable though - changing the selected dimensions often causes the measure to fail with a SQL error because of the SQL grouping (as griffoff alluded to in a previous comment). 

 

The derived table method is more reliable because you have full control of the grouping in the subquery where the window function is defined. I’m going to edit the original post to correct my mistake!

 

Reply