hive中取上月份及迪卡尔积交叉连接

1、hive中上个月份的获取

select SUBSTR(DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()),30),1,7) ;
select SUBSTR(DATE_SUB(concat('2018-02','-01'),28),1,7);
select SUBSTR(DATE_SUB(concat('2018-01','-01'),28),1,7);

其中，减去的日期为28天以内较好，免得2月份减到上上个月的情况。 

2、hive中的迪卡尔连接

set hive.mapred.mode=nonstrict;
select a1.id room_id,a2.pt_month
from oss_bi_all_room a1 
join (select distinct pt_month from oss_bi_all_finance_salary_record where pt_month between '2018-01' and '2018-10') a2 on 1=1
where a1.pt_day='2018-11-29' and a1.state=0
  and a1.id in(9899999,
2323239);

set hive.mapred.mode=nonstrict;
select a1.id room_id,a2.pt_month
from oss_bi_all_room a1 
join (select distinct pt_month from oss_bi_all_finance_salary_record where pt_month between '2018-01' and '2018-10') a2
where a1.pt_day='2018-11-29' and a1.state=0
  and a1.id in(9899999,
2323239);

要先设置成nonstrict模式，然后join on 1=1;其中后面的on 1=1可不写，非stric是必须的。

3、综合运用

set hive.mapred.mode=nonstrict;
with tab_salary as(
select pt_month,room_id,sum(case when type=1 then amount else 0 end) rank_income_amount,sum(case when type=2 then amount else 0 end) basic_income_amount
from oss_bi_all_finance_salary_record
where type in(1,2) and state=0
group by pt_month,room_id),
tab_gift as(
select pt_month,room_id,sum(gift_point)/1000/2 gift_point_rmb_income
from honeycomb_all_gift_record
group by pt_month,room_id),
tab_room as(
select a1.id room_id,a2.pt_month
from oss_bi_all_room a1 
join (select distinct pt_month from oss_bi_all_finance_salary_record where pt_month between '2018-01' and '2018-10') a2 on 1=1
where a1.pt_day='2018-11-29' and a1.state=0
  and a1.id in(9899999,
2323239)
)
select a1.pt_month,a1.room_id,coalesce(a2.rank_income_amount,0) rank_income_amount,coalesce(a2.basic_income_amount,0) basic_income_amount,coalesce(gift_point_rmb_income,0) gift_point_rmb_income
from tab_room a1
left join tab_salary a2 on a1.room_id=a2.room_id and a1.pt_month=SUBSTR(DATE_SUB(concat(a2.pt_month,'-01'),28),1,7)
left join tab_gift a3 on a1.room_id=a3.room_id and a1.pt_month=a3.pt_month
;
OK
2018-01 9899999   0.0     100000.0        10338.648
2018-02 9899999   0.0     88000.0 14191.239
2018-03 9899999   0.0     100000.0        13164.395
2018-04 9899999   0.0     100000.0        7861.15
2018-05 9899999   0.0     100000.0        9669.08
2018-06 9899999   0.0     100000.0        15429.467
2018-07 9899999   0.0     100000.0        49247.515
2018-08 9899999   0.0     100000.0        25654.0705
2018-09 9899999   0.0     92000.0 12261.001
2018-10 9899999   0.0     100000.0        14887.223
2018-01 2323239   0.0     36000.0 33853.8615
2018-02 2323239   0.0     64000.0 46537.42
2018-03 2323239   0.0     92000.0 70557.952
2018-04 2323239   0.0     44000.0 38767.9975
2018-05 2323239   0.0     100000.0        29619.5615
2018-06 2323239   0.0     100000.0        28079.6235
2018-07 2323239   0.0     88000.0 62496.0235
2018-08 2323239   0.0     8000.0  30227.96
2018-09 2323239   0.0     0.0     45338.275
2018-10 2323239   0.0     8000.0  55650.4345
Time taken: 377.051 seconds, Fetched: 20 row(s)