HiveSQL题——array_contains函数

这篇具有很好参考价值的文章主要介绍了HiveSQL题——array_contains函数。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

目录

一、原创文章被引用次数

0 问题描述

1 数据准备

2 数据分析

​编辑

3 小结

二、学生退费人数

0 问题描述

1 数据准备

2 数据分析

3 小结

一、原创文章被引用次数

0 问题描述

   求原创文章被引用的次数,注意本题不能用关联的形式求解。

1 数据准备

   id表示文章id,oid表示引用的其他文章id,当oid为0时表示当前文章为原创文章。

create table if not exists  table18
(
    id    int comment '文章id',
    oid   int comment '引用的其他文章id'
) comment '文章信息表';

insert overwrite table table18 values
(1,0),
(2,0),
(3,1),
(4,1),
(5,2),
(6,0),
(7,3);

2 数据分析

    题目要求的是原创文章被引用的次数,其中原创文章为oid等于0的文章,即求解文章id为【1,2,6】被引用的次数。常见的思路是用关联方式求解,具体SQL如下图所示:

思路一:用左连接 left join 


--思路一:用左连接 left join 
select
    t1.id,
    count(t2.oid) as cnt
from (select * from table18 where oid = 0) t1
         left join
         (select * from table18 where oid <> 0) t2
         on t1.id = t2.oid
group by t1.id
order by t1.id;

 输出结果为:
hive sql array_contains,# HIve SQL,数据仓库,大数据,hive

 题意要求不能使用join等关联形式求解,其实该题本质是存在性计数问题

思路二:借助array_contains(array,element) 函数

select
    new_id,
    sum(flag)as cnt
from (
         select
             id,
             oid,
             contains,
             -- 第二步:利用array_contains()函数判断引用的oid是否在原创文章id集合中,ture则记为1,false则记为0
             if(array_contains(contains, oid), 1, 0)    flag,
             -- 第三步:清洗数据,补充完整的原创文章
             if(array_contains(contains, oid), oid, if(oid = 0, id, null)) new_id
         from ( -- 第一步:构建原创文章id集合,作为辅助列
                  select
                      id,
                      oid,
                      collect_set(if(oid = 0, id, null)) over () contains
                  from table18
              ) tmp1
     ) tmp2
where new_id is not null
group by new_id;

    上述代码解析:通过array_contains(array,column) 函数进行存在性检测,如果array中包含column 则记为1,不存在记为0,关键公式: sum(if(array_contains(array,column),1,0))

hive sql array_contains,# HIve SQL,数据仓库,大数据,hive

上述代码解析:

第一步:构建原创文章id集合contains,将contains作为辅助列。

  select
        id,
        oid,
        collect_set(if(oid = 0, id, null)) over () contains
from table18;

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

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 () contains
          from table18
      ) tmp1;

hive sql array_contains,# HIve SQL,数据仓库,大数据,hive

第三步:清洗数据,对原创文章id补充完整

select
        id,
        oid,
        contains,
        if(array_contains(contains, oid), 1, 0)   flag,
      --清洗数据,对原创文章id补充完整
        if(array_contains(contains, oid), oid, if(oid = 0, id, null)) new_id
from ( 
          select
               id,
               oid,
               collect_set(if(oid = 0, id, null)) over () contains
          from table18
       ) tmp1;

 hive sql array_contains,# HIve SQL,数据仓库,大数据,hive

   ps: 此处需要对原创文章id补充完整,否则会丢失记录。具体是:通过array_contains(contains,oid)去判断,代码为 if(array_contains(contains, oid), oid, if(oid = 0, id, null)) as  new_id   -->  代表的意思是如果oid存在于原创文章id构建的集合中,就取得该oid,如果不存在,再判断oid是否为0,如果是0,则取得id,否则记为null。

第四步:将new_id 为null的数据滤掉,并对new_id分组,求出各原创文章被引用的次数sum(flag)as cnt

select
    new_id,
    sum(flag)as cnt
