选择15分钟窗口的数据- PostgreSQL
选择15分钟窗口的数据- PostgreSQL
提问于 2013-07-08 04:19:05
对，所以我在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-04-03 15:59:58
duration count
1 0
2 0
3 0
4 0
9 1时间戳= 2013-04-03 16:14:58
duration count
1 1
2 0
3 1
4 0
9 0时间戳= 2013-04-03 16:29:58
duration count
1 2
2 0
3 0
4 0
9 1等等。
因此，基本上它在15分钟窗口中循环时间戳，并输出不同的持续时间值及其频率(计数)。时间戳窗口开始值是窗口的最早时间戳(即时间戳窗口完成=时间戳窗口开始+ 15分钟)
这样我就可以画出15分钟的间隔直方图了。
我试着阅读，但这对我来说有点复杂，我没有太多的时间……
谢谢你的帮助！
回答 1
Stack Overflow用户
回答已采纳
发布于 2013-07-08 05:41:01
简单明了的方法：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简要说明：
使用duration
- Left join原始数据的唯一值，
- 计算最小和最大
- 交叉连接结果之间的最小和最大15分钟间隔(左连接很重要，因为这将在输出中保留所有可能的组合，并且对于给定的
null数据将存在持续时间不存在的情况。count(null)=0
如果您有更多的表，并且算法应该应用于它们的联合。假设我们有三个表，每个表都有列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子句。对于PostgreSQL中的任何数据分析来说，它都是一个无价的概念。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/17516000
复制相关文章
相似问题
腾讯云开发者
