时间戳

首页 > 解决方案 > MySQL 选择基于先前行的数据（审计表）

mysql - MySQL 选择基于先前行的数据（审计表）

问题描述

我有一个结构如下的审计表：

id    customer     field     status        timestamp
1     37399262     phone     successful    2020-10-08 20:16:39
2     37399262     website   failed        2020-10-08 20:16:39
3     37399262     website   failed        2020-10-09 21:25:22
4     37399262     website   successful    2020-10-10 09:08:35

该表跟踪我们是否能够成功地为客户获取特定数据点。

有时我们能够在第一次尝试时找到数据，您知道这是因为没有状态为失败的条目，特定字段的第一次出现被标记为成功（例如phone）

其他时候，我们在前 n 次搜索中找不到数据点，但最终能够找到它。您之所以知道这一点，是因为一个或多个条目的状态为失败，然后是成功状态（例如website）。

我在编写 MySQL 查询时遇到问题，该查询只会选择我们之前未能找到数据点但后来能够找到它的字段。

理想情况下，此查询将产生以下输出：

customer     field     success_id     success_timestamp     last_fail_id     last_fail_timestamp
37399262     website   4              2020-10-10 09:08:35   3                2020-10-09 21:25:22

我看不到任何类似的问题，尽管很难描述我在寻找什么。任何帮助，将不胜感激！

标签： mysqlsqlselect

解决方案

对于 MySql 8.0+，您可以使用 LAG() 窗口函数：

select customer, field, 
       id success_id, timestamp success_timestamp,
       last_fail_id, last_fail_timestamp
from (
  select *, 
    lag(status) over (partition by customer, field order by timestamp) prev_status, 
    lag(id) over (partition by customer, field order by timestamp) last_fail_id,
    lag(timestamp) over (partition by customer, field order by timestamp) last_fail_timestamp
  from tablename
) t
where status = 'successful' and prev_status = 'failed'

对于以前版本的 MySql，假设id任何新尝试的列都在增加：

select t.customer, t.field, 
       t.id success_id, t.timestamp success_timestamp,
       g.last_fail_id, g.last_fail_timestamp 
from tablename t
inner join (
  select customer, field,
         max(case when status = 'failed' then timestamp end) last_fail_timestamp,
         max(case when status = 'failed' then id end) last_fail_id
  from tablename
  group by customer, field
  having last_fail_timestamp is not null
) g on g.customer = t.customer and g.field = t.field   
where t.status = 'successful'

请参阅演示。
结果：

> customer | field   | success_id | success_timestamp   | last_fail_id | last_fail_timestamp
> -------: | :------ | ---------: | :------------------ | -----------: | :------------------
> 37399262 | website |          4 | 2020-10-10 09:08:35 |            3 | 2020-10-09 21:25:22

推荐阅读