hive列转行（hive列转行函数介绍）

本篇文章给大家谈谈hive列转行，以及hive列转行函数介绍对应的知识点，希望对各位有所帮助，不要忘了收藏本站喔。

本文目录一览：

• 1、开窗函数
• 2、Hive sql及窗口函数
• 3、Hive 行转列 & 列转行
• 4、hive的行转列和列转行
• 5、数据仓库面试题
• 6、hive关于行转列，列转行的使用

开窗函数

开窗函数

需求：

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

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

2.

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

u2 a

u3 a

u5 a

u5 a

u5 a

请统计：

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

维度：店铺

指标：uv =》user_id

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

维度：店铺、 访客id

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

uv pv :

pv = page 次数 不需要去重

uv = user 次数 需要去重

2.行洞孙转列 列转行

1.列转行

||

v

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没有爆破函数（免费版本没有）

Hive sql及窗口函数

hive函数：

1、根据指定条件返回结果：case when then else end as

2、基本类型转换：CAST（）

3、nvl：处理空字段：三个str时，是否为空可以指定返回不同的值

4、sql通配符：

5、count(1)与COUNT(*)：返回行数

如果表没有主键，那么count（1）比count（*）快；

如果有主键，那么count（主键，联合主键）比count（*）快；

count(1)跟count(主键)一样，只扫描主键。count(*)跟count(非主键)一样，扫描整个表。明显前者更快一些。

性能问题：

