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

Откуда:
Сообщений: 2083
Здравствуйте! Нужно посчитать что-то вроде нарастающего итога, но с вычитанием. Еще в придачу есть входящий остаток...
Проблему вроде бы решил, но боюсь, что в будущем, insert во временную таблицу не будет отсортирован по дате
declare @saldo decimal(19,2) = 7500
declare @t table (Data datetime, cl varchar(50), sum1 decimal(19,2), ost decimal(19,2))

insert @t values ('20120428','Клиент 1',1000, null), ('20120919','Клиент 1',2000,null), ('20120920','Клиент 1',1500,null), ('20120118','Клиент 2',2500,null)

update @t set @saldo = ost = @saldo - sum1
select * from @t

Как можно сделать, чтобы четко шла сортировка по полю Data ?
24 дек 12, 15:47    [13677883]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
_djХомяГ
Guest
create table #yourTabble (

                        .......
                        Date     datetime
                        UNIQUE CLUSTERED  (Date)
                        ) 


UPDATE t SET    ......
FROM #yourTabble  t WITH (INDEX(0))
24 дек 12, 16:06    [13678064]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
user89
Member

Откуда:
Сообщений: 2083
_djХомяГ,

хорошая идея! Спасибо!
24 дек 12, 16:11    [13678114]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
user89
Member

Откуда:
Сообщений: 2083
А на Select есть варианты?
Так, для общего развития...
24 дек 12, 16:19    [13678196]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
_djХомяГ
Guest
В select'e ORDER BY ))))
24 дек 12, 16:20    [13678208]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
user89
Member

Откуда:
Сообщений: 2083
_djХомяГ,

имелось в виду, чтобы конструкцию
update @t set @saldo = ost = @saldo - sum1
заменить на Select. Возможно прокатит какой-нибудь хитрый Left Join, не знаю...
24 дек 12, 16:42    [13678371]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
_djХомяГ
Guest
FAQ
24 дек 12, 16:45    [13678394]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
_djХомяГ
create table #yourTabble (

                        .......
                        Date     datetime
                        UNIQUE CLUSTERED  (Date)
                        ) 


UPDATE t SET    ......
FROM #yourTabble  t WITH (INDEX(0))
Это такая гарантия порядка записей в UPDATE, да?
Хочется на подтверждающую этот чудесный факт ссылку посмотреть
24 дек 12, 16:46    [13678413]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
_djХомяГ
Guest
нет конешна гарантий 100 пудовых нет а ля недокументированный способ
24 дек 12, 16:49    [13678436]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
user89
Member

Откуда:
Сообщений: 2083
Получился select, сортировка строго по дате
;with a as (
  select row_number() over(order by data) [rn],
  data, cl, sum1 from @t
)
select t1.rn, t1.data, t1.cl, t1.sum1, @saldo - sum(t2.sum1) [ost]
from a t1
left join a t2 on t1.rn >= t2.rn
group by t1.rn, t1.data, t1.cl, t1.sum1
24 дек 12, 17:38    [13678748]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
_djХомяГ
Guest
понятно
over(order by data)
24 дек 12, 17:43    [13678780]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
Гость333
Member

Откуда:
Сообщений: 3683
user89
Получился select, сортировка строго по дате

Сколько записей у вас предполагается в таблице @t? Стоимость запроса возрастает квадратично от количества записей. Для "N" тысяч записей работать будет медленно...
24 дек 12, 18:05    [13678926]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
Добрый Э - Эх
Guest
user89,

нарастающий итог считается через sum(...) over(order by ...) (с версии 2012).
24 дек 12, 18:10    [13678951]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
user89
Member

Откуда:
Сообщений: 2083
Гость333
Сколько записей у вас предполагается в таблице @t? Стоимость запроса возрастает квадратично от количества записей. Для "N" тысяч записей работать будет медленно...
Таблица маленькая, навряд ли превысит 200 записей (максимум 300). На больших данных возможно курсор быстрее, не проверял...

