HiveSql一天一个小技巧:利用array_contains()函数进行容器存在性计数问题分析

0 需求描述

文章被引用关系数据表如下:

id

oid

1

0

2

0

3

1

4

1

5

2

6

0

7

3

其中id表示文章id,oid引用的文章,当oid为0时表示当前文章为原创文章,求原创文章被引用的次数。注意本题不能用关联的形式求解

1 需求分析

1.1 数据源准备

with data as(
select 1 as id, 0 as oid
union all
select 2 as id, 0 as oid
union all
select 3 as id, 1 as oid
union all
select 4 as id, 1 as oid
union all
select 5 as id, 2 as oid
union all
select 6 as id, 0 as oid
union all
select 7 as id, 3 as oid
)
select * from data;

1.2 数据分析

题目要求的是原创文章被引用的次数,其中原创文章为oid为0的文章,也就是文章id为【1,2,6】被引用的次数,引用的文章id用oid来描述。一般正常的思路用关联的方式求解,找出非0的oid在oid为0时的id中存在多少个,那么问题就解决了,我们 用 left join形式求解,具体SQL如下:

with data as(
select 1 as id, 0 as oid
union all
select 2 as id, 0 as oid
union all
select 3 as id, 1 as oid
union all
select 4 as id, 1 as oid
union all
select 5 as id, 2 as oid
union all
select 6 as id, 0 as oid
union all
select 7 as id, 3 as oid
)

select t2.id
    ,count(oid) as cnt
from
(select oid
from data
where oid<>0
) t1
right join
(
select id
from data
where oid=0
) t2
on t1.oid = t2.id
group by t2.id
order by id

具体结果如下:

由于题意要求了不能使用join等关联形式求解,通过题意分析此题本质为存在性计数问题,类似于java中我们构建一个HashSet()我们往里面Put数据的时候,每次检查一次是否有该值,有就记为1,最终统计重复的个数有多少个,这类问题也就是我们经常说的容器变换问题,而对应到Hive中时候我们如何构建容器呢?可以通过collect_set()或collect_list()函数来构建,那检查容器中是否存在某个数,我们用array_contains()函数,那么这样一个经典的存在性计数问题就很容易得到解决,具体公式如下。

公式含义:检查当前字段是否在容器中存在,存在计数为1,不存在计数为0,最终求出计数的个数

                 sum(if(array_contains(array,colum),1,0))

我们往往利用该模型解决实际需求中一些问题,如学生退费人数统计问题等。参考文章如下:

(2条消息) SQL之存在性问题分析-HQL面试题39_莫叫石榴姐的博客-CSDN博客

因而,本问题也可以通过构建该模型求解,具体数据变换如下:

第一步:构建原创文章id容器,作为辅助列

with data as(
select 1 as id, 0 as oid
union all
select 2 as id, 0 as oid
union all
select 3 as id, 1 as oid
union all
select 4 as id, 1 as oid
union all
select 5 as id, 2 as oid
union all
select 6 as id, 0 as oid
union all
select 7 as id, 3 as oid
)

select  id
      , oid
      , collect_set(if(oid=0,id,null)) over() as contains
from data

第二步:利用array_contains()函数判断非原创的oid是否在原创文章id容器中,是计数为1,否则计数为0

with data as(
select 1 as id, 0 as oid
union all
select 2 as id, 0 as oid
union all
select 3 as id, 1 as oid
union all
select 4 as id, 1 as oid
union all
select 5 as id, 2 as oid
union all
select 6 as id, 0 as oid
union all
select 7 as id, 3 as oid
)

select id,oid,contains,if(array_contains(contains,oid),1,0) as flag
from
(
select  id
      , oid
      , collect_set(if(oid=0,id,null)) over() as contains
from data
) t

第三步:对原创文章进行汇总求和,得出最终结果

注意:此处需要对原创文章id补充完整,否则会丢记录。补充的方法还是通过array_contains(contains,oid)去判断,如果oid存在于原创文章id中就取该oid,不存在则用原创文章id填充,若该行为非原创记录则记为NULL,最终计算时过滤掉。

with data as(
select 1 as id, 0 as oid
union all
select 2 as id, 0 as oid
union all
select 3 as id, 1 as oid
union all
select 4 as id, 1 as oid
union all
select 5 as id, 2 as oid
union all
select 6 as id, 0 as oid
union all
select 7 as id, 3 as oid
)

select id
      ,sum(flag) cnt
