当postgresql中只有更改记录可用时，sql计算每日平均值

Question

我有一个按零件号、位置号和日期列出的库存数量表。如果当天发生变化，表中只有一条记录。例如:

part  |loc   |date      |qtyonhand
------+------+----------+---------
10544 | 3012 | 20161116 | 68
10544 | 3012 | 20170403 | 43  <----- 
10544 | 3012 | 20170413 | 33  <-----

对于每个 part/loc 键，我需要计算平均手头数量过去 3 个月。由于现在是 2017 年 5 月，我需要计算 2017 年 2 月、3 月和 4 月，其中包括显示的两条记录。但我不能只取 43 和 33 的平均值除以 2。对于这部分/loc，计算应该是这样的:

20170201 inventory was 68
20170202 inventory was 68
...for each day
20170402 inventory was 68
20170403 inventory was 43
20170404 inventory was 43
...
20170413 inventory was 33
...
20170430 inventory was 33

然后我将每天的库存加起来，然后除以 2 月 1 日到 4 月 30 日之间的天数。

我尝试使用 lag() 来计算日期之间的差异，然后将其乘以库存水平，但我不知道如何使用之前的更改记录数量从 2 月 1 日开始获取值，通过下一个变化记录。

非常感谢任何提示或指导。

Answer 1

您可以使用 lag() 和句点逻辑来执行此操作。如果我有算术权:

select part, loc, 
       sum(days_in_period * prev_quantity)/ sum(days_in_period) as average
from (select t.*,
             greatest(least(date, period_end) - greatest(prev_date, period_start) + 1, 0) as days_in_period
      from (select t.*,
                   lag(date) over (partition by part, loc order by date) as prev_date,
                   lag(qtyonhand) over (partition by part, loc order by date) as prev_qtyonhand
            from t
           ) t cross join
           (select '2017-02-01'::date as period_start, '2017-04-30'::date as period_end,
     ) t
group by part, loc;