postgresql - PostgreSQL 中的平均聚合时态数据库 10 分钟间隔-6ren

postgresql - PostgreSQL 中的平均聚合时态数据库 10 分钟间隔

转载作者：行者123 更新时间：2023-11-29 13:35:15

我有一个时间序列数据，我想通过平均函数对其执行聚合。聚合应以 10 分钟为间隔执行:(HH-1):50、HH:00、HH:10、HH:20、HH:30、HH:40 或者以 10 分钟为间隔:(HH-1) :51, HH:01, HH:11, HH:21, HH:31, HH:41。注:5点钟的小时平均值是(4):51、5:01、5:11、5:21、5:31、5:41值的平均值。

问题在于数据集包括时间上奇数和偶数频率的变量。因此，我不能仅通过瞬时应用奇数或偶数时间来为其分配聚合。我想知道如何解决这个问题。

我使用的代码:

select dt, average_temp from (  select  date_trunc('hour', dt + interval '10 minutes') dt, avg(ambtemp) over(  partition by date_trunc('hour', dt + interval '10 minutes')  ) average_temp  from n25_30 where extract(minute from dt) in (51, 01, 11, 21, 31, 41) and dt::timestamptz > '2007-09-29 23:59:00' AND dt < '2007-09-30 22:45:00') s  group by 1, 2 order by dt

select dt, average_temp from (  select  date_trunc('hour', dt + interval '10 minutes') dt, avg(ambtemp) over(  partition by date_trunc('hour', dt + interval '10 minutes')  ) average_temp  from n28_30 where extract(minute from dt) in (50, 00, 10, 20, 30, 40) and dt::timestamptz > '2007-09-29 23:59:00' AND dt < '2007-09-30 22:45:00') s  group by 1, 2 order by dt

示例数据:

