SQL相关问题小结

记录一些工作中遇到的问题，主要用的是presto查询，有一些跟hive语法的差异也会记录下来。

1.两表join时查询时条件放在on后与where后的区别

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
在使用left jion时，on和where条件的区别如下：

• on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
• where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

2. 查看分区

SHOW PARTITIONS 
FROM table1
ORDER BY p__month

3. 解析json格式数据

有时会遇到一些json格式的文本数据，我们查的时候需要解析才能方便阅读。
例：
列名：scores
内容：{“scores”: {“1”: {“score”: 0.6638045310974121, “score_dict”: null}, “3”: {“score”: 0.8710731267929077, “score_dict”: null}, “4”: {“score”: 0, “score_dict”: {“PA”: 0.025380000472068787, “TL”: 0.00005527259054360911, “EDU”: 0.0030061593279242516, “HIP”: 0.03352535516023636, “AH-DD”: 0.000055262891692109406, “ROPDD”: 0.27052855491638184, “ROPPA”: 0.10355506092309952, “AH-LTC”: 0.00005526926906895824, “ROPHIP”: 0.00008689505921211094, “ANNUITY”: 0.10434700548648834, “ROP CANCER”: 0.0000552666861040052, “ELDER CANCER”: 0.09742642194032669}}}}
加入我们拿每个类别的分数，比如：PA
Hive：

get_json_object(scores,'$.scores.4.score_dict.PA') as PA_score

presto:

json_extract_scalar(scores,'$.scores.4.score_dict.PA') as PA_score

但是如果某些类别中含有-和空格这种特殊符号,如果直接写会报错,需要加上[“”]括起来

json_extract_scalar(scores,'$.standard_scores.4.score_dict["AH-LTC"]') as AH-LTC_score

4. char和varchar的区别

CHAR与VARCHAR类型类似，但它们保存和检索的方式不同。CHAR有固定的长度，而VARCHAR属于可变长的字符类型。它们最大长度和是否尾部空格被保留等方面也不同。在存储和检索过程中不进行大小写转换。
由于某种原因CHAR有固定长度，所以在处理速度上要比VARCHAR快很多，但是相对浪费存储空间，所以对存储不大，但在速度上有要求的可以使用CHAR类型，反之可以用VARCHAR类型来实现。

文字字段若长度固定，如：身分证号码，就不要用 varchar 或 nvarchar，应该用 char 或 nchar。
支持多语言的站点应考虑使用 Unicode nchar 或 nvarchar 数据类型以尽量减少字符转换问题。
文字字段若长度不固定，如：地址，则该用 varchar 或 nvarchar。除了可节省存储空间外，存取硬盘时也会较有效率。

5. presto中MD5函数的使用

Presto中MD5函数传入的是binary类型，返回的也是binary类型，要对字符串进行MD5操作时，需要转换.

--presto
SELECT to_hex(md5(to_utf8(phone)))
--hive
select md5(phone)

6. presto的为空判断

presto中是coalesce（）
hive中是nvl（）

Coalese函数的作用是的NVL的函数有点相似，其优势是有更多的选项。

格式如下：
Coalesce(expr1, expr2, expr3…… exprn)
Coalesce是这样来处理这些参数的。如果第一个参数为空，则看第二个参数是否是空，否则则显示第一个参数，如果第二个参数是空再看第三个参数是否为空，否则显示第二个参数，依次类推。这个函数实际上是NVL的循环使用

NVL函数的格式如下：NVL(expr1,expr2)
含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。
NVL2函数的格式如下：NVL2(expr1,expr2, expr3)
含义是：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。

7. IN/NOT IN前后的对比值如果不是key值，需要排除掉null值，否则结果为0

例:

select count(cust_id)
from table1
where phone in (select phone from table2)
-- 如果查询的table2中的phone有空值,那么结果就为0,正确写法如下:
select count(cust_id)
from table1
where phone in (select phone from table2 where phone is not null)

8. UNION

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
--默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
--另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

9. 最大值greatest/max

greatest按行的求最大值
max按列求最大值

10. 字符串分割

Hive
split( 'abcdef' , 'c' )[0]
--结果：'ab'

Presto
split_part( 'abcdef' , 'c' , 2 )
--结果：'ab'

--注意：Hive索引从0开始，presto索引从1开始。