PostgreSQL中的平均聚合时态数据库间隔为10分钟 [英] Average aggregation temporal database 10-minute interval in PostgreSQL
问题描述
我有一个时间序列数据，希望通过平均函数对其进行汇总。
聚合应以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的平均值。
I have a time series data where I want to perform aggregation on it by average function. Aggregation should be performed in 10-minute intervals: (HH-1):50, HH:00, HH:10, HH:20, HH:30, HH:40 or alternatively in 10-minute intervals: (HH-1):51, HH:01, HH:11, HH:21, HH:31, HH:41. Note: hourly mean of 5 o'clock is average of (4):51, 5:01,5:11, 5:21, 5:31, 5:41 values.
问题在于数据集包含时间上具有奇数和偶数频率的变量。因此，我不能仅通过应用奇数或偶数时间来为其分配聚合。我想知道如何解决这个问题。
The problem is that a data-set includes the variables with odd and even frequencies in time. So, I cannot assign an aggregation for it just by applying odd or even time instantaneous. I wanted to know how I can treat this problem.
我正在使用的代码：
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分钟'（以排除 13:30:09 之类的值）。
1) In every 10 minutes pick the first (earliest) value, but greater then '1 minute' (to exclude values like 13:30:09 ).
2）在上一步的集合中，获取每小时的平均值，并抵消 '10分钟'。
2) In the set from previous step get the average of every hour, offset by '10 minutes'.
此查询：
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分钟） 。
will give you the earliest value, but greater then '1 minute' in every 10 minutes (hh:m1 or hh:m2 for every 10 minutes).
然后，使用第一个查询作为子查询：
Then, using the first query as a subquery :
SELECT date_trunc('hour',dt + interval '10 minutes') as hour,
avg(ambtemp)
FROM (first query here) sub
WHERE rank = 1
GROUP BY 1;
更新：这是完整的查询：
UPDATE: Here is a full query:
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 ，只需删除标记为
The query above is for (HH-1):51, HH:01, HH:11, HH:21, HH:31, HH:41. If you need the query for (HH-1):50, HH:00, HH:10, HH:20, HH:30, HH:40 just delete the marked line.
我已经测试了查询，并返回了正确的结果（对于（HH-1）：50，HH：00，.. 。查询变体，它们等于示例结果。）
I have tested the query and it returns correct results (and for (HH-1):50, HH:00, ... query variant they are equal to your example results).
这篇关于PostgreSQL中的平均聚合时态数据库间隔为10分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
