我有下面的查询,它在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个请求)
暂无答案!
目前还没有任何答案,快来回答吧!