WordPress发布查询(WordPress post query)

 Impala支持窗口功能。 这个问题是一个“缺口和孤岛”问题，因此可以使用行数的差异来解决： 
 
select id, state, min(date) as start_date, max(date) as end_date
from (select t.*,
             row_number() over (partition by id order by date) as seqnum_id,
             row_number() over (partition by id, state order by date) as seqnum_isd
      from table t
     ) t
group by id, state, (seqnum_id - seqnum_isd);
 
 差异的逻辑并不困难，但是当你第一次学习它时却很棘手。 它有助于运行子查询并查看行号值是什么 - 以及差异定义每个组的原因。 

Impala supports window functions. This problem is a "gap-and-islands" problem, so it can be solved using a difference of row numbers:
 
select id, state, min(date) as start_date, max(date) as end_date
from (select t.*,
             row_number() over (partition by id order by date) as seqnum_id,
             row_number() over (partition by id, state order by date) as seqnum_isd
      from table t
     ) t
group by id, state, (seqnum_id - seqnum_isd);
 
The logic for the difference is not difficult, but tricky when you first learn it. It helps to run the subquery and see what the row number values are -- and why the difference defines each group.