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

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

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

这是我写的查询的结果。有两个和两个以上的行具有相同的ticketid。我需要找到每个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     |

As a result;

结果是;

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

Can you tell me how I can do this?

你能告诉我怎么做吗?

Thanks.

3 个解决方案

#1

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.

对于PostgreSQL,我想你想要滞后窗函数来比较行;它比自连接和过滤器更有效。这不适用于MySQL,因为它似乎仍然不支持标准的SQL:2003窗口函数;见下文。

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.

要找到最低的两个,你可以在ticketid上使用dense_rank窗口函数,然后过滤结果只返回dense_rank()= 2的行,即具有从最低时间倒数第二的行,其中lag()将产生时间戳最短的行。

See this SQLFiddle which shows sample DDL and output.

请参阅此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;

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.

我使用了ticketdate作为日期列的名称,因为date是一个列的可怕名称(它是一个数据类型名称),永远不应该使用;它必须在许多情况下用双引号才能工作。

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.

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

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.

如果您使用SET sql_mode ='ANSI'并使用timestamp而不是timestamp with time zone,则模式定义将与MySQL一起使用。似乎窗口功能不会; MySQL在OVER子句上窒息。看到这个SQLFiddle。

#2

Try this query -

试试这个查询 -

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

#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

#1