postgreSQL查询从最近的(在时间上)上一个数据库条目获取时间差

Question

我有两个数据库表来处理问题跟踪系统(如Zendesk或Jira):tickets(父表)和messages(子表，存储票证的每条消息)。像这样:

tickets表:

id
created_at

messagestable:

id
created_at
ticket_id
text
source (could be either 'company' or 'client')

我想制作一个SQL查询，它将产生票证消息，其中每行涉及一个"公司"消息以及自最后一个"客户端"消息以来的时间延迟。按"时间延迟";我的意思是:

created_at从'company'消息减去created_at从上一个'client'消息

请注意，一行中可能有多个'company'或'client'消息。

如果ticket (id 4528)有这些消息(按时间顺序):

- 'client' message (id 1)
- 'company' message (id 2)
- 'client' message (id 3)
- 'company' message (id 4)
- 'company' message (id 5)

我希望SQL查询产生如下内容:

2和1之间的时间差

下面是一个例子。小提琴

仅joincompany行，适用的client行具有优先的created_at值。然后按相反的时间顺序为每个company的client行分配一个row_number。选择n = 1以获得company行中最近的client行。

我们可以在PARTITION BY子句中添加ticket_id来处理多个罚单。

SQL:

WITH cte1 (id, ticket_id, text, ts, client_id, ts2, n, dt) AS (
SELECT m1.id, m1.ticket_id, m1.text, m1.created_at
, m2.id AS last_client_id, m2.created_at
, ROW_NUMBER() OVER (PARTITION BY m1.id, m1.ticket_id ORDER BY m2.id DESC) AS n
, m1.created_at - m2.created_at AS dt
FROM      messages AS m1
LEFT JOIN messages AS m2
ON m1.ticket_id = m2.ticket_id
AND m2.source = 'client'
AND m2.created_at < m1.created_at
WHERE m1.source = 'company'
)
SELECT * FROM cte1 WHERE n = 1
;

我没有注意到原来问题上的MySQL标签，只有postgresql标签。两者的SQL结构基本相同，只是对时差计算做了一些调整。

这是最近版本的MariaDB/MySQL too (Fiddle):

WITH cte1 (id, ticket_id, text, ts, client_id, ts2, n, dt) AS (
SELECT m1.id, m1.ticket_id, m1.text, m1.created_at
, m2.id AS last_client_id, m2.created_at
, ROW_NUMBER() OVER (PARTITION BY m1.id, m1.ticket_id ORDER BY m2.id DESC) AS n
, ABS(timestampdiff(minute, m1.created_at, m2.created_at)) AS dt
FROM      messages AS m1
LEFT JOIN messages AS m2
ON m1.ticket_id = m2.ticket_id
AND m2.source = 'client'
AND m2.id < m1.id
WHERE m1.source = 'company'
)
SELECT * FROM cte1 WHERE n = 1
;

旧版本的MySQL或Maria将不支持WITH clause或Window Function。这还是可行的。

设置:

CREATE TABLE messages (
id          int
, created_at  timestamp
, ticket_id   int
, text        varchar(30)
, source      varchar(20)
);
INSERT INTO messages (id, ticket_id, source, text, created_at) VALUES
(1, 1234, 'client' , 'message 01', current_timestamp + INTERVAL '+1 HOUR')
, (2, 1234, 'company', 'message 02', current_timestamp + INTERVAL '+2 HOUR')
, (3, 1234, 'client' , 'message 03', current_timestamp + INTERVAL '+3 HOUR')
, (4, 1234, 'company', 'message 04', current_timestamp + INTERVAL '+4 HOUR')
, (5, 1234, 'company', 'message 05', current_timestamp + INTERVAL '+5 HOUR')
;

MySQL/MariaDB的INTERVAL语法略有不同:

INSERT INTO messages (id, ticket_id, source, text, created_at) VALUES
(1, 1234, 'client' , 'message 01', current_timestamp + INTERVAL 1 HOUR)
, (2, 1234, 'company', 'message 02', current_timestamp + INTERVAL 2 HOUR)
, (3, 1234, 'client' , 'message 03', current_timestamp + INTERVAL 3 HOUR)
, (4, 1234, 'company', 'message 04', current_timestamp + INTERVAL 4 HOUR)
, (5, 1234, 'company', 'message 05', current_timestamp + INTERVAL 5 HOUR)
;