第二十章hive

1)

窗口函数

over（partition by 分组字段 order by 排序字段 rows between 起始行 and 结束行）

窗口计算范围的指定

需要使用rows 进行指定 计算行数

范围的确认：

默认情况下没有指定partition by 的字段，范围是全表，如果指定了partition by，范围是分组内的范围

可以通过rows指定计算行的范围大小 ：rows between 起始行 and 结束行

指定计算行范围后，只对范围内的数据进行计算

select * ,count(字段) over （rows between unbounded preceding and current row ）cnt from 表名

unbounded preceding: 窗口向上无限制 

current row：当前行

行数：preceding

向下指定函数： following

向下无限制：unbounded following

不同的关键字可以组合成一个范围

between 起始范围行  and 结束行 

例如:

between 2 preceding and 1 followling  包含4行数据 

范围查找是从当前行为基准  2 preceding 向上找两行  1 followling 向下一行共4行

范围顺序：起始行向上 结束行向下

order by  计算范围说明

使用了order by后自带计算范围统计数据  范围为：rows between unbounded preceding and current now

over 中的 order by 是计算数据时，先排序在计算数据， from 后的order by  是对计算后的结果排序 （可以同时出现）

2）

CTE

CTE语法类似子查询，可以将一个select语句计算的结果当成一个新的临时表使用

基本用法

with 临时表名 as（查询语句） select * from 临时表名

多个计算结果保存

with 表名1 as（查询语句），

        表名2 as （查询语句 select * from 表名1），

        表名3 as （查询语句）

select * from 表名3 join 表名2

例如 ：

with  tb1 as（select * from emp）
select ename，sal from tb1

3） 爆炸函数和合并函数  （只能hive里面用）

函数的分类：

udf （user define function） 函数   数据输入多少行就返回多少行数据  计算是一进一出
 
                   hive中的大部分函数都是udf函数          

udaf函数  （user define aggregation  function）函数  输入多行数据返回一行结果  多进一出  例如 sum avg count...

udtf函数         输入一行返回多行  一进多出

3-1  explode方法

爆炸函数  可以将数组中的数据拆分多行

explode 不能直接和其他字段出现在select中

使用侧视图的方法和其他字段一起展示

侧视图 关键字 lateral view explode（explode（split（字段，'-'））） 表名（tb1） as

select 字段，字段 from 表名 lateral view explode（split（字段，'-'））tb1 as 别名

不能简单使用join进行关联数据

3-2  collect方法  合并操作

 collect_list（字段）from 表名 ；

collect_list 合并后不会去重

collect_set 合并后会对数据进行去重

4） 随机抽样

从海量数据中随机抽取部分样本数据进行计算得到的结果趋势和整体趋势一致

select ...from tb1 tablesample（bucket x OUT y ON (colname | read ( ))）

x代表取哪组

y代表分成几组

(colname | read ( )) = 字段

或者  select ...from tb1 tablesample（bucket x OUT y ON  rabd(）)

rabd()=随机性  通过hash计算进行随机挑选

select （abs（hash（‘1aa12023-10-10’）)+ round (rand()*100)）%4

5）

虚拟列

hive 表中自带字段列，在进行select查询时没有指定，不会出现在查询结果中

可以在select中指定这些字段显示内容

INPUT_FILE_NAME  显示数据行所在的具体文件

BLOCK_OFFSET_INSIDE_FILE 显示数据行所在文件的偏移量

ROW_OFFSET_INSIDE_BLOCK 显示数据所在的hdfs块的偏移量

    此虚拟列需要设置:SET hive.exec.rowoffset=true; 才可使用.

虚拟列可以作为select的过滤条件使用