oracle sql和spark sql表达的差异

• 本文的创作背景
• 关于对问题实质的剖析
• 需要进行的转化

本文的创作背景

笔者是个研究生，上学期学习了各类不同的数据库和spark sql，恰好自己最近一段实习工作中，涉及到了将oracle数据库可识别的sql语句转化为spark sql语句。在完成这个工作中，进行了很多搜索，但没有专门针对将oracle数据库适用的sql语句转化为spark sql的文章。因此决定自己写一篇，能够帮到遇到类似问题的人。
本篇主要来讲oracle sql和spark sql表达的差异，之后可能会继续写相同的表达和在实际进行spark sql代码书写时常见的问题。

关于对问题实质的剖析

spark sql在学校学习的时候，似乎没有感觉到和sql有什么区别，为什么oracle数据库可以识别的sql会和sqark sql不同呢？
经过查询资料后，发现：

1. 日常适用的spark sql就是和普通的标准sql一致 ：目前我们用的Spark2.x 版本中遵循的是美国的ANSI的SQL2003完全标准sql ；
2. 而之所以oracle数据库可识别的sql语句和spark sql有较大的区别，是因为oracle中有很多自己独特的函数和表达。换言之，夹杂着oracle特有的函数和特殊表达的sql，不是标准sql。
3. 其实不同数据库，一般或多或少都有一些自己独特的函数或查询表达方式，这些差异一定程度上是简化了sql代码的书写，但是也让sql的可移植性变差了。
   结论：oracle可以适用的sql转化为spark sql的核心，就是其他的不变，更改oracle数据库独有的函数或表达方式为标准sql。

需要进行的转化

一、表示表关联.
假如A、B、C、D、E是三张表
Oracle<=> spark sql
A.id=B.id <=> A inner join B on(A.id=B.id)
B.name=C.name(+) <=> B left join C on(B.name=C.name)
C.amount_id(+)=D.amount_id <=> D left join C on (D.amount_id=C.amount_id)
换言之，（+）在=左侧，则为右关联（但都要表示为右侧的表左关联左侧的表）；（+）在=右侧，则为左关联；=两侧无（+），则为内关联。

另外，需要注意的是，在普通sql中，我们经常会用select* from 表A名and表B名 where 表A关联字段=表B关联字段。
而在spark sql中，我们一般用一个起始表将所有涉及的表都关联起来。
例如，oracle sql:

select* from A,b,c,dwhere A.id=B.id and B.name=D.name(+)and C.amount_id(+)=D.amount_id

转为spark sql：

select* from Ainner join B on(A.id=B.id)left join D on(B.name=D.name)left join C  on (D.amount_id=C.amount_id)

二、oracle数据库的decode函数。
话不多说，先看例子：
decode(X，A，B，C，D，E）
这个函数运行的结果是，当X = A，函数返回B；当X != A 且 X = C，函数返回D；当X != A 且 X != C，函数返回E。 其中，X、A、B、C、D、E都可以是表达式。

用更加抽象的定义来说明decode函数。
DECODE(变量/条件,if1,then1,if2,then2,if3,then3,…,default)
含义为
IF 变量/条件=值1 THEN
　　　　RETURN(value 1)
ELSIF 变量/条件=值2 THEN
　　　　RETURN(value 2)
　　　　…
ELSIF 变量/条件=值3THEN
　　　　RETURN(value 3)
ELSE
　　　　RETURN(default)
END IF

转为标准spark sql：
有两种等价方式：
1.简单Case函数：
CASE X
WHEN A THEN B
WHEN C THEN D
ELSE E END
2.Case搜索函数：
CASE WHEN X=A THEN B
WHEN X = C THEN D
ELSE E END

case函数是符合标准sql的，需要注意的是，case函数返回的是第一个符合条件的结果，之后不会再检索其他项。
另外需要注意：结尾必须有END。

还有些人想问，如果想把decode函数的输出结果储存为一个新的字段，怎么办呢，可以在end结尾直接加as+你想命名的名字。

三、oracle中的||。
可以视作标准sql中的concat函数，
eg.
‘ABC’||‘EFG’
等价于concat(‘ABC’, ‘EFG’)
‘ABC’||’-’||‘EFG’
等价于concat(‘ABC’,’-’,’EFG’)

四、rownum=1
rownum是oracle独有的，叫做虚列或者伪列，这个用法有什么意义，在下文再阐述。
先来看如何转化为标准sql（spark sql）：
直接上例子：

Select ks.description from kswhere aida2.attribute7=ks.flex_value_meaning(+) and ks.flex_value_set_id=1004028 and rownum=1

等价于

aida2 left join(select ks.flex_value_meaning,ks.description,row_number()over(partition by ks.flex_value_meaning order by ks.description) rn from kswhere 1=1 and ks.flex_value_set_id=1004028) tempon(aida2.attribute7=ks.flex_value_meaning and temp.rn=1)

为了再方便理解和找规律，再来一组例子：

Select ftt.operation_type_name from fttwhere ft.operation_type_id=ftt. operation_type_id and ftt.language=’ZHS’and rownum=1

等价于

ft left join((select ftt.operation_type_name, ftt. operation_type_id,row_number()Over (partition by ftt. operation_type_id order by ftt.operation_type_name) rnfrom ftt where 1=1 and ftt.language=’ZHS’)tmp4on(ft.operation_type_id=ftt. operation_type_id and tmp4.rn=1)

总结一下规律：
原来的源表left join
(
select 本来需要提取的字段+表关联字段+row_number()
over (partition by 表关联字段 order by 本来需要提取的字段) rn
from 表 where 原本的条件+‘1=1’
） 命名的别名例如tmp
on（表关联条件 and tmp.rn=1）

对rownum使用中几种现象的分析说明
下面认识使用rownum的几种现象:

（1） select rownum,id from loaddata where rownum != n 是返回前n-1条数据。(2) select rownum from loaddata where rownum <n y也是返回前n-1条数据。解释：可以这样理解，rownum为n-1后，下个待取的记录的rownum为n，因条件为 !=n，所以删掉该记录。然后取下一条，其rownum又是10，也删掉。以此类推。所以只会显示前面n-1条记录。（3） select rownum,id from loaddata where rownum >1时查不到一条记录，而 rownum >0或rownum >=1 却总显示所有记录。解释：这是因为rownum是总是从1开始的。不取出rownum=1的列，之后的列就都取不出来。（可看作rownum=1的列因不符合>1的条件，被删掉，下一列也就成了rownum=1，因此全部删掉（不是真的删，只是不被筛选出来），所有列都是因为rownum=1不符合条件而没有被选出来。