SQL编程了

这看起来是个经典问题,用其他语言很容易编,但是我是在SQL里面碰到这个问题,也想挑战一下SQL编程。

有一个table,叫Account吧,它有两个columns:
AccountID int primary key,
AccountSize int

比如
AccountID, AccountSize
1, 120
2, 157
3, 196
4, 529

给定一个batch_size比如50000吧,把AccountID按顺序赋予BatchOrder,要求每填满一个batch之后(停在第一个多于batch_size的地方,另一个问题是停在最后一个少于batch_size的地方,这个可能简单一点),就赋予一个新的连续的BatchOrder。

最后输出是这样:
AccountID, AccountSize, BatchOrder
1, 120, 1
2, 157, 1

87, 324, 2
88, 426, 2

webbew的解法:

with acct as (
    select 1 as AccountId, 34 as AccountSize
    union
    select 2 as AccountId, 6 as AccountSize
    union
    select 3 as AccountId, 7 as AccountSize
    union
    select 4 as AccountId, 60 as Accountsize
    union
    select 5 as AccountId, 4 as AccountSize
    union
    select 6 as AccountId, 23 as AccountSize
    union
    select 7 as AccountId, 33 as AccountSize
    union
    select 8 as AccountId, 10 as AccountSize
    union
    select 9 as AccountId, 14 as AccountSize
    union
    select 10 as AccountId, 30 as AccountSize
    union
    select 11 as AccountId, 8 as AccountSize
    union
    select 12 as AccountId, 17 as AccountSize
),
acct_join as (
    select a.AccountId as IdStart,b.AccountId as IdEnd
    from acct a, acct b
    where a.AccountId<=b.AccountId
),
acct_sum as (
select IdStart, IdEnd,(
        select Sum(AccountSize)
        from acct where Account Id>=IdStart and AccountId<=IdEnd
    ) as SumSize
from acct_join
),
acct_sum_no_larger as (
    select *
    from acct_sum a
    where not exists (
        select *
        from acct_sumb
        where a.IdStart=b.IdStart and b.SumSize<a.SumSize and b.SumSize>=50
    )
),
acct_sum_no_smaller as (
    select *
    from acct_sum_no_larger a
    where not exists (
        select *
        from acct_sum_no_larger b
        where a.IdStart=b.Idstart and b.SumSize>a.SumSize
    )
),
acct_batch as (
    select IdStart, IdEnd, 1 as BatchNum
    from acct_sum_no_smaller
    where IdStart=1
    union all
    select b.IdStart, b.IdEnd, BatchNum+1 as BatchNum
    from acct batch a join acct_sum_no_smaller b on a.IdEnd+1=b.IdStart
)
select * from acct_batch

pda的解法:

declare @batch_size int = 10;

with act as
(
select AccountId, AccountSize, convert(int, row_number() over (order by AccountId, AccountSize)) as rn, 0 as run_total
from account
)
,
cte as
(
select 0 as AccountId, 0 as AccountSize, 0 as rn, 0 as run_total, 0 as batch
union all
select act.AccountId, act.AccountSize, act.rn,
case when cte.run_total + act.AccountSize > @batch_size then act.AccountSize else cte.run_total + act.AccountSize end as run_total,
case when cte.run_total + act.AccountSize > @batch_size then batch + 1 else batch end as batch
from act join cte on cte.rn = act.rn - 1
)
select * from cte
where
accountid > 0

LOL

The World Of dBASE