Oracle查询，以查找某个帐户是否已连续几天为负数

Question

我有一张表，里面每天都有账户余额。我需要知道如何找到超过一定天数的所有负面账户。

样本数据-

Accountid Date         Balance 
1000      01/01/2020   -1.00
1000      01/02/2020   -1.00
1000      01/03/2020   -1.00
1001      01/01/2020   -20.00
1001      01/02/2020   -20.00
1003      01/01/2020    15.00
1003      01/02/2020    16.00

我需要查询所有在为负超过2天的账户

Answer 1

您可以查询负余额的天数，按帐户ID分组，然后计算having子句中的行数：

SELECT   AccountID
FROM     mytable
WHERE    balance < 0
GROUP BY AccountID
HAVING   COUNT(*) >= 2

Answer 2

如果您只想考虑连续几天，那么：

SELECT AccountId
FROM   (
SELECT Accountid, DateTime, Balance,
SUM( has_changed_sign )
OVER ( PARTITION BY AccountId ORDER BY DateTime )
AS grp
FROM   (
SELECT Accountid, DateTime, Balance,
CASE
WHEN SIGN( balance )
= LAG( SIGN( Balance ) )
OVER ( PARTITION BY AccountId ORDER BY DateTime )
THEN 0
ELSE 1
END AS has_changed_sign
FROM   table_name t
)
WHERE Balance < 0
)
GROUP BY AccountID, grp
HAVING COUNT(*) > 2

因此，对于测试数据：

CREATE TABLE table_name ( Accountid, DateTime, Balance ) AS 
SELECT 1000, DATE '2020-01-01',  -1.00 FROM DUAL UNION ALL -- 3 consecutive -ve days
SELECT 1000, DATE '2020-01-02',  -1.00 FROM DUAL UNION ALL
SELECT 1000, DATE '2020-01-03',  -1.00 FROM DUAL UNION ALL
SELECT 1000, DATE '2020-01-04',  +1.00 FROM DUAL UNION ALL
SELECT 1001, DATE '2020-01-01', -20.00 FROM DUAL UNION ALL -- Only 2 negative
SELECT 1001, DATE '2020-01-02', -20.00 FROM DUAL UNION ALL
SELECT 1001, DATE '2020-01-03', +20.00 FROM DUAL UNION ALL
SELECT 1001, DATE '2020-01-04', +20.00 FROM DUAL UNION ALL
SELECT 1002, DATE '2020-01-01',  -1.00 FROM DUAL UNION ALL -- 3 negative days but
SELECT 1002, DATE '2020-01-02',  -1.00 FROM DUAL UNION ALL -- only 2 consecutive
SELECT 1002, DATE '2020-01-03',  +1.00 FROM DUAL UNION ALL
SELECT 1002, DATE '2020-01-04',  -1.00 FROM DUAL UNION ALL
SELECT 1003, DATE '2020-01-01', +15.00 FROM DUAL UNION ALL -- All positive
SELECT 1003, DATE '2020-01-02', +16.00 FROM DUAL UNION ALL
SELECT 1003, DATE '2020-01-03', +17.00 FROM DUAL UNION ALL
SELECT 1003, DATE '2020-01-04', +18.00 FROM DUAL;

该输出：

|ACCOUNTID||--------||1000 |

如果您只想要2天以上，那么您可以简单地使用LAG:

SELECT DISTINCT
AccountID
FROM (
SELECT AccountID,
balance,
LAG( balance, 1 ) OVER ( PARTITION BY AccountID ORDER BY DateTime )
AS balance_1_day_ago,
LAG( balance, 2 ) OVER ( PARTITION BY AccountID ORDER BY DateTime )
AS balance_2_days_ago
FROM   table_name
)
WHERE  balance < 0
AND    balance_1_day_ago < 0
AND    balance_2_days_ago < 0;

但是，如果你想在更大的时间段内进行检查，这不会很好地扩展，因为查询很快就会变得非常大。

db<>小提琴这里

Answer 3

试试这个。

从表中选择accountid，count(日期(其中平衡<0按帐户ID分组计数(日期(>2

Answer 4

在WHERE子句中使用筛选器仅获取负余额，然后按帐户ID分组，并在HAVING子句中检查不同天数是否大于您的天数限制。

SELECT accountid
FROM elbat
WHERE balance < 0
GROUP BY accountid
HAVING count(DISTINCT date) > 2;

Answer 5

如果你想要所有的列，那么使用partition-by。下面的解决方案也会计算日期的顺序

Select Accountid, Date,  Balance, 
row_number() over (Partition by 
Accountid order by Date) 
rn from table
Where balance<0 and rn>2 ;