©2020-2024 ioDraw All rights reserved,
Privacy Policy
<>概要
本文介绍一道和SQL Server窗口函数相关的面试题，主要涉及窗口函数的原理和Framing参数的设置。
<>设计和实现
<>题目介绍
输入数据如下：
主要包括账户编号（account_no），交易日期（tran_date），交易类型（tran_tyrp），交易金额（tran_amount）
要求统计账户交易总额大于等于1000的账户，已经统计每个账户总交易金额第一次达到1000的日期。
按照交易类型，如果是信用卡交易（credit），则加入总数；如果是储蓄卡交易（debit），则需要从总数中减去。
最后的输出结果是：
<>实现思路
我们以acc_1 账户进行分析，按照日期排序如下
* 因为交易类型都是信用卡交易，所以交易金额都是正值
* 统计每个日期和之前的日期的交易总额，每个日期对应的累计交易额是 100,600,900,1100
* 统计整个acc_1账户的全部交易额，已查看总交易额是否达到1000，每个日期对应的总交易额是1100
基于上述思路，我们需要逐条记录进行交易额的累计，以找到第一次交易总额超过1000的交易日期，还需要统计每个账户的总交易额，以检查其是否达到1000。
按照上述思路，使用窗口函数是最佳解决途径。
<>代码实现
查询过程中涉及大量的子查询，我们使用CTE代替嵌套子查询。
第一步， 我们根据交易类型，增加一列tran_actual_amount，用于标识实际的交易金额，如果是credit，则交易金额为负，以方便后面进行统计。
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 )
累计交易额sum_before_all，采用默认的Framing参数 即 ROWS BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW，该值可省略。
总交易金额表示统计每个账号的所有记录sum_all，所以Framing参数不能使用默认值，需要手工指定，即ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING
第三步，我们需要过滤掉交易总额不到1000的账户，并且找到每个账户第一次到1000的日期。
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
通过聚合函数min，找到最早到达1000的的日期。
<>附录
建表和数据填充代码如下：
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)