如何在 HIVE 中生成日期序列?(创建表) [英] How to generate Date Series in HIVE? (Creating table)

查看:39
本文介绍了如何在 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 中生成日期序列?(创建表)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