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值相等