我有下一個預測表：
| id | timestamp | name | temp |
------------------------------------------------------------------
| 1 | 2022-01-16 12:40:06 | Bancal 1 | 22 |
| 2 | 2022-01-16 12:58:05 | Bancal 1 | 21 |
| 3 | 2022-01-16 13:22:00 | Bancal 1 | 30 |
| 4 | 2022-01-16 13:30:20 | Bancal 1 | 10 |
| 5 | 2022-01-16 13:59:06 | Bancal 1 | 15 |
| 6 | 2022-01-16 15:40:00 | Bancal 2 | 15 |
| 7 | 2022-01-16 15:54:06 | Bancal 1 | 18 |
| 8 | 2022-01-17 10:30:05 | Bancal 2 | 23 |
| 9 | 2022-01-17 11:20:00 | Bancal 1 | 12 |
| 10 | 2022-01-17 11:32:07 | Bancal 3 | 28 |
| 11 | 2022-01-17 13:30:06 | Bancal 1 | 23 |
我想以 1 小時為間隔進行查詢并填寫空格，但我希望它準確地在指定的小時和分鐘開始，如果我說從日期時間開始，'2022-01-16 12:38:52'那么 1 小時的間隔應該是：
2022-01-16 12:38:52
2022-01-16 13:38:52
2022-01-16 14:38:52
2022-01-16 15:38:52
.
.
.
2022-01-17 09:38:52
2022-01-17 10:38:52
2022-01-17 11:38:52
2022-01-17 12:38:52
2022-01-17 13:38:52
使用 timescaledb 的 time_bucket_gapfill 函式，但間隔是在小時的開始：
SELECT time_bucket_gapfill(interval '1 hour', timestamp,) AS init,
name,
avg(temp) AS avg_temp
FROM forecast
WHERE timestamp >= '2022-01-16 12:38:52' AND timestamp<= '2022-01-17 13:38:52'
GROUP BY name, init
ORDER BY init;
| init | name | avg_temp
2022-01-16 12:00:00.000000 | Bancal 2 |
2022-01-16 12:00:00.000000 | Bancal 1 | 21.5
2022-01-16 12:00:00.000000 | Bancal 3 |
2022-01-16 13:00:00.000000 | Bancal 1 | 18.3333333333333333
2022-01-16 13:00:00.000000 | Bancal 3 |
2022-01-16 13:00:00.000000 | Bancal 2 |
2022-01-16 14:00:00.000000 | Bancal 3 |
2022-01-16 14:00:00.000000 | Bancal 1 |
2022-01-16 14:00:00.000000 | Bancal 2 |
2022-01-16 15:00:00.000000 | Bancal 2 | 15
2022-01-16 15:00:00.000000 | Bancal 1 | 18
2022-01-16 15:00:00.000000 | Bancal 3 |
...
2022-01-17 09:00:00.000000 | Bancal 1 |
2022-01-17 10:00:00.000000 | Bancal 2 | 23
2022-01-17 10:00:00.000000 | Bancal 3 |
2022-01-17 10:00:00.000000 | Bancal 1 |
2022-01-17 11:00:00.000000 | Bancal 2 |
2022-01-17 11:00:00.000000 | Bancal 1 | 12
2022-01-17 11:00:00.000000 | Bancal 3 | 28
2022-01-17 12:00:00.000000 | Bancal 2 |
2022-01-17 12:00:00.000000 | Bancal 1 |
2022-01-17 12:00:00.000000 | Bancal 3 |
2022-01-17 13:00:00.000000 | Bancal 3 |
2022-01-17 13:00:00.000000 | Bancal 1 | 23
2022-01-17 13:00:00.000000 | Bancal 2 |
avg 的結果是出乎意料的，因為它從中獲取資料，'2022-01-16 12:00:00' to '2022-01-16 13:00:00'而不是'2022-01-16 12:38: 52' to '2022-01-16 13:38:52'
time_bucket_gapfill 有辦法彌補這些差距嗎？
預期的：
| init | name | avg_temp
2022-01-16 12:38:00.000000 | Bancal 2 |
2022-01-16 12:38:00.000000 | Bancal 1 | 20.75
2022-01-16 12:38:00.000000 | Bancal 3 |
2022-01-16 13:38:00.000000 | Bancal 1 | 15
2022-01-16 13:38:00.000000 | Bancal 3 |
2022-01-16 13:38:00.000000 | Bancal 2 |
2022-01-16 14:38:00.000000 | Bancal 3 |
2022-01-16 14:38:00.000000 | Bancal 1 |
2022-01-16 14:38:00.000000 | Bancal 2 |
2022-01-16 15:38:00.000000 | Bancal 2 | 15
2022-01-16 15:38:00.000000 | Bancal 1 | 18
2022-01-16 15:38:00.000000 | Bancal 3 |
...
2022-01-17 09:38:00.000000 | Bancal 2 | 23
2022-01-17 10:38:00.000000 | Bancal 2 |
2022-01-17 10:38:00.000000 | Bancal 3 | 28
2022-01-17 10:38:00.000000 | Bancal 1 | 12
2022-01-17 11:38:00.000000 | Bancal 2 |
2022-01-17 11:38:00.000000 | Bancal 1 |
2022-01-17 11:38:00.000000 | Bancal 3 |
2022-01-17 12:38:00.000000 | Bancal 2 |
2022-01-17 12:38:00.000000 | Bancal 1 | 23
2022-01-17 12:38:00.000000 | Bancal 3 |
2022-01-17 13:38:00.000000 | Bancal 3 |
2022-01-17 13:38:00.000000 | Bancal 1 |
2022-01-17 13:38:00.000000 | Bancal 2 |
uj5u.com熱心網友回復：
我會使用generate_series函式。
generate_series(開始、停止、步長間隔)
第三個引數可以寫出你的期望區間。在你的情況下，這可能是1 hours
SELECT *
FROM generate_series('2022-01-16 12:38:52'::timestamp,'2022-01-17 13:38:52'::timestamp,'1 hours') v
sqlfiddle
編輯
您可以嘗試使用 CTE 或子查詢制作一個日歷來表示每個時間間隔name，然后使用LEAD視窗函式獲取JOIN條件的時間戳間隔。
WITH CTE AS (
SELECT DISTINCT
name,
generate_series('2022-01-16 12:38:52'::timestamp,'2022-01-17 13:38:52'::timestamp,'1 hours') dt
FROM forecast
)
SELECT t1.name init,
t1.dt,
avg(coalesce(t2.temp,0)) AS avg_temp
FROM (
SELECT *,LEAD(dt) OVER(PARTITION BY name ORDER BY dt) n_dt
FROM CTE
) t1
LEFT JOIN forecast t2
ON t1.name = t2.name AND t2.timestamp BETWEEN t1.dt AND t1.n_dt
GROUP BY t1.name,
t1.dt
ORDER BY t1.dt
sqlfiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/426895.html
標籤：sql PostgreSQL 时间尺度数据库
上一篇：已解決：postgres/psycopg：匯入CSV的日期格式錯誤
下一篇：在SQL中組合兩個幾乎相同的行