HiveSQL题——collect_set()/collect_list()聚合函数

一、collect_set() /collect_list()介绍

       collect_set()函数与collect_list()函数属于高级聚合函数(行转列),将分组中的某列转换成一个数组返回,常与concat_ws()函数连用实现字段拼接效果。

  • collect_list:收集并形成list集合,结果不去重

  • collect_set:收集并形成set集合,结果去重

二、collect_set() /collect_list()有序性

0 问题描述

  有一张用户关注表table20,需求:根据用户user_id分组,按照粉丝关注的时间升序排序,输出粉丝id数组粉丝关注的时间数组,并保障两个数组的数据能一一对应

1 数据准备

create table if not exists table20 (
    user_id int comment '用户id',
    follow_user_id int comment '粉丝id',
    update_time string comment '粉丝关注的时间'
) comment  '用户关注表';

insert overwrite table table20 values
(1, 101,'2021-09-30 10:12:00'),
(1, 103,'2021-10-01 11:00:00'),
(1, 104,'2021-11-02 10:00:00'),
(1, 103,'2021-11-28 10:22:00'),
(2, 104,'2021-11-02 10:11:00'),
(2, 100,'2021-11-03 10:21:00'),
(1, 99,'2021-11-23 12:28:00');

2 数据分析

方式一: row_number() over(partition by .. order by..) as rn 排序,然后再使用collect_list()/collect_set()进行聚合.

select
    user_id,
    concat_ws('|', collect_list(cast(follow_user_id as string))) as fui,
    concat_ws('|', collect_list(update_time))  as ut
      from (select
                user_id,
                follow_user_id,
                update_time,
                row_number() over (partition by user_id order by update_time) rn
            from table20) tmp1
group by user_id;

发现问题:ut数组内的时间并没有按照升序排序输出。

原因分析:

  • HiveSQL执行时,底层转换成MR任务执行,当同时开启多个mapper任务时,mapper1可能处理的user_id是 1,update_time排名为1,2,3的数据,mapper2可能处理的user_id是1,update_time排名为4,5的数据。
  • collect_list()的底层是arrayList 来实现的,当put到arrayList集合时,无法知道是哪个mapper先计算完,所以可能会出现ArrayList集合中的数据顺序与原来数据插入的顺序不对齐的情况。因此:row_number() over(partition by .. order by ..) 与collect_list一起使用的时候,只能是实现局部有序(单个mapper的数据有序),不能实现全局有序。

解决方案:

方案一:使用distribute by + order by

select
    user_id,
    concat_ws('|', collect_list(cast(follow_user_id as string))) as fui_list,
    concat_ws('|', collect_list(update_time))                    as ut_list
from (select
          user_id,
          follow_user_id,
          update_time,
          row_number() over (partition by user_id order by update_time ) as rn
      from (
               select
                   user_id,
                   follow_user_id,
                   update_time
               from table20
                   distribute by user_id sort by update_time
           ) tmp1) tmp2
group by user_id
order by user_id;

 上述代码用到的函数:

(1)concat_ws:带分隔符的字符串连接
   语法: concat_ws(string SEP, string A, string B…)
         select concat_ws('-','abc','def') // abc-def


(2)collect_list:收集并形成list集合,结果不去重
   语法:select id, collect_list(likes) from student group by id;


(2)collect_set:收集并形成set集合,结果去重
   语法:select id, collect_set(likes) from student group by id;

方案二:sort_array(只支持升序)

select
    user_id,
    concat_ws(',', collect_list(cast(follow_user_id as string)))   as fui,
    concat_ws(',', sort_array(collect_list
        (concat_ws('|', lpad(cast(rn as string), 2, '0'), update_time)))) as middle,
    regexp_replace(concat_ws(',', sort_array(collect_list
        (concat_ws('|', lpad(cast(rn as string), 2, '0'), update_time)))), '\\d+\\|', '') as ut
from (select
          user_id,
          follow_user_id,
          update_time,
          rn
      from (
               select
                   user_id,
                   follow_user_id,
                   update_time,
                   row_number() over (partition by user_id order by update_time ) as rn
               from table20
           ) tmp1
      order by rn) tmp2
group by user_id
order by user_id

middle字段值的结果:

ut字段值的结果:

select regexp_replace('04|','\\d+\\|','*')  --> *
正则表达式:\\d+代表所有数字字符

上述代码用到的函数:

(一)lpad / rpad:左/右补足函数
语法:lpad(string str, int len,string pad) / rpad(string str, int len, string pad)
参数说明:
第一个参数:要补齐的字符串
第二个参数:补齐之后字符串的总位数
第三个参数:从左边/右边填充的字符, lpad代表从左边填充;rpad代表从右边填充

举例:
select lpad('abc',5,'fg')  --> fgabc
select rpad('abc',7,'df') --> abcdfdf

因为sort_array 是按照顺序对字符进行排序(例如11会排在2前面),所以可以使用函数lpad补位(将原来的1,2,3,4 转换成 01,02,03,04),然后再正常排序


(二)regexp_replace : 字符串替换
语法:regexp_replace(string initial_string, string pattern, string  replacement)
参数说明:
 initial_string为要替换的字符串,
 pattern为匹配字符串的正则表达式,
 replacement为要替换为的字符串。
简述: regexp_replace (StrA,StrB,StrC) 函数:将字符串A中的符合java正则表达式B的部分替换成C


(三)sort_array : 数组排序函数
 语法:sort_array(array, [asc|desc]) : 按照指定的排序规则对数组进行排序,并返回一个排好序的新数组
 参数说明:
  第一个参数:array为需要排序的数组,
  第二个参数:asc为可选参数,如果设置为true则按升序排序;desc为可选参数,如果设置为true,则按降序排序。如果既不设置asc也不设置desc,则按升序排序

 举例:
select sort_array(array(2, 5, 3, 1)) as sorted_array; ---> [1,2,3,5]
select sort_array(array(2, 5, 3, 1), true, true) as sorted_array; ---> [5,3,2,1]

3 小结

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

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

相关文章

Amazon Bedrock 知识库现可提供完全托管的 RAG 体验

Amazon Bedrock 知识库预览版已于9月问世。从今天开始,Amazon Bedrock 知识库正式全面发布。正式推出。 借助知识库,可安全地将 Amazon Bedrock 中的基础模型 (FM) 连接到贵公司的数据,供检索增强生成 (RAG) 使用。模型生成的响应更具相关性…

海外IP代理:解锁网络边界的实战利器

文章目录 引言:正文:一、Roxlabs全球IP代理服务概览特点:覆盖范围:住宅IP真实性:性价比:在网络数据采集中的重要性: 二、实战应用案例一:跨境电商竞品分析步骤介绍:代码示…

简单几步,借助Aapose.Cells将 Excel 工作表拆分为文件

近年来,Excel 文件已成为无数企业数据管理的支柱。然而,管理大型 Excel 文件可能是一项艰巨的任务,尤其是在高效共享和处理数据时。为了应对这一挑战,大型 Excel 工作簿被拆分为较小的工作簿以增强电子表格管理。Aspose提供了这样…

堪称灾难级攻击的 UDP FLOOD洪水攻击,应该如何防护?

DDOS又称为分布式拒绝服务,全称是Distributed Denial of Service。DDOS本是利用合理的请求造成资源过载,导致服务不可用,从而造成服务器拒绝正常流量服务。就如酒店里的房间是有固定的数量的,比如一个酒店有50个房间,当…

【Kafka】服务器Broker与Controller详解

这里写自定义目录标题 Broker概述Broker总体工作流程Broker重要参数 Controller为什么需要Controller具体作用数据服务Leader选举选举流程脑裂问题羊群效应触发leader选举 Broker 概述 Kafka服务实例,负责消息的持久化、中转等功能。一个独立的Kafka 服务器被就是…

AES算法:数据传输的安全保障

在当今数字化时代,数据安全成为了一个非常重要的问题。随着互联网的普及和信息技术的发展,我们需要一种可靠的加密算法来保护我们的敏感数据。Advanced Encryption Standard(AES)算法应运而生。本文将介绍AES算法的优缺点、解决了…

金和OA jc6 UploadFileBlock 任意文件上传漏洞

免责声明:文章来源互联网收集整理,请勿利用文章内的相关技术从事非法测试,由于传播、利用此文所提供的信息或者工具而造成的任何直接或者间接的后果及损失,均由使用者本人负责,所产生的一切不良后果与文章作者无关。该…

SpringBoot实战项目第一天

环境搭建 后端部分需要准备: sql数据库 创建SpringBoot工程,引入对应的依赖(web\mybatis\mysql驱动) 配置文件application.yml中引入mybatis的配置信息 创建包结构,并准备实体类 完成今日开发后项目部分内容如下图示 用户注册于登录部分…

【MongoDB】跨库跨表查询(python版)

MongoDB跨表跨库查询 1.数据准备:2.跨集合查询3.跨库查询应该怎么做? 讲一个简单的例子,python连接mongodb做跨表跨库查询的正确姿势 1.数据准备: use order_db; db.createCollection("orders"); db.orders.insertMan…

机器学习 | 如何利用集成学习提高机器学习的性能?

目录 初识集成学习 Bagging与随机森林 Otto Group Product(实操) Boosting集成原理 初识集成学习 集成学习(Ensemble Learning)是一种通过组合多个基本模型来提高预测准确性和泛化能力的机器学习方法。它通过将多个模型的预测结果进行整合或投票来做…

增加Vscode引用路径

增加Vscode引用路径 增加Vscode引用路径问题说明解决思路1在Vscode中进行配置缺点 解决思路2 增加Vscode引用路径 问题说明 在嵌入式开发中需要经常用到库函数(SPL), Vscode需要配置引用路径才能对函数名或变量进行跳转 解决思路1 与Keil5 MDK类似, 在配置C/C的json文件中添…

计算机网络_1.6.2 计算机网络体系结构分层的必要性

1.6.2 计算机网络体系结构分层的必要性 一、五层原理体系结构每层各自主要解决什么问题1、物理层2、数据链路层3、网络层4、运输层5、应用层 二、总结三、练习 笔记来源: B站 《深入浅出计算机网络》课程 本节主要介绍实现计算机网络需要解决哪些问题?以…

【Crypto | CTF】BUUCTF 大帝的密码武器1

天命:这题真的是来刷经验的,有点吐血 首先这题是贼简单,但我居然跪到了,所以特此写这一篇来惩戒自己心太大 拿到文件,文件写着zip,改成zip后缀名即可,也不算啥难的 打开里面的两份文件&#x…

NLP入门系列—Attention 机制

NLP入门系列—Attention 机制 Attention 正在被越来越广泛的得到应用。尤其是 [BERT]火爆了之后。 Attention 到底有什么特别之处?他的原理和本质是什么?Attention都有哪些类型?本文将详细讲解Attention的方方面面。 Attention 的本质是什…

LabVIEW传感器通用实验平台

LabVIEW传感器通用实验平台 介绍了基于LabVIEW的传感器实验平台的开发。该平台利用LabVIEW图形化编程语言和多参量数据采集卡,提供了一个交互性好、可扩充性强、使用灵活方便的传感器技术实验环境。 系统由硬件和软件两部分组成。硬件部分主要包括多通道数据采集卡…

MySQL 安装配置 windows

一、下载 去官网MySQL :: MySQL Downloads 下载社区版 然后根据自己的系统选择 直接下载。 二、安装 点击安装程序 这边看样子缺少东西。 去这边下载 Latest supported Visual C Redistributable downloads | Microsoft Learn 然后再一次安装mysql 三、配置 安装完成后&a…

Vue3_基础使用_2

这节主要介绍:标签和组件的ref属性,父子组件间的传递值,ts的接口定义,vue3的生命周期 1.标签的ref属性。 1.1ref属性就是给标签打标识用的,相当于html的id,但是在vue3中用id可能会乱,下面是ref…

ncc匹配(一,理论)

前头从来没用过ncc,基于形状匹配搞定后,又翻了翻learning opencv,他并不推荐ncc,而是力推emd,这也是当初我没考虑用ncc的原因。 当初看到ncc公式很复杂,也就忘了。 我的第一个匹配,是最笨的&a…

系统架构19 - 面向对象

面向对象设计 相关概念面向对象分析基本步骤基本原则分析模型 面向对象设计设计模型类的类型 面向对象编程基本特点需求建模设计原则面向对象软件测试 相关概念 接口:描述对操作规范的说明,其只说明操作应该做什么,并没有定义操作如何做。消…

服务器基础知识(IP地址与自动化技术的使用)

目录 ip地址是什么? 如何查看ip地址 Windows的命令提示符 图形化版本: 自动化技术的应用与意义 ip地址是什么? IP地址的主要作用是**为互联网上的每个网络和每台主机分配一个逻辑地址**。 它由32位二进制数字组成,通常分为四…