mysql - 根据持续时间对 Sql 表中的日期进行分组

Question

我需要按 session 显示聊天日志，并且 session 可能会按每条消息之间 30 分钟间隔的持续时间进行分割。

这里是示例表

S.No 从 到 消息时间

1. 约翰·迈克尔你好 12:00
2. 迈克尔·约翰嗨 12:01
3. 迈克尔·约翰，你在吗 12:40

这里前两条消息是在 1 分钟间隔内发送的。但第三条消息是在 39 分钟后发送的，因此前两条消息是一个 session ，第三条消息是第二个 session 。我怎么能这样分开呢。

Answer 1

您还标记了您的问题“Oracle”，因此这是一个 Oracle 解决方案。在下一个查询中，您可以看到一个别名为“GRP”的表达式，您可以使用它对属于在一起的行进行分组:

SQL> select s_no
  2       , s_from
  3       , s_to
  4       , message
  5       , s_time
  6       , case
  7         when nvl(lag(s_time) over (partition by least(s_from,s_to),greatest(s_from,s_to) order by s_time,s_no),s_time)
  8           > s_time - interval '30' minute
  9         then
 10           first_value(s_time) over (partition by least(s_from,s_to),greatest(s_from,s_to) order by s_time,s_no)
 11         else
 12           s_time
 13         end grp
 14    from messages
 15   order by s_no
 16  /

      S_NO S_FROM  S_TO    MESSAGE       S_TIME              GRP
---------- ------- ------- ------------- ------------------- -------------------
         1 John    Michael hello         28-04-2011 12:00:00 28-04-2011 12:00:00
         2 Michael John    hi da         28-04-2011 12:01:00 28-04-2011 12:00:00
         3 Michael John    are you there 28-04-2011 12:40:00 28-04-2011 12:40:00
         4 Michael Alan    hi            28-04-2011 12:15:00 28-04-2011 12:15:00
         5 Alan    Michael bye           28-04-2011 12:18:00 28-04-2011 12:15:00

5 rows selected.

下面是如何使用分组表达式创建聊天日志的示例:

SQL> select min(s_no) s_no_from
  2       , max(s_no) s_no_to
  3       , least(s_from,s_to) participant1
  4       , greatest(s_from,s_to) participant2
  5       , min(s_time) time_from
  6       , max(s_time) time_to
  7    from ( select s_no
  8                , s_from
  9                , s_to
 10                , message
 11                , s_time
 12                , case
 13                  when nvl(lag(s_time) over (partition by least(s_from,s_to),greatest(s_from,s_to) order by s_time,s_no),s_time)
 14                    > s_time - interval '30' minute
 15                  then
 16                    first_value(s_time) over (partition by least(s_from,s_to),greatest(s_from,s_to) order by s_time,s_no)
 17                  else
 18                    s_time
 19                  end grp
 20             from messages
 21         )
 22   group by least(s_from,s_to)
 23       , greatest(s_from,s_to)
 24       , grp
 25   order by min(s_time)
 26  /

 S_NO_FROM    S_NO_TO PARTICI PARTICI TIME_FROM           TIME_TO
---------- ---------- ------- ------- ------------------- -------------------
         1          2 John    Michael 28-04-2011 12:00:00 28-04-2011 12:01:00
         4          5 Alan    Michael 28-04-2011 12:15:00 28-04-2011 12:18:00
         3          3 John    Michael 28-04-2011 12:40:00 28-04-2011 12:40:00

3 rows selected.

问候，
罗布。