SQL表包含有关出租车司机的数据,列有:
id integer
client_id integer (Foreign keyed to events.rider_id)
driver_id integer
city_id integer (Foreign keyed to cities.city_id)
client_rating integer
driver_rating integer
predicted_eta integer
actual_eta integer
first_completed_date Timestamp ,
status Enum(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)
如何计算过go 30天内完成的所有行程的实际预计预计到达时间之间的第90个百分位数的差异?
select
percentile_cont(0.90) within group (order by actual_eta-predicted_eta) as percentile_90_diff
from trips t where status='completed'
and first_completed_date > (first_completed_date - INTERVAL '30 DAY')::DATE
我关心的是最近30天内的最后一个部分--First_Complete_Date>;. 从First_Complete_Date开始计算最近30天的方法正确吗?