如何选择空数据时段? [英] How to select periods of time with empty data?

问题描述

鉴于下表my_table，我想找出所有数据为空的期间:

I want to find out all periods with empty data, given the following table my_table:

id         day       
29         2017-06-05
26         2017-06-05
30         2017-06-06
30         2017-06-06
21         2017-06-06
21         2017-07-01
29         2017-07-01
30         2017-07-20

答案将是:

Empty_start    Empty_end
2017-06-07     2017-06-30
2017-07-02     2017-07-19

考虑月数很重要.例如，在第一行中，答案2017-06- 31 是错误的.

It's important that the number of months is considered. For example, in the first row the answer 2017-06-31 would be incorrect.

如何在Hive中编写此查询?

How can I write this query in Hive?

推荐答案

您可以使用lag()或lead():

select date_add(day, 1) as empty_start, date_add(next_day, -1) as empty_end
from (select day,
             lead(day) over (order by day) as next_day
      from t
      group by day
     ) t
where next_day <> date_add(day, 1);

这篇关于如何选择空数据时段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！