从表中记录一天和第二天的计数

Question

select 
lead(count(*)) over (order by day),
day 
from device_fact_kpis 
where day=20160301 group 
by day;

Answer 1

您的查询无论如何都无法工作，因为在白天进行筛选之前，您需要执行lead()。

在任何情况下，都可以使用CTE和join:

with t as (
      select day, count(*) as cnt
      from table t
      group by day
     )
select day, cnt, tnext.cnt as next_cnt
from t left join
     t tnext
     on tnext.day = date_add(t.day, 1);

编辑：

select day, cnt, tnext.cnt as next_cnt
from (select day, count(*) as cnt
      from table t
      group by day
     ) t left join
     (select day, count(*) as cnt
      from table t
      group by day
     ) tnext
     on tnext.day = date_add(t.day, 1);