<>概要

<>设计和实现

<>题目介绍

<>实现思路

* 因为交易类型都是信用卡交易，所以交易金额都是正值
* 统计每个日期和之前的日期的交易总额，每个日期对应的累计交易额是 100,600,900,1100
* 统计整个acc_1账户的全部交易额，已查看总交易额是否达到1000，每个日期对应的总交易额是1100

<>代码实现

WITH add_tran_actual_amount AS ( SELECT account_no, tran_date, tran_type,
tran_amount, CASE WHEN tran_type = 'credit' THEN tran_amount ELSE tran_amount *
-1 END AS tran_actual_amount FROM transactions ),

add_sum AS ( SELECT *, sum(tran_actual_amount) OVER (PARTITION BY account_no
ORDER BY tran_date) AS sum_before_all , sum(tran_actual_amount) OVER (PARTITION
BY account_no ORDER BY tran_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING) AS sum_all FROM add_tran_actual_amount )

CURRENT ROW，该值可省略。

PRECEDING AND UNBOUNDED FOLLOWING

SELECT account_no, min(tran_date) AS first_reach_1000_date FROM add_sum WHERE
sum_all>= 1000 AND sum_before_all >= 1000 GROUP BY account_no

<>附录

if OBJECT_ID('transactions', 'U') is not null drop table transactions; create
table transactions ( id int primary key identity(1,1), account_no char(5) not
null, tran_date date not null, tran_type nvarchar(10) not null, tran_amount int
not null ) insert into transactions (account_no, tran_date, tran_type,
tran_amount) values ('acc_1', '2022-01-20', 'credit', 100), ('acc_1',
'2022-01-21', 'credit', 500), ('acc_1', '2022-01-22', 'credit', 300), ('acc_1',
'2022-01-23', 'credit', 200), ('acc_2', '2022-01-20', 'credit', 500), ('acc_2',
'2022-01-21', 'credit', 1100), ('acc_2', '2022-01-22', 'debit', 1000), ('acc_3',
'2022-01-20', 'credit', 1000), ('acc_4', '2022-01-20', 'credit', 1500), ('acc_4'
, '2022-01-21', 'debit', 500), ('acc_5', '2022-01-20', 'credit', 900)

GitHub

Gitee