sql根据同一列中的其他值筛选值

l7wslrjt 于 2021-06-26 发布在 Impala

关注(0)|答案(1)|浏览(375)

我有一个数据库，有时间，行动和价值列。我想根据其他操作过滤某些操作。例子：

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。

sql impala

来源：https://stackoverflow.com/questions/52810993/sql-filter-values-depending-on-other-values-from-the-same-column

1条答案

按热度按时间

qcuzuvrc1#

一种方法使用 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)
      );

赞(0）回复(0）举报 2021-06-26

我来回答

sql根据同一列中的其他值筛选值

1条答案

相关问题

热门标签

最新问答