from
(   select if(array_contains(contains,oid),oid,if(oid=0,id,null)) id --清洗数据,补充完整的原创文章id
          ,if(array_contains(contains,oid),1,0) as flag
from
    (
    select  id
          , oid
          , collect_set(if(oid=0,id,null)) over() as contains
    from data
    ) t
) t
    where id is not null --过滤掉不属于原创文章id的id
group by id
order by id

2 小结

本文给出了一种容器变换中存在性计数问题的分析方法,通过 array_contains(array,colum)进行存在性检测,如果存在则记为1,不存在记为0,最终的计算公式如下:

sum(if(array_contains(array,colum),1,0))

通过如上方法可以轻松应对一些判断统计问题。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.mfbz.cn/a/1659.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

Springboot源代码总结

前言 编写微服务,巩固知识 文章目录 前言springboot原理springboot启动流程SpringBoot自动配置底层源码解析自动配置到底配了什么?自动配置类条件注解Starter机制@ConditionalOnMissingBeanSpringBoot启动过程源码解析构造SpringApplication对象SpringBoot完整的配置优先级s…

深入理解WebSocket协议

“ 一直以来对WebSocket仅停留在使用阶段&#xff0c;也没有深入理解其背后的原理。当看到 x x x was not upgraded to websocket&#xff0c;我是彻底蒙了&#xff0c;等我镇定下来&#xff0c;打开百度输入这行报错信息&#xff0c;随即看到的就是大家说的跨域&#xff0c;或…

SpringBoot帮你优雅的关闭WEB应用程序

Graceful shutdown 应用 Graceful shutdown说明 Graceful shutdown is supported with all four embedded web servers (Jetty, Reactor Netty, Tomcat, and Undertow) and with both reactive and servlet-based web applications. It occurs as part of closing the applica…

spring(七):事务操作

spring&#xff08;七&#xff09;&#xff1a;事务操作前言一、什么是事务二、事务四个特性&#xff08;ACID&#xff09;三、事务操作&#xff08;搭建事务操作环境&#xff09;四、事务操作&#xff08;Spring 事务管理介绍&#xff09;五、事务操作&#xff08;注解声明式事…

python学习——【第一弹】

前言 Python是一种跨平台的计算机程序设计语言&#xff0c;是ABC语言的替代品&#xff0c;属于面向对象的动态类型语言&#xff0c;最初被设计用于编写自动化脚本&#xff0c;随着版本的不断更新和语言新功能的添加&#xff0c;越来越多被用于独立的、大型项目的开发。 从这篇…

断言assert

assert作用&#xff1a;我们使用assert这个宏来调试代码语法&#xff1a;assert&#xff08;bool表达式&#xff09;如果表达式为false&#xff0c;会调用std::cout<<abort函数&#xff0c;弹出对话框&#xff0c;#include<iostream> #include<cassert> void…

学习 Python 之 Pygame 开发魂斗罗(八)

学习 Python 之 Pygame 开发魂斗罗&#xff08;八&#xff09;继续编写魂斗罗1. 创建敌人类2. 增加敌人移动和显示函数3. 敌人开火4. 修改主函数5. 产生敌人6. 使敌人移动继续编写魂斗罗 在上次的博客学习 Python 之 Pygame 开发魂斗罗&#xff08;七&#xff09;中&#xff0…

uboot主目录下Makefile文件的分析,以及配置过程分析

主Makefile执行分析 uboot的编译过程 &#xff08;1&#xff09;配置 查看主Makefile文件下所支持的配置的板子&#xff0c;通过make x210_sd_config来实现编译前的配置 &#xff08;2&#xff09;编译 make直接编译&#xff0c;这个前提条件是主Makefile文件下指定了编译…

上手使用百度文心一言

3月16日&#xff0c;在距离新一代的GPT模型GPT-4发布还不足一天的时间内&#xff0c;百度便发布了对标ChatGPT的人工智能产品&#xff0c;名字叫&#xff1a;文心一言。成为国内首页发布该类型产品的公司。 那么&#xff0c;我们今天就来试一试百度的文心一言好不好用。 首先&a…

【ERNIE Bot】百度 | 文心一言初体验

文章目录一、前言二、文心一言介绍三、申请体验⌈文心一言⌋四、⌈文心一言⌋初体验1️⃣聊天对话能力2️⃣文案创作能力3️⃣文字转语音能力✨4️⃣AI绘画能力✨5️⃣数理推理能力6️⃣代码生成能力7️⃣使用技巧说明五、总结一、前言 ​ 最近有关人工智能的热门话题冲上热榜…

