每15分钟选择一次数据 - PostgreSQL

Question

每15分钟选择一次数据 - PostgreSQL

5

好的，我在PostgreSQL中有这样一个表：

timestamp              duration

2013-04-03 15:44:58    4
2013-04-03 15:56:12    2
2013-04-03 16:13:17    9
2013-04-03 16:16:30    3
2013-04-03 16:29:52    1
2013-04-03 16:38:25    1
2013-04-03 16:41:37    9
2013-04-03 16:44:49    1
2013-04-03 17:01:07    9
2013-04-03 17:07:48    1
2013-04-03 17:11:00    2
2013-04-03 17:11:16    2
2013-04-03 17:15:17    1
2013-04-03 17:16:53    4
2013-04-03 17:20:37    9
2013-04-03 17:20:53    3
2013-04-03 17:25:48    3
2013-04-03 17:29:26    1
2013-04-03 17:32:38    9
2013-04-03 17:36:55    4

我想获得以下输出：

时间戳窗口开始 = 2013-04-03 15:44:58

duration    count
1           0
2           1
3           0
4           1
9           0

时间戳窗口开始时间为2013年4月3日 15:59:58。

duration    count
1           0
2           0
3           0
4           0
9           1

时间戳窗口开始时间为2013年4月3日16时14分58秒。

duration    count
1           1
2           0
3           1
4           0
9           0

时间戳窗口开始时间为2013年4月3日16:29:58。

duration    count
1           2
2           0
3           0
4           0
9           1

基本上，它在15分钟的时间窗口内循环，并输出不同的持续时间值以及它们的频率（计数）。timestampwindowstart值是窗口的最早时间戳（即timestampwindowfinish = timestampwindowstart + 15分钟）。

这样我就可以绘制15分钟间隔的直方图了...

我已经尝试阅读，但对我来说有点复杂，而且时间不多...

感谢任何帮助！

- James Elder

好的，你可能没有时间，但是却期望别人投入他们宝贵的时间来解决你的问题。你至少可以使用你的数据创建一个 SQLfiddle 演示。 - Tomas Greif

我说我没有太多时间了，而且我已经花了很长时间试图理解这个问题，但是我无法解决... 关于 SQLfiddle 演示，不是每个人都使用 SQL fiddle，只有少数人帮助我进行了 SQL fiddle 演示，所以我没有考虑创建一个，我现在会做的，谢谢。 - James Elder

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Tomas Greif · Accepted Answer

快速而简单的方法：http://sqlfiddle.com/#!1/bd2f6/21。我将我的列命名为tstamp，而不是你的timestamp。

with t as (
  select
    generate_series(mitstamp,matstamp,'15 minutes') as int,
    duration
  from
    (select min(tstamp) mitstamp, max(tstamp) as matstamp from tmp) a,
    (select duration from tmp group by duration) b
)

select
  int as timestampwindowstart,
  t.duration,
  count(tmp.duration)
from
   t
   left join tmp on 
         (tmp.tstamp >= t.int and 
          tmp.tstamp < (t.int + interval '15 minutes') and 
          t.duration = tmp.duration)
group by
  int,
  t.duration
order by
  int,
  t.duration

简要说明：

1. 计算时间戳的最小值和最大值 2. 在最小值和最大值之间生成15分钟的时间间隔 3. 用持续时间的唯一值交叉连接结果 4. 左联接原始数据（左联接很重要，因为这将保留输出中所有可能的组合，并在给定间隔内不存在持续时间时出现null） 5. 聚合数据。 count(null)=0 如果您有更多的表格，并且该算法应用于它们的联合，则可以假设我们有三个表格tmp1, tmp2, tmp3，所有这些表格都具有列tstamp和duration。然后我们可以扩展先前的解决方案：

with 

tmpout as (
  select * from tmp1 union all
  select * from tmp2 union all
  select * from tmp3
)

,t as (
  select
    generate_series(mitstamp,matstamp,'15 minutes') as int,
    duration
  from
    (select min(tstamp) mitstamp, max(tstamp) as matstamp from tmpout) a,
    (select duration from tmpout group by duration) b
)

select
  int as timestampwindowstart,
  t.duration,
  count(tmp.duration)
from
   t
   left join tmpout on 
         (tmp.tstamp >= t.int and 
          tmp.tstamp < (t.int + interval '15 minutes') and 
          t.duration = tmp.duration)
group by
  int,
  t.duration
order by
  int,
  t.duration

在PostgreSQL中，with子句是一种非常重要的概念，对于任何数据分析来说都是不可或缺的。