Question

SQL query optimization for derived tables

  • 27 October 2021
  • 0 replies
  • 20 views

I’m trying to run SQL RANK() OVER function on top of my existing table to remove duplicated rows of BigQuery table.

Looks like I can do it only using the derived table like

```

derived_table: {
sql:
SELECT
*,
rank() over(partition by field1, field2, field3 order by timestamp desc) as rank
FROM myTable
;;
}

The problem is that Looker will always run this query before querying the required data for the widget which takes a lot of time in a column-based DB.

Is there a way to somehow optimise the derived table or the query to avoid double querying in Looker widgets? 

So instead of: 

SELECT field1, field2 FROM (
SELECT
*,
rank() over(partition by field1, field2, field3 order by timestamp desc) as rank
FROM myTable
) WHERE rank = 1

I’d get something like:

SELECT 
field1,
field2,
rank() over ... as rank
FROM myTable

?


0 replies

Be the first to reply!

Reply