from (
         select
             id,
             oid,
             contains,
             -- 第二步:利用array_contains()函数判断引用的oid是否在原创文章id集合中,ture则记为1,false则记为0
             if(array_contains(contains, oid), 1, 0)    flag,
             -- 第三步:清洗数据,补充完整的原创文章id
             if(array_contains(contains, oid), oid, if(oid = 0, id, null)) new_id
         from ( -- 第一步:构建原创文章id集合,作为辅助列
                  select
                      id,
                      oid,
                      collect_set(if(oid = 0, id, null)) over () contains
                  from table18
              ) tmp1
     ) tmp2
  -- 第四步:将为null的数值过滤掉,并对new_id分组,求出各原创文章被引用的次数sum(flag)as cnt
where new_id is not null
group by new_id;

hive sql array_contains,# HIve SQL,数据仓库,大数据,hive

3 小结

 上述例子中利用array_contains(array,column)进行存在性检测,如果存在则记为1,不存在则记为0,核心计算公式为 sum(if(array_contains(array,value),1,0))

二、学生退费人数

0 问题描述

求截止当前月的学生退费总人数【当月的学生退费人数:上月存在,这月不存在的学生个数】。

1 数据准备

create table if not exists test19( dt string comment '日期',
stu_id string comment '学生id');

insert overwrite table test19
values ('2020-01-02','1001'),
       ('2020-01-02','1002'),
       ('2020-02-02','1001'),
       ('2020-02-02','1002'),
       ('2020-02-02','1003'),
       ('2020-02-02','1004'),
       ('2020-03-02','1001'),
       ('2020-03-02','1002'),
       ('2020-04-02','1005'),
       ('2020-05-02','1006');

2 数据分析

完整的代码如下:

select month,
      sum(month_cnt) over(order by month) as result
from(
    select month,
           lag(next_month_cnt,1,0) over(order by month) as month_cnt
    from(
        select distinct 
               t0.month as month,
               sum(if(!array_contains(t1.lead_stu_id_arr,t0.stu_id),1,0)) over(partition by t0.month) as next_month_cnt
        from
            (select 
                  date_format(dt,'yyyy-MM') as month,
                  stu_id
            from test19) t0
        left join
        (
            select month,
                   lead(stu_id_arr,1) over(order by month) as lead_stu_id_arr
            from(
                 select date_format(dt,'yyyy-MM') as month,
                        collect_list(stu_id) as stu_id_arr
                 from test19
                 group by date_format(dt,'yyyy-MM') 
                ) tmp1
        ) t1
        on t0.month = t1.month
    ) tmp2
) tmp3;

第一步:聚合每个月的stu_id,利用collect_list()函数(不去重)合并,具体sql如下:

select date_format(dt,'yyyy-MM') as month,
       collect_list(stu_id) as stu_id_arr
from test19
group by date_format(dt,'yyyy-MM') 

计算结果如下:

2020-01	[1001,1002]
2020-02	[1001,1002,1003,1004]
2020-03	[1001,1002]
2020-04	[1005]
2020-05	[1006]

第二步:按照月份排序,获取下一月合并之后的值,sql如下:

 select month,
        stu_id_arr,
        lead(stu_id_arr,1) over(order by month) as lead_stu_id_arr
from(
       select
                date_format(dt,'yyyy-MM') as month,
                collect_list(stu_id) as stu_id_arr
       from test19
       group by date_format(dt,'yyyy-MM')
     ) tmp1;

计算结果如下:

2020-01	[1001,1002]	[1001,1002,1003,1004]
2020-02	[1001,1002,1003,1004]	[1001,1002]
2020-03	[1001,1002]	[1005]
2020-04	[1005]	[1006]
2020-05	[1006]	NULL

     ps:总体思路是利用数组差集函数求出差值集合后,再利用size()求出具体的个数,最后sum聚合即可。hive中的数组函数array_contains可以实现这个需求,该函数表示在数组中查询某个元素是否存在。在该题目中,借助此函数判断 当月某个学生id是否在下月(数据集合 -->数组)中存在,如果存在就为0,不存在标记为1。

 第三步:利用步骤2的结果与原表进行关联,获取当前学生id

select
    t0.*,
    t1.*
from (select
          date_format(dt, 'yyyy-MM') as month,
          stu_id
      from test19) t0
 left join ( select
                   month,
                   lead(stu_id_arr, 1) over (order by month) as lead_stu_id_arr
             from ( select
                          date_format(dt, 'yyyy-MM') as month,
                          collect_list(stu_id)       as stu_id_arr
                  from test19
                  group by date_format(dt, 'yyyy-MM')
                 ) tmp1
             ) t1
on t0.month = t1.month;

结果如下:

2020-01	1001	2020-01	[1001,1002,1003,1004]
2020-01	1002	2020-01	[1001,1002,1003,1004]
2020-02	1001	2020-02	[1001,1002]
2020-02	1002	2020-02	[1001,1002]
2020-02	1003	2020-02	[1001,1002]
2020-02	1004	2020-02	[1001,1002]
2020-03	1001	2020-03	[1005]
2020-03	1002	2020-03	[1005]
2020-04	1005	2020-04	[1006]
2020-05	1006	2020-05	NULL

第四步:利用array_contains()函数判断当月的stu_id是否在下个月array数组中,如果存在标记0,不存在标记1。具体sql如下:

        select t0.month,
               t0.stu_id,
              if(!array_contains(t1.lead_stu_id_arr,t0.stu_id),1,0) as flag
        from
            (select
                   date_format(dt,'yyyy-MM') as month,
                   stu_id
            from test19) t0
        left join
        (
            select month,
                   lead(stu_id_arr,1) over(order by month) as lead_stu_id_arr
            from(
                 select date_format(dt,'yyyy-MM') as month,
                        collect_list(stu_id) as stu_id_arr
                 from test19
                 group by date_format(dt,'yyyy-MM')
                ) tmp1
        ) t1
        on t0.month = t1.month

结果如下:

2020-01	1001	0
2020-01	1002	0
2020-02	1001	0
2020-02	1002	0
2020-02	1003	1
2020-02	1004	1
2020-03	1001	1
2020-03	1002	1
2020-04	1005	1
2020-05	1006	1

第五步:基于步骤四的结果,按照月份分组,对flag求和,得到下个月的学生退费人数

select  distinct t0.month,
       -- 求解下个月的退费人数
        sum(if(!array_contains(t1.lead_stu_id_arr,t0.stu_id),1,0)) over(partition by t0.month) as next_month_cnt
from  (select
             date_format(dt,'yyyy-MM') as month,
             stu_id
       from test19) t0
left join
        ( select month,
                 lead(stu_id_arr,1) over(order by month) as lead_stu_id_arr
          from( select 
                        date_format(dt,'yyyy-MM') as month,
                        collect_list(stu_id) as stu_id_arr
                 from test19
                 group by date_format(dt,'yyyy-MM')
                ) tmp1
        ) t1
on t0.month = t1.month;

计算结果如下:

注意:第二列求是下个月的退费人数。

2020-01	0
2020-02	2
2020-03	2
2020-04	1

第六步:计算当前月的退费人数

    步骤五计算的是下一个月的学生退费人数,再利用 lag(next_month_cnt,1,0) over(order by month) 向上偏移一行,就得到当前月的退费人数

hive sql array_contains,# HIve SQL,数据仓库,大数据,hive

sql代码如下:

select month, 
      --基于下月的退费人数month_cnt字段,向上偏移一行,就得到当前月的退费人数
       lag(next_month_cnt,1,0) over(order by month) as month_cnt
 from(
        select distinct t0.month as month,
               sum(if(!array_contains(t1.lead_stu_id_arr,t0.stu_id),1,0)) over(partition by t0.month) as next_month_cnt
        from
            (select
                  date_format(dt,'yyyy-MM') as month,
                  stu_id
            from test19) t0
        left join
        (
            select month,
                   lead(stu_id_arr,1) over(order by month) as lead_stu_id_arr
            from(
                 select date_format(dt,'yyyy-MM') as month,
                        collect_list(stu_id) as stu_id_arr
                 from test19
                 group by date_format(dt,'yyyy-MM')
                ) tmp1
        ) t1
        on t0.month = t1.month
    ) tmp2;

计算结果如下:

2020-01	0
2020-02	0
2020-03	2
2020-04	2
2020-05	1

计算截止到当前月的退费人数,sql代码如下:

select month,
       -- sum() over(order by ..) 窗口计算范围:上无边界(起始行)到当前行
       sum(month_cnt) over(order by month) as result
from(
    select month,
          lag(next_month_cnt,1,0) over(order by month) as month_cnt
    from(
        select distinct t0.month as month,
               sum(if(!array_contains(t1.lead_stu_id_arr,t0.stu_id),1,0)) over(partition by t0.month) as next_month_cnt
        from
            (select
                  date_format(dt,'yyyy-MM') as month,
                  stu_id
            from test19) t0
        left join
        (
            select month,
                   lead(stu_id_arr,1) over(order by month) as lead_stu_id_arr
            from(
                 select date_format(dt,'yyyy-MM') as month,
                        collect_list(stu_id) as stu_id_arr
                 from test19
                 group by date_format(dt,'yyyy-MM')
                ) tmp1
        ) t1
        on t0.month = t1.month
    ) tmp2
) tmp3;

计算结果为:

2020-01	0
2020-02	0
2020-03	2
2020-04	4
2020-05	5

3 小结

   针对存在性问题,一般的求解思路是:1.利用collect_set()或者 collect_list()函数进行聚合,将数据集转换成数据组。2.再利用array_contains()等函数判断集合(数组)中是否存在某元素,针对结果打上标签。3.再根据标签进行之后的分组聚合计算等。

ps:以上文章参考:

https://blog.csdn.net/godlovedaniel/article/details/119388498?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522167921970316800184142859%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=167921970316800184142859&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduend~default-1-119388498-null-null.142^v74^control_1,201^v4^add_ask,239^v2^insert_chatgpt&utm_term=%E5%AD%98%E5%9C%A8%E6%80%A7%E9%97%AE%E9%A2%98&spm=1018.2226.3001.4187文章浏览阅读741次。本文对存在性问题进行了探讨和研究,此类问题往往需要对不同的记录做对比分析,我们可以先将符合条件的数据域按照collect_set()或collect_list()函数进行聚合转换成数组,然后获取历史的数据域放入当前行,最后利用hive中数组的相关处理手段进行对比分析。常用的hive数组处理函数如expode()、size()、array()、array_contains()等函数,本题就借助于hive ,array_contains()函数进行存在性问题分析。_sql 求截止当前月退费总人数https://blog.csdn.net/godlovedaniel/article/details/119388498?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522167921970316800184142859%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=167921970316800184142859&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduend~default-1-119388498-null-null.142%5Ev74%5Econtrol_1,201%5Ev4%5Eadd_ask,239%5Ev2%5Einsert_chatgpt&utm_term=%E5%AD%98%E5%9C%A8%E6%80%A7%E9%97%AE%E9%A2%98&spm=1018.2226.3001.4187文章来源地址https://www.toymoban.com/news/detail-856540.html

