mysql - 计算记录在特定状态下花费的时间

Question

有人可以帮我完成下一个任务吗？这是一个问题:我们有一个历史表(记录的状态变化)，我们需要计算记录处于特定状态的时间(以天为单位)。下面是历史表的结构:

ID| RecordId| CreatedDate         | Field  | OldValue      | NewValue
1 | Record1 | 2013-08-07 09:40:31 | Status | Open          | Awaiting Info
2 | Record1 | 2013-08-08 07:30:20 | Status | Awaiting Info | Open
3 | Record1 | 2013-08-14 01:45:42 | Status | Open          | Resolved

因此我们需要像这样创建表:

Status   | TimeSpentInStatusInDays
Open     |     2
Awaiting |     3
Resolved |     1

例如，值(它们未连接到实际数据集)但结构完全相同，我们需要跟踪四种不同的状态。

如有任何帮助，我们将不胜感激。谢谢。

Answer 1

需要获取下一个日期，然后取差。 MySQL 没有lead() 函数，可以使用关联子查询。结果是这样的:

select h.status,
       sum(datediff(nextCreatedDate, CreatedDate)) as TotalDays
from (select h.*,
             (select h2.CreatedDate
              from history h2
              where h2.CreatedDate > h.CreatedDate and
                    h2.RecordID = h.RecordId
              order by h2.CreatedDate
              limit 1
             ) as nextCreatedDate
      from history h
     ) h
group by h.status;

这对于 history(RecordId, CreatedDate) 上的索引也有不错的性能。

编辑:

另一种方法是使用变量:

select h.status,
       sum(datediff(nextCreatedDate, CreatedDate)) as TotalDays
from (select h.*, @nextDate as nextCreatedDate,
             @nextdate := if(@RecordId = @nextRecordId, CreatedDate, NULL),
             @nextRecordId := RecordId,
      from history h cross join
           (select @nextdate := NULL, @nextRecordId := NULL) const
      order by RecordId, CreatedDate desc
     ) h
group by h.status;

我不太喜欢这种方法，因为它取决于子查询中带有变量的参数的估值顺序。 MySQL 不保证顺序。