hive经典解法总结

最新推荐文章于 2023-12-09 21:54:40 发布

A_static500

最新推荐文章于 2023-12-09 21:54:40 发布

阅读量257

点赞数

分类专栏： hive 文章标签：大数据 hive spark sql

本文链接：https://blog.csdn.net/A_static/article/details/125778049

版权

hive 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本文通过两个实例展示了大数据处理中的实时统计与行列转换问题。第一个例子讲解了如何统计商品同一时刻最多浏览人数，利用时间流思想，将进入和离开行为标记并累加。第二个案例探讨了如何在国庆前三天每天统计各品类商品的收藏和购买量，通过避免笛卡尔积的方法巧妙地整合数据。这两部分都体现了高效处理大数据的策略。

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

一、同时在线问题

问题引入：统计每个商品同一时刻最多在浏览的人数，例如同一时刻有进入也有离开，
先记录用户数增加再记录减少，按照最大的人数降序排序。

建表语句及数据导入：

--建表语句
DROP TABLE IF EXISTS user_action_log;
CREATE TABLE user_action_log (
      `user_id` varchar(32) ,
      `id` varchar(32) ,
      `start_time` timestamp  ,
      `end_time` timestamp
) COMMENT '用户行为日志表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
        NULL DEFINED AS ''
    LOCATION '/warehouse/sdc/rds/user_action_log' ;
--数据插入
INSERT INTO user_action_log VALUES ('101','9001','2021-11-01 02:00:00','2021-11-01 02:00:11'),('102','9001','2021-11-01 02:00:09','2021-11-01 02:00:38'),('103','9001','2021-11-01 02:00:28','2021-11-01 02:00:58'),('104','9002','2021-11-01 03:00:45','2021-11-01 03:01:11'),('105','9001','2021-11-01 02:00:51','2021-11-01 02:00:59'),('106','9002','2021-11-01 03:00:55','2021-11-01 03:01:24'),('107','9001','2021-11-01 02:00:01','2021-11-01 02:01:50');

思路分析：把时间比作一个流，进入某个特定时间流（本题指代进入浏览页面时间流）的用户在总数量上加一，离开特定时间流的用户在总数量上减一；时间一定具有先后，所以对时间升序排列后能够获得时间流中某个时间点，把进入这个时间点的行为标记为‘﹢1’，把离开时间点的行为标记为‘-1’；因为一个用户不可能同时浏览一个页面两次，所以不需要对数据进行去重操作。

解题代码：

select id,
       max(dynamic_exist) max_same_time_exist
from (
         select id,
                sum(flag) over (partition by id order by dt asc) dynamic_exist
         from (
                  select id,
                         start_time dt,
                         1          flag
                  from user_action_log
                  union
                  select id,
                         end_time,
                         -1
                  from user_action_log
              ) t1
     )t2
group by id
order by max_same_time_exist desc;

总结：此题解题代码并不复杂，最需要理解的是“时间流”的概念，是一种简单的流式处理思想。

二、对于“行列转换问题”的深层思考

问题引入：现在有一份数据，其中记录了国庆前后每个品类商品的收藏量和购买量；本题有三张表，分别是：用户收藏表、用户购买表、商品分类表；

需求：请统计国庆前三天的每一天的最近一周的每个品类下商品收藏量和购买量，假设前三天每天的最近一周都有记录。

建表语句及数据导入：

DROP TABLE IF EXISTS product_prop;
CREATE TABLE product_prop (
      `id` varchar(32) ,
      `name` varchar(32) ,
      `category` varchar(32)
)  COMMENT '商品属性表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
        NULL DEFINED AS ''
    LOCATION '/warehouse/sdc/rds/product_prop';
DROP TABLE IF EXISTS product_purchase;
CREATE TABLE product_purchase (
      `id` varchar(32) ,
      `user_id` varchar(32) ,
      `date` date
)  COMMENT '商品购买明细表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
        NULL DEFINED AS ''
    LOCATION '/warehouse/sdc/rds/product_purchase';
DROP TABLE IF EXISTS product_favor;
CREATE TABLE product_favor (
     `id` varchar(32) ,
     `user_id` varchar(32) ,
     `date` date
)  COMMENT '商品收藏明细表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
        NULL DEFINED AS ''
    LOCATION '/warehouse/sdc/rds/product_favor';
