postgresql：连接具有不同时间刻度的timeseries数据：每周、每天和每月

6rqinv9w 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(489)

我得到的数据来自同一来源，速率不同。我想绘制数据（并做一些阈值等），最简单的方法是将其视为时间序列数据。
我的数据看起来像：

SourceID, SubmissionTime,  Daily_Data
-------------------------------
ABC,    2020-01-01 10h32,  100
ABC,    2020-01-01 10h45,  101
ABC,    2020-01-02 09h01,  120
DEF,    2020-01-01 15h30,  123

和

SourceID, SubmissionTime,  Weekly_Data
-------------------------------
ABC,    2020-01-01 09h00,  Joe
ABC,    2020-01-08 09h21,  Soap
ABC,    2020-01-14 15h20,  Bill

每日和每周提交的内容大致相隔一天或一周，并且是用户输入，因此存在差距、延迟、不良数据等。我想说的是：

SourceID, Daily_Date, Weekly_date, Weekly_datatime, Weekly_Data
-------------------------------
ABC,    2020-01-01, 2020-01-01 09h00,  101, Joe
ABC,    2020-01-02, 2020-01-01 09h00,  120, Joe
ABC,    2020-01-03, 2020-01-01 09h00,  122, Joe
...
ABC,    2020-01-07, 2020-01-01 09h00,  133, Joe
ABC,    2020-01-07, 2020-01-14 15h20,  145, Soap

提取日期非常简单，但是如何在每周提交之后才对行执行最近的datetime联接呢。我在timscaledb中也做过类似的事情，但这是别人的数据库，现在不需要额外的扩展。。。

sql postgresql time-series

来源：https://stackoverflow.com/questions/62147411/postgresql-joining-timeseries-data-with-different-time-scaled-weekly-daily-an

1条答案

按热度按时间

rfbsl7qr1#

我想出来了。这基本上是一个“最后一个对象结转”的问题，我把每日/每周/每月的表直接连接起来，把最后一个每月/每周的观察结果结转到每日时间样本中。
更多信息请点击此处
我创建了一个函数，一个聚合，然后通过这些函数运行表：

CREATE OR REPLACE FUNCTION fun(a FLOAT, b FLOAT)
RETURNS FLOAT
LANGUAGE SQL
AS '
  SELECT COALESCE(b, a)
';
select a,b,t,v,
    fun(t,v) as v_or_t
from tbl
order by a,b,t
;

以及

create or replace function locf_s(a float, b float)
returns float
language sql
as '
  select coalesce(b, a)
';
drop aggregate if exists locf(float);
CREATE AGGREGATE locf(FLOAT) (
  SFUNC = locf_s,
  STYPE = FLOAT
);

最后：

with weekly_data as (SELECT source_id, submissiontime, weekly_data from weekly_table)
select distinct on (source_id, submissiontime)
    source_id,
    submissiontime,
    daily_data,
    locf(weekly_data) over (PARTITION by source_id ORDER by submissiontime) as weekly_data,
from daily_table
join weekly_table on daily_table.source_id = weekly_table.source_id 
order by source_id, submissiontime desc;

这只是伪代码，不完全是我所用的（因为我有多个表和奇怪的数据），所以请原谅任何错误，我希望这个想法会出现。

展开查看全部

赞(0）回复(0）举报 2021-08-09

我来回答

postgresql：连接具有不同时间刻度的timeseries数据：每周、每天和每月

1条答案

相关问题

热门标签

最新问答