近期Hive 使用笔记

最近使用在线数据库取数平台，hive SQL，一点小经验，记录一下：

1、 datediff()函数：前一个值减去后一个值。

select datediff("2017-06-16 15:00:01","2017-06-15 15:01:01"); --输出： 1

select datediff("2017-06-16 15:00:01","2017-06-18 16:01:01"); --输出： -2

2、语句结构问题：

第一种：

select ....

from (...) a

where ...

left join

(....) b

第二种：

select ....

from (...) a

left join

(....) b

where ...

也就是说，左联结left join放在中间、作为嵌套，还是放在最后、让代码行程成块的模块，哪种更好？其实，同等条件下，第一种阅读更清晰顺畅。但是，如果where后面的限制中，有涉及表b中的字段，就要用第二种。如果，where中内容不涉及b，可以就用第一种。

3、别名或命名问题：

通常情况下，写代码的时候，别名要用英文，不要用中文加单引号的形式，这样很多情况下会出错。顶多在最外层、select里面，别名用中文加单引号，这是用于：给输出的最终表格、设置直观的中文列名。

4、left join问题：

对于数据库里的宽表等原始表，不能直接联结等其他动作，例如

select jjr.corp_name

，count(case when (jjr.zj='A1' ) then 1 else null end)`A1人数`

from dw.dw_allinfo_agent_da

left join

(select ...

from .....)

where pt = '20180801000000'

and brand_name ='直营'

and uc_job_name in ('租赁经纪人', '综合经纪人', '买卖经纪人')

而应该：

left join

（select *

from dw.dw_allinfo_agent_da ）形成一个虚拟表。 因为数据库的表没有权限做这些行为，必须自己提取出一个虚拟表后，做自己的操作。

5、hive> to_date('2016-12-29 16:37:11') ，得到：2016-12-29

但是！where to_date('sign_time')>='2018-07-01'，得到：空值！！

正确：where to_date(sign_time)>='2018-07-01'，得到：正确的筛选作用。

另外，case when then else end 语句中，中间的条件尽量不需要用括号，能不加括号就不加括号，免得

括号太多，更易出错或者匹配不上。--永强的经验

6、同事习惯使用的写法：

,count(case when t1.on_job_status_code<>'170007001' then 1 end)`当前在职人数`

也就是说，end前面不加 else null。避免和sum(case when …)中else 1语句混淆。直接不写。

7、一般的，内层select语句不做聚合，只是构建底层表；最外层的才进行聚合。

最后，摸索使用了这么些代码软件，最后选择使用"notepad++中文版"作为工作软件，一是小巧，而是界面设置为白色背景，更符合我的习惯，最后一点就是（敲黑板，重点来了！），按住Ctrl加鼠标滚轮可以放大缩小界面，和Windows系统保持一致，非常方便了！好评，推荐下。