如何在 HIVE 中生成日期序列?(创建表)

问题描述

假设我目前有一个表，每个账户有 1 行，表中的数据是:

Suppose I currently have a table that has 1 row for each account and the data in the tables are:

1. 帐号
2. 开始日期
3. 结束日期

现在我想创建一个新表，该表在帐户打开的每一天都有 1 行，即每个帐户的开始日期和结束日期(含)之间的每一行都有 1 天.

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.

例如

表 1

Account Number    Start Date    End Date
123               1-Jan-17      1-Jul-17
456               1-Feb-17      4-May-17

表 2(所需表)

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

我知道在 Postgresql 中有一个名为生成系列"的函数，可以让您轻松地做到这一点.我想知道 HIVE 中是否有类似的功能可以让您也这样做?

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?

谢谢！

推荐答案

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 中生成日期序列?(创建表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！