Teradata SQL:第一次搜索列中的值

Question

所以，我有一个表，每天跟踪客户帐户并记录每个工作日的值，例如该帐户的余额。

例如：

Acct_Nbr   Acct_Open_Date   System_Date   Balance
-------------------------------------------------
111        2017-12-01       2017-12-01    0.00
111        2017-12-01       2017-12-04    0.00
111        2017-12-01       2017-12-05    0.00
111        2017-12-01       2017-12-06    500.00
222        2017-12-01       2017-12-01    0.00
222        2017-12-01       2017-12-04    0.00
222        2017-12-01       2017-12-05    0.00
222        2017-12-01       2017-12-06    0.00
222        2017-12-01       2017-12-07    0.00
222        2017-12-01       2017-12-08    250.00

我正在尝试编写一个查询，该查询将返回acct_open_date和system_date之间的数量，余额首先> 0(即帐户获得资金的日期(。

我计划使用 case when语句(即＆lt; = 5天，6-10天等(将存储桶的天数分组，并且每个存储桶返回帐户＃。

基于上表，它将返回：

Time_to_fund   Count_of_accts
-----------------------------
<=5 days       1
6-10 days      1
>10 days       0

我不确定如何编写一个查询，该查询将通过每行搜索，直到找到余额> 0的值，然后将System_date用于该行并从中减去ACCT_OPEN_DATE给我日子。

任何帮助将不胜感激。谢谢！

Answer 1

select
   CASE WHEN diff <=  5 THEN'<=5 days'
        when diff <= 10 then '6-10 days'
        ELSE '>10 days'
   END as Time_to_fund,
   COUNT(*)
from
 (
    select
       System_Date - Acct_Open_Date as diff -- number of days between
    from mytable
    where Balance > 0 
    qualify -- find the first row with a Balance > 5
       row_number()
       over (partition by Acct_Nbr
             order by System_Date) = 1
 ) as dt

Answer 2

尝试在组中使用一个组：

SELECT CASE WHEN fund_times.Time_to_fund_in_days<= 5 THEN'<=5 days'
when fund_times.Time_to_fund_in_days<= 10 then '6-10 days'
ELSE '>10 days' END as Time_to_fund, COUNT(fund_times.acct_Number) AS Count_of_accts
    (SELECT t.acct_Number, MIN( (t.System_Date - t.Acct_Open_Date) * 86400) as Time_to_fund_in_days
    from table as T
    WHERE t.Balance>0
    GROUP BY t.acct_Number) AS fund_times
GROUP BY CASE WHEN fund_times.Time_to_fund_in_days<= 5 THEN'<=5 days'
when fund_times.Time_to_fund_in_days<= 10 then '6-10 days'
ELSE '>10 days' END