我有一个查询返回两个时间戳之间的所有点.如果我做一个特别大的时间片(比如1年),我可能会得到10000行.我希望能够要求一个分辨率(比如说1天)并让它们均匀间隔1天,并收到~365行.这是我现在的查询：
SELECT *
FROM checkins
WHERE serial=${serial} AND created_at BETWEEN ${startTimestamp} AND ${endTimestamp}
ORDER BY created_at DESC
LIMIT ${limit}
OFFSET ${offset}
关于使用Postgres的好策略的任何想法？
解决方法
假设你有PG 9.4这应该做的伎俩：
SELECT *
FROM checkins
JOIN (
-- The below returns 366 created_at values within the two time points,inclusive
SELECT precentile_disc(fraction/365.) WITHIN GROUP (ORDER BY created_at)
FROM checkins,generate_series(0,365) f(fraction)
WHERE serial = ${serial} AND created_at BETWEEN ${startTimestamp} AND ${endTimestamp}
) USING (created_at)
ORDER BY created_at DESC;
percentile_disc() function根据指定的分数从排序组中为您提供离散值,其值最接近提供的分数.与generate_series()结合使用时,您可以在分数[0.,0.004,0.008,…,1.]处获得一系列此类值.然后将这些值(created_at值,而不是分数)加回到checkins表中以获得最终结果.
对于旧版本的PG,您可以“手动”执行此操作：
SELECT *
FROM (
SELECT *,rank() OVER (ORDER BY created_at) AS rnk
FROM checkins
WHERE serial = ${serial} AND created_at BETWEEN ${startTimestamp} AND ${endTimestamp}
) sub
WHERE rnk % extract(day from ${endTimestamp} - ${startTimestamp}) = 1
ORDER BY created_at;
这为startTimestamp和endTimestamp之间的每一天提供了一行,所以如果它们相隔一年就会得到365行.
