作者:坦普拉尼洛 | 来源:互联网 | 2022-11-30 10:27
我遇到了一个挑战,我曾经多次遇到但从未能找到有效的解决方案.想象一下,我有一张大表,其中包含有关银行账户及其从借方到贷方的可能循环移动的数据:
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)运行的解决方案会很好.
您可以想象,无法确定是否只有借记卡或信用额度,或者帐户是否每天都会循环使用.
1> Squirrel..:
你可以用递归的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
2> George Menou..:
如果你有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
检查此帐户的先前DebitCard是否相同.如果是,则返回0(我们不应该添加任何内容),否则返回1.
然后外部查询PartNoAdd
将此帐户的所有内容相加.