我有一個具有這種資料結構的交易表，顯示了要記住的最重要的欄位。這是一個交易串列，其中 type=1 交易是賬戶存款，而 type=2 是賬戶提款。
login | type | value | date.
1234 | 1 | 100 | 25/09/2021
1234. | 2. | 250. | 26/09/2021
4321. | 2. | 234. | 13/09/2021
4321. | 1. | 342. | 14/08/2021
...
...
我想要得到的是賬戶串列，在此期間，他們的余額大于 > 某個金額，X，并且在 2021 年 9 月 16 日之后沒有活躍存款。
SELECT t.date,
a.login, t.account, sum(d.value) as deposits, sum(w.value) as withdrawals, sum(d.value) - sum(w.value) as balance
FROM b.transactions AS t
INNER JOIN b.accounts as a ON t.account=a.id
INNER JOIN b.transactions AS d ON t.account = d.account and d.date=t.date and d.type=t.type
INNER JOIN b.transactions AS w ON t.account = w.account and w.date=t.date and w.type=t.type
WHERE
d.value - w.value > 5000 and
d.type = '1' and
w.type = '2' and
d.date<='2021-09-29' and d.date>='2021-09-29' and
w.date<='2021-09-29' and w.date>='2021-09-29' and
t.date<='2021-09-29' and t.date>='2021-09-29' and
t.account not in
(
SELECT t.account
FROM b.transactions AS t
where t.type in (1) and
t.date>='2021-09-16' and
t.value>0
group by t.account
)
我得到了一些輸出，但看起來被嚴重低估了（至少 10 倍）......找不到錯誤，我應該在哪里看？提前致謝..
uj5u.com熱心網友回復：
你可能想多了——我認為沒有必要將同一張表加入三次：
SELECT t.date,
a.login,
t.account,
sum(CASE WHEN t.type = '1'
THEN t.value
WHEN t.type = '2'
THEN -t.value
END) AS balance
FROM b.transactions AS t
INNER JOIN b.accounts AS a
ON t.account = a.id
WHERE t.date BETWEEN '2021-09-29' AND '2021-09-29'
GROUP BY t.account
HAVING sum(CASE WHEN t.type = '1'
THEN t.value
WHEN t.type = '2'
THEN -t.value
END) > 5000;
uj5u.com熱心網友回復：
首先，要計算運行余額，您可以使用視窗函式。例如：
select
login, date, type, value,
sum(
case when type = 1 then value when type = 2 then -value end
) over(partition by login order by date) as running_balance
from t
order by login, date;
結果：
login date type value running_balance
------ ------------------------- ----- ------ ---------------
1234 2021-09-25T00:00:00.000Z 1 100 100
1234 2021-09-26T00:00:00.000Z 2 250 -150
4321 2021-09-13T00:00:00.000Z 2 234 -234
4321 2021-09-14T00:00:00.000Z 1 342 108
請參閱DB Fiddle - Simple Running Balance 中的運行示例。
然后您可以使用上面的邏輯來計算更復雜的值，以便您可以使用它們來過濾資料，如下所示：
select distinct login
from (
select
login, date, type, value,
sum(
case when type = 1 then value when type = 2 then -value end
) over(partition by login order by date) as running_balance,
sum(
case when type = 1 and date > date '2021-09-16' then 1 else 0 end
) over(partition by login order by date) as extra_deposits
from t
) x
where running_balance >= 100 and extra_deposits = 0
結果：
login
-----
4321
請參閱DB Fiddle-Full Query 中的運行示例。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/324638.html
標籤：sql PostgreSQL 加入 嵌套