SQL over 子句 - 将分区划分为编号的子分区

Question

我有一个挑战，我在多个场合遇到过，但从未找到有效的解决方案。想象一下，我有一个大表，其中包含有关例如的数据银行账户及其可能的从借记到贷记的循环移动:

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)上运行的解决方案会很好。

正如您可以想象的那样，无法判断是否只有借方或贷方行，或者帐户是否会每天循环。

Answer 1

如果你有 sql server 2012+，你可以使用 lag() 和一个窗口求和来得到这个:

select *,sum(PartNoAdd) over (partition by AccountId order by AsOfDate asc) as PartNo_calc
from
(
    select *,
    case when DebitCredit=lag(DebitCredit,1) over (partition by AccountId order by AsOfDate asc) then 0 else 1 end as PartNoAdd
    from t 
)t2
order by AccountId asc, AsOfDate  asc

在内部查询中， PartNoAdd检查此帐户的先前借记卡是否相同。如果是，则返回 0(我们不应该添加任何内容)，否则返回 1。

然后外部查询对所有 PartNoAdd 求和对于此帐户。