hivegroup_concat的简单介绍

by intanet.cn ca 数据库 on 2024-03-18

本篇文章给大家谈谈hivegroup_concat，以及对应的知识点，希望对各位有所帮助，不要忘了收藏本站喔。

本文目录一览：

1、开窗函数
2、impala设置sum返回类型
3、hive关于行转列，列转行的使用
4、HIVE复杂结构体生成和使用
5、hive - concat 函数
6、hive 两字段合并成一个字段例如：字段a和字段b合并成a-b，请问如何实现、

开窗函数

需求：

既要显示聚合前的数据，又要显示聚合后的结果

rank

开窗函数：

窗口函数：窗口 + 函数

窗口：函数运行时计算的数据集的范围

函此闷数：运行时的函数：

1.常用的聚合函数

2.窗口内置的函数

1.聚合函数 -》纳扒链开窗

数据：

需求：

统计累计的问题，每个用户每天累计点外卖的次数

函数 over([partition by xxx,...] [order by xxx,...])

2.指定窗口大小

3.开窗 -内置函数

RANK

ROW_NUMBER

DENSE_RANK

NTILE

1.NTILE

需求：

把数据按照姓名进行分组时间排序结果数据分成 3份数

NTILE(N):

把数据平均分配到N中,如果不能平均分配，优先分配到较小的编号中。

2.rank相关的

RANK

ROW_NUMBER

DENSE_RANK

RANK：

从1开始，按照顺序，生成组内记录的编号，排序相同会重复，在名次中留下空位

ROW_NUMBER：

从1开始，按照顺序，生成组内记录的编号，序号没有重复的

DENSE_RANK：

从1开始，按照顺序，生成组内记录的编号，排序相同会重复，在名次中不留下空位

3.蹿行问题

lag 向上取第几行

lead 向下取第几行

4.取值问题

FIRST_VALUE(col)：取分组后截止到当前行第一个值

LAST_VALUE(col)：取分组后截止到当前行最后一个值

开窗函数案例：

1.我们有如下的用户访问数据

userId visitDate visitCount

u01 2017/1/21 5

u02 2017/1/23 6

u03 2017/1/22 8

u04 2017/1/20 3

u01 2017/1/23 6

u01 2017/2/21 8

U02 2017/1/23 6

U01 2017/2/22 4

要求使用SQL统计出每个用户的累积访问次数，如下表所示：

用户id 月份小计累积

u01 2017-01 11 11

u01 2017-02 12 23

u02 2017-01 12 12

u03 2017-01 8 8

u04 2017-01 3 3

每个用户的累积访问次数=》

每个用户每个月累计访问次数

维度：用户、月

指标：次数、累计访问次数

1.etl：

2017/2/22 =》 2017-02 日期函数，string函数 sql里面

2017/2/22=2017-2-22

1.先求每个月次数

2. 1结果 =》累计

也可以使用str_to_date

date_format(str_to_date(visitdate,'%Y/%m/%d') ,'%Y-%m')as month

2.有50W个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，

访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，数据如下：

u1 a

u2 b

u1 b

u1 a

u3 c

u4 b

u1 a

u2 c

u5 b

u4 b

u6 c

u2 c

u1 b

u2 a

u3 a

u5 a

请统计：

(1)每个店铺的UV（访客数）、pv(访问量)

维度：店铺

指标：uv =》user_id

(2)每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

维度：店铺、访客id

指标：访问次数、访问次数的top3

uv pv :

pv = page 次数不需要去重

uv = user 次数需要去重

2.行洞孙转列列转行

1.列转行

zuoshao,王者荣耀,黑丝,看小视频

xuanxuan,姐姐,天天,杰伦

mysql没有collection_list(hobby)

可以使用group_concat(hobby)

-- hive

select

name,

concat_ws(",",collection_list(hobby)) as hobbyies

from t1

group by

name;

concat_ws

concat

