5分钟学会SQL SERVER窗口函数

fireWang

发布于 2020-03-25 17:35:31

2.5K0

发布于 2020-03-25 17:35:31

文章被收录于专栏：零维领域零维领域

简介

窗口函数（window function），也可以被称为 OLAP函数或分析函数。

窗口函数是在 ISO SQL 标准中定义的。窗口是用户指定的一组行。窗口函数计算从窗口派生的结果集中各行的值。

可以在单个查询中将多个排名或聚合窗口函数与单个 FROM 子句一起使用。

窗口函数是整个SQL语句最后被执行的部分，这意味着窗口函数是在SQL查询的结果集上进行的，因此不会受到Group By， Having，Where子句的影响。

语法

-- 聚合函数
Aggregate Window Functions
< OVER_CLAUSE > :: =
    OVER ( 
        [ PARTITION BY value_expression , ... [ n ] ]
        [ORDER BY order_by_expression ]
         )

各个部分的解释如下：

PARTITION BY 将结果集分为多个分区。开窗函数分别应用于每个分区，并为每个分区重新启动计算。
value_expression 指定对相应 FROM 子句生成的行集进行分区所依据的列。value_expression 只能引用通过 FROM 子句可用的列。value_expression 不能引用选择列表中的表达式或别名。value_expression 可以是列表达式、标量子查询、标量函数或用户定义的变量。
指定按其执行窗口函数计算的逻辑顺序。
order_by_expression 指定用于进行排序的列或表达式。order_by_expression 只能引用可供 FROM 子句使用的列。不能将整数指定为表示列名或别名。

一句话总结：聚合函数 over(partition by 分组字段 order by 排序字段排序方式) as 别名

特别需要注意的是，在SQL SERVER 2012之前的版本，是不支持聚合窗口函数和order by 连用的。如果是之前的版本，就只支持-- 聚合函数 over(partition by 分组字段) as 别名

优点

SQL更加简洁
执行效率更高

实例

实例的数据使用的是和 SQL笔试50题同样的数据，使用的平台是SQLFIDDLE（提供在线数据库），鉴于近期全球病情的影响，各种网站都有一定几率无法提供服务，本次提供了SQLite数据库，已存入测试数据。

下载地址（14天内有效）：https://c-t.work/s/1786d12bba3e4c

select
  s.sid,
  sc.cid,
  s.sname,
  s.ssex,
  sc.score,
  AVG(sc.score) over(partition BY s.ssex) as [不同性别平均分],
  AVG(sc.score) over(partition BY sc.cid) as [不同课程平均分],
  SUM(sc.score) over(partition BY sc.sid) as [个人总分]
from Student s
left join Score sc
on sc.sid = s.sid
order by s.sid, sc.cid

运行结果如下：

aggregate_function

假如我想知道累计的总分呢？

--SQL SERVER 2012 及以上版本
select
  s.sid,
  sc.cid,
  s.sname,
  s.ssex,
  sc.score,
  AVG(sc.score) over(partition BY s.ssex) as [不同性别平均分],
  AVG(sc.score) over(partition BY sc.cid) as [不同课程平均分],
  SUM(sc.score) over(partition BY sc.sid) as [个人总分],
  SUM(sc.score) over(partition BY sc.sid order by sc.cid) as [个人累计总分]
from Student s
left join Score sc
on sc.sid = s.sid
order by s.sid, sc.cid

运行结果如下：

window_aggregate_function

那很不幸的，我就是SQL SERVER 2012 以前的版本，比如SQL SERVER 2008的怎么办？

--SQL SERVER 2008 及以前版本
with myquery (sid,cid,sname,ssex,score,[不同性别平均分],[不同课程平均分],[个人总分]) as 
(select
  s.sid,
  sc.cid,
  s.sname,
  s.ssex,
  sc.score,
  AVG(sc.score) over(partition BY s.ssex) as [不同性别平均分],
  AVG(sc.score) over(partition BY sc.cid) as [不同课程平均分],
  SUM(sc.score) over(partition BY sc.sid) as [个人总分]
from Student s
left join Score sc
on sc.sid = s.sid
)
select 
q1.*,
sum(q2.score) as [个人累计得分]
from myquery as q1
inner join myquery as q2
on q1.sid = q2.sid
and q1.cid >= q2.cid
group by q1.sid,q1.cid,q1.sname,q1.ssex,q1.score,q1.[不同性别平均分],q1.[不同课程平均分],q1.[个人总分]
order by q1.sid, q1.cid

很简单，将第一个实例的结果，自连接一份，正好可以利用课程ID来作为累计合计的标志位。