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
?