关于hive当中获取时间的操作。

今天突然在网上看到别人的面试题，觉得有点好奇想做一下，突然发现sql server的语法对于hive是不适用的。于是各种查资料。

然后发现hive中没有直接获取到当前时间的函数，而且对于时间定义的函数特别少，问题解决起来还是有难度。

下面介绍hive获取时间的方式：

题目：给定一张表（列有月份，销售额），要求查询出月份、本月销售额、上月销售额这三个结果，如果当月上个月的销售额不存在就显示为“*”。

看到这个我第一个想到的就是根据时间来进行分组然后累加求和，然后对分组累加的数据按照月份进行排序，取出前两条数据，然后结束。

看起来思路是对的，可是实际推敲有很多的问题。

（1）比如给定的时间是带日期的并不是按照月份来分的。比如 2018-08-12 像这种的额么分组。还是要将日期进行截取，然后分组累加才行。

（2）题目是获取到当前月份和上个月的数据。万一表中没有存当前的月份，上个月的也没有存怎么办。获取的数据不就不准确了。

针对上面的问题我们肯定要对日期进行截取，然后分组累加，这是毋庸置疑的。另外就是要限定死当前月份和上个月的额月份。然后用限定死的月份去和表中的数据进行join操作

然后问题来了：在写hql的时候发现各种问题，hive获取时间的操作都很少。

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());  hive是通过获取到时间戳的形式来获取到时间，然后通过函数FROM_UNIXTIME 转化为日期时间的形式。

OK 2018-08-12 19:02:58

然后通过substr()函数获取到当前的月份；

  SELECT SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,7) AS mo;
   OK
  2018-08

这下问题来了。获取到了当期的日期怎么获取到上个月的时间那。尝试用下面的函数进行获取；

SELECT SUBSTR(DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()),30),1,7) AS mo  

通过DATE_SUB()函数对获取的时间戳进行操作，对日期进行相减，突然发现要得到上个月的时间，我们的月份的时间都不确定，这里用30天，肯定不行。

有开始发现新的方法：

 SELECT SUBSTR(DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()),DAYOFMONTH(FROM_UNIXTIME(UNIX_TIMESTAMP()))),1,7) AS mo

还是使用的DATE_SUB对日期进行操作，这次是将后面那个参数设置为获取到当前的日期，相减就是上个月最后一天，也是代表上个月。

于是问题得到了解决；

下面是改造完成的sql:

WITH  mon AS (

     SELECT SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,7) AS mo
     UNION ALL
     SELECT SUBSTR(DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()),DAYOFMONTH(FROM_UNIXTIME(UNIX_TIMESTAMP()))),1,7) AS mo

)  --这里采用了with as的形式对经常操作的数据进行存储，起到的相当于中间表的作用。

    SELECT
    mo AS mth,
    CASE WHEN SUM(nvl(AMOUNT,0))=0 THEN '*'
    ELSE
    CAST(SUM(nvl(AMOUNT,0) AS VARCHAR(10))) END AS SUM_AMOUN
    FROM
    mon
    LEFT JOIN tb2 ON mo=SUBSTR(SALEDATE,1,7)
    GROUP BY mo;

对临用左连接的方式去连接主表，然后进行查询。

 

对于Hive 的函数。有时候真的很麻烦。有时候还需要自定义函数进行操作。