#🔎 #BigQuery #日数差 #ウィンドウ関数 #ウィンドウフレーム

days_count.sql

#standaardSQL

select
    Location
    , datetime_diff(new_date, old_date, day) as days_diff
from(
    select
        -- *
        Date
        , Location
        , first_value(Date)over(
            partition by Location
            order by Date asc) as old_date
        , last_value(Date)over(
            partition by Location
            order by Date asc
            rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING ) as new_date 
    from 
        `pj.ds.weatherAUS`
    )
group by 
    Location
    , days_diff
;