使用sql、spark根据某些条件从表中获取输出

Question

item    value      month     year
A         0          8         2020
B         0          8         2020
A         0          9         2020
B         13          9         2020
A         0          10         2020
B         0          10         2020
A         10          11         2020
B         0          11         2020
A         0          12         2020
B         0          12         2020
A         0          1         2021
B         10          1         2021
A         0          2         2021
B         0          2         2021
A         0          3         2021
B         0          3         2021
A         11          4         2021
B         0          4         2021

item  month   year
A      8       2020
A      12       2020
A      1       2021
B      10       2020
B       2       2021

输出规则：对于一个项目，月，年，我们需要寻找下两个月，如果下两个月的总和（值）为0，那么它将符合输出条件。

对于Exapmle：对于A项，2020年8个月，总和（值）为0至2020年10个月。类似地，对于项目A,2020年9个月的总和（值）在接下来的两个月中是ot 0，所以它将不会产出。

Answer 1

对于一个项目，月，年，我们需要寻找下两个月，如果和（值）下两个月是0，那么它将合格的输出。

这听起来像lead()或使用窗口框架：

select t.*
from (select t.*,
             sum(value) over (partition by item order by year, month rows between current row and 2 following) as sum_value_3
      from t
     ) t
where sum_value_3;

这将返回比您指定的更多的行，因为您的描述和所需的结果不相同。您确实希望在一行中有3个或更多0的一系列行中的第一行。解决这一问题的一种方法是作为一个缺口和岛屿问题：

select item, year, month, cnt
from (select t.*,
             count(*) over (partition by item, seqnum - seqnum_1) as cnt,
             row_number() over (partition by item, seqnum - seqnum_1 order by year, month) as seqnum_within_group
      from (select t.*,
                   row_number() over (partition by item order by year, month) as seqnum,
                   row_number() over (partition by item, (case when value = 0 then 1 else 0 end) order by year, month) as seqnum_1
            from t
           ) t
      where value = 0
     ) t
where seqnum_within_group = 1 and cnt >= 3;