问题描述
+----------+--------------+-------------------------+
| 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，我认为您需要以比较行；它比自联接和过滤器有效得多。这不适用于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.
请参见显示示例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 子句中感到窒息。参见。
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.
这篇关于两个连续行中的日期之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！