玖叶教程网

1、over函数介绍：

over开窗函数，主要与聚合函数搭配使用构成分析函数。与聚合函数的不同之处在于：对于每个组返回多行，而聚合函数对每个组只返回一行。开窗函数指定了分析函数数据窗口的大小。

语法：over（partition by column1 order by column2）按照column2排序累计，根据column1进行分区。

2、开窗的窗口范围：

三种开窗方式：rows、range、Specifying

over（order by ... range between n preceding and n following）：窗口范围为当前行数据幅度减n加n后的范围内的。
over（order by ... rows between n preceding and n following）：窗口范围为当前行前后各移动n行。

3、partition by 与group by的区别：

group by 和 partition by 都有分组统计的功能，但是partition by并不具有group by的汇总功能。
partition by统计的每一条记录都存在，而group by将所有的记录汇总成一条记录(类似于distinct去重)。
partition by可以和聚合函数结合使用，同时具有其他高级功能。

4、通常与over()函数结合使用的函数：

rank() over(partition by ... order by ...)：分组后的跳跃排序。
dense_rank() over(partition by ... order by ...)：分组后的连续排序。
row_number() over(partition by ... order by ...)：分组后的行数（去重）。
first_value() over(partition by ... order by ...)：求分组后的第一个值。
last_value() over(partition by ... order by ...)：求分组后的最后一个值。
sum() over(partition by ... order by ...)：求分组后的累计求和。
count() over(partition by ... order by ...)：求分组后的总数。
max() over(partition by ... order by ...)：求分组后的最大值。
min() over(partition by ... order by ...)：求分组后的最小值。
avg() over(partition by ... order by ...)：求分组后的平均值。
lag() over(partition by ... order by ...)：取出前n行数据。　
lead() over(partition by ... order by ...)：取出后n行数据。
ratio_to_report() over(partition by ... order by ...)：ratio_to_report() 括号中就是分子，over() 括号中就是分母。

5、日常运用实例：

（1）sum()求和汇总：查询某一时间段内门诊医生接诊量和当月门诊接诊总量：

SELECT to_char(a.visit_date, 'yyyy-mm') AS year_month,
  a.doctor,
COUNT(1) AS NUM,
SUM(COUNT(1)) OVER(PARTITION BY to_char(a.visit_date, 'yyyy-mm') ORDER BY a.doctor) AS step_total_num, --分步汇总
SUM(COUNT(1)) OVER(PARTITION BY to_char(a.visit_date, 'yyyy-mm')) AS total_num --总汇总
FROM CLINIC_MASTER a
WHERE TRUNC(a.visit_date) BETWEEN
TRUNC(to_date('2022-08-01', 'yyyy-mm-dd')) AND
TRUNC(to_date('2022-08-31', 'yyyy-mm-dd'))
GROUP BY to_char(a.visit_date, 'yyyy-mm'),a.doctor;

（2）rank(),dense_rank()：门诊医生接诊人次汇总后按接诊人次进行排名

SELECT to_char(a.visit_date, 'yyyy-mm') AS year_month,
a.doctor,
COUNT(1) AS NUM,
RANK() OVER(PARTITION BY to_char(a.visit_date, 'yyyy-mm') ORDER BY COUNT(1) DESC) AS rank_over, --有相同排序值时，名次跳跃排序
DENSE_RANK() OVER(PARTITION BY to_char(a.visit_date, 'yyyy-mm') ORDER BY COUNT(1) DESC) AS dense_rank --有相同排序值时，名次连续排序 FROM CLINIC_MASTER a
WHERE TRUNC(a.visit_date) BETWEEN
TRUNC(to_date('2022-08-01', 'yyyy-mm-dd')) AND
TRUNC(to_date('2022-08-31', 'yyyy-mm-dd'))
GROUP BY to_char(a.visit_date, 'yyyy-mm'),a.doctor;

（3）first_value(),last_value()：门诊医生接诊分组排序后显示第一条数据中医生字段值

SELECT to_char(a.visit_date, 'yyyy-mm') AS year_month,
a.doctor,
COUNT(1) AS NUM,
first_value(a.doctor) OVER(PARTITION BY to_char(a.visit_date, 'yyyy-mm') ORDER BY COUNT(1) DESC) AS first_value,
last_value(a.doctor) OVER(PARTITION BY to_char(a.visit_date, 'yyyy-mm') ORDER BY COUNT(1) DESC) AS last_value
FROM CLINIC_MASTER a
WHERE TRUNC(a.visit_date) BETWEEN
TRUNC(to_date('2022-08-01', 'yyyy-mm-dd')) AND
TRUNC(to_date('2022-08-31', 'yyyy-mm-dd'))
GROUP BY to_char(a.visit_date, 'yyyy-mm'),a.doctor;

（4）lag(),lead()：门诊医生接诊分组排序后查询排在当前医生前面和后面的医生姓名，如无则显示’NONE’

SELECT to_char(a.visit_date, 'yyyy-mm') AS year_month,
a.doctor,
COUNT(1) AS NUM,
LAG(a.doctor,1,'NONE') OVER(PARTITION BY to_char(a.visit_date, 'yyyy-mm') ORDER BY COUNT(1) DESC) AS lag_value,
LEAD(a.doctor,1,'NONE') OVER(PARTITION BY to_char(a.visit_date, 'yyyy-mm') ORDER BY COUNT(1) DESC) AS lead_vale
FROM CLINIC_MASTER a
WHERE TRUNC(a.visit_date) BETWEEN
TRUNC(to_date('2022-08-01', 'yyyy-mm-dd')) AND
TRUNC(to_date('2022-08-31', 'yyyy-mm-dd'))
GROUP BY to_char(a.visit_date, 'yyyy-mm'),a.doctor;