SQL SERVER 2012窗口函数-4 执行顺序及帧 <<Microsoft.SQL.Server.2012.窗口窗数>>笔记
关于windows function 依这样顺序执行
1.来自哪个表
2.where 条件
3.group by 分组
4.having 分组条件
5.select
6.Window Functions (窗口函数)
7,order by
举例来说
;with ct (区域 ,房型 ,单价 ) as( select '赣州','三居室',1500 union all select '赣州','一层室',1000 union all select '北京','三屋室',25000 union all select '北京','二屋室',30000 union all select '北京','一屋室',20000 union all select '深圳','一屋室',10000 )select ROW_NUMBER() over (partition by 区域 order by AVG(单价) desc) as id,区域,房型,AVG(单价) from ctgroup by 区域,房型 having 区域!='深圳'order by 房型
关于上面的代码，大家可以使用 ctrl+l 看看执行计划的顺序，
这里大概讲一下，
第一步，编译器会去找哪个数据源，
第二步分组，
第三步，分组完了，就执行having 过滤掉 区域!='深圳' ,
第四步，就是select 字段，
第五步，开始执行windows function 里的内容． 一开始是分区，然后分区里排序．
第六步，最后再执行最后的order by 房型 .虽然分区也有排序，但是如果有order by ，就依这里的排序．
帧
这里的帧 是指
函数() over (partition by 分区字段 order by 排序字段 rows 范围)
其中rows 范围就是帧．
其中有
ROWS BETWEEN UNBOUNDED PRECEDING |
<n> PRECEDING |
<n> FOLLOWING |
CURRENT ROW
AND
UNBOUNDED FOLLOWING |
<n> PRECEDING |
<n> FOLLOWING |
CURRENT ROW
,
举例来说 ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW 表示，分区最头顶数顶到当前数据 可以简写 ROWS UNBOUNDED PRECEDING
ROWS CURRENT ROW and UNBOUNDED FOLLOWING 表示分区尾部到当前行 当前行，可以简写 ROWS UNBOUNDED FOLLOWING
ROWS 3 PRECEDING AND 2 PRECEDING 表示，以当前行为标准，前三个和前二个行的范围
ROWS 2 PRECEDING AND 1 following 表示， 以当前行为标准，前二个和下一个的范围．
;with ct as (select '北京' as 区域, '01' as 月,30 as 工资union select '上海' as 区域, '01' as 月,40 as 工资unionselect '北京' as 区域, '02' as 月,40 as 工资union select '上海' as 区域, '02' as 月,50 as 工资unionselect '北京' as 区域, '03' as 月,90 as 工资union select '上海' as 区域, '03' as 月,78 as 工资unionselect '北京' as 区域, '04' as 月,90 as 工资union select '上海' as 区域, '04' as 月,78 as 工资)select 区域,月,工资 as 当月工资,sum(工资) over (partition by 区域 ORDER BY 月 rows between unbounded preceding and current row) as 累加现在的工资,sum(工资) over (partition by 区域 ORDER BY 月 rows between current row and unbounded following) as 累加现在到最后的工资,sum(工资) over (partition by 区域 ORDER BY 月 rows between 3 preceding and 2 preceding) as 前三个到前二个累加,sum(工资) over (partition by 区域 ORDER BY 月 rows between 3 preceding and 1 following) as 前二个到下一个累加 from ct order by 区域,月
