仅当余额为负且持续3个月或更长时间时才进行帐户

Question

Select * from Balance;

它有AccountID、ClosingDate和Balance。

每个月都会有一个条目。例如：

AccountID   ClosingDate   Balance
123         1/19/2017     -3.00
123         12/19/2016    -2.50
123         11/19/2016    -2.50

另一个账户

AccountID   ClosingDate   Balance
456         1/16/2017     -3.00
456         12/16/2016    2.00
456         11/16/2016    -2.50

最后一个例子：

AccountID   ClosingDate   Balance
789         1/11/2017     -9.00
789         12/11/2016    -2.00
789         11/11/2016    -5.50

我想写一个查询，生成连续3个报表周期或更长时间为负数的帐户列表。请记住，它们的截止日期各不相同。查询不应获取"456"帐号。大约有100000个账户是负面的。但我需要找到的是，是否所有人都连续3个月呈阴性。

我需要帐号和最新余额作为输出。然而，如果查询最新余额会很困难，那么只需账号就足够了。

感谢

Answer 1

如果您的dbms支持窗口函数，请使用lead和lag获取下一个和上一个余额，使用first_value获取每个accountid的最新余额。然后获取当前行余额、下一行余额和上一行余额为<0.

with cte as (select t.*, 
lead(balance) over(partition by accountid order by closingdate) as next_balance,
lag(balance) over(partition by accountid order by closingdate) as prev_balance,
first_value(balance) over(partition by accountid order by closingdate desc) as latest_balance
from t)
select distinct accountid, latest_balance
from cte
where balance < 0 and next_balance < 0 and prev_balance < 0  

编辑：解释它的工作原理。让我把它和你的样本数据一起展示一下。

123         1/19/2017     -3.00 --balance = -3.00, previous_balance = -2.50, next_balance = null
123         12/19/2016    -2.50 --balance = -2.50, previous_balance = -2.50, next_balance = -3.00
123         11/19/2016    -2.50 --balance = -2.50, previous_balance = null, next_balance = -2.50

• where balance < 0 and next_balance < 0 and prev_balance < 0-2016年12月19日的行满足该账户ID连续3个月负余额的标准，并在结果中返回
• first_value(balance) over(partition by accountid order by closingdate desc)-返回每个帐户截至最近日期的余额

您可以使用此解释来了解它如何适用于其他帐户ID。

Edit_2：要获得连续7个月或以上余额为负的账户，请使用

WITH CTE AS
(SELECT T.*,
FIRST_VALUE(BALANCE) OVER(PARTITION BY ACCOUNTID ORDER BY CLOSINGDATE DESC) AS LATEST_BALANCE ,
ROW_NUMBER() OVER(PARTITION BY ACCOUNTID ORDER BY CLOSINGDATE) - 
ROW_NUMBER() OVER(PARTITION BY ACCOUNTID, SIGN(BALANCE) ORDER BY CLOSINGDATE) AS GRP
FROM T
)
,COUNTS AS
(SELECT C.*,
SUM(CASE WHEN BALANCE < 0 THEN 1 ELSE 0 END) OVER(PARTITION BY ACCOUNTID,GRP ORDER BY CLOSINGDATE) AS CONSECUTIVE_COUNT
FROM CTE C
)
SELECT DISTINCT ACCOUNTID, LATEST_BALANCE
FROM COUNTS
WHERE CONSECUTIVE_COUNT >= 7

或者，如果准备编写一个冗长的查询，请使用lead和lag函数中的可选参数来向前或向后查找n行。在这里，你可以回顾和展望每行3行。

with cte as (select t.*, 
lead(balance,1) over(partition by accountid order by closingdate) as next_balance_1,
lead(balance,2) over(partition by accountid order by closingdate) as next_balance_2,
lead(balance,3) over(partition by accountid order by closingdate) as next_balance_3,
lag(balance,1) over(partition by accountid order by closingdate) as prev_balance_1,
lag(balance,2) over(partition by accountid order by closingdate) as prev_balance_2,
lag(balance,3) over(partition by accountid order by closingdate) as prev_balance_3,
first_value(balance) over(partition by accountid order by closingdate desc) as latest_balance
from t)
select distinct accountid, latest_balance
from cte
where balance < 0 
and next_balance_1 < 0 and next_balance_2 < 0 and next_balance_3 < 0
and prev_balance_1 < 0 and prev_balance_2 < 0 and prev_balance_3 < 0