连续两行中的日期之间的差异

一尘不染

连续两行中的日期之间的差异

sql

+----------+--------------+-------------------------+
| ticketid | ticketpostid |           date          |
+----------+--------------+-------------------------+
|  1387935 |      3147808 | 2012-09-17 13:33:01     |
|  1387935 |      3147812 | 2012-09-17 13:33:41     |
|  1387938 |      3147818 | 2012-09-17 13:35:01     |
|  1387938 |      3148068 | 2012-09-17 13:37:01     |
|  1387938 |      3148323 | 2012-09-17 14:47:01     |
|  1387939 |      3147820 | 2012-09-17 13:36:01     |
|  1387939 |      3147834 | 2012-09-17 13:36:25     |
|  1387939 |      3147851 | 2012-09-17 13:41:01     |
|  1387939 |      3147968 | 2012-09-17 13:59:06     |
|  1387939 |      3147996 | 2012-09-17 14:03:01     |

这是我编写的查询的结果。有两行和多于两行的票证编号相同。我需要在每个票证中找到前两个日期之间的时差

前任。

+----------+--------------+-------------------------+
| ticketid | ticketpostid |           date          |
+----------+--------------+-------------------------+
|  1387935 |      3147808 | 2012-09-17 13:33:01     |
|  1387935 |      3147812 | 2012-09-17 13:33:41     |
|  1387938 |      3147818 | 2012-09-17 13:35:01     |
|  1387938 |      3148068 | 2012-09-17 13:37:01     |
|  1387939 |      3147820 | 2012-09-17 13:36:01     |
|  1387939 |      3147834 | 2012-09-17 13:36:25     |

因此;

+----------+--------------+
| ticketid |time diff(sec)|
+----------+--------------+
|  1387935 |      40      |
|  1387938 |      120     |
|  1387939 |      24      |

你能告诉我我该怎么做吗？

谢谢。

阅读 118

2021-05-23

共1个答案

一尘不染

对于PostgreSQL，我认为您想让lagwindow函数比较行。它比自连接和过滤器要有效得多。这不适用于MySQL，因为它似乎仍然不支持标准的SQL：2003窗口函数。见下文。

要只找到最低的两个，可以在上使用dense_rankwindow函数ticketid，然后过滤结果以仅返回where的行dense_rank() = 2，即时间戳从最低的倒数第二lag()的行，在那里将产生时间戳最小的行。

请参阅此SQLFiddle ，其中显示了示例DDL和输出。

SELECT ticketid, extract(epoch from tdiff) FROM (
  SELECT
      ticketid,
      ticketdate - lag(ticketdate) OVER (PARTITION BY ticketid ORDER BY ticketdate) AS tdiff,
      dense_rank() OVER (PARTITION BY ticketid ORDER BY ticketdate) AS rank
  FROM Table1
  ORDER BY ticketid) x
WHERE rank = 2;

我将其ticketdate用作date列的名称，因为它date是列的可怕名称（它是数据类型名称），并且永远不应该使用；在许多情况下，必须使用双引号将其引用。

便携式方法可能是其他人发布的自加入方法。上面的窗口函数方法可能也适用于Oracle，但似乎不适用于MySQL。据我所知，它不支持SQL：2003窗口函数。

如果您SET sql_mode = 'ANSI'使用模式定义，它将与MySQL一起使用timestamp，并使用代替timestamp with time zone。似乎窗口功能不会；MySQL对该OVER子句感到窒息。请参阅此SQLFiddle。

2021-05-23