sql - 如何简化配置单元的计算效率？

Question

代码在hive上运行:

select day,count(mdn)*5 as number from
(select distinct a.mdn,a.day from 
flow a
left outer join
flow b
on a.day=date_add(b.day,-1) and a.mdn=b.mdn
left outer join
flow c
on a.day=date_add(c.day,-2) and a.mdn=c.mdn
left outer join
flow d
on a.day=date_add(d.day,-3) and a.mdn=d.mdn
where b.mdn is null  and c.mdn is null  and d.mdn is null)t 
group by day

代码的逻辑是今天选择一个 future 三天没有出现的mdn，计算mdn的个数。但是这段代码的效率很低，因为同一个大表的流程是三次join .如何化简高效？

Answer 1

那么，您可以使用 lead() 查看第二天​​并比较日期时间:

select f.*
from (select f.*,
             lead(f.day) over (partition by f.mdn order by f.day) as next_day
      from flow f
     ) f
where next_day > date_add(day, 3) or next_date is null;