两个相邻行之间的日期差异

Question

+----------+--------------+-------------------------+
| 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。我需要找到每个ticketid中前两个日期之间的时间差。

例如：

+----------+--------------+-------------------------+
| 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      |

你能告诉我如何完成这个操作吗？

谢谢。

- TraviJuu

---

感谢提供数据和期望结果。如果您能以“INSERT”表单的形式展示，那就太好了，这样可以轻松地创建一个示例，但是这也可以。顺便问一下，您使用的是哪个版本的PostgreSQL？ - Craig Ringer

Answer 1

对于PostgreSQL，我认为你需要使用lag窗口函数来比较行；这比使用自连接和筛选要高效得多。由于MySQL仍然不支持标准的SQL:2003窗口函数，所以这种方法在MySQL中不起作用。

要查找仅最低的两个值，您可以在ticketid上使用dense_rank窗口函数，然后过滤结果，只返回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;

因为date是一个数据类型的名称，而不是列名，所以我使用ticketdate作为日期列的名称，这是一个糟糕的列名，不应该被使用；在许多情况下，它必须用双引号括起来才能起作用。

可移植的方法可能是其他人发布过的自连接方法。上面介绍的窗口函数方法在Oracle中可能也适用，但在MySQL中似乎不行。据我所知，MySQL不支持SQL:2003窗口函数。

如果您设置SET sql_mode ='ANSI'并使用timestamp而不是timestamp with time zone，则模式定义将适用于MySQL。似乎窗口函数不行；MySQL会在OVER子句上出现错误。请参见此SQLFiddle。

- Craig Ringer

Answer 2

尝试使用以下查询 -

INSERT INTO ticket_post(ticketid, ticketpostid, date) VALUES
(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');

SELECT
  ticketid,
  TIME_TO_SEC(TIMEDIFF((
    SELECT t.date FROM ticket_post t WHERE t.ticketid = t1.ticketid AND t.date > t1.date ORDER BY t.date LIMIT 1),
    MIN(date)
  )) diff FROM ticket_post t1
GROUP BY ticketid;

+----------+------+
| ticketid | diff |
+----------+------+
|  1387935 |   40 |
|  1387938 |  120 |
|  1387939 |   24 |
+----------+------+

- Devart

---

我得到了不同的结果，有点困惑。你能展示一下你的模式和样本数据吗？对比：http://sqlfiddle.com/#!9/846e8/6 - Craig Ringer

啊，那里有两个样本数据集。我用的是上面那个，你用的是另一个。这就解释了。我的错。顺便说一下，在你的答案中值得注意的是它只适用于MySQL。 - Craig Ringer

Answer 3

select 
  ticketid
  ,time_to_sec(timediff(t2.date, t1.date))  as timediff
from table t1
join table t2 on t1.ticketid=t2.ticketid and t1.ticketpostid<t2.ticketpostid

- Dumitrescu Bogdan

---

我认为这种方法是有效的，但是当有两个以上相同的票证ID时，它会返回额外的行。它将比较ticket1和ticket2，ticket2和ticket3，但也会比较ticket1和ticket3，这不是期望的结果。 - Cargo23

是的，但是SQL是根据问题的数据完成的。它很容易修改以考虑第一和第二个，或第一个和最后一个或其他任何情况。它是为2完成的 :)。 - Dumitrescu Bogdan