INSERT INTO product_prop VALUES ('1','xiaomi','phone'),('2','vivo','phone'),('3','apple','phone'),('4','dami','food'),('5','kuzi','cloth');
INSERT INTO product_purchase VALUES ('1','1002','2021-09-25'),('1','1003','2021-09-27'),('1','1006','2021-10-01'),('1','1007','2021-10-03'),('2','1001','2021-09-24'),('2','1002','2021-09-25'),('2','1003','2021-09-27'),('2','1005','2021-09-30'),('2','1006','2021-10-01'),('2','1007','2021-10-02'),('3','1001','2021-09-24'),('3','1002','2021-09-25'),('3','1003','2021-09-29'),('3','1005','2021-09-30'),('3','1006','2021-10-01'),('3','1007','2021-10-02'),('4','1001','2021-09-24'),('4','1002','2021-09-25'),('4','1003','2021-09-26'),('5','1005','2021-09-27'),('5','1006','2021-10-01'),('5','1008','2021-10-02'),('5','1007','2021-10-03');
INSERT INTO product_favor VALUES ('1','1001','2021-09-24'),('1','1002','2021-09-25'),('1','1003','2021-09-26'),('1','1005','2021-09-30'),('1','1006','2021-10-01'),('1','1007','2021-10-03'),('2','1001','2021-09-24'),('2','1002','2021-09-25'),('2','1003','2021-09-26'),('2','1005','2021-09-30'),('2','1006','2021-10-01'),('2','1007','2021-10-02'),('3','1001','2021-09-24'),('3','1002','2021-09-25'),('3','1003','2021-09-26'),('3','1005','2021-09-30'),('3','1006','2021-10-01'),('3','1007','2021-10-02'),('4','1001','2021-09-24'),('4','1002','2021-09-25'),('4','1003','2021-09-26'),('5','1005','2021-09-27'),('5','1006','2021-10-01'),('5','1007','2021-10-03');

思路分析：此题如果单独求收藏量或者购买量其实难度不大，但是该题需要整合这两种需求的结果显示于一张表上，这样可以对数据做对比分析；问题在于选用何种方式更加简便的整合数据，通过explode(array('2021-10-01','2021-10-02','2021-10-03'))的方式似乎可以对数据进行按天匹配，但是会造成笛卡尔积，使得同一份数据被强制扩大三倍，不适用于大数据场景；此时可以考虑使用array(array(),array(),array())嵌套的写法再通过下标取值匹配的方式，该写法避免了笛卡尔积以及数据量被强制扩大的情况。

解答代码：

select category,
       g[1] date_point,
       sum(if(g[2]='favor',g[0],null)) favor,
       sum(if(g[2]='buy',g[0],null)) buy
from (
         select category,
                `array`(`array`(count(if(`date` <= '2021-10-01' , 1, null)),'2021-10-01','buy'),
                        `array`(count(if(`date` <= '2021-10-02' and `date` >= date_sub('2021-10-02', 6), 1, null)),'2021-10-02','buy'),
                        `array`(count(if(`date` >= date_sub('2021-10-03', 6), 1, null)),'2021-10-03','buy')) gather
         from product_purchase pp
                  join product_prop ppp
                       on pp.id = ppp.id
         where `date` <= '2021-10-03' and `date` >= date_sub('2021-10-01', 6)
         group by category
         union all
         select category,
                `array`(`array`(count(if(`date` <= '2021-10-01',1, null)),'2021-10-01','favor'),
                        `array`(count(if(`date` <= '2021-10-02' and `date` >= date_sub('2021-10-02', 6), 1, null)),'2021-10-02','favor'),
                        `array`(count(if(`date` >= date_sub('2021-10-03',6), 1, null)),'2021-10-03','favor'))
         from product_favor pf
                  join product_prop ppp on pf.id = ppp.id
         where `date` <= '2021-10-03' and `date` >= date_sub('2021-10-01', 6)
         group by category
     )t1 lateral view explode(gather) tmp as g
group by category, g[1];

总结：此题思路上并不复杂，重点在于如何对多张表的数据进行变换处理，去避免不必要的笛卡尔积以及膨胀数据的情况。

注：有什么更好的写法或者需要改进的地方，欢迎大家留言交流，共同进步。