Spatial SQL: Code Reference

6.4 Statistical functions

6.22

select
    corr(assesstot :: numeric, lotarea :: numeric)
from
    nyc_mappluto

6.23

select
    stddev_samp(lotarea :: numeric)
from
    nyc_mappluto
where
    borough = 'BK'

6.24

select
    var_samp(lotarea :: numeric)
from
    nyc_mappluto
where
    borough = 'BK'

6.25

select
    mode() within group (
        order by
            lotarea :: numeric desc
    )
from
    nyc_mappluto
where
    borough = 'BK'

6.26

select
    ogc_fid,
    tip_amount,
    percent_rank() over(
        order by
            tip_amount asc
    )
from
    nyc_yellow_taxi_0601_0615_2016
where
    pickup_datetime between '2016-06-10 15:00:00'
    and '2016-06-10 15:00:05'
    and tip_amount > 0

6.27

select
    ogc_fid,
    tip_amount,
    rank() over(
        order by
            tip_amount asc
    )
from
    nyc_yellow_taxi_0601_0615_2016
where
    pickup_datetime between '2016-06-10 15:00:00'
    and '2016-06-10 15:00:05'
    and tip_amount > 0

6.28

select
    ogc_fid,
    tip_amount,
    dense_rank() over(
        order by
            tip_amount asc
    )
from
    nyc_yellow_taxi_0601_0615_2016
where
    pickup_datetime between '2016-06-10 15:00:00'
    and '2016-06-10 15:00:05'
    and tip_amount > 0

6.29

select
    percentile_disc(0.25) within group (
        order by
            tip_amount
    ) as per_25,
    percentile_disc(0.5) within group (
        order by
            tip_amount
    ) as per_50,
    percentile_disc(0.75) within group (
        order by
            tip_amount
    ) as per_75
from
    nyc_yellow_taxi_0601_0615_2016
where
    pickup_datetime between '2016-06-10 15:00:00'
    and '2016-06-10 15:00:05'
    and tip_amount > 0