Hive使用笔记

注意事项

select user_id from b_order bo limit 1 union all select user_id from temp_b_order tbo limit 1;

-- 对于union如果存在相同的数据记录会被合并，而union all不会合并相同的数据记录
-- hive union 必须有 all，且需要在子查询里进行

select unix_timestamp('2017-07-20 00:10:00') from temp limit 1;
vs
select unix_timestamp('2017-07-20') from temp limit 1;
下面的unix_timestamp返回null

select id,name from prefix_user
where created>=unix_timestamp('2017-07-20') and created<unix_timestamp('2017-07-30')  ;

例子

1. left join

select distinct(user_id) as uid
from prefix_salelog s 
left join (
      SELECT 
      v.inner_code as inner_code, 
      place_name, -- 场所
      node_name  -- 点位名称
      FROM vms v
      LEFT JOIN  nodes n ON v.node_id = n.node_id
      LEFT JOIN node_place np ON np.id= n.node_place
) v on s.inner_code = v.inner_code
where dt in ('2017-06', '2017-07')
and pay_type_id=11 and order_id<>0 and place_name='制造业'

-- 制造业,教育业
-- select * from ucore.node_place 

2. 按天统计1元2元的销售笔数

SELECT created_date,
count(if(unitPrice-discountPrice=100,true,null)) as yh1,
count(if(unitPrice-discountPrice=200,true,null)) as yh2
FROM prefix_order WHERE ubox_app_type='app_type' and created_date>='2016-11-09'
GROUP BY created_date ORDER BY created_date;

3. select子查询

SELECT count(1) as num, b.youhui
FROM (SELECT unitPrice-discountPrice as youhui FROM prefix_order WHERE ubox_app_type='app_type' and created_date>='2016-11-09') as b
GROUP BY b.youhui;

4. 按月统计，字符串截取函数

SELECT count(distinct uid) as num, substr(created_date,0,7) as m
FROM prefix_order 
where pay_type_id=11
group by substr(created_date,0,7);