最常用的窗口函数例子

窗口函数是个特棒的函数，用的场景也比较多。下面我举几个比较典型的例子

找出连续7天登陆的用户

select *
from
（
  select user_id ,count(1) as num
  from
     (select user_id,date_sub(log_in_date, rank) dts
     from    (select user_id,log_in_date,	
                  row_number() over(partitioned by user_id order by log_in_date ) as	rank
		  from user_log
	   )t
      )a
  group by dts
）b
where num = 7

思路是：先根据user_id 排名，同日期相加，最后计数等于7的即可

去头去尾求平均值

select a.deptno，avg(a.salary)
from  
 (
 select *, rank() over( partition by deptno order by salary ) as rank_1
 , rank() over( partition by deptno order by salary desc) as rank_2 
 from emp
 )  a 
group by a.deptno
where a.rank_1 >1 and a.rank_2 >1

思路：两个排序，一个正序一个倒序，最后筛选