mysql - 根据 Tableau 中其他列的条件计算单列中的时间差

Question

我有一个充当状态更改日志的数据库表。所有时间戳都在单个列中提供。我需要计算每个“on_id”的特定状态更改之间耗时(以分钟为单位)。正如您从我的示例数据中看到的，我不能简单地计算一行与下一行之间的差异，因为单个“on_id”的状态更改之间存在不相关的行。

id          state                 from_state                to_state                   start_time             on_id   user_id 
45   transition_failed      volunteer_to_donor       claimed                  2016-11-28 18:05:59.509807+00      10        53  
 1   transition_completed   drafting                 available                2016-11-10 19:56:16.454458+00       2        12  
 2   transition_failed      available                available                2016-11-10 19:57:01.199609+00       2        12  
 3   transition_failed      available                available                2016-11-10 19:58:08.134549+00       2        12  
28   transition_completed   volunteer_to_donor       volunteer_to_recipient   2016-11-22 22:14:57.060536+00       9        51  
 4   transition_completed   drafting                 available                2016-11-14 16:36:17.802104+00       3        12  
 5   transition_completed   drafting                 available                2016-11-16 14:59:56.925226+00       5        15  
29   transition_failed      volunteer_to_recipient   volunteer_to_donor       2016-11-22 22:51:01.250038+00       9        51  
 6   transition_completed   drafting                 available                2016-11-16 18:04:04.172773+00       6        15  
 7   transition_completed   available                claimed                  2016-11-16 18:05:23.30427+00        6        16  
30   transition_failed      volunteer_to_recipient   volunteer_to_recipient   2016-11-22 22:51:12.458881+00       9        51  
 8   transition_completed   claimed                  volunteer_to_donor       2016-11-16 18:05:28.546312+00       6        16  
 9   transition_completed   volunteer_to_donor       volunteer_to_recipient   2016-11-16 18:05:39.388517+00       6        16  
10   transition_failed      volunteer_to_recipient   volunteer_to_recipient   2016-11-16 18:05:48.772071+00       6        16  
11   transition_completed   volunteer_to_recipient   complete                 2016-11-16 18:06:56.9068+00         6        16  
31   transition_completed   volunteer_to_recipient   complete                 2016-11-22 22:51:38.570253+00       9        51  
12   transition_completed   drafting                 available                2016-11-21 15:11:01.842671+00       7        12  

我认为我需要一个计算字段，但我无法弄清楚要使用的语法或函数。

例如，我希望能够计算每个“on_id”从“可用”状态到“已声明”状态所用的时间。对于 on_id 6，这需要 1 分 19 秒。

id          state                 from_state                to_state                   start_time             on_id   user_id 

6   transition_completed   drafting                 available                2016-11-16 18:04:04.172773+00       6        15  
7   transition_completed   available                claimed                  2016-11-16 18:05:23.30427+00        6        16  

Answer 1

我在这里创建了示例表和 SQL:希望它有帮助。

Create table tbl(
  Id int, 
  to_state varchar (10), 
  On_id int,
dte date);

 Insert into tbl values ( 1, 'available', 6, '2018-02-22')
 , ( 2, 'claimed' , 6 , '2018-02-25')
 , ( 3, 'drafted' , 6 , '2018-02-23);

Sql:
Select distinct 
t1.on_id,
t2.availstartdte,
t2.claimstartdte,
datediff(t2.claimstartdte, t2.availstartdte) as date_diff
From tbl t1
Join ( select id, 
      (Select Max(b.dte) from tbl b where b.to_state='available' and a.on_id=b.on_id) as availstartdte,
      (Select Max(b.dte) from tbl b where b.to_state='claimed' and a.on_id=b.on_id) as claimstartdte
      From tbl a
      Where a.to_state in('available', 'claimed')
      ) t2
 On t1.id = t2.id

结果:

on_id   availstartdte   claimstartdte   date_diff
6             2018-02-22    2018-02-25          3