我有一个棘手的缺口和岛屿问题。日期孤岛必须在字段层次结构中标识。
这种差距和孤岛问题与传统类型有以下几个方面的不同：
日期在两个字段（startdate和enddate）中采用范围样式格式
孤岛的分组必须考虑字段的层次结构
这些数据在cloudera 6.2x中，这限制了一些潜在的解决方案（例如递归cte）
如果层次结构的结束日期与下一次发生的开始日期在同一天或前一天，则应为孤岛。在给定约束和示例数据的情况下，如何实现这一点？
我尝试了一些解决方案，包括以下伪代码：
在由这些层次结构和startdate排序的层次结构字段的分区上创建行号。
当days\u add（end\u date，1）>=lead（start\u date，1）和row\u number<lead（row\u number，1）然后0 else 1时，使用案例逻辑确定差距
使用case逻辑识别孤岛：当row_number=1时，lag（gaps，1）=1时，lag（islands，1）+1 else lag（islands，1）
这在逻辑上应该可以用来创建我可以用来分组的孤岛，并采用按层次结构分组的startdate的最小值和enddate的最大值。当我把数据放进excel并应用这个算法时，我得到了正确的结果。显然，在cloudera中，在每个记录的评估过程中没有滞后（islands，1）值？

CREATE TABLE sampleInput (
  person int, 
  level1 int, 
  level2 int, 
  level3 int, 
  StartDate DATETIME, 
  EndDate DATETIME,
  rowNumber int)

INSERT INTO sampleInput
VALUES
(1,1,17,101,'2001-09-16','2001-09-19',1),
(1,1,17,102,'2001-09-20','2001-09-24',2),
(1,1,17,103,'2001-04-15','2001-04-25',3),
(1,1,17,104,'2001-08-02','2001-08-15',4),
(1,1,20,105,'2001-03-10','2001-03-18',5),
(1,1,20,105,'2001-04-01','2001-04-08',6),
(1,1,20,105,'2001-07-20','2001-07-25',7),
(1,1,20,106,'2001-02-20','2001-02-08',8),
(1,1,31,107,'2001-04-25','2001-04-30',9),
(1,1,31,107,'2001-05-01','2001-05-29',10),
(2,3,42,111,'2002-04-05','2002-04-05',11),
(2,3,42,111,'2002-04-06','2002-04-06',12),
(2,3,42,111,'2002-04-07','2002-04-30',13),
(2,3,42,111,'2002-05-01','2002-05-25',14),
(2,3,42,111,'2002-05-28','2002-06-01',15),
(2,3,42,111,'2002-06-04','2002-06-06',16),
(2,3,42,111,'2002-06-08','2002-06-20',17)

以及：

CREATE TABLE sampleOutput (
  person int, level1 int, level2 int, level3 int, 
  StartDate DATETIME, EndDate DATETIME ,rowNumberConcat varchar(max)
)

INSERT INTO sampleOutput
VALUES
(1,1,17,101,'2001-09-16','2001-09-19','1'),
(1,1,17,102,'2001-09-20','2001-09-24','2'),
(1,1,17,103,'2001-04-15','2001-04-25','3'),
(1,1,17,104,'2001-08-02','2001-08-15','4'),
(1,1,20,105,'2001-03-10','2001-03-18','5'),
(1,1,20,105,'2001-04-01','2001-04-08','6'),
(1,1,20,105,'2001-07-20','2001-07-25','7'),
(1,1,20,106,'2001-02-20','2001-02-08','/'),
(1,1,31,107,'2001-04-25','2001-05-29','9,10'),
(2,3,42,111,'2002-04-05','2002-05-25','11,12,13,14'),
(2,3,42,111,'2002-05-28','2002-06-01','15'),
(2,3,42,111,'2002-06-04','2002-06-06','16'),
(2,3,42,111,'2002-06-08','2002-06-20','17')

我已将输入和输出包含在以下sql fiddle中：http://www.sqlfiddle.com/#!18/770768/1包含的rownumber和rownumberconcat字段仅用于显示输入和输出的沿袭。为mssql设置fiddle只是因为没有cloudera选项。作为补充，如果我应该使用不同的口味或完全不同的网站，请让我知道。