侧边栏壁纸
  • 累计撰写 128 篇文章
  • 累计创建 13 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

【ORACLE】21c版本中分析函数的加强(window、exclude、groups)

DarkAthena
2021-11-22 / 0 评论 / 0 点赞 / 1181 阅读 / 6848 字

前言

在21c版本中,分析窗口得到了增强,支持了WINDOW子句,并且在窗口中增加支持EXCLUDE及GROUPS。
以下以HR Schema Demo为例,可以在墨天轮在线实训环境进行验证
Oracle 21c 实训环境

1.window

在21c之前,如果我们要对同一个窗口进行多次统计分析,一般是像下面这么写

select EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       salary,
       sum(salary) over(partition by DEPARTMENT_ID) sum_salary,
       min(salary) over(partition by DEPARTMENT_ID) min_salary,
       max(salary) over(partition by DEPARTMENT_ID) max_salary
  from hr.employees;

其中的窗口部分“(partition by DEPARTMENT_ID)”重复出现了3次,让语句显得太过冗余,但在21c版本后,支持将window子句单独提出来,可以使sql得到很大的简化,像下面这样

select EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       salary,
       sum(salary) over w1 sum_salary,
       min(salary) over w1 min_salary,
       max(salary) over w1 max_salary
  from hr.employees
  window w1 as (partition by DEPARTMENT_ID);

还可以支持多个窗口,像下面这样

select EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       salary,
       sum(salary) over w1 sum_salary,
       min(salary) over w1 min_salary,
       max(salary) over w1 max_salary,
       sum(salary) over w2 sum_salary2,
       min(salary) over w2 min_salary2,
       max(salary) over w2 max_salary2
  from hr.employees
  window w1 as (partition by DEPARTMENT_ID),
         w2 as (partition by job_id);

你还可以定义一个窗口,然后在原有的over子句中再对此窗口缩小范围,实现“窗口中的窗口”

select row_number() over w1 as row_order,
       salary,
       avg(salary) over(w1 rows between unbounded preceding and current row) as avg_rolling,
       avg(salary) over(w1 rows between unbounded preceding and unbounded following) as avg_all
  from hr.employees 
  window w1 as(order by salary);

2.GROUPS

GROUPS是用于window子句中的,之前只有ROWS和RANGE,比如下例
(备注:以下sql为了说明range的用法,union all了一个特别的值2401)

select row_number() over(order by salary) as row_order,
       salary,
       avg(salary) over w1 as avg_rows,
       avg(salary) over w3 as avg_range,
       avg(salary) over w2 as avg_groups
  from (select salary
          from hr.employees
         where salary <= 2500
        union all
        select 2401 from dual) 
window w1 as(order by salary rows between 1 preceding and current row), 
       w2 as(order by salary groups between 1 preceding and current row),
       w3 as(order by salary range between 1 preceding and current row);

ROW_ORDERSALARYAVG_ROWSAVG_GROUPSAVG_RANGE
12100210021002100
2220021502166.666666666672200
3220022002166.666666666672200
42400230023002400
52400240023002400
624012400.52400.333333333332400.33333333333
725002450.52485.857142857142500
8250025002485.857142857142500
9250025002485.857142857142500
10250025002485.857142857142500
11250025002485.857142857142500
12250025002485.857142857142500
  • avg_rows 当前行的前一行到当前行的平均
  • avg_groups 按值相等的行进行分组,计算当前组的上一组到当前组的平均,比如第5行,当前组是2400,第4、5行为一组,上一组是同为2200的第2、3行,所以是2、3、4、5行求平均
  • avg_range 当前行的值减1,得到的值对应的行 到当前行的平均 ,比如第6行2401-1为2400,等于2400的有第4、5行,所以是4、5、6三行求平均

3.EXCLUDE

  • EXCLUDE NO OTHERS: 什么都不排除。相当于没有使用EXCLUDE。
  • EXCLUDE CURRENT ROW : 仅排除当前行。
  • EXCLUDE GROUP : 排除与当前行具有相同值的所有行和当前行。
  • EXCLUDE TIES : 排除与当前行具有相同值的所有行,但不排除当前行。

(备注:下面这个例子我先构造了一个1到7的数字序列,然后让数字5出现了3次)

with t1 as
 (select rownum value from dual connect by rownum <= 7 
 union all 
 select 5 from dual
union all 
select 5 from dual
)
select value,
avg(value) over w1 ex_current_row,
avg(value) over w2 ex_group,
avg(value) over w3 ex_ties
  from t1 
window w1 as(order by value rows between 1 preceding and 1 following exclude current row),
       w2 as(order by value rows between 1 preceding and 1 following exclude group),
       w3 as(order by value rows between 1 preceding and 1 following exclude ties)
VALUEEX_CURRENT_ROWEX_GROUPEX_TIES
1221.5
2222
3333
4444
54.544.5
555
55.565.5
6666
7666.5
  • EX_CURRENT_ROW 为前一行到后一行这三行剔除当前行后的平均值
  • EX_GROUP_ROW 为前一行到后一行这三行剔除和当前值相等的所有行后,再算平均。比如第5行value等于5,它的前一行是4,后一行是5,剔除和它相等的行后,得4的平均值为4;而三个5中间的那个5对应的计算结果由于3行数据全部都被剔除,所以为空
  • EX_TIES_ROW 为前一行到后一行这三行,剔除和当前值相等的所有行但不剔除当前行后,算平均。同样以第5行为例,和GROUP的区别在于,它不剔除当前行,和它相等的第6行已经被剔除,因此是第4行的4和第5行的5计算平均得4.5

参考官方文档 Analytic Functions

0
  1. 支付宝打赏

    qrcode alipay
  2. 微信打赏

    qrcode weixin
博主关闭了所有页面的评论