SQL Server over()

--開窗函數over()不需要使用group by 就可以對資料進行分組。還可以同時傳回基礎行的所有列和聚合列。

--over 函數 配合聚合函數(max、min、sum、avg、count等)或row_number等函數，可以在不改變原顯示資料的情況下，

--新增一列作為聚合函數的計算值；

--group by 子句隻能同聚合函數(max、min、sum、avg、count),對相關列進行分組，隻能展示出分組列和聚合列的資料。

--1、partition by 用于将結果集進行分組，開窗函數應用于每一組。

--2、order by 用于排序。

--3、row_number()為每一組的行按順序生成一個惟一的序号

--4、rank()也為每一組的行生成一個序号，但不同于row_number()，如果按照order by 排序，如果有相同的值會生成相同的序号。

--并且接下來生成的序号是不連續的。

--5、dense_rank()與rank()相似。不同的是，如果有相同的序号，那接下來的序号不會間斷。也就是接下來的序号是連續的。

--6、NTILE：按指定數目将資料進行分組。并為每組生成一個序号。

--7、視窗架構範圍限定方式：一種是使用ROWS子句，通過指定目前行之前或之後的固定數目的行來限制分區中的行數；

--另一種是RANGE子句，按照排序列的目前值，根據相同值來确定分區中的行數。

--注意：聚合函數不能與ORDER BY 一同使用。

執行個體如下：

create table Student(

    Sid int primary key identity(1,1) not null,

    StudentName varchar(50) not null,

    Age int null,

    Sex varchar(2) check(sex='male' or sex='remale'),

    Birthday datetime 

)

create table ProjectTb(

    Pid int primary key identity(1,1) not null,

    ProjectName varchar(20) not null,

    orderNum int 

)

create table Score(

    ScoreID int primary key identity(1,1) not null,

    Sid int foreign key references Student(Sid) not null,

    Pid int foreign key references ProjectTb(Pid) not null,

    Score int

)

--insert into dbo.Student(StudentName,Age,Sex,Birthday)

--values('Lucy',21,'remale','1990-09-09'),('Lilei',22,'male','1989-01-03'),('Hanmeimei',20,'remale','1991-03-05')

--insert into dbo.ProjectTb(ProjectName,orderNum) values('Math',1),('English',2),('Chinese',3)

--insert into dbo.Score(Sid,Pid,Score)

--values(1,1,90),(1,2,67),(1,3,84),(2,1,90),(2,2,62),(2,3,87),(3,1,80),(3,2,83),(3,3,89)

--1、求學生的平均成績：

--(1)使用group by 

select s.StudentName,avg(sco.Score) as avg_score

from dbo.Score sco left join dbo.Student s

on sco.Sid=s.Sid

group by sco.Sid,s.StudentName

--(2)使用over()

with ScoreInfo as(

    select *,avg(Score) over(partition by Sid) avg_score from dbo.Score

)

select distinct(sco.avg_score),s.StudentName

from ScoreInfo sco left join dbo.Student s

on sco.Sid=s.Sid

--此情形下，比較使用group by、over()。使用group by的話，想要select的列都必須受限于group by 。

--但over()就不一樣了。聚合列之外的所有資料行的列都可随意select。

--2、求每個科目的平均成績（與1同理）

--(1)使用group by 

select p.ProjectName,avg(s.Score) as avg_score

from dbo.Score s left join dbo.ProjectTb p

on s.Pid=p.Pid

group by s.Pid,p.ProjectName

--(2)使用over()

with ScoreInfo as(

    select *,avg(Score) over(partition by Pid) avg_score from dbo.Score

)

select distinct(sco.avg_score),p.ProjectName

from ScoreInfo sco left join dbo.ProjectTb p

on sco.Pid=p.Pid

--3、求每個學生的最好成績的科目

with ScoreInfo as (

select max(Score) over(partition by Sid) as max_score,ScoreID,Sid,Pid,Score

from dbo.Score

)

select s.StudentName,p.ProjectName,sco.Score

