HIVE 报表的日期相关

背景：

   月初需要给财务出报表，大部分使用hive在BI平台跑的，结果会导出Excel。

知识点：

  1. 分区

   1、在Hive Select查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入了partition概念。

2、分区表指的是在创建表时指定的partition的分区空间。

3、如果需要创建有分区的表，需要在create表的时候调用可选参数partitioned by，详见表创建的语法结构。

我们的分区常见的是单分区，双分区的很少，单分区是指按天，或者按月来分的。

  SQL：

insert overwrite table .XXspace.XXorder partition(mt='201901')  select * from tablea;

这就是采用了覆盖的形式插入指定分区了。

还有一种方式，先删除指定分区再插入。

alter table **space.**table
 drop if exists partition(mt='${#date(0,-1):yyyyMM#}');

insert into table **space.**table partition(mt='${#date(0,-1):yyyyMM#}')
Select * from tablex;

这个效果是一样的。

其中的日期函数，实现了当前月份-1，就是上个月的。这样的好处是不用写死SQL，每次都要修改，只要在启动的时候输入日期即可。

类似的日期处理函数还有：

select * from **space.**table where dt=date_sub('${#date(0,0,0):yyyy-MM-01#}',1) and create_time >= '${#date(0,-1,0):yyyy-MM-01#}'
 and create_time < '${#date(0,0,0):yyyy-MM-01#}'limit 10;

这个翻译出来的效果就是：

解析完公式后的sql内容为：
select * from **space.**table where dt=date_sub('2019-06-01',1) and create_time >= '2019-05-01' and create_time < '2019-06-01'limit 10;

创建时间去了5月份的记录，分区dt是按日的。去了昨天是因以为同步的策略问题，延迟一天。

2 coalesce 的使用。

作用是将空值替换成其他值 ，COALESCE是一个函数， (expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。

在报表的实战：

  需求：统计押金的报表。

押金的特点，入职的时候交，离职的时候退，平时不参与太多业务。

select   coalesce (newtable.xxmoney,oldtable.xxmoney),XXXX,

 from    oldtable full join newtable on oldtable.id= newtable.id;

这里的newtable取得是本月的变量，可能是新增或者减少。oldtable 是上个月底的汇总数据。

这样本月有变化，就以本月为准，本月无变化，则以历史数据为准。

其实还是报标的维度相对 复杂，有计算规则，不然就直接在mysql上面查了。