Добрый Э - Эх
нарастающий итог считается через sum(...) over(order by ...) (с версии 2012).
Спасибо, возьму в копилку знаний. Сейчас у нас 2008 :(
24 дек 12, 22:20    [13679809]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
но может и не ты не суть
Guest
iap
_djХомяГ
create table #yourTabble (

                        .......
                        Date     datetime
                        UNIQUE CLUSTERED  (Date)
                        ) 


UPDATE t SET    ......
FROM #yourTabble  t WITH (INDEX(0))
Это такая гарантия порядка записей в UPDATE, да?
Хочется на подтверждающую этот чудесный факт ссылку посмотреть

год или два назад бурная темочка была, ссылку давал ты сам на материальчик про то что апдейт будет идти всегда в порядке индекса, чтобы не поймать одну и ту же строчку два раза.
25 дек 12, 07:57    [13680578]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
Гость333
Member

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

А можно ссылку на материальчик? Насколько я понимаю, проблема "поймать одну и ту же строчку два раза" известна как Halloween Problem. Для её решения используется не порядок прохода индекса, а спулинг промежуточных данных (Table Spool) для изоляции проводимых изменений.

Если ещё и порядок индекса важен, то хотелось бы почитать-просветиться.
25 дек 12, 14:42    [13683164]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Гость333
но может и не ты не суть
год или два назад бурная темочка была, ссылку давал ты сам на материальчик про то что апдейт будет идти всегда в порядке индекса, чтобы не поймать одну и ту же строчку два раза.

А можно ссылку на материальчик? Насколько я понимаю, проблема "поймать одну и ту же строчку два раза" известна как Halloween Problem. Для её решения используется не порядок прохода индекса, а спулинг промежуточных данных (Table Spool) для изоляции проводимых изменений.

Если ещё и порядок индекса важен, то хотелось бы почитать-просветиться.
По-моему, тут какая-то ошибка.
У меня в голове прямо-таки отштамповано: порядка у UPDATEа нет и быть не может!
Не зря же постоянно Microsoftу много лет на мозг капают просьбой реализовать, наконец, такой UPDATE!
Я обычно даю ссылки как раз про неупорядоченность UPDATE/SELECT, независимо от индекса:

Вопрос на злобу дня - Просмотр кластеризованного индекса - Часть I
Вопрос на злобу дня - Просмотр кластеризованного индекса - Часть II
Вопрос на злобу дня - Просмотр кластеризованного индекса - Часть III
25 дек 12, 14:59    [13683352]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
"Поймать одну строчку два раза" - это уже другая тема, про "фантомы".
Относится к уровню изоляции READ UNCOMMITTED (хинт NOLOCK)
Про это тут тоже статья есть.
25 дек 12, 15:04    [13683387]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
Гость333
Member

Откуда:
Сообщений: 3683
iap
"Поймать одну строчку два раза" - это уже другая тема, про "фантомы".
Относится к уровню изоляции READ UNCOMMITTED (хинт NOLOCK)
Про это тут тоже статья есть.

Ну, вообще, фантомы возникают при любом уровне изоляции, кроме SNAPSHOT и SERIALIZABLE. И фантомное чтение — это не "одну строчку два раза", а нечто иное :-)

Про "поймать одну строчку два раза" приведу нехитрый пример. Пусть в таблице есть числовое поле X и индекс по этому полю. Мы хотим увеличить X на два, если X находится в диапазоне от 1 до 10. Идём по таблице в порядке возрастания индекса. Взяли первую запись, X=1, увеличили на 2. Взяли вторую запись, X=2, увеличили. Взяли третью запись, X=3... опа, а это наша изменённая первая запись... мы её не узнали и снова увеличили на 2 — это и есть Halloween Problem.

Поэтому в любую РСУБД встроен механизм Halloween Protection. Вот пример, наглядно это демонстрирующий. Сперва создадим таблицу с индексом:
create table tab_with_index(id int identity primary key, a int);
create index i on tab_with_index (a);
go

