Window Functions in Looker

Looker Tips & Resources Technical Tips & Tricks
,
1

Knowledge Drop

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.

2 Likes
2

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.

8 Likes
3

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

2 Likes
4

@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!

1 Like
5

Please find an example for a non-aggregate measure of type number below, and note the nested SUM that is required because window functions are executed after the GROUP BY clause.

  measure: dynamic_sales_value_window {
    description: "A measure containing the total sales value over a dynamic window."
    type: number
    sql: SUM(SUM(${TABLE}.sales_value)) OVER (PARTITION BY
      {% if product._is_selected %} ${product}, {% endif %} 
      {% if category._is_selected %} ${category}, {% endif %}
      {% if sales_date._is_selected %} ${sales_date}, {% endif %}
      1 -- helper if none of the above dimensions is selected
    )
    ;;
  }

This was only tested for BigQuery.

3 Likes
6

Hi @nicohein It’s great solution as for me. Have you thought about more generic case when you have to group by all selected fields except some specific dims? In other words I’m looking for solution for generating PARTITION BY statement for about 100 possible dimensions without few specific ones.

1 Like
7

Hi @Dmitri_S , thank you for your feedback and I am happy to hear that it was helpful. Indeed I have thought about this without finding a satisfying solution just yet.

I thought about two different approaches. The first is still hypothetical and a feature request with Looker. I am proposing set operations in Liquid and am currently collecting my thoughts here: https://github.com/nicohein/looker-exploration/blob/main/hypothetical_code/liquid.md

The second approach is to generate the required LookML via Python using (https://pypi.org/project/lkml) to extract all dimensions.

1 Like