开窗函数详解（MySQL\SQL Server）（二）window定义

WINDOW window_name AS (window_spec)
    [, window_name AS (window_spec)] ...

window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

window 主要就分成三个部份
partition_clause 指定分区部份
order_clause 指定排序部分
frame_clause 指定窗口框架范围

窗口函数可以放在select 语句的哪里，并在什么时候执行呢？

Window functions are permitted only in the select list and ORDER BY clause. Query result rows are determined from the FROM clause, after WHERE, GROUP BY, and HAVING processing, and windowing execution occurs before ORDER BY, LIMIT, and SELECT DISTINCT.

官方文档有明确说明
窗口函数只能存在于select部份，与order 部份
window的执行是在 WHERE, GROUP BY, and HAVING 之后 ORDER BY, LIMIT, and SELECT DISTINCT之前
windows的显示定义也须在 ORDER BY, LIMIT 之前。

  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)
  order by    rank() over w  

window 可以显示定义，也可以隐式直接使用。显示定义的好处是节约代码复用。
显示定义的另一个好处是性能更优
官方在窗口函数优化中专门有提到

Window functions affect the strategies the optimizer considers:
Derived table merging for a subquery is disabled if the subquery has window functions. The subquery is always materialized.
Semijoins are not applicable to window function optimization because semijoins apply to subqueries in WHERE and JOIN … ON, which cannot contain window functions.
The optimizer processes multiple windows that have the same ordering requirements in sequence, so sorting can be skipped for windows following the first one.
The optimizer makes no attempt to merge windows that could be evaluated in a single step (for example, when multiple OVER clauses contain identical window definitions). The workaround is to define the window in a WINDOW clause and refer to the window name in the OVER clauses.

上面的官方引用，我用粗体标注了。多窗口需要显示定义

比如下面两种写法结果完全一样

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      
 )
  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)

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 (partition by proName order by score desc)  as score_rank,
  DENSE_RANK() over (partition by proName order by score desc)  as score_denserank, 
  ROW_NUMBER() over (partition by proName order by score desc) as score_rownumber
  from  cte_window

上面两种得到的结果是一样的。但明显第一种写法更优。

partition_clause 指定分区部份
order_clause 指定排序部分
这两部份 开窗函数或聚合函数都支持的。
frame_clause 指定窗口框架范围 只有部份函数才允许

先写一个包含最全的三部份开窗函数
下面的语句是求得最近两个月的累计

 with cte_window as
 (
  select  '张三' as name,'1月' as salemonth, 1000 as salemoney from dual
  union all
   select '张三' as name,'2月' as salemonth, 800 as salemoney from dual
  union all
   select '张三' as name,'3月' as salemonth, 900 as salemoney from dual
  union all
   select '张三' as name,'4月' as salemonth, 1300 as salemoney from dual   
   union all
  select  '李四' as name,'1月' as salemonth, 800 as salemoney from dual
  union all
   select '李四' as name,'2月' as salemonth, 900 as salemoney from dual
  union all
   select '李四' as name,'3月' as salemonth, 800 as salemoney from dual
  union all
   select '李四' as name,'4月' as salemonth, 900 as salemoney from dual      
 )
 select *,sum(salemoney) over(partition by name order by salemonth rows 1 PRECEDING ) as t
 from  cte_window

整个窗口函数中 frame_clause 子句是最复杂的。后续专门写一篇文章来讲。
参考：
https://dev.mysql.com/doc/refman/8.0/en/window-functions-named-windows.html
https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html