我有以下疑问
`SELECT t.client_id,
count(t.client_id) as trips_xdays
FROM trips t
JOIN users u ON t.client_id = u.usersid
WHERE t.city_id = 12
AND t.status = 'completed'
AND ( date_trunc('day',t.dropoff_at) - date_trunc('day',u.creationtime) <= 30 days, 0:00:00)
GROUP BY t.client_id`
当我试图将查询限制为
`select date_trunc('day',t.dropoff_at) - date_trunc('day',u.creationtime)
from trips t
inner join users u ON t.client_id = u.usersid`
它自己回复了30天,0:00:00的回复
关于如何正确查询以便将查询限制在
最佳答案
假设我们处理的是数据类型timestamp
,您可以简化:
SELECT t.client_id, count(t.client_id) AS trips_xdays
FROM trips t
JOIN users u ON t.client_id = u.usersid
WHERE t.city_id = 12
AND t.status = 'completed'
AND t.dropoff_at::date < u.creationtime::date + 30
GROUP BY 1;
到目前为止,一个简单的cast更短,您只需将
integer
添加到date
即可。或者对于稍有不同的结果和更快的执行:
...
AND t.dropoff_at < u.creationtime + interval '30 days'
最后一个表单可以更容易地使用普通索引。准确的说是30天。
关于sql - 在WHERE子句中减去日期,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/16977439/