2.行转列

hive爆破函数实现

mysql没有爆破函数（免费版本没有）

[img]

impala设置sum返回类型

impala设置sum返回类型衡贺

impala 用法同hive

Aggregate 聚合函数

appx_median([DISTINCT|ALL] T col)

avg([DISTINCT|ALL] col)

count([DISTINCT|ALL] col)

group_concat([ALL] col [, separator])

max([DISTINCT | ALL] T col)

min([DISTINCT | ALL] T col)

ndv([DISTINCT | ALL] col)

stddev([DISTINCT | ALL] col) 返回组中数字列的标准偏差

stddev_pop([DISTINCT | ALL] col) 返回组中数字列的总体标准偏差

stddev_samp([DISTINCT | ALL] col) 返回组中数字列的无偏样本缺拦标准差

sum([DISTINCT | ALL] col)

variance([DISTINCT | ALL] col) 一个聚合函数，返回一组数字的方差。这是一个数学性质，表示值与平均值之间的距离差。返回值可以为零（如果输入为单个值或一组相同的值），否则为正数

variance_pop([DISTINCT | ALL] col) 返回组中数字列的总体标准偏差

variance_samp([DISTINCT | ALL] col) 返回组中数字列的无偏样本标准差

var_pop(col) 返回组中数字列的方差

var_samp(col) 返回组中数伏拦胡字列的无偏样本方差

————————————————

原文链接：

hive关于行转列，列转行的使用

行转列：

定义：就是把形如

id --------- name

1 --------- a

2 --------- a

3 --------- a

4 --------- a

的数据转换为

id ------------- name

1，2，3，4 --------- a

这里把四行的数据卜颂瞎变成了由一行显示，这就是行转列。

首先要用到的是collect_set或者collect_list，前者去重，后者不去重，一般配合group使用，能把形如：

id --------- name

1 --------- a

2 --------- a

3 --------- a

4 --------- a

转换为

id --------- name

["1","2","3","4"] --------- a

然后需要用到的是concat_ws函数，这个函数需要传递指定分隔符，以及字符串或者字符串数组。能起到把多个字符串拼接到一起的作用。

形如：

id --------- name

["1","2","3","4"] --------- a

通过concat_ws(',',[1,2,3,4])转换为

id --------- name

1，2，3，4 --------- a

连起来就完成了行转列

一般sql为:

列转行

定义：就是把形如

id ------------- name

1，2，3，4 --------- a

的数据樱败转换为

id --------- name

1 --------- a

2 --------- a

3 --------- a

4 --------- a

这里把一行的数据变成了由四行显示，这就是列转行。

首先要用到split函数，这个行数可以把字符串按照指定的规则切分为字符串数组。

形如：

id --------- name

1，2，3，4 --------- a

通过split('1,2,3,4',',')转换为

id --------- name

["1","2","3","4"] --------- a

然后是explode函数，这个行数可以把数组的每个元素转换为一行。

形如

["1","2","3","4"]

通过explode(["1","2","3","4"] )转换为

最后为了拼接其它字段，还需要用到lateral view把explode获得的行当虚拟表来用型空。

使用方法为 lateral view explode(split(["1","2","3","4"] , ',')) tmptable as new_id ;

其中new_id元素为转换为行后的字段名。

把以上综合起来就完成了列转行

一般sql为:

HIVE复杂结构体生成和使用

test_person样例数据：

biansutao beijing,shanghai,tianjin,hangzhou

linan changchu,chengdu,wuhan

create table default.test_person(name string,work_locations arraystring)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

COLLECTION ITEMS TERMINATED BY ',';

LOAD DATA 散段肆 INPATH 'hdfs://sfbdp1/user/xxxx/upload/test_person.txt' OVERWRITE INTO TABLE default.test_person;

select name,work_locations,work_locations[0] from default.test_person limit 10;

insert into table default.test_person select 'AA' as name,array('china','beijing') from dual