330    1.78 2007-09-30 10:39:52
331    2.06 2007-09-30 10:41:52
332    1.90 2007-09-30 10:43:52
333    2.28 2007-09-30 10:45:52
334    1.89 2007-09-30 10:47:52
335    2.04 2007-09-30 10:49:52
336    2.31 2007-09-30 10:51:52
337    2.50 2007-09-30 10:53:52
338    2.29 2007-09-30 10:55:52
339    2.47 2007-09-30 10:57:52
340    2.48 2007-09-30 10:59:52
341    1.74 2007-09-30 11:01:52
342    1.70 2007-09-30 11:03:52
343    2.30 2007-09-30 11:05:52
344    3.01 2007-09-30 11:07:52
345    2.78 2007-09-30 11:09:52
346    2.90 2007-09-30 11:11:52
347    2.50 2007-09-30 11:13:52
348    2.58 2007-09-30 11:15:52
349    3.72 2007-09-30 11:17:52
350    3.29 2007-09-30 11:19:52
351    2.32 2007-09-30 11:21:52
352    2.86 2007-09-30 11:23:52
353    3.11 2007-09-30 11:25:52
354    3.04 2007-09-30 11:27:51
355    2.39 2007-09-30 11:29:51
356    2.68 2007-09-30 11:31:51
357    2.32 2007-09-30 11:33:52
358    2.33 2007-09-30 11:35:52
359    2.50 2007-09-30 11:37:51
360    2.55 2007-09-30 11:39:51
361    3.16 2007-09-30 11:41:51
362    3.24 2007-09-30 11:43:51
363    3.90 2007-09-30 11:45:51
364    5.13 2007-09-30 11:47:51
365    3.94 2007-09-30 11:49:51
366    3.18 2007-09-30 11:51:51
367    4.54 2007-09-30 11:53:51
368    5.78 2007-09-30 11:55:51
369    4.72 2007-09-30 11:57:51
370    3.66 2007-09-30 11:59:51
371    3.22 2007-09-30 12:01:51
372    3.38 2007-09-30 12:03:51
373    3.96 2007-09-30 12:05:51
374    4.82 2007-09-30 12:07:51
375    4.09 2007-09-30 12:09:51
376    5.56 2007-09-30 12:11:51
377    6.60 2007-09-30 12:13:52
378    5.36 2007-09-30 12:15:52
379    6.04 2007-09-30 12:17:51
380    5.56 2007-09-30 12:19:51
381    4.60 2007-09-30 12:21:52
382    4.08 2007-09-30 12:23:51
383    4.44 2007-09-30 12:25:51
384    5.23 2007-09-30 12:27:52
385    3.16 2007-09-30 12:29:52
386    3.10 2007-09-30 12:31:52
387    3.50 2007-09-30 12:33:52
388    3.38 2007-09-30 12:35:52
389    3.65 2007-09-30 12:37:52
390    3.68 2007-09-30 12:39:52
391    4.40 2007-09-30 12:41:52
392    4.80 2007-09-30 12:43:52
393    5.44 2007-09-30 12:45:52
394    4.52 2007-09-30 12:47:52
395    3.64 2007-09-30 12:49:52
396    3.42 2007-09-30 12:51:52
397    3.74 2007-09-30 12:53:52
398    4.78 2007-09-30 12:55:52
399    4.03 2007-09-30 12:57:52
400    3.78 2007-09-30 12:59:52
401    4.52 2007-09-30 13:01:52
402    4.02 2007-09-30 13:03:52
403    4.07 2007-09-30 13:05:52
404    3.78 2007-09-30 13:07:52
405    4.04 2007-09-30 13:09:52
406    4.00 2007-09-30 13:11:52
407    4.29 2007-09-30 13:13:52
408    4.57 2007-09-30 13:15:52
409    4.83 2007-09-30 13:17:52
410    5.24 2007-09-30 13:19:52
411    6.96 2007-09-30 13:21:52
412    6.83 2007-09-30 13:23:52
413    8.58 2007-09-30 13:26:09
414    6.34 2007-09-30 13:28:09
415    8.34 2007-09-30 13:30:09
416    7.14 2007-09-30 13:32:09
417    5.26 2007-09-30 13:34:09
418    5.04 2007-09-30 13:36:09
419    5.96 2007-09-30 13:38:09
420    5.71 2007-09-30 13:40:09
421    7.16 2007-09-30 13:42:09
422    6.50 2007-09-30 13:44:09
423    5.54 2007-09-30 13:46:09
424    4.76 2007-09-30 13:48:09
425    4.87 2007-09-30 13:50:09
426    4.73 2007-09-30 13:52:09
427    4.74 2007-09-30 13:54:09
428    4.14 2007-09-30 13:56:09
429    4.74 2007-09-30 13:58:09
430    5.52 2007-09-30 14:00:09
431    4.86 2007-09-30 14:02:09
432    5.20 2007-09-30 14:04:09
433    6.98 2007-09-30 14:06:09
434    5.43 2007-09-30 14:08:09
435    4.90 2007-09-30 14:10:09
436    5.06 2007-09-30 14:12:09
437    6.74 2007-09-30 14:14:09
438    5.56 2007-09-30 14:16:09
439    5.22 2007-09-30 14:18:09
440    5.44 2007-09-30 14:20:09
441    4.84 2007-09-30 14:22:09
442    4.84 2007-09-30 14:24:09
443    4.50 2007-09-30 14:26:09
444    5.98 2007-09-30 14:28:09
445    4.90 2007-09-30 14:30:09
446    5.22 2007-09-30 14:32:09
447    6.41 2007-09-30 14:34:09
448    5.20 2007-09-30 14:36:09
449    4.74 2007-09-30 14:38:09
450    5.54 2007-09-30 14:40:09
451    5.04 2007-09-30 14:42:09
452    4.78 2007-09-30 14:44:09
453    4.87 2007-09-30 14:46:09
454    5.95 2007-09-30 14:48:09
455    5.10 2007-09-30 14:50:09
456    5.18 2007-09-30 14:52:09
457    4.94 2007-09-30 14:54:09
458    4.94 2007-09-30 14:56:09
459    5.01 2007-09-30 14:58:09
460    5.12 2007-09-30 15:00:09
461    4.92 2007-09-30 15:02:09
462    5.34 2007-09-30 15:04:09
463    5.14 2007-09-30 15:06:09
464    5.07 2007-09-30 15:08:09
465    5.35 2007-09-30 15:10:09
466    5.24 2007-09-30 15:12:09
467    5.50 2007-09-30 15:14:09
468    6.51 2007-09-30 15:16:09
469    7.31 2007-09-30 15:18:09
470    6.99 2007-09-30 15:20:09
471    6.91 2007-09-30 15:22:09
472    5.69 2007-09-30 15:24:09
473    5.75 2007-09-30 15:26:09
474    6.08 2007-09-30 15:28:09
475    6.94 2007-09-30 15:30:09
476    5.60 2007-09-30 15:32:09
477    5.08 2007-09-30 15:34:09
478    4.66 2007-09-30 15:36:09
479    5.52 2007-09-30 15:38:09
480    4.48 2007-09-30 15:40:09