from ScoreInfo sco 

left join dbo.Student s

on sco.Sid=s.Sid

left join dbo.ProjectTb p

on sco.Pid=p.Pid

where sco.Score=sco.max_score

order by sco.Score desc

--4、求每個科目最好成績（包含對應的學生）（同理于3）

with ScoreInfo as(

    select max(Score) over(partition by Pid) as max_score,*

    from dbo.Score

)

select p.ProjectName,s.StudentName,sco.Score

from ScoreInfo sco 

left join dbo.Student s

on sco.Sid=s.Sid

left join dbo.ProjectTb p

on sco.Pid=p.Pid

where sco.Score=sco.max_score

order by sco.Score desc

--5、擷取每個學生按成績高到低排序，并顯示相關科目。

with ScoreInfo as(

    select *,row_number() over(partition by Sid order by Score desc) number from dbo.Score

)

select s.StudentName,p.ProjectName,sco.Score,sco.number

from ScoreInfo sco left join dbo.Student s

on sco.Sid=s.Sid

left join dbo.ProjectTb p

on sco.Pid=p.Pid

--6、擷取每個科目成績由高到低排序，并顯示相應的學生（同理于5）

with ScoreInfo as(

    select *,row_number() over(partition by Pid order by Score desc) number from dbo.Score

)

select p.ProjectName,s.StudentName,sco.Score,sco.number

from ScoreInfo sco left join dbo.ProjectTb p

on sco.Pid=p.Pid

left join dbo.Student s

on sco.Sid=s.Sid

--7、對6進行改進，并分數相同的顯示為并列

with ScoreInfo as(

    select *,rank() over(partition by Pid order by Score desc) number from dbo.Score

)

select p.ProjectName,s.StudentName,sco.Score,sco.number

from ScoreInfo sco left join dbo.ProjectTb p

on sco.Pid=p.Pid

left join dbo.Student s

on sco.Sid=s.Sid

--8、對7再進行改進，并分數相同的顯示為并列，把自然順序的第3顯示為第2。

--相當于Math這一科目，有2個第1名，1個第2名。不能沒有第2名，直接跳到第3名了，是吧

with ScoreInfo as(

    select *,dense_rank() over(partition by Pid order by Score desc) number from dbo.Score

)

select p.ProjectName,s.StudentName,sco.Score,sco.number

from ScoreInfo sco left join dbo.ProjectTb p

on sco.Pid=p.Pid

left join dbo.Student s

on sco.Sid=s.Sid

--9、NTILE()按指定數目進行分組。這個感覺比較随機，想到是小朋友做遊戲的分組了，哈。。。

--成績表分組。正好我們的成績表有9條資料，分3組。

with ScoreInfo as(

    select *,NTILE(3) over(order by Sid) rn from dbo.Score

)

select * from ScoreInfo

--10、在9的基礎上，再玩一下，是不是可以計算出這3組成員的總成績呢？

with ScoreInfo as(

    select *,NTILE(3) over(order by Sid) rn from dbo.Score

)

select sum(Score) from ScoreInfo

group by rn

--11、求每個學生的總成績

with ScoreInfo as(

    select *,sum(Score) over(partition by Sid) score_sum

    from dbo.Score

)

select distinct(sco.score_sum),s.StudentName

from ScoreInfo sco left join dbo.Student s

on sco.Sid=s.Sid

--總提示“'ROWS' 附近有文法錯誤。”。

select *,

sum(Score) over(ORDER BY Pid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as row1,

--sum(Score) over(partition by Sid order by Pid) row1,

--sum(Score) over(partition by Sid order by Pid rows between unbounded preceding and current row) row2

from dbo.Score

--12、求各科目的總成績

with ScoreInfo as(

    select *,sum(Score) over(partition by Pid) score_sum

    from dbo.Score

)

select distinct(sco.score_sum),p.ProjectName

from ScoreInfo sco left join dbo.ProjectTb p

on sco.Pid=p.Pid