select * from default.test_person limit 10;

test_score样例数据：

AA 数学:90,语文:80,英语:100

BB 数学:90,语文:80,英语:100

drop table default.test_score;

create table default.test_score(name string, score mapstring,int)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ' '

COLLECTION ITEMS TERMINATED BY ','

MAP KEYS TERMINATED BY ':';

truncate table default.test_score;

load data inpath 'hdfs://sfbdp1/user/xxx/upload/test_score.txt' overwrite into table default.test_score;

select *, score['英语'] from default.test_score limit 10;

create table test_map as select 'CC' as name, str_to_map('唱歌:90,跳舞:80',',',':') as score from dual

select * from test_map limit 10;

其他例子：

CREATE TABLE IF NOT EXISTS default.explode_laterview_org(

day1_num BIGINT

,day2_num BIGINT

,day3_num BIGINT

,day4_num BIGINT

,day5_num BIGINT

,day6_num BIGINT

,day7_num BIGINT

冲轿 ,campaign_name STRING

,campaign_id BIGINT

);

INSERT OVERWRITE TABLE default.explode_laterview_org VALUES

(40, 20, 10, 4, 4, 2, 1, 'zoo', 2 )

,(100, 80, 53, 40, 7, 6, 5, 'moji', 3)

;

str_to_map使用：

select campaign_id,campaign_name,

STR_TO_MAP(

CONCAT(

'day1_num='燃没,CAST (day1_num AS STRING),

'day2_num=',CAST (day2_num AS STRING),

'day3_num=',CAST (day3_num AS STRING),

'day4_num=',CAST (day4_num AS STRING),

'day5_num=',CAST (day5_num AS STRING),

'day6_num=',CAST (day6_num AS STRING),

'day7_num=',CAST (day7_num AS STRING)

),'', '=') as aa

from default.explode_laterview_org

str_to_map行转列：

select * from (

select campaign_id,campaign_name,

STR_TO_MAP(

CONCAT(

'day1_num=',CAST (day1_num AS STRING),

'day2_num=',CAST (day2_num AS STRING),

'day3_num=',CAST (day3_num AS STRING),

'day4_num=',CAST (day4_num AS STRING),

'day5_num=',CAST (day5_num AS STRING),

'day6_num=',CAST (day6_num AS STRING),

'day7_num=',CAST (day7_num AS STRING)

),'', '=') as aa

from default.explode_laterview_org

) t lateral view explode(aa) mycol as mycol1,mycol2

备注：explode的用法：

explode(ARRAY) 列表中的每个元素生成一行

explode(MAP) map中每个key-value对，生成一行，key为一列，value为一列

explode（array）

select explode(array_col) as new_col from table_name

array_col：为数组类型的字段

new_col：array_col被explode之后对应的列

explode（map）

map是key-value结构，所以在用explode转换的时候，会转换为2列，一列是key转换而成的，一列是vaule转换而成的

select explode（map_col） as （map_key_col，map_value_col） from table_name

map_col：map类型的字段

map_key_col：是map_col被拆分之后的map映射的key

map_value_col：是map_col被拆分之后的map映射的value

explode局限性：

1）不能关联原有的表中的其他字段

2）不能与group by、cluster by、distribute by、sort by联用。

3）不能进行UDTF嵌套

4）不允许选择其他表达式

lateral view explode() 能解除以上限制：

一个或多个lateral view explode：

SELECT myCol1, myCol2 FROM baseTable

LATERAL VIEW explode(col1) myTable1 AS myCol1

LATERAL VIEW explode(col2) myTable2 AS myCol2;

注意：lateral view explode(array())，当array()为空的时候，查询的结果返回空，而不是查询结果的对应列为空，使用lateral view outer explode（array（）），查询的结果不为空，只有查询的对应列返回的结果为空

test_struct样例数据：

1 english,80

2 math,89

