两个连续行中的日期之间的差异 [英] Difference between dates in two consecutive rows

问题描述

+----------+--------------+-------------------------+
| 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中找到前两个日期之间的时差

This is a result of a query that I wrote. There are two and more than two rows with same ticketid. I need to find the time difference between first two date in each ticketid

Ex。

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

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

Can you tell me how I can do this?

谢谢。

推荐答案

对于PostgreSQL，我认为您需要 lag 窗口函数以比较行；它比自联接和过滤器有效得多。这不适用于MySQL，因为它似乎仍然不支持标准的SQL：2003窗口函数；

For PostgreSQL, I think you want the lag window function to compare the rows; it'll be much more efficient than a self-join and filter. This won't work with MySQL, as it still doesn't seem to support the standard SQL:2003 window functions; see below.

要查找仅两个最低值，可以在<$ c $上使用 dense_rank 窗口函数c> ticketid ，然后过滤结果以仅返回 dense_rank（）= 2 的行，即时间戳从低至低的第二行，其中 lag（）将产生时间戳最小的行。

To find only the two lowest you can use the dense_rank window function over the ticketid, then filter the results to return only rows where dense_rank() = 2, ie row with the second-from-lowest timestamp, where lag() will produce the row with the lowest timestamp.

请参见此SQLFiddle 显示示例DDL和输出。

See this SQLFiddle which shows sample DDL and output.

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 是列的可怕名称（它是数据类型名称），并且永远不应使用；

I've used ticketdate as the name for the date column because date is a terrible name for a column (it's a data type name) and should never be used; it has to be double quoted in many situations to work.

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

The portable approach is probably the self-join others have posted. The window function approach above probably works on Oracle too, but doesn't seem to in MySQL. As far as I can find out it doesn't support the SQL:2003 window functions.

如果您 SET sql_mode ='ANSI'并使用 timestamp ，而不是带有时区的时间戳。窗口功能似乎没有； MySQL在 OVER 子句中感到窒息。参见此SQLFiddle 。

The schema definition will work with MySQL if you SET sql_mode = 'ANSI' and use timestamp instead of timestamp with time zone. It seems the window functions won't; MySQL chokes on the OVER clause. See this SQLFiddle.

这篇关于两个连续行中的日期之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！