有几种方法可以得到此结果,Teradata中最简单的方法是使用时间序列的时间段扩展:
WITH cte AS
(
SELECT Cust_id, Balance, Txn_dt,
-- return the next row's date
Coalesce(Min(Txn_dt)
Over (PARTITION BY Cust_id
ORDER BY Txn_dt
ROWS BETWEEN 1 Following AND 1 Following)
,Txn_dt+1) AS next_Txn_dt
FROM tab
)
SELECT Cust_id, Balance
,Last(pd) -- last day of the period
FROM cte
-- make a period of the current and next row's date
-- and return one row per day
EXPAND ON PERIOD(Txn_dt, next_Txn_dt) AS pd
如果您运行TD16.10 +,则可以将替换MIN OVER
为简化的LEAD
:
Lead(Txn_dt)
Over (PARTITION BY Cust_id
ORDER BY Txn_dt)