3 chinese,95

CREATE TABLE default.test_struct(id int,course structcourse:string,score:int)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ' '

COLLECTION ITEMS TERMINATED BY ',';

load data inpath 'hdfs://sfbdp1/user/xxxx/upload/test_struct.txt' overwrite into table default.test_struct;

select *, course.course,course.score from default.test_struct limit 10

insert into table default.test_struct select '4' as id, named_struct('course','ant','score',100) from dual

select * from default.test_struct limit 10;

生成：

concat('{"barscantm":"',barscantm,

'","opcode":"',opcode

'}') as json_new

使用：

get_json_object( json_new,'$.opcode')

array_contains(字段,'str') 判断字段中是否包含有str字符串

with tables as

(

select 'A' as useid,'1' as pageid

union all

select 'B' as useid,'1' as pageid

union all

select 'A' as useid,'2' as pageid

union all

select 'B' as useid,'3' as pageid

union all

select 'A' as useid,'4' as pageid

union all

select 'B' as useid,'4' as pageid

)

select * from (

select useid,collect_set(pageid) as pageid

from

(

select useid,pageid

from tables

group by useid,pageid

) t

group by useid

) t1

where array_contains(pageid,'1') and array_contains(pageid,'4')

-- 同时访问了pageid为1和4的userid数

sort_array(字段) 对字段进行升序排序

select name,work_locations from default.test_person limit 10;

select name,sort_array(work_locations) from default.test_person limit 10;

select waybill_no,package_no,

packageno

from gdl.tt_waybill_info

lateral view explode(package_no) mycol as packageno

where inc_day='20210519' and size(package_no)1

hive - concat 函数

concat 函数在连接字符串的时候，只要察团其中一个裂汪是NULL，那么将返回NULL

hive select concat('a','b');

hive select concat('a','肆没仔b',null);

NULL

hive 两字段合并成一个字段例如：字段a和字段b合并成a-b，请问如何实现、

使用函数concat

select concat(a,'-',b) from就可以实现穗核了。

hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的sql查询功者族肆能，可以将sql语句转换为MapReduce任务进行运行。其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。

Hive是建立在 Hadoop 上的数据仓库基础构架。它提供了一系列的工具，可以用来进行数据提取转化加载（ETL），这是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制。Hive 定义了简单的类 SQL 查询语言，称为 HQL，它允许熟悉 SQL 的用户查询数据。同时，这个语言也允许熟悉 MapReduce 开发者的开发自定义的 mapper 和 reducer 来处理内建的 mapper 和 reducer 无法完成的复杂的分析工作。

Hive 是一种底层封装了Hadoop 的数据仓库处理工具，使用类SQL 的HiveQL 语言实现数据查询，所有Hive 的数据都存储在Hadoop 兼容的文件系统（例如，Amazon S3、HDFS）中。Hive 在加载数据过程中不会对数据进行任何的修改，只是将数据移动到HDFS 中Hive 设定的目录下，因此，Hive 不支持对数据的改写和添加，所有的数据都是在加载的时候确定的。Hive 的设计特点如下。

● 支持索引，加快数据查询。

● 不同的存储类型，例如，纯文本文件、HBase 中的文件。

● 将元数据保存在关系数据库中，大大减少了在查询过首轿程中执行语义检查的时间。

● 可以直接使用存储在Hadoop 文件系统中的数据。

● 内置大量用户函数UDF 来操作时间、字符串和其他的数据挖掘工具，支持用户扩展UDF 函数来完成内置函数无法实现的操作。

● 类SQL 的查询方式，将SQL 查询转换为MapReduce 的job 在Hadoop集群上执行。

关于hivegroup_concat和的介绍到此就结束了，不知道你从中找到你需要的信息了吗？如果你还想了解更多这方面的信息，记得收藏关注本站。

vscode新建项目（vscode新建项目文件夹） c语言完数（c语言完数是什么意思）