最佳答案

任务可以简化为:

1) 每 10 分钟选择第一个(最早的)值，但大于 '1 minute'(排除 13:30:09 之类的值)。

2) 在上一步的集合中获取每小时的平均值，偏移 '10 分钟'。

这个查询:

SELECT ambtemp,
       dt,
       row_number() OVER (PARTITION BY date_trunc('hour',dt), 
                                       date_part('minute',dt)::int / 10
                          ORDER BY dt) as rank
FROM your_table
WHERE date_part('minute',dt)::int % 10 > 0

将为您提供最早的值，但大于 '1 分钟' 每 10 分钟一次(hh:m1 或 hh:m2每 10 分钟一次)。

然后，使用第一个查询作为子查询:

SELECT date_trunc('hour',dt + interval '10 minutes') as hour,
       avg(ambtemp)
FROM (first query here) sub
WHERE rank = 1
GROUP BY 1;

更新:这是一个完整的查询:

SELECT date_trunc('hour',dt + interval '10 minutes') as hour,
       avg(ambtemp)
FROM (SELECT ambtemp,
             dt,
             row_number() OVER (PARTITION BY date_trunc('hour',dt), 
                                             date_part('minute',dt)::int / 10
                                ORDER BY dt) as rank
      FROM table1

      -- delete this WHERE to get (HH-1):50, HH:00, HH:10, HH:20, HH:30, HH:40
      -- with this WHERE it gets  (HH-1):51, HH:01, HH:11, HH:21, HH:31, HH:41
      WHERE date_part('minute',dt)::int % 10 > 0          
     ) sub
WHERE rank = 1
GROUP BY 1
ORDER BY 1;

这里是 SQLFiddle使用您的示例数据。

上面的查询是针对 (HH-1):51, HH:01, HH:11, HH:21, HH:31, HH:41。如果您需要查询 (HH-1):50, HH:00, HH:10, HH:20, HH:30, HH:40 只需删除标记的行。

我已经测试了查询，它返回了正确的结果(对于 (HH-1):50, HH:00, ... 查询变体，它们与您的示例结果相同)。

关于postgresql - PostgreSQL 中的平均聚合时态数据库 10 分钟间隔，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/14342830/

文章推荐： php - 如何对 2 个字段使用 WHERE 和 AND？

文章推荐： javascript - 卸载某些页面的js请求

文章推荐： java - MYSQL拆分字符串并将单个单词插入数据库

文章推荐： ruby-on-rails - 从 Rails 将 float 保存到 PostgreSQL 时出错

