Предложение 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), было бы неплохо.

Как вы можете себе представить, нет никакого способа узнать, будут ли только дебетовые или кредитные строки или будет ли счет вращаться каждый день.

Ответ 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 проверяет, PartNoAdd ли предыдущая дебетовая карта для этой учетной записи. Если это так, он возвращает 0 (мы ничего не должны добавлять), иначе он возвращает 1.

Затем внешний запрос суммирует все PartNoAdd для этой учетной записи.

Ответ 2

Вы можете сделать это с помощью рекурсивного 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

Ответ 3

Вы можете использовать плотный ранг

select *,dense_rank() over(partition by AccountId order by DebitCredit desc) as PartNo
from t