SQL 问题汇总

1. How do we convert a string into Array in hive?

https://stackoverflow.com/questions/45965889/how-do-we-convert-a-string-into-array-in-hive

2. 行转列

https://zhuanlan.zhihu.com/p/115913870

3. Hive sql left join 丢失null值

背景：TABLE1中dept_id有null，导致left join之后丢失null的数据。

-- 1.错误的写法：
SELECT
    a.user_id
    ,b.dept_name
FROM TABLE1 AS a
LEFT JOIN TABLE2 AS b ON a.dept_id=b.dept_id AND a.date=b.date
WHERE a.date='${date}' AND b.date='${date}'

-- 2.正确写法：
SELECT
    a.user_id
    ,b.dept_name
FROM 
    (    
        SELECT  
            user_id
            ,NVL(dept_id, '') AS dept_id
        FROM TABLE1
        WHERE date = '${date}'
    ) AS a
LEFT JOIN 
    (
        SELECT 
            NVL(dept_id, '') AS dept_id
            ,NVL(dept_name, '') AS dept_name
        FROM TABLE2
        WHERE date='${date}'
    ) AS b ON a.dept_id=b.dept_id

4. 正则表达式–反斜杠转义问题

https://blog.youkuaiyun.com/jinixin/article/details/56705284

5. 条件写在where和on的区别