其他sql格式也在更新中,可直接查看这个系列,要是没有你需要的格式,可在评论或私信我
Oracle转换hive
oracle的rownum和hive的row_number()理论
oracle | hive | 示例 |
---|---|---|
rownum | row_number()over() | oracle查询数据默认自带rownum。 hive中不带 ,查询时需要自己加上 :row_number() over (partition by contno order by statetype ) rownum 注: partition by 后面跟分组 order by 后面跟排序字段 |
oracle的rownum和hive的row_number()示例代码
一条关联条件的时候sql改造
oracle 原sql
select
(
select bankname
from B
where bankcode = a.bankcode
and rownum = 1
) as bankname
from
A a
hive 改sql
select
b.bankname as bankname
from
A a
LEFT JOIN
(select bankname ,row_number() over (partition by bankcode order by bankname ) num, bankcode
from B
) b
on b.bankcode = a.bankcode
and b.num = 1
讲解(个人理解):
(1),partition by中就等于是分组的用处,根据bankcode 来分组,在外面on中拿到 b.bankcode 数值和a表的bankcode值比较,如果不止一个条件在partition by bankcode后面加逗号连接和上篇from中子查询一样,算了在写一个吧
(2),order by 就是分组,根据什么来排序,后面b.num = 1就是显示第一条,我的理解就是oracle里面有自带的排序函数,所以直接rownum =1就行了,hive要自己写而已
多条关联条件的时候sql改造
oracle 原sql
select
(
select bankname
from B
where bankcode = a.bankcode
and id = a.id
and rownum = 1
) as bankname
from
A a
hive 改sql
select
b.bankname as bankname
from
A a
LEFT JOIN
(select bankname ,row_number() over (partition by bankcode,id order by bankname ) num, bankcode,id
from B
) b
on b.bankcode = a.bankcode
and b.id=a.id
and b.num = 1
讲解:多条件直接在后面加逗号关联,多加了一个id字段