我有一个审计表，其结构如下：
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
我看不到任何类似的问题，尽管很难描述我在寻找什么。任何帮助都将不胜感激！
对于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