时间戳

首页 > 解决方案 > Retain values till there is a change in value in Teradata

sql - Retain values till there is a change in value in Teradata

问题描述

There is a transaction history table in teradata where balance gets changed only when there is a transaction Data as below:

Cust_id Balance Txn_dt
123     1000    27MAY2018
123     350     31MAY2018

For eg,For a customer(123) on May 27 we have a balance of 1000 and on May 31 there is a transaction made by the customer so balance becomes 350. There is no record maintained for May 28 to May 30 with same balance as on May 27 . I want these days data also to be there (With same balance retained and the date is incremented ) Its like same record has to be retained for rest of the days till there is a change in a balance done by the transaction . How to do this in teradata? Expected output:

Cust_id Balance Txn_dt
123     1000    27MAY2018
123     1000    28MAY2018
123     1000    29MAY2018
123     1000    30MAY2018
123     350     31MAY2018

Thanks Sandy

Hi Dnoeth. It seems to work, but can you let me know how to expand till a certain day for eg : till 30JUN2018 ?

标签： sqlteradata

解决方案

有几种方法可以得到这个结果，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)

推荐阅读