SQL -从不同行中获取基于同一列的日期差异
SQL -从不同行中获取基于同一列的日期差异
提问于 2019-04-08 19:44:12
我有一个表，其中存储了表单域上的所有更新。我正在尝试构建一个查询，其中我想要计算每次更新之间经过了多少时间。
下面是我的表格的一个例子：
ticket,last_update,status
12345,2019-03-29 13:54:55.000000,canceled
12345,2019-03-29 12:46:20.000000,analysis
12345,2019-03-28 18:30:55.000000,canceled
12345,2019-03-28 09:31:20.000000,analysis我想检查分析到其他状态之间的状态更改的差异时间(每个分析都有一个后续状态)。
输出示例：
First analysis: differente between analysis 2019-03-28 09:31:20.000000 and 2019-03-28 18:30:55.000000 canceled
First analysis: differente between analysis 2019-03-29 12:46:20.000000 and 2019-03-29 13:54:55.000000 canceled是否可以编写一条SQL语句来返回此数据？我被这句话卡住了：
select ticket, last_update, status from history as h
where h.ticket = 12345
and h.field = 'custom_field_a';我想避免在后台写一些代码来执行它。
尝试使用PARTITION BY：
select ticket,
last_update - lag(last_update) over (partition by ticket order by last_update) as difference
from history as h
where h.ticket = 12345
and h.field = 'custom_field_a'
group by ticket, last_update;它应该返回2行包含差异的分析->取消，分析->取消，但我得到4行。
回答 4
Stack Overflow用户
发布于 2019-04-08 19:47:55
你可以这样做：
select ticket,
max(last_update) filter (where status = 'created') as created_ts,
max(last_update) filter (where status = 'cancelled') as cancelled_ts,
max(last_update) filter (where status = 'analysis') as analysis_ts,
from history as h
where h.ticket = 12345 and
h.field = 'custom_field_a'
group by ticket;我不确定您希望如何表达差异，但您可以只减去相关的值。
Stack Overflow用户
发布于 2019-04-08 19:51:06
您可以使用LAG功能，该功能从前一行获取数据。下面的查询应该能够计算差额：
SELECT last_update - lag(last_update) over (order by last_update) as difference
FROM history AS h
where h.ticket = 12345
and h.field = 'custom_field_a';/A
Stack Overflow用户
发布于 2019-04-08 19:52:34
您可以加入相关行，如下所示：
select created.ticket
, created.last_update as created_ts
, analysis.last_update as analysis_ts
, canceled.last_update as canceled_ts
from history as created
left join history as analysis
on created.ticket = analysis.ticket
and created.field = analysis.field
and analysis.status = 'analysis'
left join history as canceled
on created.ticket = canceled.ticket
and created.field = canceled.field
and canceled.status = 'canceled'
where created.ticket = 12345
and created.field = 'custom_field_a'
and created.status = 'created'不确定field如何在其中发挥作用，它可能也是所有联接的联接条件。如果每个状态都有一个条目，这将起作用，否则会得到重复的行，并且可能需要不同的策略。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/55572703
复制相关文章
相似问题
腾讯云开发者
