HiveSQL如何生成连续日期剖析

2024-04-08 07:28

本文主要是介绍HiveSQL如何生成连续日期剖析,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

HiveSQL如何生成连续日期剖析

情景假设:
有一结果表,表中有start_dt和end_dt两个字段,,想要根据开始和结束时间生成连续日期的多条数据,应该怎么做?直接上结果sql。(为了便于演示和测试这里通过SELECT '2024-03-01' AS start_dt,'2024-03-06' AS end_dt模拟一个结果表数据)

SELECT  t1.start_dt,t1.end_dt,t2.pos,date_add(t1.start_dt,t2.pos) AS conn_dt
FROM
(SELECT  '2024-03-01' AS start_dt,'2024-03-06' AS end_dt
) t1 lateral view posexplode(split(repeat(',', DATEDIFF(end_dt, start_dt)), ',')) t2 AS pos, val;
+--------------+-------------+---------+-------------+
| t1.start_dt  |  t1.end_dt  | t2.pos  |   conn_dt   |
+--------------+-------------+---------+-------------+
| 2024-03-01   | 2024-03-06  | 0       | 2024-03-01  |
| 2024-03-01   | 2024-03-06  | 1       | 2024-03-02  |
| 2024-03-01   | 2024-03-06  | 2       | 2024-03-03  |
| 2024-03-01   | 2024-03-06  | 3       | 2024-03-04  |
| 2024-03-01   | 2024-03-06  | 4       | 2024-03-05  |
| 2024-03-01   | 2024-03-06  | 5       | 2024-03-06  |
+--------------+-------------+---------+-------------+

如果对涉及到的函数和语法不是特别了解,直接看到上述结果可能有点懵,接下来换个形式理解下,即如下sql

SELECT  t1.start_dt,t1.end_dt,t2.pos,date_add(t1.start_dt,t2.pos) AS conn_dt
FROM
(SELECT  '2024-03-01' AS start_dt,'2024-03-06' AS end_dt
) t1
CROSS JOIN
(SELECT  posexplode(split(repeat(',',DATEDIFF('2024-03-06','2024-03-01')),',')) AS(pos,val)
) t2;
-- 执行结果同上

如上sql结构比较简单,即t1表和t2表进行笛卡尔集,t1是原始表只有1行数据,但是结果是6行数据,因此关键点是t2的结果。
t2本质上其实就是先生成一组从0开始编号的6行结果,笛卡尔积之后,从而将数据从原先的一行变成6行,然后再根据生成的序号,每一行数据使用开始日期+序号得到一个新日期。最终得到的结果中新日期是连续日期。
可以看出生成连续序号结果的关键语句是lateral view posexplode(split(repeat(',', DATEDIFF(end_dt, start_dt)), ',')) t2 AS pos, val,这条语句包可以拆解为以下两部分

  • lateral view,是hive支持的语法
  • posexplode(split(repeat(',', DATEDIFF(end_dt, start_dt)), ',')),是hive提供的函数。

先看下posexplode函数以及嵌套的内部函数都干了什么,对函数依次拆解执行,直接从结果来了解每个函数作用。

select datediff('2024-03-06','2024-03-01'); -- 5
select repeat(',',datediff('2024-03-06','2024-03-01')); -- ,,,,,
select split(repeat(',',datediff('2024-03-06','2024-03-01')),','); -- ["","","","","",""]select posexplode(split(repeat(',',datediff('2024-03-06','2024-03-01')),','));
+------+------+
| pos  | val  |
+------+------+
| 0    |      |
| 1    |      |
| 2    |      |
| 3    |      |
| 4    |      |
| 5    |      |
+------+------+

看到这里就明白posexlode函数及其嵌套函数干了什么,其他几个函数可能比较熟悉,不做过多介绍,在这里仅介绍下posexplode函数和lateral view语法。

在说明posexplode函数之前,有必要先学习下explode函数。从字面意思来看posexplode其实是pos+explode。
explode和posexplode是udtf函数。

1. explode 函数

explode函数有两种入参形式,分别支持数组和map。依次看下传入数组和map的处理结果。

格式:explode(ARRAY<T> a)
将数组分解为多行。返回一个包含单列 (col) 的行集,数组中的每个元素对应一行。

1.1. 数组作为入参

简而言之,对数组进行行转列,示例如下

select explode(array('a','b','c'));
+------+
| col  |
+------+
| a    |
| b    |
| c    |
+------+-- 通过as对生成的结果列命名
select explode(array('a','b','c')) as c1;
+-----+
| c1  |
+-----+
| a   |
| b   |
| c   |
+-----+

1.2. map作为入参

