hive的窗口函数详解 DW(数仓)和RDBS的行转列，列转行总结

1.1 hive窗口函数

窗口函数指定了函数工作的数据窗口大小(当前行的上下多少行),这个数据窗口大小可能会随着行的变化而变化.

窗口函数和聚合函数区别?

窗口函数对于每个组返回多行,组内每一行对应返回一行值.

聚合函数对于每个组只返回一行.

1.1.1在日常的开发中常用哪些类型函数

1.字符串操操作函数: split, concat

1.字符串连接函数: concat

语法:concat(string A, string B)

返回值:string

说明:返回输入字符串连接后的结果,支持任意个输入字符串

举例:

hive>select concat('abc', 'def', 'gh');

abcdefgh

2.字符串截取函数:substr, substring

语法:substr(string A, int start), substring(string A, int start)

返回值:string

说明:返回字符串A从start位置到结尾的字符串

举例:

hive>select substr('abcde', 3);

cde

hive>select substring('abcde', 3);

cde

hive> select substr('abcde', -1);

e

语法: substr(string A, int start, int len),substring(string A, int start, int len)

返回值: string

说明：返回字符串A从start位置开始，长度为len的字符串

举例：

hive> select substr('abcde',3,2) from lxw_dual;

cd

hive> select substring('abcde',3,2) from lxw_dual;

cd

hive>select substring('abcde',-2,2) from lxw_dual;

de

3.正则表达式替换函数:regexp_replace

语法:regexp_replace(stringA, stringB, stringC);

说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。

举例：

hive> select  regexp_replace('foobar', '00|ar', '');

fb

--将所有的点去掉
select regexp_replace(url,'\\.',''); 需要转义

4.分割字符串函数:split

语法: split(string str, string pat)

返回值:array

说明:按照pat字符串分割str,会返回分割后的字符串数组

举例:

hive> select split('abctcdtef', 't');

["abc","cd","ef"]

--数据url
www.itcast.cn
www.heima.com
www.itcast.com.cn

--将所有名字中包含itcast的域名过滤出来
select url from table where instr(url,'itcast') > 0;

instr: 返回这个子串再字符串中的下标位置

coalesce：返回第一个非空的值，coalesce（null，null，‘null’, 1）

对员工表中的员工奖金进行判断，如果奖金为null就显示为0，如果奖金不为null，就显示原来的值

select coalesce(奖金,0) from emp;

2.聚合函数: hive适用于分析,所以常用

3.日期函数:数仓的特征随时间变化而变化,所以时间也特别多

1.时间戳转日期函数: from_unixtime

语法: from_unixtime(bigint unixtime[, string format])

返回值: string

说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式

举例：hive> select from_unixtime(1323308943,'yyyyMMdd')

20111208

2. 获取当前UNIX时间戳函数: unix_timestamp

语法:unix_timestmp()

返回值:bigint

说明:获取当前时区的UNIX 时间戳,也可以将时间日期转换成时间戳

举例:

hive>select unix_timestmp();\\\获取当前时间戳

1323309615

hive>select unix_timestmp("2011-12-07 13:01:03");\\将时间日期转换成时间戳

1323234063

3. 日期时间转日期函数: to_date

语法:to_date(string timestamp)

返回值: string

说明:返回日期时间字段中的日期部分

举例:

hive> select to_date('2011-12-08 10:03:01');

2011-12-08

year month day hour minute second  weekofyear

4.日期比较函数:datediff

语法:datediff(string endate, string startdate)

返回值:int

说明:返回结束日期减去开始日期的天数

举例:

hive>select datediff('2012-12-08','2012-05-09');

结果相差 218天

5.日期增加函数:date_add

语法:date_add(string,startdate, int days)

返回值:string

说明:返回开始日期startdate增加days天后的日期

举例:

hive>select date_add('2012-12-08', 10);

2012-12-18

6.日期减少函数:date_sub

语法:date_sub(string startdate, int days)

返回值:string

说明:返回开始日期startdate减少days天后的日期

举例:

hive>select date_sub('2012-12-08', 10);

2012-11-28

current_date：获取今天的日期

trunc: 取今年第一天

last_day: 取这个月的最后一天

获取二月的最后一天  select last_day(date_add(trunc(current_date(), 'YY'), 31));

将 29/Aug/2021 12:30:00转换成yyyy-MM-dd HH:mm:ss
select  from_unixtime(unix_timestmp('29/Aug/2021 12:30:00', 'dd/MM/yyyy HH:mm:ss), yyyy-MM-dd HH:mm:ss);

4.窗口函数:sum() over(), count() over(),排名函数

1.1.2了解哪些窗口函数,都是什么意思

sum(col) over() : 分组对col累计求和,over() 中的语法如下

count(col) over() : 分组对col累计, over中的语法如下

min(col)  over() : 分组对col求最小

max(col)  over() : 分组对col求最大值

avg(col) over() : 分组求col列的平均值

first_value (col) over() : 某分区排序后的第一个col值

last_value(col) over() : 某分区排序后的最后一个col值

lag(col, n, DEFAULT) :统计往前n行的col值, n可选, 默认为1, DEFAULT往上第n行为NULL时,取默认值,如不指定,则为NULL

lead(col, n, DEFAULT):统计往后n行的col值, n可选, 默认为1, DEFAULT往下第n行为NULL时,取默认值,如不指定,则为NULL

ntile(n) : 用于将分组数据按照顺序切分成n片, 返回当前切片值, n必须为int类型

---------------

排名函数:

row_number() over()  :  排名函数,不会重复,适合用于生成主键或者不并列排名

rank() over () : 排名函数, 有并列名次, 名次不连续. 如: 1, 1, 3

dense_rank() over() : 排名行数, 有并列名次, 名次连续. 如:1, 1, 2

ntile:(N) over(partition by  order by ) as rn 将分区等分成 N份 同一批次的返回值rn值相等