postgresql - 计算平均增重

Question

我有下表:

ganado_id created     weight
1         2018-12-24  285
2         2018-12-24  288
2         2018-10-13  241
1         2018-10-13  244
1         2018-08-11  202

我需要计算每个 ganado_id 的平均增重。期望的输出:

ganado_id avg_weight_gain
1         0.618
2         0.652

ganado_id = 1 的平均增重是这样计算的:

SELECT ((285 - 244)::NUMERIC / ('2018-12-24'::DATE - '2018-10-13'::DATE)::NUMERIC + (244 - 202)::NUMERIC / ('2018-10-13'::DATE - '2018-08-11'::DATE)::NUMERIC) / 2

ganado_id = 2 的平均增重是这样计算的:

SELECT (288 - 241)::NUMERIC / ('2018-12-24'::DATE - '2018-10-13'::DATE)::NUMERIC

在生产中，每个ganado_id可以有1到15条重量记录(第一张表)

Answer 1

尝试使用 lag 聚合函数来显示上一条记录的权重和上一条记录的日期。然后，您可以将两者相加(从以前的记录中获得的 yield ，从以前的记录中得到的天数)以获得平均值:

with gains as (
  select
    ganado_id, weight, created,
    weight - lag (weight) over (partition by ganado_id order by created) as gain,
    created - lag (created) over (partition by ganado_id order by created) as days
  from table1
)
select
  ganado_id, sum (gain) * 1.0 / sum (days) as avg_gain
from gains
group by
  ganado_id

-- 编辑--

根据您的反馈，这将是平均值的平均值:

with gains as (
  select
    ganado_id, weight, created,
    1.0 * (weight - lag (weight) over (partition by ganado_id order by created)) /
    (created - lag (created) over (partition by ganado_id order by created)) as gain_per_day
  from table
)
select
  ganado_id, avg (gain_per_day)
from gains
group by
  ganado_id

结果:

1   0.61805555555555555556
2   0.65277777777777777778