HiveSQL连接查询分析练习

最新推荐文章于 2023-03-10 18:46:33 发布

XSTARK51416

最新推荐文章于 2023-03-10 18:46:33 发布

阅读量265

点赞数

文章标签： hadoop

本文链接：https://blog.csdn.net/qq_37936002/article/details/105915568

版权

本文通过一系列的HiveSQL查询需求，涵盖了连接查询、用户行为分析、交易统计等方面，旨在练习和提升在大数据场景下进行复杂SQL操作的能力。涉及到的查询包括：购买与退款用户分析、跨年交易用户筛选、用户交易频次统计、用户属性分布（如学历、性别、城市、手机品牌）等。

摘要由CSDN通过智能技术生成

HiveSQL连接查询分析练习

需求1：2019年购买后又退款的用户。

SELECT a.user_name
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;

需求2：2017年和2018年都购买了的用户。

SELECT a.user_name
FROM (SELECT distinct user_name
      FROM user_trade
      WHERE year(dt)=2017)a
JOIN 
     (SELECT distinct user_name
      FROM user_trade
      WHERE year(dt)=2018)b
on a.user_name=b.user_name;

需求3：2017，2018，2019年都有交易的用户。
写法1：

SELECT distinct a.user_name
FROM trade_2017 a
JOIN trade_2018 b on a.user_name=b.user_name
JOIN trade_2019 c on b.user_name=c.user_name;

写法2：

SELECT a.user_name
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；

当表的数量级很大时推荐使用第二种方法。

需求4：在2019年购买但是没有退款的用户。

SELECT a.user_name
FROM 
       (SELECT distinct user_name
       FROM user_trade
       WHERE year(dt)=2019)a
     LEFT JOIN
       (SELECT distinct user_name
       FROM user_refund
       WHERE year(dt)=2019)b on a.user_name=b.user_name
WHERE b.user_name is null;

需求5：2019年购买用户的学历分布情况

SELECT b.education,
       count(distinct a.user_name)
FROM
       (SELECT distinct user_name
       FROM user_trade
       WHERE year(dt)=2019)a
     LEFT JOIN
       (SELECT user_name,
               extra2['education'] as education
       FROM user_info)b
       on a.user_name=b.user_name
GROUP BY b.education;

需求6：2017，2018年都购买但是2019年没有购买的用户。

SELECT c.user_name
FROM
       (SELECT distinct a.user_name
       FROM trade_2017 a JOIN trade_2018 b on a.user_name=b.user_name)c
     LEFT JOIN
       (SELECT distinct user_name
       FROM trade_2019)d on c.user_name=d.user_name
WHERE d.user_name is null;

需求7：2017到2019年有交易的所有用户数。

SELECT count(distinct a.user_name),
       count(a.user_name)
FROM 
       (SELECT user_name
       FROM trade_2017
     UNION 
       SELECT user_name
       FROM trade_2018
     UNION 
       SELECT user_name
       FROM trade_2019)a;

需求8-1：2019年每个用户的支付退款金额汇总。

SELECT a.user_name,
       sum(a.pay_amount),
       sum(a.refund_amount)
FROM
      (SELECT user_name,
              sum(pay_amount) as pay_amount,
              0 as refund_amount
       FROM user_trade
       WHERE year(dt)=2019
       GROUP BY user_name
     UNION ALL
       SELECT user_name,
              0 as pay_amount,
              sum(refund_amount) as refund_amount
       FROM user_refund
       WHERE year(dt)=2019
       GROUP BY user_name
       )a
GROUP BY a.user_name;

需求8-2：2019年每个支付用户的支付金额和退款金额。

SELECT a.user_name,
       a.pay_amount,
       b.refund_amount
FROM 
      (SELECT user_name,
              sum(pay_amount) as pay_amount
      FROM user_trade
      WHERE year(dt)=2019
      GROUP BY user_name)a
    LEFT JOIN
      (SELECT user_name,
              sum(refund_amount) as refund_amount
      FROM user_refund
      WHERE year(dt)=2019
      GROUP BY user_name)b
    on a.user_name=b.user_name;

需求9-1：首次激活时间在2017年，但是一直没有支付的用户年龄段分布。

SELECT a.age_level,
       count(a.user_name)
FROM 
       (SELECT user_name,
               case when age<20 then '20以下'
            when age>=20 and age<30 then '20-30'
            when age>=30 and age<40 then '30-40'
            else '40以上' end as age_level
        FROM user_info
        WHERE year(firstactivetime)=2017)a
      LEFT JOIN
        (SELECT distinct user_name
        FROM user_trade
        WHERE dt>0)b
      on a.user_name=b.user_name
WHERE b.user_name is null
GROUP BY a.age_level;

需求9-2：2018，2019年交易的用户，其激活时间段分布

SELECT hour(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(firstactivetime);

需求10：2019年购买后又退款的用户性别分布。

SELECT c.sex,
       count(a.user_name)
FROM 
      (SELECT user_name
      FROM user_trade
      WHERE year(dt)=2019
      GROUP BY user_name)a
    JOIN
      (SELECT user_name
      FROM user_refund
      WHERE year(dt)=2019
      GROUP BY user_name)b
    on a.user_name=b.user_name
    LEFT JOIN
      (SELECT user_name,
              sex
       FROM user_info)c
     on b.user_name=c.user_name
GROUP BY c.sex;

需求11：在2018年购买，但是没有在2019年购买的用户城市分布。

SELECT d.city,
       count(c.user_name)
FROM 
       (SELECT a.user_name
       FROM 
               (SELECT user_name
               FROM user_trade
               WHERE year(dt)=2018
               GROUP BY user_name)a
             LEFT JOIN
               (SELECT user_name
               FROM user_trade
               WHERE year(dt)=2019
               GROUP BY user_name)b
             on a.user_name=b.user_name
               WHERE b.user_name is null)c
     LEFT JOIN
       (SELECT user_name,
               city
        FROM user_info)d
      on c.user_name=d.user_name
GROUP BY d.city;

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

SELECT d.phonebrand,
       count(c.user_name)
FROM 
       (SELECT a.user_name
       FROM
               (SELECT user_name
               FROM trade_2017
               UNION ALL
               SELECT user_name
               FROM trade_2018
               UNION ALL
               SELECT user_name
               FROM trade_2019)a
             LEFT JOIN
               (SELECT user_name
               FROM user_refund
               WHERE dt>0)b
             on a.user_name=b.user_name
       WHERE b.user_name is null)c
     LEFT JOIN
       (SELECT user_name,
               extra2['phonebrand'] as phonebrand
        FROM user_info)d
      on c.user_name=d.user_name
GROUP BY d.phonebrand;