如何生成HIVE中的日期系列? (创建表) [英] How to generate Date Series in HIVE? (Creating table)
问题描述
假设我目前有一个表,每个帐户有一行,表中的数据是:
- 帐号 li>
- 开始日期
- 结束日期
d希望创建一个新表格,该表格每天有一行帐户处于打开状态,即每个帐户的开始日期和结束日期(含)之间的每一行为1天。
例如
表1
帐号开始日期结束日期
$ p $表2 (所需表格)
123 1-Jan-17 1-Jul-17
456 1-Feb-17 4-May-17
账户号码日
123 1-Jan-17
123 1-Jan-17
...
123 1-Jul-17
456 1-Feb-17
456 2-Feb-17
...
456 4-May-17
我知道在Postgresql中有一个叫做'generate series'的函数,它可以让你轻松地做到这一点。我想知道HIVE中是否有类似的功能可以让你做到这一点?
谢谢!
解决方案
select t.AccountNumber
,date_add(t.StartDate,pe.i)as Day
从表1 t
横向视图
posexplode(split(space(datediff(t.EndDate,t.StartDate)),''))pe作为i,x
Suppose I currently have a table that has 1 row for each account and the data in the tables are:
- Account Number
- Start Date
- End Date
Now I'd like to create a new table that has 1 row for each day the account is open, i.e. 1 day for each row between the start and end dates (inclusive) for each account.
E.g.
Table 1
Account Number Start Date End Date 123 1-Jan-17 1-Jul-17 456 1-Feb-17 4-May-17
Table 2 (Desired table)
Account Number Day 123 1-Jan-17 123 1-Jan-17 ... 123 1-Jul-17 456 1-Feb-17 456 2-Feb-17 ... 456 4-May-17
I know in Postgresql there's a function called 'generate series' that would allow you to do that easily. I'm wondering if there's a similar function in HIVE that would allow you to do that as well?
Thanks!
解决方案select t.AccountNumber ,date_add (t.StartDate,pe.i) as Day from Table1 t lateral view posexplode(split(space(datediff(t.EndDate,t.StartDate)),' ')) pe as i,x
这篇关于如何生成HIVE中的日期系列? (创建表)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!