Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Нарастающий итог  [new]
minva
Member

Откуда: г. Калуга
Сообщений: 1209
Есть таблица с такими данными, см. картинку.
Нужно в незаполненные столбцы посчитать значения нарастающим итогом
В причем столбец AmountAccruingPercent расчет вести с сортировкой по полю AmountPercent, а в CostAccruingPercent с сортировкой по CostPercent. Есть еще две пары подобных полей, в картинку не попали, по ним нужно сделать тоже самое.
Для 2012 сервака в FAQ есть такое решение, так понимаю, чтоб можно было одним запросом проапдейтить таблицу
select t.id, t.sum1, tot_sum=sum(t.sum1) over (order by t.id)
from #t t

А для 2008 как красиво сделать?
Пока пихаю нужные поля во временную таблицу, сортирую-считаю, а потом делаю из неё апдейт. И так для каждой пары

К сообщению приложен файл. Размер - 11Kb
2 фев 16, 11:03    [18760319]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
Glory
Member

Откуда:
Сообщений: 104760
minva
А для 2008 как красиво сделать?

Прочитать FAQ ?
2 фев 16, 11:04    [18760327]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
minva
Member

Откуда: г. Калуга
Сообщений: 1209
Glory, прочитал раза три, прежде чем писать. В исходном сообщении код из FAQ же приведен
2 фев 16, 11:17    [18760399]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
Glory
Member

Откуда:
Сообщений: 104760
minva
В исходном сообщении код из FAQ же приведен

И что вам помешало превратить этот select в update ?
2 фев 16, 11:20    [18760421]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
minva
Member

Откуда: г. Калуга
Сообщений: 1209
С четвертого раза нашел то, что нужно. Не знал, что можно так писать.
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 
2 фев 16, 11:23    [18760449]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
iap
Member

Откуда: Москва
Сообщений: 47045
minva
С четвертого раза нашел то, что нужно. Не знал, что можно так писать.
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 
Последовательность строк в UPDATE задать невозможно.
Да и TOP(100) PERCENT ... ORDER BY игнорируется, начиная с SQL2005.
Хотя это можно обойти с помощью фокуса: TOP(SELECT 100) PERCENT.
2 фев 16, 11:31    [18760525]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
iap
Member

Откуда: Москва
Сообщений: 47045
iap
Последовательность строк
Или точнее сказать - порядок строк?
2 фев 16, 11:32    [18760534]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
minva
Member

Откуда: г. Калуга
Сообщений: 1209
iap, да вроде и TOP(100) PERCENT ... ORDER BY работает.
А про эту конструкцию в БОЛ не нашел :( TOP(select 100) PERCENT ... ORDER BY
2 фев 16, 11:40    [18760616]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
Glory
Member

Откуда:
Сообщений: 104760
minva
А про эту конструкцию в БОЛ не нашел :( TOP(select 100) PERCENT ... ORDER BY

Да что вы говорите
Может у вас гранатыхелп того... не той системы
[
TOP (expression) [PERCENT]
[ WITH TIES ]
]


expression
Is the numeric expression that specifies the number of rows to be returned.

An expression can be any of the following:

Constant
Function
Column name
Variable
Subquery
CASE, NULLIF, or COALESCE

An expression can also be built from combinations of these entities joined by operators.
2 фев 16, 11:43    [18760647]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
iap
Member

Откуда: Москва
Сообщений: 47045
minva,

что значит "работает"?
В вашем-то случае смысла в подобной конструкции вообще нет.
О чём я и пытался намекнуть.
2 фев 16, 11:43    [18760648]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
iap
Member

Откуда: Москва
Сообщений: 47045
iap
minva,

что значит "работает"?
Я утверждаю, что, начиная с SQL2005, "работает" без ошибок, но порядок строк получается какой придётся.
2 фев 16, 11:47    [18760688]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
minva
Member

Откуда: г. Калуга
Сообщений: 1209
Glory,
>numeric expression
Да, согласен, ступил. тогда какая принципиальная разница между Top (100) и Top(select 100)???

>iap
Если каждую пару полей апдейтить отдельно, то получается в нужном порядке
create table #t(id int, sum1 decimal(10,2), tot_sum decimal(10,2))

insert #t(id, sum1) values(2, 1.2)	--3 строка
insert #t(id, sum1) values(4, 1.5)	--2
insert #t(id, sum1) values(3, 0.7)	--4
insert #t(id, sum1) values(1, 2.5)	--1

 select TOP(select 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 sum1 desc


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 sum1 desc
) as a on t.ID = a.ID 

select * FROM #t order by sum1 desc

 drop table #t
2 фев 16, 11:59    [18760788]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
Glory
Member

Откуда:
Сообщений: 104760
UPDATE b
set tot_sum = (select sum(a.sum1) from #t a where a.id < b.id)
FROM #t b


Сообщение было отредактировано: 2 фев 16, 12:06
2 фев 16, 12:03    [18760816]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
minva
Member

Откуда: г. Калуга
Сообщений: 1209
Glory, ключевое слово в моем вопросе - сортировка. Без сортировки я и сам сделал.
В принципе вопрос решен приведенным выше примером, если только там каких-то подводных камней нет
2 фев 16, 12:13    [18760906]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
Glory
Member

Откуда:
Сообщений: 104760
minva
Glory, ключевое слово в моем вопросе - сортировка. Без сортировки я и сам сделал.

В вашем потоке сознания нет ключевых слов.
Сортировка невозможна без критериев сортировки.
2 фев 16, 12:15    [18760933]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
minva
Member

Откуда: г. Калуга
Сообщений: 1209
Glory, приведенный пример из FAQ - поток сознания??? Если уж то, что я написал непонятно, то FAQ тоже непонятен?
2 фев 16, 12:25    [18761055]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
Glory
Member

Откуда:
Сообщений: 104760
minva
приведенный пример из FAQ - поток сознания??

В примере из FAQ указан критерий нарастающего итого, если вы не заметили
И критерий этот - поле id
2 фев 16, 12:31    [18761126]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
sergeimv
Member

Откуда: Россия, г.Казань
Сообщений: 42
1) Если есть кластерный индекс а таблице, то UPDATE будет идти в порядке соответствующий этому индексу.
2) Если кластерный индекс отсутствует, то можно создать индекс и использовать его для указания порядка обновления
update #t
set
 col = value
with(index(ix_#t)) -- Используем индекс, что бы update шел в нужной последовательности
2 фев 16, 17:52    [18763598]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
sergeimv
Если есть кластерный индекс а таблице, то UPDATE будет идти в порядке соответствующий этому индексу.
Неужели?

Порядок изменения строк в UPDATE зависит от плана выполнения, а не от наличия/отсутствия индексов.
2 фев 16, 19:53    [18764144]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог  [new]
sergeimv
Member

Откуда: Россия, г.Казань
Сообщений: 42
Да, конечно же от плана выполнения.
3 фев 16, 09:50    [18765479]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить