时间戳

首页 > 解决方案 > 计算平均体重增加

postgresql - 计算平均体重增加

问题描述

我有下表：

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 个权重记录（第一个表）

标签： postgresql

解决方案

尝试使用lag聚合函数来显示上一条记录的重量和上一条记录的日期。然后，您可以将两者相加（从以前的记录获得的收益，从以前的记录获得的天数）以获得平均值：

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

推荐阅读