仅当某个日期在过去超过30天时才返回帐户余额

Question

我有一个表accounts，它在列account_ID中有客户ID，在列account_balance中每个ID有多个帐户余额。每个ID的account_statement_date列也有多个语句日期。

如果账户余额大于50.00美元，并且最近的对账单日期大于过去30天，我需要返回ID和账户余额。

如果账户余额大于$50.00，则返回ID和账户余额：

select account_ID, sum(account_balance)
from accounts
where account_balance > 50
group by account_ID

如果帐户余额大于50.00美元，并且最近的对账单日期大于过去30天，我如何优化查询以仅返回ID和帐户余额？

Answer 1

只需在查询中添加一个having子句：

select account_ID, sum(account_balance)
from accounts
where account_balance > 50
group by account_ID
having max(account_statement_date) < sysdate - 30;

编辑：

尚卡的评论似乎是正确的。以下修复了它：

select account_ID,
        sum(case when account_balance > 50 then account_balance else 0 end)
from accounts
group by account_ID
having max(account_statement_date) < sysdate - 30;

Answer 2

我不确定你是否应该合计账户余额。

SELECT * FROM (
    SELECT account_id, account_balance, account_statement_date
         , MAX(account_statement_date) OVER ( PARTITION BY account_id ) AS max_statement_date
      FROM accounts
) WHERE account_balance > 50
    AND account_statement_date = max_statement_date
    AND max_statement_date < TRUNC(SYSDATE-30);

Answer 3

如果您的数据如下所示：

account_ID  account_statement_date  account_balance
1529        2014-12-01              $40.00
1529        2015-01-01              $60.00
1529        2015-02-01              $65.00 -- < 30 days
2647        2014-12-01              $20.00
2647        2015-01-01              $25.00 -- > 30 days but < $50
3198        2014-12-01              $10.00
3198        2015-01-01              $50.00 -- > 30 days and >= $50

在2015-02-01上运行时的正确答案是：

account_ID  account_statement_date  account_balance
3198        2015-01-01              $50.00

那么我想你想要的查询可能看起来像这样：

SELECT
   a.account_ID,
   a.account_statement_date,
   a.account_balance
FROM
   (
      SELECT
         row_number() OVER (PARTITION BY account_ID ORDER BY account_statement_date DESC) AS latest,
         account_ID,
         account_statement_date,
         account_balance
      FROM
         accounts
   ) AS a
WHERE
   a.account_statement_date < sysdate - 30
   AND a.latest = 1
   AND a.account_balance >= 50
;

但是，如果你的数据看起来像这样：

account_ID  account_balance_date account_statement_date  account_balance
1529        2014-12-10           2015-01-01              $40.00
1529        2015-12-16           2015-01-01              $60.00
1529        2015-01-10           2015-02-01              $30.00
1529        2015-01-19           2015-02-01              $65.00 -- < 30 days
2647        2014-12-25           2015-01-01              $20.00
2647        2014-12-30           2015-01-01              $25.00 -- > 30 days
2647        2014-01-02           NULL                    $75.00
2647        2014-01-15           NULL                    $20.00 -- but < $50
3198        2014-12-14           2015-01-01              $20.00
3198        2014-12-30           2015-01-01              $25.00 -- > 30 days
3198        2014-01-09           NULL                    $20.00
3198        2014-01-22           NULL                    $50.00 -- and >= $50!

在2015-02-01上运行时的正确答案是：

account_ID  last_account_statement_date  last_account_balance
3198        2015-01-01                   $50.00

那么我想你想要的查询可能看起来像这样：

SELECT
   a.account_ID,
   a.account_balance AS last_account_balance,
   (
      SELECT Max(account_statement_date)
      FROM accounts a3
      WHERE
         a1.account_ID = a3.account_ID
         AND account_statement_date IS NOT NULL
   ) AS last_statement_date
FROM
   (
      SELECT
         row_number() OVER (PARTITION BY account_ID ORDER BY account_balance_date DESC) AS latest,
         account_ID,
         account_balance_date,
         account_statement_date,
         account_balance
      FROM
         accounts
   ) AS a
 WHERE
    a.latest = 1 -- the most recent balance by account_balance_date
    AND a.account_balance >= 50
    AND ( -- this clause is optional and may aid or harm performance
       a.account_statement_date IS NULL
       OR a.account_statement_date < sysdate - 30
    )
    AND NOT EXISTS ( -- no statement in the last 30 days
       SELECT *
       FROM
          accounts AS a2
       WHERE
          a.account_ID = a2.accountID
          AND a2.account_statement_date >= sysdate - 30
    )
;

我对30天的部分持怀疑态度——可能是正确的计算会涉及到从上一个报表日期算起"一个月"的一些日期数学，并有已知的规则来决定不同天数的月份意味着什么。

Answer 4

我认为这应该对你有用。。

select * from
(select account_ID, sum(account_balance) bal, max(account_statement_date) st_date
from accounts
group by account_ID) tab1
where tab1.bal > 50 and tab1.st_date > (sysdate - 30)