<>概要

本文介绍一道和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)

技术
下载桌面版
GitHub
百度网盘(提取码:draw)
Gitee
云服务器优惠
阿里云优惠券
腾讯云优惠券
华为云优惠券
站点信息
问题反馈
邮箱:[email protected]
QQ群:766591547
关注微信