SQL 窗口查询引入了三个新的概念:窗口分区、窗口帧、以及窗口函数。 PARTITION 语句会按照一个或多个指定字段,将查询结果集拆分到不同的 窗口分区 中,并可按照一定规则排序。如果没有 PARTITION BY,则整个结果集将作为单个窗口分区;如果没有 ORDER BY,我们则无法定义窗口帧,进而整个分区将作为单个窗口帧进行处理。 窗口帧 用于从分区中选择指定的多条记录,供窗口函数处理。Hive 提供了两种定义窗口帧的形式:ROWS 和 RANGE。两种类型都需要配置上界和下界。例如,ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示选择分区起始记录到当前记录的所有行;SUM(close) RANGE BETWEEN 100 PRECEDING AND 200 FOLLOWING 则通过 字段差值 来进行选择。如当前行的 close 字段值是 200,那么这个窗口帧的定义就会选择分区中 close 字段值落在 100 至 400 区间的记录。以下是所有可能的窗口帧定义组合。如果没有定义窗口帧,则默认为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 窗口函数 会基于当前窗口帧的记录计算结果。Hive 提供了以下窗口函数: 通常情况下,RANK() 在遇到相同值时会返回同一个排名,并 跳过 下一个排名序数。如果想保证排名连续,可以改用 DENSE_RANK() 这个函数 在SQL处理中,窗口函数都是最后一步执行,而且仅位于Order by子句之前 可以想象成sql的输出结果,就是窗口函数输入的结果 实操-- 默认false 不用设置,设置了远程插数据不行
set hive.exec.mode.local.auto=false;
create table if not exists t_user_cost(
name string comment '用户名',
ymd string comment '月份',
cost int comment '花费'
) comment '用户花费表'
row format delimited fields terminated by ",";
load data local inpath '/root/data/user_cost.csv' into table t_user_cost;
select * from t_user_cost;
-- 视图
CREATE VIEW test_view(
ymd,
name_length
)
AS SELECT ymd,length(name) FROM t_user_cost;
select * from test_view;
--一般,聚合函数返回行数都会比原有行数少 但我们又想看原始函数,又想看聚合的值,怎么办,在聚合函数上加over()
select name,count(1) over()
from t_user_cost
where substring(ymd,1,7) = '2015-01';
--a3,3
--a2,3
--a1,3
--查询分区子句,将数据按照边界值分组,而over()之前的函数在每个分组内执行
select name,ymd,cost,sum(cost) over(partition by month(ymd)) from t_user_cost;
--假如我们想把cost按月进行累加,这时我们引入order by 子句
--order by 子句会让输入数据强制排序
select name,ymd,cost,sum(cost) over(partition by month(ymd) order by cost) from t_user_cost;
select name,ymd,cost,
sum(cost) over() sample1, -- 所有行累加
sum(cost) over(partition by name) sample2, -- 按照name相加
sum(cost) over(partition by name order by cost) sample3, --按照name累加
sum(cost) over(partition by name order by cost rows between unbounded preceding and current row) sample4, --和sample3一样的效果
sum(cost) over(partition by name order by cost rows between 1 preceding and current row) sample5, -- 当前行和上一行相加
sum(cost) over(partition by name order by cost rows between 1 preceding and 1 following) sample6, -- 上一行、当前行、后一行相加
sum(cost) over(partition by name order by cost rows between current row and unbounded following) sample7 -- 当前行到末尾
from t_user_cost;
--- 排序 row_number() 和 rank() 和 dense_rank()
select name,ymd,cost,
row_number() over(partition by name order by cost),
rank() over(partition by name order by cost),
dense_rank() over(partition by name order by cost)
from t_user_cost;
--lag和lead函数
--lag()函数是在窗口内,在指定列上,取上N行的数据,并且有默认值。没有设置默认值的话,为null ; lead相反,往下取
--第一个参数是列名,第二个参数是取上多少行的数据,第三个参数是默认值
select name,ymd,cost,
lag(ymd,1,'2015-06-13') over(partition by name order by ymd),
lag(ymd,2,'2015-06-13') over(partition by name order by ymd),
lead(ymd,1,'2015-06-13') over(partition by name order by ymd)
from t_user_cost;
-- first_value() 和 last_value() 当前分区的第一个值和最后一个值
select name,ymd,cost,
first_value(ymd) over(partition by name order by ymd),
last_value(ymd) over(partition by name order by ymd)
from t_user_cost;