开窗函数详解（MySQL\SQL Server）（一）常用场景

我工作中经常用到开窗函数，开窗函数本身也比较复杂，我也只用到了部份，没有完整的深入，借写文章的机会自己也深入学习一下。
开窗函数将以系列的方式出现，每篇只讲一两个知识点，避完写一个让人没有耐心看完的大文章。

在此系列中我将以MySQL举例。但实际SQL Server的开窗函数和MySQL几乎是一样的。
所有例子，都放心使用，MySQL的例子我将使用 公用表达式的方式，SQL Server我将以表变量的方式来写例子。这样的测试用例，会不依赖于任何表，也不会对你现在的库创建任何表。开箱即用，用完对整个库没有任务侵入

为了更好的举例说明。例子中用到的表，不会遵守范式。

1、按总分排名

 with cte_window as
 (
    select '张三' as stuName,'语文' as proName, 98 as score from dual
    union all
    select '张三' as stuName,'数学' as proName,88 as score from dual
    union all
    select '李四' as stuName,'语文' as proName, 99 as score from dual
    union all
    select '李四' as stuName,'数学' as proName,78 as score from dual    
    union all
    select '王五' as stuName,'语文' as proName, 94 as score from dual
    union all
    select '王五' as stuName,'数学' as proName,88 as score from dual      
 ),
cte1 as
 (
   select stuName,sum(score) as totalScore from cte_window 
   group by stuName
 )
 select *,rank() over(order by totalScore desc)  as score_rank from  cte1

结果

2、按科目分别排名

 with cte_window as
 (
    select '张三' as stuName,'语文' as proName, 98 as score from dual
    union all
    select '张三' as stuName,'数学' as proName,88 as score from dual
    union all
    select '李四' as stuName,'语文' as proName, 99 as score from dual
    union all
    select '李四' as stuName,'数学' as proName,78 as score from dual    
    union all
    select '王五' as stuName,'语文' as proName, 94 as score from dual
    union all
    select '王五' as stuName,'数学' as proName,88 as score from dual      
 )
 
  select *,
  rank() over w  as score_rank,
  DENSE_RANK() over w  as score_denserank, 
  ROW_NUMBER() over w as score_rownumber
  from  cte_window
  window w as (partition by proName order by score desc)

通过下面结果，还能看到 rank()、DENSE_RANK()、ROW_NUMBER() 排名的区别。

3、求累计

 with cte_window as
 (
  select '一月' as salemonth, 1000 as salemoney from dual
  union all
   select '二月' as salemonth, 800 as salemoney from dual
  union all
   select '三月' as salemonth, 900 as salemoney from dual
  union all
   select '四月' as salemonth, 1300 as salemoney from dual   
 )
 select *,sum(salemoney) over(order by salemonth) as accumulation 
 from cte_window

接下来，我们将从定义开始说起。