sql - 如何以编程方式为过去和 future 增加两周的开始日期和结束日期？

Question

I need to create a hive table with three columns (sprint_name, begin_date, end_date). I have the sprint_name column populated and based on that I want to populate the other two columns on a two weeks increment. So basically, a sprint lasts for two weeks. If hive is not possible, please help me on how to create this in sql server.

Sprint_name                   Begin_date    End_date
Sprint 1.1  METADATA FYE20    2/6/2019      2/19/2019  
Sprint 1.2  METADATA FYE20    2/20/2019     3/5/2019   
Sprint 1.3  METADATA FYE20    3/6/2019      3/19/2019 
Sprint 1.4  METADATA FYE20            
Sprint 1.5  METADATA FYE20                           
Sprint 1.6  METADATA FYE20                           
Sprint 1.6  METADATA SALE FYE20                      
Sprint 1.7  METADATA FYE20      
Sprint 2.1  METADATA FYE20
Sprint 2.2  METADATA FYE20
Sprint 2.3  METADATA FYE20
Sprint 2.3  METADATA SALE FYE20
Sprint 2.3  METADATA DOWN FYE20
Sprint 2.4  METADATA FYE20
Sprint 2.5  METADATA FYE20
Sprint 2.6  METADATA FYE20      7/10/2019        7/23/2019
Sprint 2.7  SALE FYE20          7/24/2019        8/6/2019      
Sprint 2.7  METADATA FYE20      7/24/2019        8/6/2019
Sprint 3.1  METADATA FYE20     
.
.
.
Sprint 4.6  METADATA FYE20
.
.
Sprint 1.1  METADATA FYE21

Answer 1

在 Hive 中使用 CTE 和 Posexplode 更新表还有一个技巧。

CREATE TABLE IF NOT EXISTS db.test_date
(
 userid int
,sprint_name string
,start_date date
,end_date date
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS orc
;

插入第一条记录，其中包含您的开始日期和结束日期，其余条目的日期保留为空。

INSERT INTO TABLE db.test_date VALUES
(1,'Sprint 1.1  METADATA FYE20','2019-02-06','2019-02-19' ),
(2,'Sprint 1.2  METADATA FYE20',NULL,NULL),
(3,'Sprint 1.3  METADATA FYE20',NULL,NULL ),
(4,'Sprint 1.4  METADATA FYE20',NULL,NULL),
(5,'Sprint 1.5  METADATA FYE20',NULL,NULL),
(6,'Sprint 1.6  METADATA FYE20',NULL,NULL),
(7,'Sprint 1.6  METADATA SALE ',NULL,NULL),
(8,'Sprint 1.7  METADATA FYE20',NULL,NULL),
(9,'Sprint 2.1  METADATA FYE20',NULL,NULL),
(10,'Sprint 2.2  METADATA FYE20',NULL,NULL);

查询:

with
CTE AS (
select date_add(start_date,((pe.i)* 14)) as start_date,date_add(end_date,((pe.i)*14)) as end_date,pe.i+1 as userid
from  db.test_date 
lateral view 
posexplode(split(space(10-1),' ')) pe as i,x
where start_date is not null
)
insert overwrite table db.test_date
select 
 t.Userid
,t.sprint_name
,d.start_date
,d.end_date
FROM db.test_date t
CROSS JOIN CTE d
on d.userid=t.userid

;

注意:我想到了使用 posexplode(split(space(max(userid)-min(userid),' ')) pe 作为 i,x但 max & min 不支持 UDAF。

结果:

hive> select * from db.test_date;
OK
1       Sprint 1.1  METADATA FYE20      2019-02-06      2019-02-19
2       Sprint 1.2  METADATA FYE20      2019-02-20      2019-03-05
3       Sprint 1.3  METADATA FYE20      2019-03-06      2019-03-19
4       Sprint 1.4  METADATA FYE20      2019-03-20      2019-04-02
5       Sprint 1.5  METADATA FYE20      2019-04-03      2019-04-16
6       Sprint 1.6  METADATA FYE20      2019-04-17      2019-04-30
7       Sprint 1.6  METADATA SALE       2019-05-01      2019-05-14
8       Sprint 1.7  METADATA FYE20      2019-05-15      2019-05-28
9       Sprint 2.1  METADATA FYE20      2019-05-29      2019-06-11
10      Sprint 2.2  METADATA FYE20      2019-06-12      2019-06-25