到了这里,关于HiveSQL题——array_contains函数的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 1/50 hive sql 日期处理函数

    目录 一、获取当前时间 二、获取指定时间 三、转换时间格式 【1】to_date(string timestamp) 【2】date_format(date string timestamp,string format) 【3】from_unixtime(bigint unixtime,string format) 【4】unix_timestamp(date string timestamp,string pattern)  【5】其余格式的指定转换 regexp_replace()、from_unixtime(unix_ti

    2023年04月25日
    浏览(47)
  • SQL使用技巧(6)HIVE开窗函数

    开窗函数的使用铁律:不要滥用 先想明白自己要实现什么样的功能,然后再去实践。没有目的的尝试段不可取,会难以理解各开窗函数的真正用法。 如果没有现成可用HIVE库,可以参见本人 大数据单机学习环境搭建 系列文章。 1.1Hive建表 1.2数据准备 1.3保存为HDFS文件 1.4验证

    2024年02月08日
    浏览(60)
  • hive sql—开窗函数—累积求和和滑动求和

    数据集有三列数据,姓名、月份和数量: 图1 使用 sum() 函数和 over() 来实现,如下: 结果如下: 同一个name,后一个月份都是前几个月份的累加和 图2 需要稍微骚一点的操作,加上一个限制条件: 数字:可正可零可负,正往前,负向后;preceding:向前几行;following:向后几行

    2024年02月11日
    浏览(46)
  • SQL使用技巧(4.1)Hive日期时间函数

    常用的格式化(format)标识符: 本章节每一行代码后都有运算说明和执行结果样例,例如 - - 返回当前系统日期 yyyy-MM-dd, 【2023-04-01】 原生hive中不支持 now() 的写法,经过加工的工具(TDWTDH)可能支持,不绝对。 unix_timestamp() 也会返回一个时间戳,但并不是系统当前时间的时间

    2024年02月12日
    浏览(45)
  • Hive SQL——explode拆分函数&多行(列)合并为一行(列)&reflect函数

    cd /data/import/ sudo vi test_explode_map_array.txt 添加以下文件内容 小明    产品1,产品2,产品3    性别:男,年龄:24 小花    产品4,产品5,产品6    性别:女,年龄:22  map_key map_value 年龄 24 性别 男 年龄 22 性别 女 prod_arr_new 产品1 产品2 产品3 产品4 产品5 产品6 name prod_arr_new 小明 产品1

    2024年02月15日
    浏览(55)
  • 【Hive】HQL Array 『CRUD | 相关函数』

    语法: array基本数据类型 注意是 ,不是 () 例子: 创建表时: 字段填充时: cast(null as arraystring) as XXX 没有删除,只能覆盖 注意:数组越界会报错。 array() :创建一个数组。例如,array(1,2,3)将创建一个包含1、2、3三个元素的数组。 array_max(array) :返回数组中的最大值。例如,

    2024年02月11日
    浏览(36)
  • 44、Flink之module模块介绍及使用示例和Flink SQL使用hive内置函数及自定义函数详细示例--网上有些说法好像是错误的

    一、Flink 专栏 Flink 专栏系统介绍某一知识点,并辅以具体的示例进行说明。 1、Flink 部署系列 本部分介绍Flink的部署、配置相关基础内容。 2、Flink基础系列 本部分介绍Flink 的基础部分,比如术语、架构、编程模型、编程指南、基本的datastream api用法、四大基石等内容。 3、

    2024年02月09日
    浏览(44)
  • [hive] 在hive sql中定义变量

    在Hive SQL中,可以使用 SET 命令来定义变量。 变量可以用于存储和引用常量或表达式的值,以便在查询中重复使用。 下面是定义和使用变量的示例: 使用 SET 命令定义了一个名为 my_var 的变量, 在查询中使用 ${my_var} 引用变量。这样,变量的值将被替换为实际的字符串,从而

    2024年02月03日
    浏览(36)
  • hive sql 和 spark sql的区别

    Hive SQL 和 Spark SQL 都是用于在大数据环境中处理结构化数据的工具,但它们有一些关键的区别: 底层计算引擎: Hive SQL:Hive 是建立在 Hadoop 生态系统之上的,使用 MapReduce 作为底层计算引擎。因此,它的执行速度可能相对较慢,尤其是对于复杂的查询。 Spark SQL:Spark SQL 则建

    2024年01月20日
    浏览(40)
  • Hive SQL题库(初级)

    第一章 环境准备 1. 1  建表语句 1.2 数据准备 (1)创建/opt/module/data目录 (2)将如下4个文件放到/opt/module/data目录下 1. 3  插入数据 (1)插入数据 (2)验证插入数据情况 第二章 简单查询 2.1 查找特定条件 2.1.1 查询姓名中带“冰”的学生名单 2.1.3 检索课程编号为“04”且分

    2024年02月10日
    浏览(40)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包