我有下面的查询，它在SQLServer中运行良好，但我正尝试在hadoop/hiveql中使用它。
但是，hiveql没有top 1函数或outer apply。有人能提供一个替代方案，但结果相同吗？
下面是数据（表格），下面是我试图实现的结果。
谢谢，丹尼

CREATE TABLE #temp
(
ID varchar(20)NOT NULL
,CreatedDate DATETIME NOT NULL
,CompletedDate DATETIME NOT NULL
,TYPES varchar(20) NOT NULL
,STATUSS varchar(20) NOT NULL
);
INSERT INTO #temp
VALUES ('61030203647','20160427','20160427','Re-Activattion', 'COMP');
INSERT INTO #temp
VALUES('61030203647','20160425','20160426','Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES('61030203647','20160422','20160422','Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES('61030203647','20170311','20170613','Re-Activattion', 'COMP');
INSERT INTO #temp
VALUES('64074558792','20160731','20160805','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES('64074558792','20160801','20160805','Re-Activattion','PARTIALLY');
INSERT INTO #temp
VALUES('64074558792','20160809','20160809','Re-Activattion','PARTIALLY');
INSERT INTO #temp
VALUES('64074558792','20160810','20160810','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES('64074558792','20160810','20160810','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES('64074558792','20160811','20160811','Re-Activattion','COMP');
INSERT INTO #temp
VALUES('64074558792','20160812','20160814','Re-Activattion','N-CO');
;WITH src AS (
SELECT ID, CreatedDate, CompletedDate, TYPES, STATUSS,
 ROW_NUMBER() OVER(PARTITION BY ID ORDER BY CreatedDate , CompletedDate) AS rn
 FROM #temp 
)
,grouped as (
Select s.* ,d.rnGrp from src s
outer apply (select top 1 rn rnGrp from src s2 
where s.ID=s2.ID and s2.STATUSS='COMP' and s2.rn>=s.rn ) d(rnGrp))
,grouped1 as ( 
Select ID, min(CreatedDate) CreatedDate,  max(CompletedDate) CompletedDate
,rnGrp,
Case when SUM(CASE WHEN STATUSS = 'COMP' THEN 1 ELSE 0 END) >0 then 
Case when TYPES='De-Activattion' then 'NOT A RE-ACT' else
CAST( DATEDIFF(day,min(CreatedDate) ,max(CompletedDate)  )  AS VARCHAR(25))
END
ELSE 'NOT COMPLETED' END AS ACT_COMPLETION_TIME 
,Sum(CASE WHEN STATUSS = 'N-CO' THEN 1 ELSE 0 END) as [ACT NCO #]
From grouped 
Group by ID, rnGrp,TYPES
)
,grouped2 as (
select  ID, CreatedDate,    CompletedDate,   ACT_COMPLETION_TIME,   [ACT NCO #]
  ,Count(*) Over(Partition by ID) cnt
  ,row_number()Over(Partition by ID Order by CreatedDate) rn 
 from grouped1
 )
 Select g2.ID,
 Stuff(Convert(varchar(11),g2.CreatedDate,100),4,4,'-') as MIN_CREATED_MONTH_YEAR
 ,g2.ACT_COMPLETION_TIME,   g2.[ACT NCO #]
 from grouped2 g2 
 left join grouped2 g3 on g2.ID=g3.ID and g2.rn=g3.rn+1

希望

CREATE TABLE #temp2
(
 ID varchar(20) NOT NULL
,MIN_CREATED_MONTH_YEAR  varchar(20)
,ACT_COMPLETION_TIME varchar(20)
,ACT_NCO varchar(20)
);
INSERT INTO #temp2
VALUES ('61030203647','Apr-2016','5','2');
INSERT INTO #temp2
VALUES ('61030203647','Mar-2017','94','0');
INSERT INTO #temp2
VALUES ('64074558792','Jul-2016','11','3');
SELECT *
FROM #temp2

我正在尝试在2周内为分组id添加2周的容差。所附的查询做了它需要做的，我知道如何在最后添加2周的公差（我没有包括在这里，不复杂的事情）
我的解释如下，基本上我真正需要的是用hiveql编写的外部apply和top 1，因为这些函数不存在。
说明：2016年和2017年分别创建了61030203647个重新行动请求（过去2周，因此可以将其视为提出的两个独立问题（应为5天（最小创建日期）到27天（最大完成日期））
然而，64074558792，在11.08完成，在31.07创建，然后在12.08创建了另一个重演。这可能是一个错误，这是在2周内的公差，如果在完成日期的2周内将它们视为同一个问题（与上面的示例不同，该示例过去2周，被分隔为2个请求）