sql - SQL 根据同一列中的其他值过滤值

Question

我有一个包含时间、操作和值列的数据库。我想根据其他操作过滤某些操作。例子：

Time    Action              Value
1:30    Drink sold          5
1:31    Happy hour          1
1:32    Drink sold          5 *
1:33    Happy hour          1
1:34    Drink sold          5 *
1:35    Drink sold          5 *
1:36    Drink sold          5 *
1:37    Happy hour          0
1:38    Drink sold          5
1:39    Drink sold          5
1:40    Drink sold          5
1:41    Happy hour          0
1:42    Drink sold          5
1:43    Happy hour          1
1:44    Drink sold          5 *
1:45    Happy hour          1
1:46    Drink sold          5 *
1:47    Drink sold          5 *
1:48    Drink sold          5 *
1:49    Happy hour          0
1:50    Drink sold          5

结果应仅包含欢乐时光为 1 时售出的饮品。标有 *。欢乐时光可以重复设置。数据库是impala，但理想的解决方案只使用普通 SQL。

标签： sqlimpala

Answer 1

一种方法用于lag()将最近的行与欢乐时光记录进行比较。然后只需考虑最近的值为“1”的位置：

select t.*
from (select t.*,
             max(case when action = 'Happy hour' and value = 0 then time end) over (order by time) as happy_hour_0,
             max(case when action = 'Happy hour' and value = 1 then time end) over (order by time) as happy_hour_1
      from t
     ) t
where action = 'Drink sold' and
      ((happy_hour_0 is null and happy_hour_1 is not null) or
       (happy_hour_1 > happy_hour_0)
      );