格式:explode(MAP<Tkey,Tvalue> m)
将map分解为多行。返回具有两列(key,value)的行集,输入map中的每个键值对变成输出中的一行。从 Hive 0.8.0 开始。

在此插入根据字符串生成map的方法,方便explode方法进行测试。
格式:str_to_map(text[, delimiter1, delimiter2])
使用两个分隔符将文本拆分为键值对。Delimiter1 将文本分隔为 K-V 对,Delimiter2 拆分每个 K-V 对。
delimiter1的默认值为,
delimiter2的默认值为:

select str_to_map('a:1,b:2,c:3');
+----------------------------+
|            _c0             |
+----------------------------+
| {"a":"1","b":"2","c":"3"}  |
+----------------------------+
select explode(str_to_map('a:1,b:2,c:3'));
+------+--------+
| key  | value  |
+------+--------+
| a    | 1      |
| b    | 2      |
| c    | 3      |
+------+--------+-- 通过as与对生成的结果列命名
select explode(str_to_map('a:1,b:2,c:3')) as (c1,c2);
+-----+-----+
| c1  | c2  |
+-----+-----+
| a   | 1   |
| b   | 2   |
| c   | 3   |
+-----+-----+

因此explode函数的作用是将数组或map中的每一个元素作为结果中的一行,如果是map从将key和value分别作为结果中的两列值。

接下来再看posexlpode函数就很好理解了。

2. posexplode 函数

格式:posexplode(ARRAY<T> a)
将数组分解为多行,并附加 int 类型的位置列(原始数组中项的位置,从 0 开始)。返回一个包含两列 (pos,val) 的行集,数组中的每个元素对应一行。

简而言之,posexplode函数就是在explode函数的结果上增加一列序号值,序号从0开始,从函数名称可以看出posexplode函数就是pos+explode.

select posexplode(array('a','b','c'));
+------+------+
| pos  | val  |
+------+------+
| 0    | a    |
| 1    | b    |
| 2    | c    |
+------+------+-- 同样可以通过as对结果进行命名
select posexplode(array('a','b','c')) as(index,value);
+--------+--------+
| index  | value  |
+--------+--------+
| 0      | a      |
| 1      | b      |
| 2      | c      |
+--------+--------+
  • posexplode函数仅支持数组作为入参。
  • 单独使用posexplode函数时(区别于和lateral view一起使用)通过as对结果进行重命名时,必须带有括号,否则sql执行报错。

到此posexplode函数的作用已经搞清楚了,接下来学习下lateral view语法。

3. lateral view语法

语法格式如下

lateralView: LATERAL VIEW (OUTER) udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*

定义描述
简单说,lateral view是用来和udtf函数结合使用的,udtf函数为每个输入行生成零个或多个输出行,通过上面explode函数结果也可以说明这点。
lateral view将udtf应用于基表的每一行,然后将生成的输出行join到输入行上,以形成具有所提供表别名的虚拟表。

从 hive 0.12.0版本开始,可以省略列别名。默认使用udft函数返回的字段名。

详细示例参考官网文档。

根据描述再理解语法格式中每一部分的含义
语法描述
现在重新来看开头的结果sql就比较清晰了,t1是basicTabel的别名,t2是udtf输出结果的虚拟表别名,as pos,val则是posexplode函数生成的两列结果的别名,然后在select中分别使用t1和t2来获取两个表的字段。又因为lateral view将基表的每一行都作为udtf函数的输入,因此可以在datediff函数中直接使用end_dt和start_dt列。

SELECT  t1.start_dt,t1.end_dt,t2.pos,t2.val,date_add(t1.start_dt,t2.pos) AS conn_dt
FROM
(SELECT  '2024-03-01' AS start_dt,'2024-03-06' AS end_dt
) t1 lateral view posexplode(split(repeat(',', DATEDIFF(end_dt, start_dt)), ',')) t2 AS pos, val;

3.1. outer 关键字

作用:当udft的结果不会生成任何行时,如果不使用outer关键字,则最终结果也不会生成任何行,可以这样理解,左表inner join右表(udtf结果),当右表是空表时,则最终结果也一定是空的,指定outer后inner join就会变成left join。示例如下

select posexplode(array());
+------+------+
| pos  | val  |
+------+------+
+------+------+SELECT  t1.start_dt,t1.end_dt,t2.pos,t2.val,date_add(t1.start_dt,t2.pos) AS conn_dt
FROM
(SELECT  '2024-03-01' AS start_dt,'2024-03-06' AS end_dt
) t1 lateral view posexplode(array()) t2 AS pos, val;
-- 由于posexplode函数的结果为空,最终结果为空。
+--------------+------------+---------+---------+----------+
| t1.start_dt  | t1.end_dt  | t2.pos  | t2.val  | conn_dt  |
+--------------+------------+---------+---------+----------+
+--------------+------------+---------+---------+----------+SELECT  t1.start_dt,t1.end_dt,t2.pos,t2.val,date_add(t1.start_dt,t2.pos) AS conn_dt
FROM
(SELECT  '2024-03-01' AS start_dt,'2024-03-06' AS end_dt
) t1 lateral view outer posexplode(array()) t2 AS pos, val;
-- 使用outer关键字后,基表数据会输出,而右表字段为null
+--------------+-------------+---------+---------+----------+
| t1.start_dt  |  t1.end_dt  | t2.pos  | t2.val  | conn_dt  |
+--------------+-------------+---------+---------+----------+
| 2024-03-01   | 2024-03-06  | NULL    | NULL    | NULL     |
+--------------+-------------+---------+---------+----------+

这篇关于HiveSQL如何生成连续日期剖析的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!


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

相关文章

MySQL中动态生成SQL语句去掉所有字段的空格的操作方法

《MySQL中动态生成SQL语句去掉所有字段的空格的操作方法》在数据库管理过程中,我们常常会遇到需要对表中字段进行清洗和整理的情况,本文将详细介绍如何在MySQL中动态生成SQL语句来去掉所有字段的空... 目录在mysql中动态生成SQL语句去掉所有字段的空格准备工作原理分析动态生成SQL语句在MySQL

Mysql表如何按照日期字段的年月分区

《Mysql表如何按照日期字段的年月分区》:本文主要介绍Mysql表如何按照日期字段的年月分区的实现方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、创键表时直接设置分区二、已有表分区1、分区的前置条件2、分区操作三、验证四、注意总结一、创键表时直接设置分区

Java利用docx4j+Freemarker生成word文档

《Java利用docx4j+Freemarker生成word文档》这篇文章主要为大家详细介绍了Java如何利用docx4j+Freemarker生成word文档,文中的示例代码讲解详细,感兴趣的小伙伴... 目录技术方案maven依赖创建模板文件实现代码技术方案Java 1.8 + docx4j + Fr

Java编译生成多个.class文件的原理和作用

《Java编译生成多个.class文件的原理和作用》作为一名经验丰富的开发者,在Java项目中执行编译后,可能会发现一个.java源文件有时会产生多个.class文件,从技术实现层面详细剖析这一现象... 目录一、内部类机制与.class文件生成成员内部类(常规内部类)局部内部类(方法内部类)匿名内部类二、

使用Jackson进行JSON生成与解析的新手指南

《使用Jackson进行JSON生成与解析的新手指南》这篇文章主要为大家详细介绍了如何使用Jackson进行JSON生成与解析处理,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录1. 核心依赖2. 基础用法2.1 对象转 jsON(序列化)2.2 JSON 转对象(反序列化)3.

java中使用POI生成Excel并导出过程

《java中使用POI生成Excel并导出过程》:本文主要介绍java中使用POI生成Excel并导出过程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录需求说明及实现方式需求完成通用代码版本1版本2结果展示type参数为atype参数为b总结注:本文章中代码均为

在java中如何将inputStream对象转换为File对象(不生成本地文件)

《在java中如何将inputStream对象转换为File对象(不生成本地文件)》:本文主要介绍在java中如何将inputStream对象转换为File对象(不生成本地文件),具有很好的参考价... 目录需求说明问题解决总结需求说明在后端中通过POI生成Excel文件流,将输出流(outputStre

Python使用date模块进行日期处理的终极指南

《Python使用date模块进行日期处理的终极指南》在处理与时间相关的数据时,Python的date模块是开发者最趁手的工具之一,本文将用通俗的语言,结合真实案例,带您掌握date模块的六大核心功能... 目录引言一、date模块的核心功能1.1 日期表示1.2 日期计算1.3 日期比较二、六大常用方法详

C/C++随机数生成的五种方法

《C/C++随机数生成的五种方法》C++作为一种古老的编程语言,其随机数生成的方法已经经历了多次的变革,早期的C++版本使用的是rand()函数和RAND_MAX常量,这种方法虽然简单,但并不总是提供... 目录C/C++ 随机数生成方法1. 使用 rand() 和 srand()2. 使用 <random

Flask 验证码自动生成的实现示例

《Flask验证码自动生成的实现示例》本文主要介绍了Flask验证码自动生成的实现示例,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习... 目录生成图片以及结果处理验证码蓝图html页面展示想必验证码大家都有所了解,但是可以自己定义图片验证码