当前位置: 首页 > article >正文

HiveSQL——条件判断语句嵌套windows子句的应用

注:参考文章:

SQL条件判断语句嵌套window子句的应用【易错点】--HiveSql面试题25_sql剁成嵌套判断-CSDN博客文章浏览阅读920次,点赞4次,收藏4次。0 需求分析需求:表如下user_idgood_namegoods_typerk1hadoop1011hive1221sqoop2631hbase1041spark1351flink2661kafka1471oozie108以上数据._sql剁成嵌套判断https://blog.csdn.net/godlovedaniel/article/details/118220935

0 需求

   基于下表的表结构及数据,求出每个用户每次搜索非广告类型的商品位置排序。假设字段goods_type为26代表该商品类型是广告。

想达到的效果:

1 数据加载

--建表
create table window_goods_test (
user_id int,    --用户id
goods_name string,  --商品名称
goods_type int, --标识每个商品的类型,比如广告,非广告
rk int  --这次搜索下商品的位置,比如第一个广告商品就是1,后面的依次2,3,4...
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

-- 加载数据
load data local  inpath '/opt/module/hive_data/window_goods_test.txt' into table window_goods_test ;

 vim window_goods_test.txt

1    hadoop    10    1
1    hive    12    2
1    sqoop    26    3
1    hbase    10    4
1    spark    13    5
1    flink    26    6
1    kafka    14    7
1    oozie    10    8

2 数据分析

    代码分析:最开始的思路:先过滤掉非广告的商品,再重新排序

select user_id,
       goods_name,
       goods_type,
       rk,
       if(goods_type != 26,row_number() over (partition by user_id order by rk),null) as rk1
from window_goods_test;

输出结果如下:显然没有达到预期的结果。

出错原因在于对窗口函数的执行原理及顺序不了解,可以通过执行计划来判断SQL执行顺序。

explain 
select user_id,
       goods_name,
       goods_type,
       rk,
       if(goods_type != 26,row_number() over (partition by user_id order by rk),null) as rk1
from window_goods_test;

具体执行步骤如下:

(1)扫描表

(2)按照user_id分组

(3)按照user_id和rk进行升序排序

(4)执行row_number()函数进行分析

(5)使用if进行判断

   由执行计划可以得出 if函数是在row_number()函数之后执行的

  上述sql可以拆解为三部分进行执行:

 step1:扫描表,获取select的结果集

select user_id,
       goods_name,
       goods_type,
       rk
from window_goods_test;

step2:执行窗口函数

select user_id,
    goods_name,
    goods_type,
    rk,
   row_number() over (partition by user_id order by rk) as rk1
from window_goods_test;

step3:基于step2结果集执行 if判断

因此正确代码如下:

方式一:union all 拆解成两段逻辑

-- 第一段逻辑:先限制goods_type != 26,再排序
select
    user_id,
    goods_name,
    goods_type,
    rk,
    row_number() over (partition by user_id order by rk) as rk1
from window_goods_test
where goods_type != 26
union all
-- 第二段逻辑:将goods_type = 26的记录的rk1 直接记为null
select
    user_id,
    goods_name,
    goods_type,
    rk,
    null as rk1
from window_goods_test
where goods_type = 26
order by rk;

 上述代码的缺点:window_goods_test表需要扫描两次,显然不是最优解。

优化的解题思路为:

方式二:

  step1:  partition by分组中先进行 if 语句过滤,如果goods_type!=26则取对应的user_id 进行分组,如果goods_type=26 则置为随机数rand(), 再按照随机数分组

 ps: 这里采用随机数是考虑到万一 goods_type=26的记录数很多,通过rand()随机分组可以将key值打散避免数据倾斜

select
    user_id,
    goods_name,
    goods_type,
    rk,
    row_number() over (partition by
        if(goods_type != 26, user_id, rand())
        order by rk) rk1
from window_goods_test

step2:在step1的外侧利用 if函数进一步判断


select
    user_id,
    goods_name,
    goods_type,
    rk,
    if(goods_type != 26,
        row_number() over (partition by if(goods_type != 26, user_id, rand()) order by rk),
        null) rk1
from window_goods_test

3 小结

   通过本案例得出的结论:

  • case when或if语句中嵌套窗口函数时,条件判断语句的执行顺序是在窗口函数之后的;
  • 窗口函数partition by 子句中是允许嵌套条件判断语句的;

http://www.kler.cn/a/234767.html

相关文章:

  • windows11家庭版安装docker无法识别基于wsl2的Ubuntu
  • 网络下载ts流媒体
  • 探索AI代理在《我的世界》中的奇妙之旅:代理IP的角色与影响
  • VSCode:IDE显示设置 --自定义字体及主题颜色
  • 运维工程师面试系统监控与优化自动化与脚本云计算的理解虚拟化技术的优点和缺点
  • BunkerWeb 开源项目安装与使用教程
  • UR10+gazebo+moveit吸盘抓取搬运demo
  • 数据分析基础之《pandas(8)—综合案例》
  • 解决Spring Boot Configuration Annotation Processor not configured的问题
  • KY139 毕业bg
  • 协议-TCP协议-基础概念04-可能发生丢包的位置-linux配置项梳理(TCP连接的建立和断开、收发包过程)
  • 更换商品图片日期JSON格式报错 - 序列化与反序列化日期格式设置
  • springcloud分布式架构网上商城源码和论文
  • Linux笔记之Docker进行镜像备份与迁移
  • openresty (nginx)快速开始
  • 【数学建模】【2024年】【第40届】【MCM/ICM】【F题 减少非法野生动物贸易】【解题思路】
  • 分享76个时间日期JS特效,总有一款适合您
  • Redis进阶(二):事务
  • 巴尔加瓦算法图解:算法运用(上)
  • Java安全 CC链1分析(Lazymap类)
  • 防火墙的区域隔离
  • 操作系统——内存管理(附带Leetcode算法题LRU)
  • Xcode配置GLFW GLAD (MAC)
  • MongoDB聚合:$unionWith
  • 数据库恢复
  • 【EAI 020】Diffusion Policy: Visuomotor Policy Learning via Action Diffusion