HiveSQL如何生成连续日期剖析

2024-04-08 07:28
文章标签 剖析 生成 连续 日期 hivesql

本文主要是介绍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。

相关文章

AI一键生成 PPT

AI一键生成 PPT

AI一键生成 PPT 操作步骤 作为一名打工人，是不是经常需要制作各种PPT来分享我的生活和想法。但是，你们知道，有时候灵感来了，时间却不够用了！😩直到我发现了Kimi AI——一个能够自动生成PPT的神奇助手！🌟 什么是Kimi？ 一款月之暗面科技有限公司开发的AI办公工具，帮助用户快速生成高质量的演示文稿。 无论你是职场人士、学生还是教师，Kimi都能够为你的办公文
阅读更多...
poj2406（连续重复子串）

poj2406（连续重复子串）

题意:判断串s是不是str^n，求str的最大长度。 解题思路：kmp可解，后缀数组的倍增算法超时。next[i]表示在第i位匹配失败后，自动跳转到next[i]，所以1到next[n]这个串 等于 n-next[n]+1到n这个串。 代码如下； #include<iostream>#include<algorithm>#include<stdio.h>#include<math.
阅读更多...
pdfmake生成pdf的使用

pdfmake生成pdf的使用

实际项目中有时会有根据填写的表单数据或者其他格式的数据，将数据自动填充到pdf文件中根据固定模板生成pdf文件的需求 文章目录 利用pdfmake生成pdf文件1.下载安装pdfmake第三方包2.封装生成pdf文件的共用配置3.生成pdf文件的文件模板内容4.调用方法生成pdf 利用pdfmake生成pdf文件 1.下载安装pdfmake第三方包 npm i pdfma
阅读更多...
poj 1258 Agri-Net（最小生成树模板代码）

poj 1258 Agri-Net（最小生成树模板代码）

感觉用这题来当模板更适合。 题意就是给你邻接矩阵求最小生成树啦。~ prim代码：效率很高。172k...0ms。 #include<stdio.h>#include<algorithm>using namespace std;const int MaxN = 101;const int INF = 0x3f3f3f3f;int g[MaxN][MaxN];int n
阅读更多...
poj 1287 Networking（prim or kruscal最小生成树）

poj 1287 Networking（prim or kruscal最小生成树）

题意给你点与点间距离，求最小生成树。 注意点是，两点之间可能有不同的路，输入的时候选择最小的，和之前有道最短路WA的题目类似。 prim代码： #include<stdio.h>const int MaxN = 51;const int INF = 0x3f3f3f3f;int g[MaxN][MaxN];int P;int prim(){bool vis[MaxN];
阅读更多...
poj 2349 Arctic Network uva 10369（prim or kruscal最小生成树）

poj 2349 Arctic Network uva 10369（prim or kruscal最小生成树）

题目很麻烦，因为不熟悉最小生成树的算法调试了好久。 感觉网上的题目解释都没说得很清楚，不适合新手。自己写一个。 题意：给你点的坐标，然后两点间可以有两种方式来通信：第一种是卫星通信，第二种是无线电通信。 卫星通信：任何两个有卫星频道的点间都可以直接建立连接，与点间的距离无关； 无线电通信：两个点之间的距离不能超过D，无线电收发器的功率越大，D越大，越昂贵。 计算无线电收发器D
阅读更多...
hdu 1102 uva 10397（最小生成树prim）

hdu 1102 uva 10397（最小生成树prim）

hdu 1102: 题意： 给一个邻接矩阵，给一些村庄间已经修的路，问最小生成树。 解析： 把已经修的路的权值改为0，套个prim（）。 注意prim 最外层循坏为n-1。 代码： #include <iostream>#include <cstdio>#include <cstdlib>#include <algorithm>#include <cstri
阅读更多...
【生成模型系列（初级）】嵌入（Embedding）方程——自然语言处理的数学灵魂【通俗理解】

【生成模型系列（初级）】嵌入（Embedding）方程——自然语言处理的数学灵魂【通俗理解】

【通俗理解】嵌入（Embedding）方程——自然语言处理的数学灵魂 关键词提炼 #嵌入方程 #自然语言处理 #词向量 #机器学习 #神经网络 #向量空间模型 #Siri #Google翻译 #AlexNet 第一节：嵌入方程的类比与核心概念【尽可能通俗】 嵌入方程可以被看作是自然语言处理中的“翻译机”，它将文本中的单词或短语转换成计算机能够理解的数学形式，即向量。 正如翻译机将一种语言
阅读更多...
XTU 1233 n个硬币连续m个正面个数（dp）

XTU 1233 n个硬币连续m个正面个数（dp）

题面： Coins Problem Description: Duoxida buys a bottle of MaiDong from a vending machine and the machine give her n coins back. She places them in a line randomly showing head face or tail face o
阅读更多...
poj 3723 kruscal，反边取最大生成树。

poj 3723 kruscal，反边取最大生成树。

题意： 需要征募女兵N人，男兵M人。 每征募一个人需要花费10000美元，但是如果已经招募的人中有一些关系亲密的人，那么可以少花一些钱。 给出若干的男女之间的1~9999之间的亲密关系度，征募某个人的费用是10000 - （已经征募的人中和自己的亲密度的最大值）。 要求通过适当的招募顺序使得征募所有人的费用最小。 解析： 先设想无向图，在征募某个人a时，如果使用了a和b之间的关系
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2