如何基于开始日期和结束日期创建循环

ulmd4ohb  于 2021-06-24  发布在  Hive
关注(0)|答案(1)|浏览(413)

我有下表:

我想每隔10天做一个循环,从最小开始,直到最大。最后,我需要将相应的数据带到新的日期:
如下图所示:

有人知道如何在配置单元sql中执行此操作吗?
在c++中很容易,但由于某些原因,我无法让它在hive中工作。我真的需要所有能得到的帮助。谢谢您!

t1.reading_date t1.use_date t1.name t1.reading_pct
12/17/2019  12/11/2019  file1   75.00915527
12/22/2019  12/11/2019  file1   75.5859375
12/27/2019  12/11/2019  file1   76.90429688
1/1/2020    12/11/2019  file1   74.29199219
1/2/2020    12/11/2019  file1   64.93835449
1/7/2020    12/11/2019  file1   65.10620117
1/12/2020   12/11/2019  file1   66.90063477
1/17/2020   12/11/2019  file1   66.47033691
1/22/2020   12/11/2019  file1   66.35131836
1/27/2020   12/11/2019  file1   59.61303711
new(t1.reading_date)    t1.use_date t1.name t1.reading_pct
12/17/2019  12/11/2019  file1   75.00915527
12/27/2019  12/11/2019  file1   76.90429688
1/7/2020    12/11/2019  file1   65.10620117
1/17/2020   12/11/2019  file1   66.47033691
1/27/2020   12/11/2019  file1   59.61303711
xeufq47z

xeufq47z1#

类似的内容,请参见代码中的注解。无法测试,因为您已将数据发布为图片。自己调试:

With minmaxdt as (
select min(reading_date) as mindt, max(reading_date) as maxdt from your_table
),

date_range as 
(--this query generates date range
select date_add (mindt,s.i+10) as dt 
  from minmaxdt
       lateral view posexplode(split(space(int(datediff(maxdt,mindt) div 10)),' ')) s as i,x 
) 

--Join with date range 
select reading_date, use_date, name, reading_pct
  from your_table t1
       inner join date_range dr on t1.reading_date=dr.dt

相关问题