按ID计算的两个日期之间的平均天数(但仅限于当前行之前的行)
按ID计算的两个日期之间的平均天数(但仅限于当前行之前的行)
提问于 2018-06-26 09:13:21
我正在尝试计算每个id的开始日期和结束日期之间的平均天数，只有一个捕获...它必须是截至上一行/日期的平均值。我完全被难住了。
之前的
id date_start date_end
1 3/3/2015 3/24/2015
1 3/27/2015 4/14/2015
1 4/15/2015 4/30/2015
1 5/1/2015 5/13/2015
1 5/17/2015 5/28/2015
1 7/9/2015 8/7/2015
2 3/7/2015 3/11/2015
3 2/17/2015 4/8/2015
3 4/13/2015 5/5/2015
4 3/12/2015 3/25/2015
4 3/25/2015 4/3/2015
4 4/3/2015 4/10/2015
4 4/10/2015 4/18/2015
5 5/13/2015 6/12/2015
5 6/12/2015 7/15/2015
5 4/19/2015 5/13/2015之后的
id date_start date_end AVG_previous_cycle_days
1 3/3/2015 3/24/2015 NULL
1 3/27/2015 4/14/2015 21
1 4/15/2015 4/30/2015 19.5
1 5/1/2015 5/13/2015 18
1 5/17/2015 5/28/2015 16.5
1 7/9/2015 8/7/2015 15.4
2 3/7/2015 3/11/2015 NULL
3 2/17/2015 4/8/2015 NULL
3 4/13/2015 5/5/2015 50
4 3/12/2015 3/25/2015 NULL
4 3/25/2015 4/3/2015 13
4 4/3/2015 4/10/2015 11
4 4/10/2015 4/18/2015 9.67
5 5/13/2015 6/12/2015 NULL
5 6/12/2015 7/15/2015 30
5 4/19/2015 5/13/2015 29回答 1
Stack Overflow用户
发布于 2018-06-26 09:15:38
您可以将累积和除以累积计数进行计算--但您必须减去当前值：
select t.*,
( (sum(diff) over (partition by id order by date_start) - diff) /
(count(*) over (partition by id order by date_start) - 1)
) as avg_through_previous
from t cross apply
(values (datediff(day, date_start, date_end)) v(diff);实际上，您也可以使用窗口子句来表达：
select t.*,
avg(diff) over (partition by id
order by date_start
rows between unbounded preceding and 1 preceding
) as avg_through_previous
from t cross apply
(values (datediff(day, date_start, date_end)) v(diff);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51033642
复制相关文章
相似问题