甚至用PostgreSQL进行数据采样

发布时间：2020-12-13 15:51:45 所属栏目：百科来源：网络整理

导读：我有一个查询返回两个时间戳之间的所有点.如果我做一个特别大的时间片(比如1年),我可能会得到10000行.我希望能够要求一个分辨率(比如说1天)并让它们均匀间隔1天,并收到~365行.这是我现在的查询： SELECT * FROM checkins WHERE serial=${serial} AND created

我有一个查询返回两个时间戳之间的所有点.如果我做一个特别大的时间片(比如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行.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!