保留值直到 Teradata 中的值发生变化

问题描述

teradata 中有一个交易历史表，只有在有交易时才会改变余额数据如下:

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

Cust_id Balance Txn_dt
123     1000    27MAY2018
123     350     31MAY2018

例如，对于 5 月 27 日的客户(123)，我们有 1000 的余额，并且在 5 月 31 日有客户进行的交易，因此余额变为 350.没有保留 5 月 28 日至 5 月 30 日的记录余额与 5 月 27 日相同.我希望这些天的数据也存在(保留相同的余额并增加日期)在剩余的日子里必须保留相同的记录，直到交易完成的余额发生变化.如何在teradata中做到这一点?预期输出:

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

谢谢桑迪

德诺思.它似乎有效，但你能告诉我如何扩展到某一天，例如:直到 30JUN2018 吗?

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

推荐答案

有几种方法可以得到这个结果，Teradata 中最简单的就是利用 Time Series Expansion for Periods:

There are several ways to get this result, the simplest in Teradata utilizes Time Series Expansion for Periods:

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+，您可以用简化的 LEAD 替换 MIN OVER:

If you run TD16.10+ you can replace the MIN OVER with a simplified LEAD:

Lead(Txn_dt)
Over (PARTITION BY Cust_id
      ORDER BY Txn_dt)

这篇关于保留值直到 Teradata 中的值发生变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！