Teradata/SQL :如何使用 teradata SQL 比较连续行中的日期，并在结果上编写案例语句

Question

我需要将第一行中的日期(结束 dt(与 secord 行中的日期(strt dt(进行比较，如果匹配，我需要选择第二行的 strt dt。 如果它匹配，我需要拿起第一行的 strt dt。一个订阅和频道包可以有多行。 我发现很难用 teradata 编写查询以获得所需的输出。

以下是案例和预期结果。

案例1

Subscription    Channel package Start dt    End dt
11111112    Sports  7/3/2015    11/28/2015
11111112    Sports  4/1/2016    11/23/2016
11111112    Sports  11/23/2016  12/17/2017

需要输出

Subscription    Channel package Start dt    End dt
11111112    Sports  4/1/2016    12/17/2017

案例2

Subscription    Channel package Start dt    End dt
11111112    Sports  7/3/2015    11/28/2015
11111112    Sports  11/28/2015  4/1/2016
11111112    Sports  4/1/2016    11/23/2016
11111112    Sports  11/23/2016  12/17/2017

需要输出

Subscription    Channel package Start dt    End dt
11111112    Sports  7/3/2015    12/17/2017

我试过用

MIN(Start dt) OVER (PARTITION BY Subscription   , Channel package   
ORDER BY Start dt ROWS BETWEEN 1 FOLLOWING  AND 1 FOLLOWING
) AS NXT Start dt

但是无法更进一步，因为我在使用资格时遇到了一些问题。

Answer 1

我知道这可能是一个较晚的解决方案，也可能不是最快的解决方案。 我会这样开始：

(
SELECT
TBL.SUBSCRIPTION
,   TBL.CHANNEL
,   TBL.SUBSCRIPTION || TBL.CHANNEL AS SUB_CHANNEL
,   CASE WHEN TBL.END_DT = TBL.START_DT THEN 1 ELSE 0 END AS SAME_DAY_FLAG
,   Dense_Rank() Over (ORDER BY SUB_CHANNEL ) AS GRP_RANK
,   TBL.START_DT
,   TBL.END_DT
,   PERIOD(TBL.START_DT, CASE WHEN TBL.END_DT > DATE THEN DATE ELSE TBL.END_DT + 1 END) AS COVERD_DTES

FROM    <YOUR_TABLE> AS TBL
) WITH DATA
ON COMMIT PRESERVE ROWS;

根据需要创建索引。创建SUB_CHANNEL的原因是因为由于某种愚蠢的原因，我无法将手指放在上面，我无法为下面的某些功能拉动多列。

/* Create a table that will contain the Merged rows with proper start and end dates combining adjacent periods*/
CREATE VOLATILE TABLE VT_TBL_MERGED_ROWS AS
(
WITH  CTE (GRP_RANK, COVERD_DTES) AS
(
SELECT 
GRP_RANK, COVERD_DTES
FROM VT_PRE_CTE 

)
SELECT * FROM TABLE
(    
TD_SYSFNLIB.Td_Normalize_Overlap_Meet(NEW VARIANT_TYPE( CTE.GRP_RANK),  CTE.COVERD_DTES)
RETURNS (GRP_RANK INT, COVERD_DTES PERIOD(DATE))
HASH BY  GRP_RANK
LOCAL ORDER BY GRP_RANK, COVERD_DTES
)  AS TBL_ROWS

)   WITH DATA
PRIMARY INDEX (GRP_RANK);

该函数TD_SYSFNLIB.Td_Normalize_Overlap_Meet规范化重叠的结束日期和开始日期，并分别为您提供一行最低和最后一个开始日期和结束日期。由于我无法理解传递多列的方法，因此我创建了可以绑定的GRP_RANK。

CREATE VOLATILE TABLE VT_PRE_OUTPUT AS 
(SELECT 
A.SUBSCRIPTION
,   A.CHANNEL
,   A.GRP_RANK
,   B.COVERD_DTES
,   Min(A.EFF_DT) AS EFF_DT
,   Max(A.EXP_DT) AS EXP_DT
,   End(B.COVERD_DTES)- Begin(B.COVERD_DTES) AS TOTAL_DAYS
FROM    VT_PRE_CTE AS A
JOIN    VT_TBL_MERGED_ROWS AS B
ON      A.GRP_RANK = B.GRP_RANK 
AND     A.COVERD_DTES OVERLAPS B.COVERD_DTES
GROUP BY 1,2,3) 
WITH DATA 
PRIMARY INDEX(MTN,  EXP_DT)
ON COMMIT PRESERVE ROWS;

如果SUBSCRIPTION/CHANNEL组合之间有中断，此A.COVERD_DTES OVERLAPS B.COVERD_DTES实际上会为您提供多行。 您可以决定如何处理多个片段，使用QUALIFY Row_Number() OVER(PARTITION BY SUBSCRIPTION, CHANNEL ORDER BY MIN_DT ASC) = 1获取第一个匹配项或ORDER BY MIN_DT DESC)获取最新匹配项。