sql - SQL over 子句 - 将分区划分为编号的子分区
问题描述
我有一个挑战,我在多个场合都遇到过,但从来没有找到有效的解决方案。想象一下,我有一个大表,其中包含有关银行账户及其可能的从借方到贷方的循环移动的数据:
AccountId DebitCredit AsOfDate
--------- ----------- ----------
aaa d 2018-11-01
aaa d 2018-11-02
aaa c 2018-11-03
aaa c 2018-11-04
aaa c 2018-11-05
bbb d 2018-11-02
ccc c 2018-11-01
ccc d 2018-11-02
ccc d 2018-11-03
ccc c 2018-11-04
ccc d 2018-11-05
ccc c 2018-11-06
在上面的示例中,我想将子分区号分配给 AccountId 和 DebitCredit 的组合,其中分区号在每次 DebitCredit 移动时递增。换句话说,在上面的例子中,我想要这个结果:
AccountId DebitCredit AsOfDate PartNo
--------- ----------- ---------- ------
aaa d 2018-11-01 1
aaa d 2018-11-02 1
aaa c 2018-11-03 2
aaa c 2018-11-04 2
aaa c 2018-11-05 2
bbb d 2018-11-02 1
ccc c 2018-11-01 1
ccc d 2018-11-02 2
ccc d 2018-11-03 2
ccc c 2018-11-04 3
ccc d 2018-11-05 4
ccc c 2018-11-06 5
我无法真正弄清楚如何快速有效地做到这一点。该操作必须每天在具有数百万行的表上完成。
在这个例子中,我们保证所有账户都有连续的行。但是,当然,客户可能会在当月 15 日开立账户和/或在 26 日关闭他的账户。
挑战将在 MSSQL 2016 服务器上解决,但可以在 2012(甚至可能是 2008r2)上运行的解决方案会很好。
正如您可以想象的那样,无法判断是否只有借记或贷记行,或者帐户是否每天都在循环。
解决方案
你可以用递归 cte 做到这一点
; with
-- the purpose of `cte` is to generate running number in the order of AsOfDate
cte as
(
select AccountId, DebitCredit, AsOfDate, rn = row_number() over (partition by AccountId order by AsOfDate)
from tbl
),
-- this is the recursive CTE
rcte as
(
-- anchor member. Starts with `PartNo 1`
select AccountId, DebitCredit, AsOfDate, rn, PartNo = 1
from cte
where rn = 1
union all
-- recursive member. Incrememt `PartNo` if there is a change in debitcredit
select c.AccountId, c.DebitCredit, c.AsOfDate, c.rn,
PartNo = case when r.DebitCredit = c.DebitCredit then r.PartNo else r.PartNo + 1 end
from rcte r
inner join cte c on r.AccountId = c.AccountId
and r.rn = c.rn - 1
)
select *
from rcte
order by AccountId, AsOfDate
推荐阅读
- php - 让 PHP7 和 PHP 8 共存
- javascript - 如何遍历对象 - JavaScript
- azure - Azure 中的真实 MTLS(使用客户端私钥验证)
- vue.js - 从 rootState vuex 获取对象而不是数字
- javascript - 使用 jquery 而不是第一个单词在引导模式中显示完整字符串
- r - 在具有唯一行的多列中计算是/否
- java - 如何从另一个类调用一个类的方法?
- swiftui - 无法使用结合 SwiftUI 从 URL 获取响应
- perl - 如何在 List::Util 中使用与 sort 或 reduce 相同的签名编写子代码
- python - 更改值中的特定位