hive如何实现oracle的connect by prior函数网站首页 技术交流

hive如何实现oracle的connect by prior函数

cclovezbf 2024-07-13 12:01:02

简介hive如何实现oracle的connect by prior函数

Hive中如何实现层级查询类似oracle中 connect by prior 实现的效果？ - 知乎

大佬写的很详细，有兴趣自己看，但是存在一个问题

create table test.emp
(
empno string,
ename string,
job string,
mgr string,
hiredate string,
sal string,
comm string,
deptno string
) stored as orc;

insert into test.emp
values (7369, 'SMITH', 'CLERK', 7902, 1980 - 12 - 17, 800.00, null, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, 1981 - 02 - 20, 1600.00, 300.00, 30),
(7521, 'WARD', 'SALESMAN', 7698, 1981 - 02 - 22, 1250.00, 500.00, 30),
(7566, 'JONES', 'MANAGER', 7839, 1981 - 04 - 02, 2975.00, null, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, 1981 - 09 - 28, 1250.00, 1400.00, 30),
(7698, 'BLAKE', 'MANAGER', 7839, 1981 - 05 - 01, 2850.00, null, 30),
(7782, 'CLARK', 'MANAGER', 7839, 1981 - 06 - 09, 2450.00, null, 10),
(7788, 'SCOTT', 'ANALYST', 7566, 0087 - 07 - 13, 3000.00, null, 20),
(7839, 'KING', 'PRESIDENT', null, 1981 - 11 - 17, 5000.00, null, 10),
(7844, 'TURNER', 'SALESMAN', 7698, 1981 - 09 - 08, 1500.00, 0.00, 30),
(7876, 'ADAMS', 'CLERK', 7788, 0087 - 07 - 13, 1100.00, null, 20),
(7900, 'JAMES', 'CLERK', 7698, 1981 - 12 - 03, 950.00, null, 30),
(7902, 'FORD', 'ANALYST', 7566, 1981 - 12 - 03, 3000.00, null, 20),
(7934, 'MILLER', 'CLERK', 7782, 1982 - 01 - 23, 1300.00, null, 10);

select *
from test.emp;