Microsoft SQL Server
Transact-SQL

Нарастающий итог

Опубликовано: 02 окт 02
Рейтинг:

Автор: Glory
Прислал:

create table #t(id int, sum1 decimal(10,2), tot_sum decimal(10,2))

insert #t(id, sum1) values(1, 1.2)
insert #t(id, sum1) values(2, 1.5)
insert #t(id, sum1) values(3, 1.7)
insert #t(id, sum1) values(4, -0.5)

select a.id, max(a.sum1) as sum1, sum(isnull(b.sum1, 0))+max(a.sum1) as tot_sum
from #t a 
left outer join #t b on b.id < a.id
group by a.id

declare @tot_sum decimal(10,2)
set @tot_sum = 0

update #t set @tot_sum = tot_sum = @tot_sum + ISNULL(sum1, 0)
select * from #t

drop table #t 

Комментарии


  • dino_raptor,
    Вариант 3 работает с версии 2012

  • Для множественных группировок
    вариант 1
    SELECT order_id,product_id,customer_id,purchase_date,amount,
    (
    SELECT SUM(T1.amount)
    FROM order_detail T1
    WHERE T1.purchase_date <= T2.purchase_date and T1.customer_id=T2.customer_id
    and T1.product_id=T2.product_id
    ) total_vol
    FROM order_detail T2
    Вариант 2
    select t1.order_id,t1.customer_id,t1.product_id,t1.purchase_date,t1.amount,sum(t2.amount)
    from order_detail t1,order_detail t2
    where t1.purchase_date>=t2.purchase_date and t1.[product_id]=t2.product_id and t1.customer_id=t2.customer_id
    group by t1.order_id,t1.purchase_date,t1.product_id,t1.customer_id,t1.amount
    Вариант 3 (для 2008r2 и старше)
    SELECT
    t.order_id,
    t.product_id,
    t.customer_id,
    t.purchase_date,
    t.amount,
    sum(t.amount) over(partition by customer_id,t.product_id order by purchase_date ) total
    FROM test.dbo.order_detail AS t

  • Для множественных группировок
    вариант 1
    SELECT order_id,product_id,customer_id,purchase_date,amount,
    (
    SELECT SUM(T1.amount)
    FROM order_detail T1
    WHERE T1.purchase_date <= T2.purchase_date and T1.customer_id=T2.customer_id
    and T1.product_id=T2.product_id
    ) total_vol
    FROM order_detail T2
    Вариант 2
    select t1.order_id,t1.customer_id,t1.product_id,t1.purchase_date,t1.amount,sum(t2.amount)
    from order_detail t1,order_detail t2
    where t1.purchase_date>=t2.purchase_date and t1.[product_id]=t2.product_id and t1.customer_id=t2.customer_id
    group by t1.order_id,t1.purchase_date,t1.product_id,t1.customer_id,t1.amount
    Вариант 3 (для 2008r2 и старше)
    SELECT
    t.order_id,
    t.product_id,
    t.customer_id,
    t.purchase_date,
    t.amount,
    sum(t.amount) over(partition by customer_id,t.product_id order by purchase_date ) total
    FROM test.dbo.order_detail AS t

  • Спасибо!

  • SQL Server 2012.

    select t.id, t.sum1, tot_sum=sum(t.sum1) over (order by t.id)
    from #t t

  • Не пинайте за ламерство...
    Как сделать так, чтобы итог считался за год от заданной даты? SQL Server

  • если даже таблица сортированная по возрастанию,
    сумма идет не по порядку. проверено : в таблице около 500 тысяч записей.

  • хм
    а если просто изменить порядок инсертов

    insert #t(id, sum1) values(2, 1.5)
    insert #t(id, sum1) values(3, 1.7)
    insert #t(id, sum1) values(4, -0.5)
    insert #t(id, sum1) values(1, 1.2)

    то второй способ вернет совсем не то. Это логично, так как порядок "нарастания" нигде не задан. Что делает этот вариант практически бесполезным.

  • крута

  • --нарастающий итог
    create table #t (id int primary key, qty int)
    insert into #t
    select 1,10
    union all select 2,15
    union all select 3,5
    union all select 4,12
    union all select 5,-2
    union all select 6,-28

    --традиционный метод
    select t.id, sum(t1.qty) as p_total
    from #t t
    join #t t1 on (t1.id <= t.id)
    group by t.id
    order by t.id

    -- использование предложения over
    select distinct t.id, sum(t1.qty) over (partition by t.id) as p_total
    from #t t
    join #t t1 on (t1.id <= t.id)
    order by t.id

    -- использование common table expressions
    ;with cte (id, qty) as
    (
    select id, qty
    from #t
    where id = (select min(id) from #t)
    union all
    select t.id, t.qty + c.qty as qty
    from #t t
    join cte c on (t.id > c.id)
    where not exists (select * from #t where id < t.id and id > c.id)
    )
    select * from cte
    order by id

    drop table #t

  • Eсли данные в базе перемешаны то нужно тогда делать так (а иначе только курсором):
    [SRC]create table #t(id int, sum1 decimal(10,2), tot_sum decimal(10,2))

    insert #t(id, sum1) values(2, 1.2)
    insert #t(id, sum1) values(4, 1.5)
    insert #t(id, sum1) values(3, 1.7)
    insert #t(id, sum1) values(1, -0.5)

    select * FROM #t order by id

    UPDATE t
    set tot_sum = a.tot_sum
    FROM #t t
    inner join (
    select a.id, max(a.sum1) as sum1, sum(isnull(b.sum1, 0))+max(a.sum1) as tot_sum
    from #t a
    left outer join #t b on b.id < a.id
    group by a.id
    ) as a on t.ID = a.ID

    select * FROM #t order by id

    drop table #t[/SRC]

    А может быть даже и так:
    [SRC]
    UPDATE t
    set tot_sum = a.tot_sum
    FROM #t t
    inner join (
    select top 100 percent a.id, max(a.sum1) as sum1, sum(isnull(b.sum1, 0))+max(a.sum1) as tot_sum
    from #t a
    left outer join #t b on b.id < a.id
    group by a.id
    order by a.id
    ) as a on t.ID = a.ID
    [/SRC]

  • ИМХО так проще

    create table #t(id int, sum1 decimal(10,2), tot_sum decimal(10,2))

    insert #t(id, sum1) values(1, 1.2)
    insert #t(id, sum1) values(2, 1.5)
    insert #t(id, sum1) values(3, 1.7)
    insert #t(id, sum1) values(4, -0.5)

    select * FROM #t

    DECLARE @sum decimal(10, 2)
    SET @sum = 0

    update #t set @sum = @sum + ISNULL(sum1,0), tot_sum = @sum from #t

    select * FROM #t

    drop table #t

  • на милиооных табличках вариант с sum() тяжеловат,
    курсор быстрее, а вот
    update #tRest set @КвоОстаток = КвоОстаток = КвоОстаток + КвоОборот

    надо проверить

  • Взял на себя смелость дополнить мастера :-)
    select
    t2.id,
    t2.sum1,
    (
    select sum(t1.sum1)
    from #t t1
    where t1.id<=t2.id
    ) as tot
    from #t t2

  • Супер++

  • Нарастающий
    update #tRest set @КвоОстаток = КвоОстаток = Case when КвоОстаток = 0 then @КвоОстаток else 0 end + КвоОстаток + КвоОборот

  • Нарастающий
    update #tRest set @КвоОстаток = КвоОстаток = Case when КвоОстаток = 0 then @КвоОстаток else 0 end + КвоОстаток + КвоОборот

  • Весчь хорошая и полезная...
    Только вот вопрос, делал ил что нечто подобное но для пересчета себестоимости?

  • Отлично

  • Хах, супер!
    Вчера случайно прочитал, подумал, прикольно, но зачем...
    А сегодня понял, что мне это супер-подходит для вычисления отстатков по счетам на дату ))

    Спасибо!

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

  • было бы здорово еще и курсорный вариант описать.



Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: Microsoft SQL Server / Transact-SQL / Нарастающий итог