根据缺失的时间范围自动填充数据库中的缺失行[英] Autofill missing row in database based on missing time range

本文是小编为大家收集整理的关于根据缺失的时间范围自动填充数据库中的缺失行的处理方法,想解了根据缺失的时间范围自动填充数据库中的缺失行的问题怎么解决?根据缺失的时间范围自动填充数据库中的缺失行问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我在 Postgresql 数据库中有一个表,它存储一个日期时间以及一些如下所示的整数:

      dt                total                                                   
--------------------------------                                        
2019-07-01 10:00:00     150                                      
2019-07-01 10:15:00     153                                      
2019-07-01 10:30:00     155                                      
2019-07-01 10:45:00     160                                      
2019-07-01 11:00:00     161                                   
....

如您所见,dt 列中的日期时间将连续在 15 分钟范围内.我的问题是,有时传入的数据可能会丢失某些行.

例如:

     dt                total                                                   
--------------------------------                                        
2019-07-01 10:00:00     150                                      
2019-07-01 10:15:00     153                                      
2019-07-01 10:30:00     155                                      
2019-07-01 10:45:00     160                                      
2019-07-01 11:00:00     161
2019-07-01 11:15:00     163
2019-07-01 12:00:00     170

在此示例中,有 n=2 缺失的行分别是时间 11:30 和 11:45 的行.我想在这里做的是自动填充这些行的日期时间,并使用缺失行(11:15)之前最后一行的总列和缺失行(12:00)之后的第一行的平均值作为每个缺失的总数行

对于这个例子,每一行缺失的总列将加上 (170-163)/(n+1) = 7/3 = 2.333 (这里使用 3 个十进制数字) 所以结果会变成这样:

     dt                total                                                   
--------------------------------                                        
2019-07-01 10:00:00     150                                      
2019-07-01 10:15:00     153                                      
2019-07-01 10:30:00     155                                      
2019-07-01 10:45:00     160                                      
2019-07-01 11:00:00     161
2019-07-01 11:15:00     163
2019-07-01 11:30:00     165.333
2019-07-01 11:45:00     167.666
2019-07-01 12:00:00     170

我认为它不能直接用 SQL 来完成.所以,我认为 Python 可能有助于解决这个问题.有什么想法吗?

推荐答案

你可以使用 generate_series() 和一些数学.以下假设 total 正在增加(如您的示例数据):

select d.dt, seqnum,
       coalesce(t.total,
                (max(t.total) over (order by d.dt asc) +
                 (min(t.total) over (order by d.dt desc) - 
                  max(t.total) over (order by d.dt asc)
                 ) *
                 (seqnum - max(seqnum) filter (where t.total is not null) over (order by d.dt asc)) /
                  nullif(min(seqnum) filter (where t.total is not null) over (order by d.dt desc) -
                         max(seqnum) filter (where t.total is not null) over (order by d.dt asc),
                         0
                        )
                 )
                )
from (select dt, count(*) over (order by dt) as seqnum
      from (select generate_series(min(dt), max(dt), interval '15 minute') as dt
            from t
            ) d
     ) d left join
     t
     on t.dt = d.dt;

这里是一个 db<>fiddle.

计算很麻烦,因为您需要进行加权平均才能获得中间值.公式为:

prev_value + (next_value - previous_value) * ratio

比例为:

(current_time - prev_time) / (next_time - prev_time)

但不是时间,而是使用顺序计数.

本文地址:https://www.itbaoku.cn/post/1764109.html