OVER 子句

over() 子句

  • 窗口函数用于计算基于组的某种聚合值

    • 窗口函数对于每个组返回多行
    • 而聚合函数对每个组只返回一行

  • 窗口函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

  • 而在使用窗口函数时，需要通过 over() 子句执行 “开窗”

  • 示例表：

    • -- 示例表
drop table if exists test.test;
create table IF NOT EXISTS test.test(
    name string,
    salary int,
    dept string
);
insert into test.test values
('刘一', 5000, '行政'),
('陈二', 6000, '行政'),
('张三', 5500, '人事'),
('李四', 4500, '人事'),
('王五', 12000, '技术'),
('赵六', 15000, '技术'),
('孙七', 15000, '技术'),
('周八', 8000, '销售'),
('吴九', 10000, '销售'),
('郑十', 7500, '销售');
select * from test.test;

  • 在不使用 over() 子句时，要获取各员工工资占总工资支出比重时：

    • -- 允许笛卡尔积
set hive.strict.checks.cartesian.product=false;

-- 利用笛卡尔积进行联表（tmp子表只有一行结果）
with tmp as(
    select sum(salary) as total from test.test
)
select * from test.test, tmp;

with tmp as(
    select sum(salary) as total from test.test
)
select
    test.name, test.salary, tmp.total,
    round(test.salary/tmp.total * 100, 2)
from test.test, tmp;

    • 084

  • 而使用 over() 子句时，可以这样获取：

    • select
    name, salary,
    sum(salary) over(),
    round(salary/sum(salary) over() * 100, 2)
from test.test;

    • 085

partition by 子句

  • 直接使用 <聚合函数> over() 的形式，相当于将所有结果开一个窗

  • 而配合 partition by 子句，可以按指定的字段进行分组开窗

    • select
    name, salary, dept,
    sum(salary) over(),
    sum(salary) over(partition by dept)
from test.test;

    • 086

order by 子句

  • 按指定的字段对数据进行排序，并在完成排序之后，再 “逐行” 执行聚合函数

    • select
    name, salary, dept,
    sum(salary) over(order by name),
    sum(salary) over(partition by dept order by name)
from test.test;

    087

    • 注意：对于并列排序，并列执行聚合函数：

      • select
    name, salary, dept,
    count(salary) over(order by salary),
    max(salary) over(order by salary),
    sum(salary) over(order by salary) 
from test.test;

    088

  • 支持在后面添加 desc ，实现从大到小排序