日期中的 SQL 间隔
我试图根据表格看起来像这样的状态代码来查找表格中的空白。状态表: StateID (PK) | Code -------------------- 1 | AK 2
python - 如何按相反顺序找到两个字母之间的长度/间隔？
我有一个配对字符串列表。我想找到两个字母之间的长度/间隔。到目前为止，我可以使用找到有序字母的间隔 alpha =["AM", "KQ", "ZN", "XM", "UK"] leng
python - 如何按相反顺序找到两个字母之间的长度/间隔？
我有一个配对字符串列表。我想找到两个字母之间的长度/间隔。到目前为止，我可以使用找到有序字母的间隔 alpha =["AM", "KQ", "ZN", "XM", "UK"] leng
jquery TimePicker 间隔
我正在努力弄清楚如何将时间选择器的下拉间隔设置为 15 分钟间隔。默认为 30 分钟 atm。让它工作的正确调用/符号是什么？我已经尝试了很多将它们放入 '' 的变体，但没有任何进展。谢谢! $
MySQL加入 "connected"间隔
假设我有 table teach_subject(teacher_id, subject_id, min_grade_of_school, max_grade_of_school, color_in_
Javascript 一张一张显示图片，间隔
我有下面的图像，我试图以 3 秒的间隔一张一张地显示它们，但我无法让它工作。它继续停留在 0 并且不显示图像，帮助会很好: JavaScript: window.animate = functio
algorithm - 在最少数量的槽中安排作业/间隔
我认为这个问题类似于加权间隔调度问题，但略有不同。假设您有一个具有开始时间和结束时间的类次 s，该类次从 s.start 开始有 n 个空位到s.end。时隙是从 s.start 到 s.end 的
ios - 我怎样才能插入两个观点？间隔()不起作用
我试图将一个 GeometryReader 作为按钮推到屏幕底部，但 Spacer 在这里不起作用...... 这个想法是让应用程序响应所有屏幕尺寸。 VStack { GeometryRea
r - 2个逻辑向量的元素之间的快速最小距离(间隔)(取2)
我问了一个相关问题 here但意识到我在计算这个复杂的度量时花费了太多时间(目标是与随机化测试一起使用，所以速度是一个问题)。所以我决定放弃权重，只使用两个度量之间的最小距离。所以这里我有 2 个向量
docker-compose 健康检查重试频率 != 间隔
我最近成立 healthcheck s 在我的 docker-compose配置。它做得很好，我喜欢它。下面是一个典型的例子: services: app: healthcheck:
cocoa - 更改重复计时器的 NSTimer 间隔
我正在 Cocoa 中使用如下设置的 NSTimer 运行 mainLoop: mainLoopTimer = [NSTimer scheduledTimerWithTimeInter
android - 间隔 flutter 后命中api的更好方法
目前正在开发家庭自动化应用程序，其中有事件 API 可以在事件被触发时为我提供事件。但我想持续运行 API，以便跟踪在整个应用程序中触发的事件。还有一个主页，我在其中显示曾经发生的事件。它是一个简单的
elasticsearch - 日期范围查询中的 Elasticsearch 间隔
我有一个查询应该是这样的要求: { "size": 0, "_source": [ "dateCreated" ], "query": { "bool": {
php - 间隔 30 分钟的循环时间字符串
我有一个 UNIX 格式的时间字符串。我需要将该字符串四舍五入到最接近的 30 分钟间隔。例如:我的时间是上午 9:20，而不是应该四舍五入到上午 9:30。如果分钟数大于 30，例如上午 9:4
java - 运算符(operator)间隔
我有网络调用，我想定期调用它。我只想将运算符 Interval 与 flatMap 一起使用，但在间隔线程上。你能解释一下这种情况吗？我知道Interval只使用一个线程，任务是按顺序处理的。我有
ios - 减慢 CADisplayLink 间隔
我在我的 iOS 应用程序中使用了 NSTimer，但由于 SetNeedsDisplay，我没有得到我想要的结果。我做了一些研究并找到了 CADisplayLink，它为我提供了我想要的动画结果。
Java - 根据最小值、最大值和计数生成间隔/间隔
我需要通过给出值数组来生成 map 上图例的值。Java 库中是否有函数可以从值数组和计数值生成范围或区间？像这样的东西: Integer[] getIntervals(Number[] values
java - 在Java中以一定的时间间隔提供 sleep 间隔
我的函数中有以下代码，我试图从数据库中获取参数MAX_FAILED_ATTEMPT，并且基于此，如果检查失败，我将发送警报。当前代码将尝试从 MAX_FIELD_ATTEMPT 获取值并立即依次进行检
c# - Trackbar 上的特定值/间隔
我在这里要做的是像 Windows XP 上的那样放下一个轨迹栏来更改分辨率:( http://puu.sh/7Li5h.png ) 我想设置特定的间隔/增量值，如上图所示。目前，实际栏下方的线条已经
javascript - 停止 JavaScript 间隔
是否可以停止当前作为 setInterval 运行的函数？这是我的代码: 这是我调用的函数 function pull_light_status (lights_array) { $.get

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

postgresql - PostgreSQL 中的平均聚合时态数据库 10 分钟间隔