Java教程
Hive 之函数
本文主要是介绍Hive 之函数，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
查看系统函数
-- 查看系统自带函数 show functions; -- 显示自带函数的用法 desc function upper; desc function extended upper;
日期函数
-- 当前前日期 current_date带不带小括号都行
select current_date, unix_timestamp();
+-------------+-------------+
| _c0 | _c1 |
+-------------+-------------+
| 2021-10-06 | 1633488449 |
+-------------+-------------+
-- 时间戳转日期
select from_unixtime(1633488449), from_unixtime(1633488449, 'yyyyMMdd'), from_unixtime(1633488449, 'yyyy-MM-dd HH:mm:ss');
+----------------------+-----------+----------------------+
| _c0 | _c1 | _c2 |
+----------------------+-----------+----------------------+
| 2021-10-06 10:47:29 | 20211006 | 2021-10-06 10:47:29 |
+----------------------+-----------+----------------------+
---- 日期转时间戳
select unix_timestamp('2021-10-06 10:47:29');
+-------------+
| _c0 |
+-------------+
| 1633488449 |
+-------------+
-- 计算时间差
select datediff('2020-04-18','2019-11-21');
+-------+
| _c0 |
+-------+
| 149 |
+-------+
select datediff('2019-11-21', '2020-04-18');
+-------+
| _c0 |
+-------+
| -149 |
+-------+
-- 查询当月第几天
select dayofmonth(current_date);
+------+
| _c0 |
+------+
| 6 |
+------+
-- 计算月末:
select last_day(current_date);
+-------------+
| _c0 |
+-------------+
| 2021-10-31 |
+-------------+
-- 当月第1天:
select date_sub(current_date, dayofmonth(current_date)-1)
+-------------+
| _c0 |
+-------------+
| 2021-10-01 |
+-------------+
-- 下个月第1天:
select add_months(date_sub(current_date,
dayofmonth(current_date)-1), 1)
+-------------+
| _c0 |
+-------------+
| 2021-11-01 |
+-------------+
-- 字符串转时间（字符串必须为：yyyy-MM-dd格式）
select to_date('2020-01-01'), to_date('2020-01-01 12:12:12');
+-------------+-------------+
| _c0 | _c1 |
+-------------+-------------+
| 2020-01-01 | 2020-01-01 |
+-------------+-------------+
字符串函数
-- lower 转小写。 -- upper 转大写
select lower("HELLO WORLD"), upper("hello world");
+--------------+--------------+
| _c0 | _c1 |
+--------------+--------------+
| hello world | HELLO WORLD |
+--------------+--------------+
--求字符串长度。length
select length(ename), ename from emp limit 3;
+------+--------+
| _c0 | ename |
+------+--------+
| 5 | SMITH |
| 5 | ALLEN |
| 4 | WARD |
+------+--------+
-- 字符串拼接。 concat / ||
select empno,
ename,
empno || " " ||ename idname1,
concat(empno, " " ,ename) idname2
from emp limit 3;
+--------+--------+-------------+-------------+
| empno | ename | idname1 | idname2 |
+--------+--------+-------------+-------------+
| 7369 | SMITH | 7369 SMITH | 7369 SMITH |
| 7499 | ALLEN | 7499 ALLEN | 7499 ALLEN |
| 7521 | WARD | 7521 WARD | 7521 WARD |
+--------+--------+-------------+-------------+
SELECT concat_ws('.', 'www', array('baidu', 'com'));
+----------------+
| _c0 |
+----------------+
| www.baidu.com |
+----------------+
SELECT concat_ws(" ", "hello", "hive");
+-------------+
| _c0 |
+-------------+
| hello hive |
+-------------+
-- 求子串。substr，两个参数 index (从1开始)和length
SELECT substr('www.baidu.com', 5),substr('www.baidu.com', -5),substr('www.baidu.com', 5, 5);
+------------+--------+--------+
| _c0 | _c1 | _c2 |
+------------+--------+--------+
| baidu.com | u.com | baidu |
+------------+--------+--------+
-- 字符串切分。split，注意 '.' 要转义
select split("www.baidu.com", "\\."),split("2021-10-7", "-");
+------------------------+--------------------+
| _c0 | _c1 |
+------------------------+--------------------+
| ["www","baidu","com"] | ["2021","10","7"] |
+------------------------+--------------------+
数学函数
-- 四舍五入。round select round(314.15926),round(314.15926, 2),round(314.15926, -2); +------+---------+------+ | _c0 | _c1 | _c2 | +------+---------+------+ | 314 | 314.16 | 300 | +------+---------+------+ -- 向上取整。ceil select ceil(3.1415926); +------+ | _c0 | +------+ | 4 | +------+ -- 向下取整。floor select floor(3.1415926); +------+ | _c0 | +------+ | 3 | +------+
条件函数
-- if (boolean testCondition, T valueTrue, T valueFalseOrNull)
select sal ,
if (sal < 1500,1 ,if (sal < 3000,2,3)),
case when sal < 1500 then 1
when sal < 3000 then 2
else 3 end level
from emp;
+-------+------+--------+
| sal | _c1 | level |
+-------+------+--------+
| 800 | 1 | 1 |
| 1600 | 2 | 2 |
| 1250 | 1 | 1 |
| 2975 | 2 | 2 |
| 1250 | 1 | 1 |
| 2850 | 2 | 2 |
| 2450 | 2 | 2 |
| 3000 | 3 | 3 |
| 5000 | 3 | 3 |
| 1500 | 2 | 2 |
| 1100 | 1 | 1 |
| 950 | 1 | 1 |
| 3000 | 3 | 3 |
| 1300 | 1 | 1 |
+-------+------+--------+
-- -- CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
select emp.deptno,
case deptno
when 10 then 'accounting'
when 20 then 'research'
when 30 then 'sales'
else 'unknown' end deptname
from emp;
+-------------+-------------+
| emp.deptno | deptname |
+-------------+-------------+
| 20 | research |
| 30 | sales |
| 30 | sales |
| 20 | research |
| 30 | sales |
| 30 | sales |
| 10 | accounting |
| 20 | research |
| 10 | accounting |
| 30 | sales |
| 20 | research |
| 30 | sales |
| 20 | research |
| 10 | accounting |
+-------------+-------------+
-- case when a = b then c
-- when a = d then e
-- when a = f then g
-- else h end name
select deptno,
case when deptno = 10 then 'accounting'
when deptno = 20 then 'research'
when deptno = 30 then 'sales'
else 'unknown' end deptname
from emp;
+---------+-------------+
| deptno | deptname |
+---------+-------------+
| 20 | research |
| 30 | sales |
| 30 | sales |
| 20 | research |
| 30 | sales |
| 30 | sales |
| 10 | accounting |
| 20 | research |
| 10 | accounting |
| 30 | sales |
| 20 | research |
| 30 | sales |
| 20 | research |
| 10 | accounting |
+---------+-------------+
-- COALESCE(T v1, T v2, ...)。返回参数中的第一个非空值；如果所有值都为
select sal, comm,coalesce(comm, 0) from emp;
+-------+-------+-------+
| sal | comm | _c2 |
+-------+-------+-------+
| 800 | NULL | 0 |
| 1600 | 300 | 300 |
| 1250 | 500 | 500 |
| 2975 | NULL | 0 |
| 1250 | 1400 | 1400 |
| 2850 | NULL | 0 |
| 2450 | NULL | 0 |
| 3000 | NULL | 0 |
| 5000 | NULL | 0 |
| 1500 | 0 | 0 |
| 1100 | NULL | 0 |
| 950 | NULL | 0 |
| 3000 | NULL | 0 |
| 1300 | NULL | 0 |
+-------+-------+-------+
-- isnull(a) isnotnull(a)
SELECT comm,isnull(comm) FROM EMP WHERE isnull(comm);
+-------+-------+
| comm | _c1 |
+-------+-------+
| NULL | true |
| NULL | true |
| NULL | true |
| NULL | true |
| NULL | true |
| NULL | true |
| NULL | true |
| NULL | true |
| NULL | true |
| NULL | true |
+-------+-------+
SELECT comm,isnotnull(comm) FROM EMP WHERE isnotnull(comm);
+-------+-------+
| comm | _c1 |
+-------+-------+
| 300 | true |
| 500 | true |
| 1400 | true |
| 0 | true |
+-------+-------+
-- nvl(T value, T default_value)
select comm,nvl(comm,0) from emp;
+-------+-------+
| comm | _c1 |
+-------+-------+
| NULL | 0 |
| 300 | 300 |
| 500 | 500 |
| NULL | 0 |
| 1400 | 1400 |
| NULL | 0 |
| NULL | 0 |
| NULL | 0 |
| NULL | 0 |
| 0 | 0 |
| NULL | 0 |
| NULL | 0 |
| NULL | 0 |
| NULL | 0 |
+-------+-------+
-- nullif(x, y) 相等为空，否则为x
SELECT nullif("b", "b"), nullif("b", "a");
+-------+------+
| _c0 | _c1 |
+-------+------+
| NULL | b |
+-------+------+
这篇关于Hive 之函数的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
结对编程到底难不难？答案在这里06-26
-
《2023版Java工程师》课程升级公告06-19
-
matplotlib作图不显示3D图，怎么办？06-15
-
03-Loki 日志监控06-15
-
04-让LLM理解知识 -Prompt06-15
-
做软件测试需要懂代码吗？06-05
-
14-ShardingSphere的分布式主键实现06-05
-
为什么以及如何要进行架构设计权衡？06-03
-
全网首发第二弹！软考2024年5月《软件设计师》真题+解析+答案！（11-20题）05-31
-
全网首发！软考2024年5月《软件设计师》真题+解析+答案！（21-30题）05-31
-
【Java】百万数据excel导出功能如何实现05-30
-
我们小公司，哪像华为一样，用得上IPD（集成产品开发）？05-30
-
java excel上传--poi05-30
-
安装笔记本应用商店的pycharm，再安排pandas等模块，说是没有打包工具?05-30
-
java11新特性05-29
栏目导航
