NVL(T value, T default_value)
- 定义：
nvl(T value, T default_value)
- 返回值类型：T
- 说明：给NULL值赋默认值
- 可用版本：hive-0.11+
- 案例：
SELECT comm, NVL(comm, 'Hello') FROM emp WHERE ename = 'SMITH';
+
| comm | _c1 |
+
| NULL | Hello |
+
SELECT comm, NVL(comm, -99) FROM emp WHERE ename = 'SMITH';
+
| comm | _c1 |
+
| NULL | -99.0 |
+
SELECT comm, NVL(comm, ename) FROM emp WHERE ename = 'SMITH';
+
| comm | _c1 |
+
| NULL | SMITH |
+
CASE WHEN
- 定义
CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] ENDCASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
- 返回值类型：T
- 说明
- 如果 a = b，返回 c；如果 a = d，返回 e；否则返回 f。
- 如果a = true，返回b；如果c = true，返回d；否则返回e。
- 案例
SET hive.exec.mode.local.auto=true;
CREATE TABLE IF NOT EXISTS case_when_test (
name STRING,
sex STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
INSERT INTO TABLE case_when_test VALUES
('小明', '男'),
('小红', '女'),
('小李', '未知');
SELECT
name,
CASE sex WHEN '男' THEN 1 WHEN '女' THEN 0 ELSE -99 END AS sex_num
FROM case_when_test;
SELECT
name,
CASE WHEN sex = '男' THEN 1 WHEN sex = '女' THEN 0 ELSE -99 END AS sex_num
FROM case_when_test;
+
| name | sex_num |
+
| 小明 | 1 |
| 小红 | 0 |
| 小李 | -99 |
+
IF(BOOLEAN testCondition, T valueTrue, T valueFalseOrNull)
- 定义：
if(boolean testCondition, T valueTrue, T valueFalseOrNull)
- 返回值类型：T
- 说明：如果testCondition = true，返回 valueTrue，否则返回 valueFalseOrNull
- 案例
SET hive.exec.mode.local.auto=true;
CREATE TABLE IF NOT EXISTS if_test (
name STRING,
sex STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
INSERT INTO TABLE if_test VALUES
('小明', '男'),
('小红', '女'),
('小张', NULL),
('小李', '未知');
SELECT
name,
IF(sex = '男', 1, IF(sex = '女', 0, -99)) AS sex_num
FROM if_test;
+
| name | sex_num |
+
| 小明 | 1 |
| 小红 | 0 |
| 小张 | -99 |
| 小李 | -99 |
+
ISNULL()和IS NULL
- 定义：
isnull(a)
- 返回值类型：boolean
- 说明：如果 a IS NULL，返回true，否则返回false；IS NULL要写在WHERE关键字之后用于过滤字段值为NULL的数据。
- 案例
SELECT comm, ISNULL(comm) FROM emp WHERE empno IN (7369, 7499);
+
| comm | _c1 |
+
| NULL | true |
| 300.0 | false |
+
SELECT ename FROM emp WHERE deptno IS NULL;
+
| ename |
+
| KING |
+
SELECT ename FROM emp WHERE ISNULL(deptno) = true;
+
| ename |
+
| KING |
+
ISNOTNULL()和IS NOT NULL
- 定义：
isnotnull(a)
- 返回值类型：boolean
- 说明：如果 a IS NOT NULL，返回true，否则返回false；IS NOT NULL要写在WHERE关键字之后用于过滤字段值不为NULL的数据。
- 案例
SELECT comm, ISNOTNULL(comm) FROM emp WHERE empno IN (7369, 7499);
+
| comm | _c1 |
+
| NULL | false |
| 300.0 | true |
+
SELECT ename FROM emp WHERE ISNOINULL(deptno) = true;
+
| ename |
+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+
SELECT ename FROM emp WHERE deptno IS NOT NULL;
+
| ename |
+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+
NULLIF()
- 定义：
nullif(a,b)
- 返回值类型：T
- 说明：如果a = b，返回NULL，否则返回a
- 可用版本：hive-2.3.0
SET hive.exec.mode.local.auto=true;
CREATE TABLE IF NOT EXISTS nullif_test (
a STRING,
b STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
INSERT INTO TABLE nullif_test VALUES
('Hello', 'Hello'),
('Hello', 'World'),
('Hello', NULL),
(NULL, 'World'),
(NULL, NULL);
SELECT a, b, NULLIF(a, b) FROM nullif_test;
+
| a | b | _c2 |
+
| Hello | Hello | NULL |
| Hello | World | Hello |
| Hello | NULL | Hello |
| NULL | World | NULL |
| NULL | NULL | NULL |
+
ASSERT_TRUE(BOOLEAN condition)
- 定义：
assert_true(boolean condition)
- 返回值类型：void
- 说明：如果条件判断为true，返回NULL，否则抛出异常
- 可用版本：hive-0.8.0+
- 案例
SELECT ASSERT_TRUE(1 < 2);
+
| _c0 |
+
| NULL |
+
SELECT ASSERT_TRUE(1 < 0);
Error: java.io.IOException: org.apache.hadoop.hive.ql.metadata.HiveException: ASSERT_TRUE(): assertion failed. (state=,code=0)
COALESCE
- 定义：
COALESCE(T v1, T v2, ...)
- 返回值类型：T
- 说明：传入多列，返回不是NULL的第一个值，如果全为NULL，则返回NULL
- 案例
SELECT mgr, sal, deptno, COALESCE(mgr, sal, deptno) FROM emp WHERE empno IN (7521, 7839);
+
| mgr | sal | deptno | _c3 |
+
| NULL | 1250.0 | 30 | 1250.0 |
| NULL | NULL | NULL | NULL |
+
