Skip to content
Advertisement

Get Other Columns based on window function maximum value, Bigquery

How to get the whole row or other column value for the same row for which window function in over clause gave output.

For ex.

with o as (
    select date from unnest(GENERATE_TIMESTAMP_ARRAY('2021-01-01 00:00:00',current_timestamp(),interval 1 hour)) as date
enter code here
), p as (
    select *,RAND()*100 as Number from o
), q as (
    select *,max(number) over(order by date) as best from p
    order by date
)
select * from q

Using the above query I get output as the best value which defined the maximum number above me when order by timestamp.

The output of the above column :

Query Output

I calculated the best value using the over function, but I also want the date column on which day it was best.

Advertisement

Answer

Maybe this one?

with o as (
    select date from unnest(GENERATE_TIMESTAMP_ARRAY('2021-01-01 00:00:00',current_timestamp(),interval 1 hour)) as date
), p as (
    select *,RAND()*100 as Number from o
), q as (
    select *,max(number) over(order by date) as best from p
)
select * except(date_new_best), max(date_new_best) over (order by date) as date_best 
from (
    select *, if(number=best, date, NULL) as date_new_best
    from q
)
order by date

enter image description here

10 People found this is helpful
Advertisement