hive 第五次作业

作业4：
SELECT * FROM (SELECT distinct user_name FROM user_trade WHERE year(dt)=2019 ) a JOIN (SELECT distinct user_name FROM user_refund WHERE year(dt)=2019 ) b on a.user_name=b.user_name;

作业5：
SELECT b.user_name,b.city FROM (SELECT distinct user_name FROM user_trade WHERE year(dt)=2018 ) a JOIN (SELECT distinct user_name, city FROM user_info) b on a.user_name=b.user_name;

作业6：
SELECT d.user_name, d.phone_brand
FROM (SELECT distinct user_name FROM trade_2017 ) a
JOIN (SELECT distinct user_name FROM trade_2018 ) b on a.user_name=b.user_name
JOIN (SELECT distinct user_name FROM trade_2019 ) c on b.user_name=c.user_name
JOIN (SELECT distinct user_name, get_json_object(extra1, ‘$.phonebrand’) as phone_brand FROM user_info ) d on c.user_name=d.user_name
LEFT JOIN (SELECT distinct user_name FROM user_refund ) e on d.user_name=e.user_name;

作业7：

作业8：
SELECT hour(b.firstactivetime),
count(a.user_name)
FROM (
SELECT user_name
FROM trade_2018
UNION
SELECT user_name
FROM trade_2019
)a
LEFT JOIN user_info b on a.user_name=b.user_name
GROUP BY hour(b.firstactivetime);