我有一个谷歌电子表格表：
user | message_id | event | timestamp
--------------------------------------------------------------------------------
john player | ekjf939e9313140_34k | delivered | 04/13/2018 12:56:30
john player | ekjf939e9313140_34k | opened | 04/15/2018 16:05:00
john player | ekjf939e9313140_34k | opened | 04/16/2018 22:15:20
john player | ekjf939e9313140_34k | opened | 04/16/2018 23:47:33
cristian dior | dsfsk0340344030fkjkj | delivered | 04/12/2018 18:45:21
cristian dior | dsfsk0340344030fkjkj | opened | 04/13/2018 15:40:17
cristian dior | dsfsk0340344030fkjkj | clicked | 04/13/2018 16:00:07
cristian dior | dsfsk0340344030fkjkj | clicked | 04/13/2018 16:04:10
cristian dior | dsfsk0340344030fkjkj | clicked | 04/14/2018 12:30:11对于一个用户，对于每个唯一的message_id，我如何创建一个Google电子表格数据透视表，以显示从发送电子邮件到打开电子邮件之间经过的小时数？
编辑：
表数据可以包含所有可能的值。
1条答案
按热度按时间cwtwac6a1#
=QUERY( ArrayFormula( QUERY({A:C,value(D:D)}, "select Col1, Col2, sum(Col4) where Col1 is not null group by Col1, Col2 pivot Col3")), "select Col1, Col2, Col4 - Col3 label Col4 - Col3 'Time' format Col4 - Col3 'hh:mm:ss'")第一个查询是按事件透视列。第二个查询是计算差值。
结果：
参考：https://developers.google.com/chart/interactive/docs/querylanguage
编辑
对于多个事件，我建议修改公式：
=QUERY(QUERY(filter({A:C,value(D:D)},REGEXMATCH(C:C,"delivered|opened")),"select Col1, Col2, sum(Col4) where Col1 is not null group by Col1, Col2 pivot Col3"), "select Col1, Col2, Col4 - Col3 label Col1 'user', Col2 'id', Col4 - Col3 'Time' format Col4 - Col3 'hh:mm:ss'")它只过滤需要的事件：
REGEXMATCH(C:C,"delivered|opened")