Hive连接查询

Hive连接查询

需求1：某年度对用户满意度进行调研分析，找出目标人群。
参考实现：2019年购买又退款的客户

select a.user_name
from
  (select distinct user_name
  from user_trade
  where year(dt)=2019) as a
join
  (select distinct user_name
  from user_refund
  where year(dt)=2019) as b
on a.user_name=b.user_name;

注意：一定要先去重再做表连接

需求3：高忠诚度用户的匹配分析，用以生成心路历程类报表推送给用户，找出目标人群。
参考实现：2017、2018、2019年都有交易的用户

select a.user_name
from
  (select distinct user_name
  from trade_2017) as a
  join
  (select distinct user_name
  from trade_2018) as b on a.user_name=b.user_name
  join
  (select distinct user_name
  from trade_2019) as c on b.user_name=c.user_name;

需求4：无退款服务类用户的定位分析，用以发送服务判断类用户调研参考实现：2019年购买且无退款的用户

select distinct trade_2019.user_name
from trade_2019
left join 
  (select distinct user_name
  from user_refund
  where year(dt)=2019) as a
on a.user_name=trade_2019.user_name
where a.user_name is null;

需求：2017-2019年，有交易但是没退款的用户的手机品牌分布

select get_json_object(extra1,'$.phonebrand') as phonebrand,
       count(c.name) as name_num
from user_info
join
(
select distinct a.user_name as name
from
    (
    select user_name
    from trade_2017
    union
    select user_name
    from trade_2018
    union
    select user_name
    from trade_2019
    ) as a
  left join 
    (select distinct user_name
    from user_refund
    where year(dt)=2017 or year(dt)=2018 or year(dt)=2019) as b
on a.user_name=b.user_name
where b.user_name is null
) as c
on c.name=user_info.user_name
group by get_json_object(extra1,'$.phonebrand');