Spatial SQL: Code Reference

6.5 Windows

6.30

with taxis as (
    select
        sum(total_amount) as total,
        pickup_datetime :: date as date
    from
        nyc_yellow_taxi_0601_0615_2016
    group by
        pickup_datetime :: date
    order by
        pickup_datetime :: date asc
)
select
    date,
    total,
    avg(total) over (
        order by
            date rows between 2 preceding
            and current row
    )
from
    taxis

6.31

with taxis as (
    select
        sum(total_amount) as total,
        passenger_count,
        pickup_datetime :: date as date
    from
        nyc_yellow_taxi_0601_0615_2016
    group by
        pickup_datetime :: date,
        passenger_count
    order by
        pickup_datetime :: date asc,
        passenger_count desc
)
select
    date,
    total,
    passenger_count,
    sum(total) over (
        partition by passenger_count
        order by
            date rows between 2 preceding
            and current row
    )
from
    taxis

6.32

select
    avg(tip_amount / total_amount),
    extract(
        hour
        from
            pickup_datetime
    ) as hour_of_day
from
    nyc_yellow_taxi_0601_0615_2016
where
    pickup_datetime :: date = '2016-06-15'

    -- since we can't divide by 0 we will remove all amounts 
    -- that equal 0
    
    and total_amount > 0
group by
    extract(
        hour
        from
            pickup_datetime
    )
order by
    extract(
        hour
        from
            pickup_datetime
    ) asc

6.33

with taxis as (
    select
        avg(tip_amount / total_amount) as tip_percentage,
        date_trunc('hour', pickup_datetime) as day_hour
    from
        nyc_yellow_taxi_0601_0615_2016
    where
        total_amount > 5
    group by
        date_trunc('hour', pickup_datetime)
)
select
    day_hour,
    tip_percentage,
    avg(tip_percentage) over (
        order by
            day_hour asc rows between 5 preceding
            and current row
    ) as moving_average
from
    taxis

6.34

with taxis as (
    select
        sum(total_amount) as total,
        passenger_count,
        pickup_datetime :: date as date
    from
        nyc_yellow_taxi_0601_0615_2016
    group by
        pickup_datetime :: date,
        passenger_count
    order by
        pickup_datetime :: date asc,
        passenger_count desc
)
select
    date,
    total,
    passenger_count,
    sum(total) over (
        partition by passenger_count
        order by
            date
    )
from
    taxis

6.35

select
    row_number() over() as row_no,
    ogc_fid
from
    nyc_yellow_taxi_0601_0615_2016
limit
    5

6.36

select
    row_number() over(partition by pickup_datetime) as row_no,
    ogc_fid,
    pickup_datetime
from
    nyc_yellow_taxi_0601_0615_2016
limit
    5 offset 100000 -- using offset since the first part of the datasets has all passenger counts as 0

6.37

with taxis as (
    select
        sum(total_amount) as total,
        passenger_count,
        pickup_datetime :: date as date
    from
        nyc_yellow_taxi_0601_0615_2016
    group by
        pickup_datetime :: date,
        passenger_count
    order by
        pickup_datetime :: date asc,
        passenger_count desc
)
select
    date,
    total,
    passenger_count,
    total - lag(total, 1) over (
        partition by passenger_count
        order by
            date
    )
from
    taxis