Затем сравним планы выполнения таких запросов:
-- скан кластерного индекса, значение кластерного индекса не модифицируется
update t set a = a + 1 from tab_with_index t with(index(1));

-- модифицируется значение индекса, по которому идет скан
-- нужен Halloween Protection, для этого сервер использует промежуточное хранилище (Table Spool)
update t set a = a + 1 from tab_with_index t with(index(i));


К сообщению приложен файл. Размер - 78Kb
25 дек 12, 15:50    [13683903]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Гость333,

Для реализации Halloween Protection не обязательно используется спул, иногда используется Sort.

Гость333
И фантомное чтение — это не "одну строчку два раза", а нечто иное :-)

На мой взгляд, фантомное чтение, это то, во избежании чего используется Range lock. Т.е. если в одной транзакции прочитан диапазон, потом вторая транзакция либо вставляет новое значение, либо апдейтит старое значение в результате чего оно перемещается внутрь диапазона, и выполняется повторное чтение диапазона в первой транзакции - возникает чтение этой новой/обновленной строки. Это и есть фантомное чтение. К Halloween protection это не имеет отношения.
25 дек 12, 16:43    [13684385]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
Гость333
Member

Откуда:
Сообщений: 3683
SomewhereSomehow
Для реализации Halloween Protection не обязательно используется спул, иногда используется Sort.

Можно примерчик, в образовательно-познавательных целях? Если несложно :-)

SomewhereSomehow
На мой взгляд, фантомное чтение, это то, во избежании чего используется Range lock. ... К Halloween protection это не имеет отношения.

Да, и я про то же :-) Фантомы возникают при многопользовательской работе, а хэллоуин-проблема — она "внутризапросная", т.е. однопользовательская. И небольшое дополнение — для борьбы с фантомами может использоваться не только Range lock (т.е. serializable-блокировка), но и уровень изоляции транзакции Snapshot.

Просто я пытался расшифровать, что именно значит "не поймать одну и ту же строчку два раза".
25 дек 12, 16:55    [13684475]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Гость333,

Craig Freedman
One simple solution to the Halloween problem is to physically separate the read and write cursors of an update plan using a blocking operator such as an eager spool or sort. Inserting a blocking operator between the two halves of an update plan ensures that the read cursor runs in its entirety and generates all rows that it will generate before the write cursor begins executing or modifying any rows.

Halloween Protection
Пример, честно говоря сейчас лень придумывать, я думаю вы и сами придумаете после прочтения =)

Гость333
Просто я пытался расшифровать, что именно значит "не поймать одну и ту же строчку два раза".

Я понял. Написал просто к тому, что iap тоже прав, на мой взгляд под термин "фантомное чтение" вполне попадает прочтение одной строчки два раза, если эта строчка была перемещена.
25 дек 12, 17:09    [13684562]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
Гость333
Member

Откуда:
Сообщений: 3683
SomewhereSomehow
url=http://blogs.msdn.com/b/craigfr/archive/2008/02/27/halloween-protection.aspx]Halloween Protection[/url]
Пример, честно говоря сейчас лень придумывать, я думаю вы и сами придумаете после прочтения =)

Ок, спасибо. Пример там указан. Для таблицы, которую я описал чуть выше, такой запрос выполняется с сортировкой, без спула:
update t set id = id + 1 from tab_with_index t with(index(1));
25 дек 12, 17:29    [13684731]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Кстати по теме нарастающего итога. Поделюсь замечательной статьей, в которой делается обзор многих существующих способов, в том числе и с точки зрения производительности.
Рекомендую: Best approaches for running totals – updated for SQL Server 2012
25 дек 12, 17:41    [13684824]     Ответить | Цитировать Сообщить модератору
 Re: Не совсем нарастающий итог  [new]
Гость333
Member

Откуда:
Сообщений: 3683
SomewhereSomehow,

Спасибо, прекрасная иллюстрация того, что курсоры могут быть лучше set-based подхода.
25 дек 12, 17:58    [13684934]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить