sql - 如何根据“datediff”创建列

Question

我想从原始表格下方制作两列。两列的名称是“Avg_term”和“Max_term”。两列概念基于 datediff。

1. 首先，我希望“Max_term”列显示两个连续日期之间的最大日期差异（最长天数）。
2. 其次，我希望“Avg_term”列显示所有“datediff”的平均天数

谢谢你。

标签： sqlsql-servertsql

Answer 1

您可以使用lag()获取以前的日期/时间，然后聚合：

select max(datediff(day, prev_datetime, datetime)) as max_diff,
       avg(datediff(day, prev_datetime, datetime)) as avg_diff
from (select t.*,
             lag(datetime) over (partition by id order by datetime) as prev_datetime
      from t
     ) t;

如果您希望每个 id 都有这个，我不会感到惊讶：

select id,
       max(datediff(day, prev_datetime, datetime)) as max_diff,
       avg(datediff(day, prev_datetime, datetime)) as avg_diff
from (select t.*,
             lag(datetime) over (partition by id order by datetime) as prev_datetime
      from t
     ) t
group by id;