SQL-如何查找两行之间缺少值的条目

Question

我们在工作中使用Presto SQL。我花了几个小时试图寻找这个问题的答案，但找不到答案，而且很难找到。解决这个问题为解决许多问题打开了大门。

我需要编写一个查询，尝试查找REQUEST_CANCEL&CHARGED存在，但CANCEL_ACCOUNT丢失。

充电&CANCEL_ACCOUNT应始终位于REQUEST_CANCEL之后。

表名：CUSTOMER_INFO

|DATE_TIME|CUST_ID |ACTION        |
|20180726 |1234    |CHARGED       |
|20180726 |1234    |CANCEL_ACCOUNT|
|20180726 |1234    |REQUEST_CANCEL|

所有这些值都存在于同一个表中。这是我迄今为止所拥有的。

SELECT *
FROM
(SELECT *
FROM CUSTOMER_INFO
WHERE 
DATE_TIME = 20180726
AND ACTION = REQUEST_CANCEL) as a
JOIN
(SELECT *
FROM CUSTOMER_INFO
WHERE
DATE_TIME = 20180726
AND ACTION = CHARGED) as b
ON a.CUST_ID = b.CUST_ID
WHERE 
a.TIME < b.TIME

让我用一种有意义的方式来解释它。

A = REQUEST_CANCEL
B = CANCEL_ACCOUNT
C = CHARGED

当A和C存在但B不存在时，您如何查询。序列需要精确到A>B>C。它本质上是在查询两个存在的值之间不存在的东西。在我当前的查询中，可以在两个值之间返回B，这不是我想要的。

Answer 1

我认为您正在搜索NOT EXISTS和一个共格子查询。

SELECT *
FROM (SELECT *
FROM customer_info
WHERE action = 'REQUEST_CANCEL') rc
INNER JOIN (SELECT *
FROM customer_info
WHERE action = 'CHARGED') c
ON c.cust_id = rc.cust_id
AND c.date_time >= rc.date_time
WHERE NOT EXISTS (SELECT *
FROM customer_info ca
WHERE ca.cust_id = rc.cust_id
AND ca.action = 'CANCEL_ACCOUNT'
AND ca.date_time >= rc.date_time
AND ca.date_time <= c.date_time);

Answer 2

使用group by和having:

select cust_id
from customer_info ci
where date_time = 20180726 and
action in ('REQUEST_CANCEL', 'CHARGED', 'CANCEL_ACCOUNT')
group by cust_id
having sum(case when action = 'REQUEST_CANCEL' then 1 else 0 end) > 0 and
sum(case when action = 'CHARGED' then 1 else 0 end) > 0 and
sum(case when action = 'CANCEL_ACCOUNT' then 1 else 0 end) = 0 ;

每个sum()都会统计具有该操作的客户的匹配记录的数量。> 0说存在一个。= 0说不存在。

数据库对于这个逻辑来说并不重要。下面是一个使用MySQL的SQL Fiddle。