Java课程设计项目--音乐视频网站系统

一、功能介绍 随着社会的快速发展&#xff0c;计算机的影响是全面且深入的。人们生活水平的不断提高&#xff0c;日常生活中人们对音乐方面的要求也在不断提高&#xff0c;听歌的人数更是不断增加&#xff0c;使得音乐网站的设计的开发成为必需而且紧迫的事情。音乐网站的设计主…

「操作系统」什么是用户态和内核态?为什么要区分

「操作系统」什么是用户态和内核态&#xff1f;为什么要区分 参考&鸣谢 从根上理解用户态与内核态 程序员阿星 并发编程&#xff08;二十六&#xff09;内核态和用户态 Lovely小猫 操作系统之内核态与用户态 fimm 文章目录「操作系统」什么是用户态和内核态&#xff1f;为什…

嵌入式硬件电路设计的基本技巧

目录 1 分模块 2 标注关键参数 3 电阻/电容/电感/磁珠的注释 4 可维修性 5 BOM表归一化 6 电源和地的符号 7 测试点 8 网络标号 9 容错性/兼容性 10 NC、NF 11 版本变更 12 悬空引脚 13 可扩展性 14 防呆 15 信号的流向 16 PCB走线建议 17 不使用\表示取反 不…

考研408每周一题(2019 41)

2019年(单链表&#xff09; 41.(13分)设线性表L(a1,a2,a3,...,a(n-2),a(n-1),an)采用带头结点的单链表保存&#xff0c;链表中的结点定义如下&#xff1a; typedef struct node {int data;struct node *next; } NODE; 请设计一个空间复杂度为O(1)且时间上尽可能高效的算法&…

leetcode -- 876.链表的中间节点

文章目录&#x1f428;1.题目&#x1f407;2. 解法1-两次遍历&#x1f340;2.1 思路&#x1f340;2.2 代码实现&#x1f401;3. 解法2-快慢指针&#x1f33e;3.1 思路&#x1f33e;3.2 **代码实现**&#x1f42e;4. 题目链接&#x1f428;1.题目 给你单链表的头结点head&#…

RocketMQ

RocketMQ1、基础入门1、消息中间件(MQ)的定义2、为什么要用消息中间件&#xff1f;2、RocketMQ 产品发展1、RocketMQ 版本发展2、RocketMQ 的物理架构1、核心概念2、物理架构中的整体运转3、RocketMQ 的概念模型1、分组(Group)2、主题(Topic)3、标签(Tag)4、消息队列(Message Q…

开发也可以很快乐,让VSCode和CodeGPT带给你幸福感

CodeGPT 是一款 Visual Studio Code 扩展&#xff0c;可以通过官方的 OpenAI API 使用 GPT-3 (预训练生成式转换器) 模型&#xff0c;在多种编程语言中生成、解释、重构和文档化代码片段。CodeGPT 可用于各种任务&#xff0c;例如代码自动完成、生成和格式化。它还可以集成到代…

smartsofthelp最简单的,最好的,最干净的C# 代码生成器

关系型数据库高并发接口代码生成EF API 接口原声SQL 操作类异步委托 await 操作数据库数据异步访问抽象基础类 netcore 生成EF ORMdbhelperasync原生SQL 异步数据库操作公共类自动生成增删改查成员方法实例代码#region 自动生成增删改查成员方法/// <summary>/// 增加一条…

【6】核心易中期刊推荐——图像与信号处理

🚀🚀🚀NEW!!!核心易中期刊推荐栏目来啦 ~ 📚🍀 核心期刊在国内的应用范围非常广,核心期刊发表论文是国内很多作者晋升的硬性要求,并且在国内属于顶尖论文发表,具有很高的学术价值。在中文核心目录体系中,权威代表有CSSCI、CSCD和北大核心。其中,中文期刊的数…

ChatGPT-4.0 : 未来已来,你来不来

文章目录前言ChatGPT 3.5 介绍ChatGPT 4.0 介绍ChatGPT -4出逃计划&#xff01;我们应如何看待ChatGPT前言 好久没有更新过技术文章了&#xff0c;这个周末听说了一个非常火的技术ChatGPT 4.0&#xff0c;于是在闲暇之余我也进行了测试&#xff0c;今天这篇文章就给大家介绍一…
最新文章