有关开窗函数OVER()的SUM(), AVG(), MAX(), MIN(), COUNT()

sum() over()(partition by ), 联想avg()，max(), min(), count()sum() over()(partition by order by )，联想avg(), count()

---

数据表： 名为GRADE 的表格，记录学生id, 学生多次考试成绩score, 考试日期dt等数据

iddtscore12019-05-01100………

1. sum() over()(partition by ) :

分组列表，属于同一id的每条数据总成绩相同： select id,score, sum(score) over(patition by id) total_scores from GRADE

查询结果：

idscoretotal_scores1100198198198295190295190 同分组仅取1个值： select distict id,total_scores from( select id,score, sum(score) over(patition by id) total_scores from GRADE) tb

查询结果：

idtotal_scores11982190

2. sum() over()(partition by order by ) :

分组列表，组内按排列顺序求累加和, 每组的相同level分享同一累加值。

select id,score, sum(score) over(patition by id order by score) cumm_scores from GRADE

查询结果：

idscorecumm_scores198981100198295190295190

3.逐行累加:

相同id共享一样的累加和

select *, (select (sum(score) from GRADE t2 where t2.id <= t1.id)as cumm_score from GRADE t1 order by id

查询结果：

idscorecumm_scores1981981100198295388295388

个人SQL学习笔记，欢迎阅览，欢迎指出问题！！！