1.任何情况下SELECT COUNT(*) FROM tablename是最优选择,(指没有where的情况）；

2.尽量减少SELECT COUNT(*) FROM tablename WHERE COL = ‘value’ 这种查询；

3.杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出现。

count(expression)：查询 is_reply=0 的数量： SELECT COUNT(IF(is_reply=0,1,NULL)) count FROM t_iov_help_feedback;

6、distinct与group by

distinct去重所有distinct之后所有的字段，如果有一个字段值不一致就不作为一条

group by是根据某一字段分组，然后查询出该条数据的所需字段，可以搭配 where max(time)或者Row_Number函数使用，求出最大的一条数据

7、使用with 临时表名 as() 的形式,简单的临时表直接嵌套进枝段sql中，复杂的和需要复用的表写到临猛行誉时表中，关联的时候先找到关联字段，过滤条件最好在临时表中先过滤后关联

处理json的函数：

split(json_array_string(schools), '\\|\\|') AS schools

get_json_object(school, '$.id') AS school_id,

字符串函数：

1、instr（’源字符串’ , ‘目标字符串’ ,’开始位置’,’第几次出现’）

instr(sourceString,destString,start,appearPosition)

1.sourceString代表源字符串； destString代表要从源字符串中查找的子串；

2.start代表查找的开始位置，这个参数可选的，默认为1；

3.appearPosition代表想从源字符中查找出第几次出现的destString，这个参数也是可选的， 默认为1

4.如果start的值为负数，则代表从右往左进行查找，但是位置数据仍然从左向右计带枯算。

5.返回值为：查找到的字符串的位置。如果没有查找到，返回0。

最简单例子： 在abcd中查找a的位置，从第一个字母开始查，查找第一次出现时的位置

select instr(‘abcd’,’a’,1,1) from dual; —1

应用于模糊查询：instr(字段名/列名, ‘查找字段’)

select code,name,dept,occupation from staff where instr(code, ‘001’) 0;

等同于 select code, name, dept, occupation from staff where code like ‘%001%’ ;

应用于判断包含关系：

select ccn,mas_loc from mas_loc where instr(‘FH,FHH,FHM’,ccn)0;

等同于 select ccn,mas_loc from mas_loc where ccn in (‘FH’,’FHH’,’FHM’);

2、substr（string A，int start，int len）和 substring（string A，int start，int len），用法一样

substr(time,1,8) 表示将time从第1位开始截取，截取的长度为8位

第一种用法：

substr（string A，int start）和 substring（string A，int start），用法一样

功效：返回字符串A从下标start位置到结尾的字符串

第二种用法：

substr（string A，int start，int len）和 substring（string A，int start，int len），用法一样

功效：返回字符串A从下标start位置开始，长度为len的字符串

3、get_json_object(form_data,'$.学生姓名') as student_name

json_tuple 函数的作用：用来解析json字符串中的多个字段

4、split(full_name, '\\.') [5] AS zq;  取的是数组里的第六个

日期(时间)函数：

1、to_date(event_time) 返回日期部分

2、date_sub：返回当前日期的相对时间

当前日期：select curdate() 

当前日期前一天：select  date_sub(curdate(),interval 1 day)

当前日期后一天：select date_sub(curdate(),interval -1 day)

date_sub(from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss'), 14)  将现在的时间总秒数转为标准格式时间，返回14天之前的时间

时间戳日期：

from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') 将现在的时间总秒数转为标准格式时间

from_unixtime(get_json_object(get_json_object(form_data,'$.挽单时间'),'$.$date')/1000) as retain_time

unix_timestamp('2019-08-15 16:40:00','yyyy-MM-dd HH:mm:ss')  --1565858400

日期时间戳：unix_timestamp()

date_format：yyyy-MM-dd HH:mm:ss 时间转格式化时间

select date_format('2019-10-07 13:24:20', 'yyyyMMdd000000')-- 20191007000000select date_format('2019-10-07', 'yyyyMMdd000000')-- 20191007000000

1.日期比较函数: datediff语法: datediff(string enddate,string startdate) 

返回值: int 

说明: 返回结束日期减去开始日期的天数。 

举例：  hive select datediff('2016-12-30','2016-12-29');  1

2.日期增加函数: date_add语法: date_add(string startdate, intdays) 

返回值: string 

说明: 返回开始日期startdate增加days天后的日期。 

举例：  hiveselect date_add('2016-12-29',10);  2017-01-08

3.日期减少函数: date_sub语法: date_sub (string startdate,int days) 

返回值: string 

说明: 返回开始日期startdate减少days天后的日期。 

举例：  hiveselect date_sub('2016-12-29',10);  2016-12-19

4.查询近30天的数据

select * from table where datediff(current_timestamp,create_time)=30；

create_time 为table里的字段，current_timestamp 返回当前时间 2018-06-01 11:00:00

3、trunc()函数的用法：当前日期的各种第一天,或者对数字进行不四舍五入的截取

日期：

1.select trunc(sysdate) from dual  --2011-3-18  今天的日期为2011-3-18

2.select trunc(sysdate, 'mm')   from   dual  --2011-3-1    返回当月第一天.

上月1号    trunc(add_months(current_date(),-1),'MM')

3.select trunc(sysdate,'yy') from dual  --2011-1-1       返回当年第一天

4.select trunc(sysdate,'dd') from dual  --2011-3-18    返回当前年月日

5.select trunc(sysdate,'yyyy') from dual  --2011-1-1   返回当年第一天

6.select trunc(sysdate,'d') from dual  --2011-3-13 (星期天)返回当前星期的第一天

7.select trunc(sysdate, 'hh') from dual   --2011-3-18 14:00:00   当前时间为14:41  

8.select trunc(sysdate, 'mi') from dual  --2011-3-18 14:41:00   TRUNC()函数没有秒的精确

数字：TRUNC（number,num_digits） Number 需要截尾取整的数字。Num_digits 的默认值为 0。TRUNC()函数截取时不进行四舍五入

11.select trunc(123.458,1) from dual --123.4

12.select trunc(123.458,-1) from dual --120

4、round()：四舍五入：

select round(1.455, 2)  #结果是：1.46，即四舍五入到十分位，也就是保留两位小数

select round(1.5)  #默认四舍五入到个位，结果是：2

select round(255, -1)  #结果是：260，即四舍五入到十位，此时个位是5会进位

floor()：地板数

ceil()天花板数

5、

6.日期转年函数: year语法:   year(string date) 

返回值: int

说明: 返回日期中的年。

举例：

hive   select year('2011-12-08 10:03:01') from dual;

2011

hive   select year('2012-12-08') fromdual;

2012

7.日期转月函数: month语法: month   (string date) 

返回值: int

说明: 返回日期中的月份。

举例：

hive   select month('2011-12-08 10:03:01') from dual;

12

hive   select month('2011-08-08') fromdual;

8

8.日期转天函数: day语法: day   (string date) 

返回值: int

说明: 返回日期中的天。

举例：

hive   select day('2011-12-08 10:03:01') from dual;

8

hive   select day('2011-12-24') fromdual;

24

9.日期转小时函数: hour语法: hour   (string date) 

返回值: int

说明: 返回日期中的小时。

举例：

hive   select hour('2011-12-08 10:03:01') from dual;

10

10.日期转分钟函数: minute语法: minute   (string date) 

返回值: int

说明: 返回日期中的分钟。

举例：

hive   select minute('2011-12-08 10:03:01') from dual;

3

11.日期转秒函数: second语法: second   (string date) 

返回值: int

说明: 返回日期中的秒。

举例：

hive   select second('2011-12-08 10:03:01') from dual;

1

12.日期转周函数: weekofyear语法:   weekofyear (string date) 

返回值: int

说明: 返回日期在当前的周数。

举例：

hive   select weekofyear('2011-12-08 10:03:01') from dual;

49

查看hive表在hdfs中的位置：show create table 表名;

在hive中hive2hive，hive2hdfs：

HDFS、本地、hive ----- Hive：使用 insert into | overwrite、loaddata local inpath "" into table student;

Hive ---- Hdfs、本地：使用：insert overwrite | local

网站访问量统计：

uv：每用户访问次数

ip：每ip(可能很多人)访问次数

PV:是指页面的浏览次数

VV:是指你访问网站的次数

sql：

基本函数：

count、max、min、sum、avg、like、rlike（'2%'、'_2%'、%2%'、'[2]'）（java正则）

and、or、not、in   

where、group by、having、{ join on 、full join}  、order by（desc降序）

sort by需要与distribut by集合结合使用：

hive (default) set mapreduce.job.reduces=3;  //先设置reduce的数量 

insert overwrite local directory '/opt/module/datas/distribute-by'

row format delimited fields terminated by '\t'

先按照部门编号分区，再按照员工编号降序排序。

select * from emp distribute by deptno sort by empno desc;

外部表  create external table if not exists dept

分区表：create table dept_partition ( deptno int, dname string, loc string )  partitioned by ( month string )

load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201809'); 

 alter table dept_partition add/drop partition(month='201805') ,partition(month='201804');

多分区联合查询：union

select * from dept_partition2 where month='201809' and day='10';

show partitions dept_partition;

desc formatted dept_partition;

二级分区表：create table dept_partition2 ( deptno int, dname string, loc string ) partitioned by (month string, day string) row format delimited fields terminated by '\t';

分桶抽样查询：分区针对的是数据的存储路径；分桶针对的是数据文件

create table stu_buck(id int, name string) clustered by(id) into 4 bucketsrow format delimited fields terminated by '\t';

设置开启分桶与reduce为1：

set hive.enforce.bucketing=true;

set mapreduce.job.reduces=-1;

分桶抽样：select * from stu_bucktablesample(bucket x out of y on id);

抽取，桶数/y，x是从哪个桶开始抽取，y越大 抽样数越少，y与抽样数成反比，x必须小于y

给空字段赋值：

如果员工的comm为NULL，则用-1代替或用其他字段代替  ：select nvl(comm,-1) from emp;

case when:如何符合记为1，用于统计、分组统计

select dept_id, sum(case sex when '男' then 1 else 0 end) man , sum(case sex when '女' then 1 else 0 end) woman from emp_sex group by dept_id;

用于组合归类汇总(行转列)：UDAF：多转一

concat：拼接查询结果

collect_set(col)：去重汇总，产生array类型字段，类似于distinct

select t.base, concat_ws('|',collect_set(t.name))   from (select concat_ws(',',xingzuo,blood_type) base,name  from person_info) t group by t.base;

解释：先第一次查询得到一张没有按照（星座血型）分组的表，然后分组，使用collect_set将名字组合成数组，然后使用concat将数组变成字符串

用于拆分数据：(列转行)：UDTF：一转多

explode(col)：将hive一列中复杂的array或者map结构拆分成多行。

lateral view  侧面显示：用于和UDTF一对多函数搭配使用

用法：lateral view udtf(expression) tablealias as cate

cate：炸开之后的列别名

temptable ：临时表表名

解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

开窗函数：

Row_Number,Rank，Dense_Rank  over：针对统计查询使用

Row_Number：返回从1开始的序列

Rank:生成分组中的排名序号，会在名词s中留下空位。3 3 5

dense_rank：生成分组中的排名序号，不会在名词中留下空位。3 3 4

over：主要是分组排序，搭配窗口函数使用

结果：

SUM、AVG、MIN、MAX、count

preceding：往前

following：往后

current row：当前行

unbounded：unbounded preceding 从前面的起点， unbounded following：到后面的终点

sum：直接使用sum是总的求和，结合over使用可统计至每一行的结果、总的结果、当前行+之前多少行/之后多少行、当前行到往后所有行的求和。

over(rowsbetween 3/current rowprecedingandunboundedfollowing )  当前行到往后所有行的求和

ntile：分片，结合over使用，可以给数据分片，返回分片号

使用场景：统计出排名前百分之或n分之一的数据。

lead,lag,FIRST_VALUE,LAST_VALUE

lag与lead函数可以返回上下行的数据

lead(col,n,dafault) 用于统计窗口内往下第n行值

第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值

第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

使用场景：通常用于统计某用户在某个网页上的停留时间

FIRST_VALUE:取分组内排序后，截止到当前行，第一个值

LAST_VALUE:取分组内排序后，截止到当前行,最后一个值

范围内求和:

cume_dist，percent_rank

–CUME_DIST :小于等于当前值的 行数 / 分组内总行数

–比如，统计小于等于当前薪水的人数，占总人数的比例

percent_rank:分组内当前行的RANK值-1/分组内总行数-1

总结：

在Spark中使用spark sql与hql一致，也可以直接使用sparkAPI实现。

HiveSql窗口函数主要应用于求TopN，分组排序TopN、TopN求和，前多少名前百分之几。

与Flink窗口函数不同。

Flink中的窗口是用于将无线数据流切分为有限块处理的手段。

window分类：

CountWindow：按照指定的数据条数生成一个 Window，与时间无关。

TimeWindow：按照时间生成 Window。

1. 滚动窗口（Tumbling Windows）：时间对齐，窗口长度固定，不重叠：：常用于时间段内的聚合计算

2.滑动窗口（Sliding Windows）：时间对齐，窗口长度固定，可以有重叠：：适用于一段时间内的统计（某接口最近 5min 的失败率来报警）

3. 会话窗口（Session Windows）无时间对齐，无长度，不重叠：：设置session间隔，超过时间间隔则窗口关闭。

Hive 行转列 & 列转行

hive中行转列和列转行比较常见，一般是用于中间数据的生产。

行转列一般采用IF进行判断，筛选出本列对应的数据进行统计。

如果我们直笑并接

就会报错

那么，我们由此引入LATERAL VIEW函数

对一列进行炸裂，其他列保持不变

对多列进行炸裂，数升塌其他列保持不变

行和列转换的问题主要薯圆解决存储和计算过程中关联关系不对等的问题。

[img]

hive的行转列和列转行

如下图：

1、列转行，即table1查询族滚出table2

select

    `order_id`,

    concat_ws(',', collect_list(`item_sku_id`))

from

    table1

group by

    `order_id`;

2、行转列，即table2查询出table1

select

   老腊 `order_id`,

 兆含余   `item_sku_id`

from

    table2 lateral view explode(split(item_sku_id), ',') as table as item_sku_id;

数据仓库面试题

一般数据仓库面试会面两轮，第一轮一般是sql技术面，第二轮就是 维度建模 和 数据治理 的问题。

一轮技术面（待补充）：

1、数据倾斜：

数据倾斜一般产生的原因是数据在map端hash分配到reduce端时，某一个key的数量远大于其他的key，导致某一个reduce的处理时间较长。

1）key分布不均匀

2）数据本身就是如此

3）数据关联时没有把握好关联键

解决方案

1）参数调整：hive.map.aggr = true;hive.groupby.skewindata=true;

 当存在数据倾斜时开启负载均衡，此时会生成两个MapReduce任务，第一个MR任务会将map端产生的key随机的分配到reduce，先进行一次聚合，第二个MR任务会将第一个任务的预处理结果作为输入，将相同的key分配到同一个reduce当中。

2）sql调整：在处理大量空值导致数据倾斜的情况下，可以先将空值附一个特殊值去处理，比如给一个随机数加字符串的方式，因为空值数据是关联不上的，不会影响后期处理。

2、order by，sort by，cluster by，distribute by的区别

1）order by是全局排序，排序过程在一个reduce中进行，在数据量较大时就会很慢

2）sort by是局部排序，排序结果在同一个reduce中使有序的

3）distribute by是将数据按照字段划分到一个reduce中，一般与sort by连用进行分组排序的作用

4）cluster by除具有distribute by功能外慎首滑还具有sort by的功能

order by优化（阿里面试）：

1）开启严格模式，order by之后添加limit子句

2）利用sort by，在每个reduce中先排序取出top项，再把预处理结果order by输出

3、hive中内部表和外部表的区别

1）在创建表的时候，内部表是将数据移动到数据仓库指向的路径，外部表仅记录数据所在的路径，不对数据的位置做任何改变。

2）在删除表的时候，内部表会将元数据和数据都删除，外部表只删除元芹升数据。

4、列转行、行转列

1）列转行：lateral view explode(split('column_name',','))作为宽腊一个新表

2）行转列：concat_ws(',',collect_set(column_name))

5、mapreduce运行原理

6、数据仓库分层原理（阿里面试）

7、维度建模中三种事实表的应用场景（阿里面试）

二轮面试（待补充）

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函数，这个行数可以把数组的每个元素转换为一行。

形如

id

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

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

id

1

2

3

4

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

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

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

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

一般sql为:

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