我有一个表如下,它有开始范围和结束范围列。
+------+----------+--------+--+
| f1 | start_r | end_r |
+------+----------+--------+--+
| ABC | 31 | 29 |
+------+----------+--------+--+
我需要使用start\u r和end\u r中的值,并产生如下输出(在start和end范围之间生成递减1的序列号)
f1 seq_no
ABC 31
ABC 30
ABC 29
我只需要一个方法来生成值。。Hive里有没有什么内置功能?
2条答案
按热度按时间cwdobuhd1#
编辑:
已替换
split(space(start_r - end_r - 1),'')
与split(space(start_r - end_r),' ')
```select t.f1
,t.start_r - pe.i as seq_no
from (select 'ABC' as f1,31 as start_r,29 as end_r) t
lateral view posexplode(split(space(start_r - end_r),' ')) pe as i,s
;
hive> select t.f1,t.start_r - pe.i as seq_no from (select 'ABC' as f1,31 as start_r,29 as end_r) t lateral view posexplode(split(space(start_r - end_r - 1),'')) pe as i,s;
OK
ABC 31
ABC 30
ABC 29
4nkexdtk2#
请使用下面的查询;n(n)为(选择31
union all select n-1 from n where n>29)select n from n order by n desc option(最大递归1000);