Hive基础之COALESCE用法

语法: COALESCE(T v1, T v2, …)
返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

以emp表为例：

desc emp;

empno                   int                     None

ename                   string                  None

job                     string                  None

mgr                     int                     None

hiredate                string                  None

sal                     double                  None

comm                    double                  None

deptno                  int                     None

select * from emp;

7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    20

7499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   30

7521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   30

7566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    20

7654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  30

7698    BLAKE   MANAGER 7839    1981-5-1        2850.0  NULL    30

7782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    10

7788    SCOTT   ANALYST 7566    1987-4-19       3000.0  NULL    20

7839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    10

7844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     30

7876    ADAMS   CLERK   7788    1987-5-23       1100.0  NULL    20

7900    JAMES   CLERK   7698    1981-12-3       950.0   NULL    30

7902    FORD    ANALYST 7566    1981-12-3       3000.0  NULL    20

7934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    10

comm字段有空值（NULL）

select empno,ename,job,mgr,hiredate,sal, COALESCE(comm, 0),deptno from emp;

7369    SMITH   CLERK   7902    1980-12-17      800.0   0.0     20

7499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   30

7521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   30

7566    JONES   MANAGER 7839    1981-4-2        2975.0  0.0     20

7654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  30

7698    BLAKE   MANAGER 7839    1981-5-1        2850.0  0.0     30

7782    CLARK   MANAGER 7839    1981-6-9        2450.0  0.0     10

7788    SCOTT   ANALYST 7566    1987-4-19       3000.0  0.0     20

7839    KING    PRESIDENT       NULL    1981-11-17      5000.0  0.0     10

7844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     30

7876    ADAMS   CLERK   7788    1987-5-23       1100.0  0.0     20

7900    JAMES   CLERK   7698    1981-12-3       950.0   0.0     30

7902    FORD    ANALYST 7566    1981-12-3       3000.0  0.0     20

7934    MILLER  CLERK   7782    1982-1-23       1300.0  0.0     10

注意观察comm字段的值。

巴特西

Hive基础之COALESCE用法

最新文章

热门文章