大数据开发第16课 hive 视图及窗口函数

1. 视图在Hive的用法和SQL视图用法相同。它是一个标准的RDBMS概念
2. 窗口函数

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 提供了以下窗口函数：

• FIRST_VALUE(col), LAST_VALUE(col) 可以返回窗口帧中第一条或最后一条记录的指定字段值；
• LEAD(col, n), LAG(col, n) 返回当前记录的上 n 条或下 n 条记录的字段值；
• RANK(), ROW_NUMBER() 会为帧内的每一行返回一个序数，区别在于存在字段值相等的记录时，RANK() 会返回相同的序数；
• COUNT(), SUM(col), MIN(col) 和一般的聚合操作相同

通常情况下，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;