Microsoft Russia ISV Team Microsoft Russia ISV Team. Нарастающий итог sql
SQL - нарастающий итог
Вниз
SQL - нарастающий итог
Silver_ (2003-04-25 16:40) [0]fld1 fld2 Calc_fld3 1 5 5 2 2 7 13 10 17 4 11 28 35 1 29
SELECT * SUM(fld1 до текущей записи) AS Calc_fld3 FROM Table1
Silver_ (2003-04-25 17:30) [1]ладно пойдем по другому пути КАК узнать номер текущей записи типа RecNo
забыл СУБД Access
Silver_ (2003-04-25 18:42) [2]Не ребята не поверю что энто никому не нужно
хоть идеи какие-нить есть??? тожа не лишним будет!!!
NickBat (2003-04-25 18:49) [3]Хочешь верь - хочешь не верь. Нарастающий итог, как правило, необходим при выводе отчета на печать, как итог по отдельным группам. Это делается генераторами отчетов. Номер записи не играет в реляционнных БД никакого значения, а если ты сортируешь их по какому-то признаку, то должен знать на какой записи тебе необходимо закрнчить подсчет, вот и ограничь условием where field<n
дело в том что результаты нужны для последубщей обработки
NickBat (2003-04-25 19:09) [5]обработки кем? чем?
sniknik (2003-04-25 23:34) [6]Silver_ © (25.04.03 17:30) > КАК узнать номер текущей записи типа RecNo не повериш но значение RecNo можно узнать через ADODataSet1.RecNo хотя в большинстве случаев этот номер смысла не имеет, и иногда (от настроек) равен -1 для всех записей.
(лутще пересмотри алгоритм, не закладываясь на RecNo)
Silver_ (2003-04-29 09:54) [7]> Silver_ © (25.04.03 17:30) > > КАК узнать номер текущей записи типа RecNo
Silver_ (2003-04-29 09:57) [8]> Silver_ © (25.04.03 17:30) > > КАК узнать номер текущей записи типа RecNo
имел ввиду через SQL
P.S. Извиняюсь за предыдущее послание (празники, просыпаться не хочется блин :) )
Соловьев (2003-04-29 09:57) [9]> > Silver_ © (25.04.03 17:30) > > > КАК узнать номер текущей записи типа RecNo тебе же написал NickBat © (25.04.03 18:49), что все зависит от сортировки. Нет такого понятия как номер записи. Это иллюзия.
Можно его сгенирировать если есть уникальный ключ.... И только при сортировке по нему! Так: Select id,f1,f2,f3,...,(select count(*) from table1 where id<t1.id) from Table1 t1;
sniknik (2003-04-29 10:46) [11]Соловьев © (29.04.03 09:57) > Нет такого понятия как номер записи. Это иллюзия. ну не нужно из крайности в крайность, только изза того что он бессмысленен (в большинстве случаев, уже говорил), и SQL почти всех баз его не поддерживают. Есть исключения.
и тогда работает (раз драйвер базы поддерживает номер) SELECT *, RecNo() AS RecordNum FROM Table убедится что это именно номер записи можно сделав сортировку SELECT *, RecNo() AS RecordNum FROM Table ORDER BY Field номера пойдут не по порядку. но это только если драйвер/sql сервер поддерживает. надо смотреть конкретно в документацию. эти 2 примера будут работать только с - Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DRIVER={Microsoft Visual FoxPro Driver};UID=;SourceDB=d:\;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=RUSSIAN;Null=Yes;Deleted=Yes;"
для Access такого нет, во всяком случае я не знаю.
GLUKAS © (29.04.03 09:58) ну это же будет не совсем то, правильно? если например нужно сделать order by по какому нибудь полю, номера для полей покажет другие.
может лутше сделать автоикремент? и все? лутше конечно менять логику.
Silver_ (2003-04-29 10:58) [12]Выкладываю РАБОТАЮЩИЙ код
SELECT MIN(A.Fld1)as f1, MIN(A.Fld2) AS f2, SUM(B.Fld2) AS SUM_f2 FROM Table1 A INNER JOIN Table1 B ON A.Fld1 >= B.Fld1 GROUP BY A.Fld1
P.S. Думаю вопрос закрыт, за исключением представления более лудшего варианта :)
sniknik (2003-04-29 11:25) [13]> за исключением представления более лудшего варианта а ошибки этого обсудить?
Fld1-это автоикремент? а если он с "разрывами" что будет?
GLUKAS © (29.04.03 09:58) дал лутший вариант (по моему).
Sergey13 (2003-04-29 11:34) [14]Что-то я ни в одном варианте запросов не увидел WHERE. Если надо постоянно по всей таблице иметь этот итог, то может проще поле добавить? Если же все таки надо по произвольной выборке, то я для таких штук использовал таблицы в памяти RxMemoryData. Загонял туда датасет (или его часть), добавлял поле и в цикле считал этот итог. ИМХО, это выгоднее получится, чем в запросе.
> Fld1-это автоикремент?
ДА.
SELECT MIN(A. ID )as f1, MIN(A.Fld2) AS f2, SUM(B.Fld2) AS SUM_f2 FROM Table1 A INNER JOIN Table1 B ON A. ID >= B. ID GROUP BY A. ID
так понятней будет
> а если он с "разрывами" что будет? все прекрасно работает и с "разрывами" тоже, Проверено лично мной на конкретном примере.
Silver_ (2003-04-29 11:48) [16]> 2 Sergey13 © (29.04.03 11:34) > Что-то я ни в одном варианте запросов не увидел WHERE ...и не нужен. Происходит следующее для каждой записи в подчинении находятся все предыдущие, их сумма (получаемая по GROUP BY A.ID) и получается искомое значение
> добавлял поле и в цикле считал этот итог не знаю как "таблицы в памяти RxMemoryData" но в цикле так тормозит что... а SQL хоть и не моментально (данных много) но терпимо.
Если это на Interbase - то можно при помощи ХП
sumqty = 0; for select id, qty from "table1" where .... order by id into :id, :qty do begin sumqty = sumqty + :qty; suspend; end
sniknik (2003-04-29 12:38) [18]Silver_ © (29.04.03 11:40) тоже пробовал в "реале" SELECT MIN(A.ID) as RecNo, MIN(A.ID) AS f2, SUM(B.ID) AS SUM_f2 FROM Data_OUT A INNER JOIN Data_OUT B ON A.ID >= B.ID GROUP BY A.ID
RecNo я правильно поставил?
значения ID 1,2,3,9 получившиеся значения RecNo 1,2,3,9 и ли я не там смотрю?
Sergey13 (2003-04-30 08:48) [19]2Silver_ © (29.04.03 11:48) > Происходит следующее для каждой записи в подчинении находятся все предыдущие, их сумма (получаемая по GROUP BY A.ID) и получается искомое значение Если на таблице отсутствует update/delete то ИМХО лучше добавить поле и писать итог туда
>не знаю как "таблицы в памяти RxMemoryData" но в цикле так тормозит что... а SQL хоть и не моментально (данных много) но терпимо. А сколько много? У тебя возможно тормозит выкачивание "данных много", а не обход циклом по ним.
> Если на таблице отсутствует update/delete то ИМХО лучше > добавить поле и писать итог туда Вся беда в том что есть требуется предвидеть ситуацию/возможность ввода/корректировки данных задним числом (забыли ввести некие данные или на момент ввода небыло известно онеких изменениях...). Если итог напрямую туда написать получим неверные вычисления...
> У тебя возможно тормозит выкачивание "данных много", а не > обход циклом по ним. с RxMemoryData вопще не работал и даже принципа его работы не знаю
сравни ADODataSet1.CommandText:="SELECT * FROM Table1" ADODataSet1.Open; Total:=0; //----- сравни скорость начиная здесь while not ADODataSet.Eof do Total:=Total + ADODataSet.FieldByName("Num_Value").AsInteger; //----- сравни скорость заканчивая здесь
ADODataSet1.CommandText:="SELECT * SUM(Num_Value) AS Total FROM Table1" //----- сравни скорость начиная здесь ADODataSet1.Open; //----- сравни скорость заканчивая здесь
2Silver_ © (30.04.03 10:46) >Вся беда в том что есть требуется предвидеть ситуацию/возможность ввода/корректировки данных задним числом (забыли ввести некие данные или на момент ввода небыло известно онеких изменениях...). Если такая ситуация только возможна, но не очевидна, то ИМХО, можно сделать процедуру пересчета по всей таблице и вызывать ее после ввода/исправления всех данных.
>с RxMemoryData вопще не работал и даже принципа его работы не знаю Обычная таблица, но не в базе а в памяти.
>сравни... А что у тебя за запрос? Если он вообще отработает (сомневаюсь) то ты просто получишь сумму по столбцу, без всякого нарастающего итога. Ты его наверное "творчески переработал" для публикации на форуме. "Ускорение" в твоем случае скорее всего происходит из за того, что при Open ты еще не получил всех записей. Добавь строчку ADODataSet1.Last (я с ADO не работал, но наверное там есть такой метод) и меряй после нее. При вычислении SUM в запросе происходит многократное (на каждую запись) выполнение этого агрегата для всех предыдущих записей. А при обходе циклом все делается за один проход. Кстати, может попробовать добавить вычисляемое поле, значение которого будет считаться как select sum(Num_value) from table where id<:id. Но в твоем случае (ИМХО) все таки лучше наверное нормальное поле.
22Silver_ © (30.04.03 10:46) В догонку. А зачем тебе вся таблица на клиенте? Может надо то только за последний месяц (неделю, день). Ограничь выборку. Посчитай одним запросом сумму "до начала выборки", а дальше как я предлагал. Вообще летать все будет.
Silver_ (2003-04-30 12:09) [23]> Обычная таблица, но не в базе а в памяти. это я понял (даже смею предположить Metods, Events те же/похожие)
> А что у тебя за запрос? Если он вообще отработает (сомневаюсь) > то ты просто получишь сумму по столбцу, без всякого нарастающего > итога. Ты его наверное "творчески переработал" для публикации > на форуме.
Само сабой > Silver_ © (25.04.03 19:02) > дело в том что результаты нужны для последубщей обработки ...тоже желательно через SQL
так как спрашивал про нарастающий итог выложил рабочий пример упрощенный для более легкого понимания
> При вычислении SUM в запросе происходит многократное (на > каждую запись) выполнение этого агрегата для всех предыдущих > записей. согласен но это происходот быстрее цикла даже по всей таблице (нельзя исключать и такой ситуации)
> Ограничь выборку. обязательно условие WHERE для этого имеется/использую
P.S. В конце концов частичные вычисления все равно придется делать прходом уж больно много вычислений и исключений и... но что возможно буду через SQL делать уже убедился Хорошая/быстрая вещч (раньше кстати недолюбливал/не пользовал)
Silver_ (2003-04-30 12:49) [24]> sniknik © (29.04.03 12:38) > Silver_ © (29.04.03 11:40) > тоже пробовал в "реале" > SELECT MIN(A.ID) as RecNo, MIN(A.ID) AS f2, SUM(B.ID) AS > SUM_f2 > FROM Data_OUT A INNER JOIN Data_OUT B ON A.ID >= B.ID > GROUP BY A.ID > > RecNo я правильно поставил? > > значения ID 1,2,3,9 > получившиеся значения RecNo 1,2,3,9 > и ли я не там смотрю?
не там интересует SUM_f2 (f2=10,20,30,90 => SUM_f2= 10,30,60,150)
> Dnico (29.04.03 12:20) Но у меня кстати Access тут мона ХП раньше пользовал SyBase там понятно в базе и делаеш а в Access??
Форум: "Базы";Поиск по всему сайту: www.delphimaster.net;Текущий архив: 2003.05.22;Скачать: [xml.tar.bz2];Наверх
Память: 0.79 MBВремя: 0.033 c
www.delphimaster.net
Нарастающий итог – сравнение производительности – Microsoft Russia ISV Team
В посте "Нарастающий итог в Денали" были рассмотрены основные способы вычисления нарастающего итога средствами Transact-SQL: 1) курсор; 2) джойн таблицы самой на себя; 3) подзапрос; 4) упорядоченная оконная сумма. 4-й способ является новой функциональностью вышедшего две недели назад CTP3 следующей версии SQL Server под кодовым названием Denali. Было бы интересно сравнить перечисленные способы с точки зрения производительности на объемах данных, более-менее заслуживающих внимания. В данном посте я выполню это упражнение на самой большой по количеству записей таблице любимой базы данных AdventureWorks2008R2.
Рис.1
Рис.2
Самой большой по количеству записей является таблица Sales.SalesOrderDetail. Существенными для целей теста полями в ней будут: SalesOrderID (для группирования), SalesOrderDetailID (сквозной ключ записей), UnitPrice (поле, по которому будет считаться нарастающий итог). В нижеприведенном тестировочном скрипте эти колонки переносятся в отдельную таблицу. Создаются 4 временные хранимые процедуры, каждая из которых реализует подсчет накопительного итога одним из вышеперечисленных способов. Процедура #Sposob1_Cursor соответствует Скрипту 5 предыдущего поста, #Sposob2_SelfJoin - Скрипту 7, #Sposob3_Subquery - Скрипту 8, #Sposob4_SumOverOrderedWindow - Скрипту 11. Единственное идейное отличие состоит в том, что там нарастающий итог считался вдоль всей таблицы Customer, а здесь я добавил в порядке эксперимента промежуточное группирование по полю SalesOrderID, т.е. нарастающий итог нарастает внутри каждого отдельного заказа. Как только наступает новый заказ, он сбрасывается и начинает нарастать с нуля по-новой. Временная процедура #header для чистоты эксперимента перед каждым испытанием приводит боевые и учебные патроны в исходное состояние.
use tempdb
if OBJECT_ID('dbo.SalesOrderDetails', 'U') is not null drop table dbo.SalesOrderDetails
select SalesOrderID, SalesOrderDetailID, UnitPrice, cast(null as money) as RunningTotal into SalesOrderDetails from [Adventure Works 2008R2].Sales.SalesOrderDetail
alter table SalesOrderDetails add primary key clustered (SalesOrderDetailID)
go
if OBJECT_ID('#header', 'P') is not null drop proc #header
go
create proc #header as
update SalesOrderDetails set RunningTotal = NULL
dbcc dropcleanbuffers
dbcc freeproccache
go
if OBJECT_ID('#Sposob1_Cursor', 'P') is not null drop proc #Sposob1_Cursor
go
create proc #Sposob1_Cursor as
exec #header
declare @cur cursor, @SalesOrderID int, @SalesOrderID_Prev int = 0, @Price money, @RunningTotal money
set @cur = cursor local keyset for select SalesOrderID, UnitPrice from SalesOrderDetails order by SalesOrderDetailID for update of RunningTotal
open @cur
set nocount on
while 1 = 1 begin
fetch next from @cur into @SalesOrderID, @Price
if @@FETCH_STATUS <> 0 break
if @SalesOrderID <> @SalesOrderID_Prev set @RunningTotal = 0
update SalesOrderDetails set RunningTotal = @RunningTotal where current of @cur
select @RunningTotal += @Price, @SalesOrderID_Prev = @SalesOrderID
end
set nocount off
close @cur
deallocate @cur
go
if OBJECT_ID('#Sposob2_SelfJoin', 'P') is not null drop proc #Sposob2_SelfJoin
go
create proc #Sposob2_SelfJoin as
exec #header;
with cte as (select t2.SalesOrderDetailID ID, sum(isnull(t1.UnitPrice, 0)) RunningTotal from SalesOrderDetails t1
right join SalesOrderDetails t2 on t1.SalesOrderID = t2.SalesOrderID and t1.SalesOrderDetailID < t2.SalesOrderDetailID
group by t2.SalesOrderID, t2.SalesOrderDetailID)
update SalesOrderDetails set RunningTotal = cte.RunningTotal from SalesOrderDetails t join cte on t.SalesOrderDetailID = cte.ID
go
if OBJECT_ID('#Sposob3_Subquery', 'P') is not null drop proc #Sposob3_Subquery
go
create proc #Sposob3_Subquery as
exec #header;
with cte as (select SalesOrderDetailID ID,
(select isnull(sum(UnitPrice), 0) from SalesOrderDetails t2
where t2.SalesOrderID = t1.SalesOrderID and t2.SalesOrderDetailID < t1.SalesOrderDetailID) as RunningTotal
from SalesOrderDetails t1)
update SalesOrderDetails set RunningTotal = cte.RunningTotal from SalesOrderDetails t join cte on t.SalesOrderDetailID = cte.ID
go
if OBJECT_ID('#Sposob4_SumOverOrderedWindow', 'P') is not null drop proc #Sposob4_SumOverOrderedWindow
go
create proc #Sposob4_SumOverOrderedWindow as
exec #header;
with cte as (select SalesOrderDetailID ID,
isnull(SUM(UnitPrice) over (partition by SalesOrderID order by SalesOrderDetailID rows between unbounded preceding and 1 preceding), 0) as RunningTotal
from SalesOrderDetails)
update SalesOrderDetails set RunningTotal = cte.RunningTotal from SalesOrderDetails t join cte on t.SalesOrderDetailID = cte.ID
go
Скрипт 1
Тесты производились на виртуальной машине Windows 7 Ultimate x64 SP1, которой были выделены 2 потока Intel Core i7 720QM (1.6GHz) и 2 гига оперативки. Установлен Microsoft SQL Server "Denali" (CTP3) - 11.0.1440.19 (X64) Enterprise Evaluation Edition. Каких-либо специальных настроек после установки не производилось.
exec #Sposob1_Cursor
go
exec #Sposob2_SelfJoin
go
exec #Sposob3_Subquery
go
exec #Sposob4_SumOverOrderedWindow
Скрипт 2
Estimated plan прогнозирует, что наиболее затратным будет способ с подзапросом, а наиболее легким - треугольный джойн таблицы самой на себя:
Рис.3
#Sposob1_Cursor (8%) + #Header (5%) = 12%
#Sposob2_SelfJoin (5%) + #Header (5%) = 10%
#Sposob3_Subquery (49%) + #Header (5%) = 54%
#Sposob4_SumOverOrderedWindow (19%) + #Header (5%) = 24%
Он ошибается. Вот фактические показания по количеству операций ввода/вывода и длительностям выполнения, снятые в профайлере. Длительность CPU означает непосредственно расход процессорного времени на выполнение процедуры, Duration - общее время с учетом ожиданий блокированных ресурсов, стояния в очередях к планировщику, пока тот подхватит и кинет на конвейер и т.д.
Рис.4
|
| Reads | Writes | CPU | Duration |
| #Sposob1_Cursor | 2308642 | 803 | 24250 | 25623 |
| #Sposob2_SelfJoin | 736181 | 447 | 4139 | 6303 |
| #Sposob3_Subquery | 981768 | 787 | 4438 | 4527 |
| #Sposob4_SumOverOrderedWindow | 492204 | 422 | 2844 | 2537 |
Мы видим, что самым дорогим вышел курсор. Таблица SalesOrderDetails содержит 121317записей, и их прямолинейный перебор влетает в копеечку. Наиболее оптимально проявил себя появившийся в СТР3 способ sum() over (p
blogs.technet.microsoft.com
Нарастающий итог – обратная задача – Microsoft Russia ISV Team
В постах "Нарастающий итог в Денали", "Нарастающий итог - сравнение производительности" мы посмотрели, какие удобные возможности появились в SQL Server 11 CTP3 для вычисления скользящих агрегатов. Здесь мы продолжим эту тему и разберем решение обратной задачи - как из колонки, где ведется учет нарастающим итогом получить дельту прироста между двумя соседними записями. Пусть имеем таблицу
use tempdb
if OBJECT_ID('#t', 'U') is not null drop table #t
create table #t (id int identity primary key, grouping_id int, dt date, x int)
insert #t (grouping_id, dt, x) values (1, '2011-01-14', 5), (1, '2011-03-06', 8), (1, '2011-05-20', 15),
(2, '2011-01-18', 2), (2, '2011-02-27', 6), (2, '2011-04-02', 9), (2, '2011-05-07', 10),
(3, '2011-02-09', 3), (3, '2011-04-19', 9),
(4, '2011-01-06', 2), (4, '2011-01-08', 5), (4, '2011-02-10', 7), (4, '2011-03-21', 12), (4, '2011-05-12', 21),
(5, '2011-03-17', 8)
select * from #t
Рис.1
Колонка x прирастает в пределах каждой группы grouping_id, так что в каждый в момент времени dt мы видим ее нарастающее значение. Чтобы узнать, сколько составила дельта прироста в момент времени dt, надо, очевидно, получить рядом значение за предыдущий момент dt и из значения за текущий момент вычесть значение за предыдущий. До Денали СТР3 это можно было сделать джойном таблицы самой на себя подобно Скрипту 6 в сумме нарастающим итогом:
with cte (id1, gr1, dt1, x1, id2, gr2, dt2, x2, n) as
(select *, ROW_NUMBER() over (partition by t1.id order by t2.dt desc) from #t t1 left join #t t2 on t1.grouping_id = t2.grouping_id and t1.dt > t2.dt)
select * from cte where n = 1 order by id1
Скрипт 1
Замечательная функция LAG(x, n) позволяет непосредственно получить значение из колонки х, отступив n записей назад:
select *, lag(x, 1) over (partition by grouping_id order by dt) from #t
Рис.2
NULL означает, что более ранних записей внутри этой группы нет. Для удобства дальнейших вычислений хотелось бы в этом случае получить не NULL, а 0. Третий аргумент функции LAG позволяет задать значение по умолчанию, если отступ назад принес NULL:
select *, lag(x, 1, 0) over (partition by grouping_id order by dt) from #t
Рис.3
Окончательно имеем:
select *, (x - lag(x, 1, 0) over (partition by grouping_id order by dt)) as delta from #t
Рис.4
Рассмотрим чуть более хитрую ситуацию, когда нарастающий итог учитывается с запозданием, т.е. дельта за текущую запись идет в зачет нарастающего итога не в ней, а в следующей записи. Например,
if OBJECT_ID('#t', 'U') is not null drop table #t
create table #t (id int identity primary key, grouping_id int, dt date, x int)
insert #t (grouping_id, dt, x) values (1, '2011-01-14', 0), (1, '2011-03-06', 5), (1, '2011-05-20', 8),
(2, '2011-01-18', 0), (2, '2011-02-27', 2), (2, '2011-04-02', 6), (2, '2011-05-07', 9),
(3, '2011-02-09', 0), (3, '2011-04-19', 3),
(4, '2011-01-06', 0), (4, '2011-01-08', 2), (4, '2011-02-10', 4), (4, '2011-03-21', 7), (4, '2011-05-12', 12),
(5, '2011-03-17', 0)
select * from #t
Рис.5
Понятно, что дельту за последнюю запись в группе мы в этом случае не узнаем, но как ее посчитать для остальных? Надо взять х не из предыдущей, а из следующей записи, т.е. lag(-1). Функция LAG не воспринимает отрицательное смещение:
select *, lag(x, -1, 0) over (partition by grouping_id order by dt) from #t
Msg 8730, Level 16, State 1, Line 1
Offset parameter for Lag and Lead functions cannot be a negative value.
Этим она невыгодно отличается от MDX. Чтобы продвинуться вперед, следует использовать функцию LEAD:
select *, lead(x, 1) over (partition by grouping_id order by dt) from #t
Рис.6
Соответственно, прирост в этом случае получается как
with cte as (select *, lead(x, 1) over (partition by grouping_id order by dt) next_x from #t)
select *, delta = next_x - x from cte
Рис.7
Заодно давайте посмотрим родственные функции first_value() и last_value(). Как нетрудно догадаться из названий, они возвращают первое и последнее значение в группе.
select *, first_value(x) over (partition by grouping_id order by dt), last_value(x) over (partition by grouping_id order by dt) from #t
Рис.8
Но что это? Функция last_value возвращает не последнее значение х в группе, а просто берет его из текущей записи. Неужели ее не доделали в СТР3, ужаснулся я и в отчаянии поинтересовался у старших товарищей. Спасибо, Боб Бушмен, дай Бог ему здоровья, разъяснил, что когда внутри over() стоит order by, то по умолчанию диапазон действия функции - не целиком группа, а от ее начала и по текущую запись. См. OVER Clause в BOL, ROW or RANGE clause. Например, подсчет суммы нарастающим итогом по таблице Рис.4:
use tempdb
if OBJECT_ID('#t', 'U') is not null drop table #t
create table #t (id int identity primary key,
blogs.technet.microsoft.com
Нарастающий итог в Денали – Microsoft Russia ISV Team
Начнем с практического примера. Вернее, продолжим. В предыдущем посте мы доставали из таблицы случайную запись. Чуть усложним задачу. Пусть записи выбираются не равномерно, а в соответствии с проставленными им весами. Например, если в таблице из двух записей первая запись имеет вес 2, а вторая - 3, это означает, что первая запись должна выбираться с вероятностью 2/5, а вторая - 3/5. То есть на каждые 5 попыток первая запись должна выпадать в среднем 2 раза, а вторая - 3. Добавим в таблицу Customers (см. Скрипт 1 предыдущего поста) колонку Weight:
use tempdb
if OBJECT_ID('dbo.Customer', 'U') is not null drop table dbo.Customer
create table dbo.Customer (CustomerID nchar(5) primary key, CompanyName nvarchar(40), Weight float)
insert dbo.Customer values ('ALFKI', 'Alfreds Futterkiste', 2), ('ANATR', 'Ana Trujillo Emparedados y helados', 1), ('ANTON', 'Antonio Moreno Taqueria', 3), ('AROUT', 'Around the Horn', 2), ('BERGS', 'Berglunds snabbkop', 2)
Скрипт 1
Совершенно очевидно, как выбирать из таблицы записи в соответствии с их вероятностями. Отнормируем проставленные веса на диапазон 0, 1:
update Customer set Weight = Weight / (select SUM(Weight) from Customer)
Скрипт 2
Разобьем диапазон от 0 до 1последовательно на интервалы длиной 0.2, 0.1, 0.3, 0.2, 0.2, т.е. на отрезки [0, 0.2), [0.2, 0.3), [0.3, 0.6), [0.6, 0.8), [0.8, 1.0). (*)
Бросаем в диапазон [0, 1) равномерно распределенный датчик случайных чисел. Если точка попала в первый отрезок, выбирается первая запись (ALFKI), во второй - вторая (ANATR) и т.д. Чтобы реализовать этот алгоритм, добавим в таблицу Сustomer поле Weight_RunningTotal
alter table Customer add Weight_RunningTotal float
Если в этом поле для каждой записи будет лежать левая граница соответствующего отрезка (*)
Скрипт 3
остается кинуть случайную точку и найти максимальную запись с левой границей, меньше этой точки
select top 1 * from Customer where Weight_RunningTotal <= RAND() order by Weight_RunningTotal desc
Скрипт 4
Все, что для этого нужно - заполнить колонку Weight_RunningTotal нарастающей суммой колонки Weight, т.е. в n-ю запись вставляется сумма весов n-1 предыдущих (в порядке ключевого поля CustomerID). Навскидку можно предложить 3 сравнительно честных способа решения.
Способ 1. С использованием курсора. Здесь все просто - ползем от записи к записи, по мере продвижения накапливая сумму в переменной @Weight_RunningTotal.
declare @cur cursor, @Weight float, @Weight_RunningTotal float = 0
set @cur = cursor local keyset for select Weight from Customer order by CustomerID for update of Weight_RunningTotal
open @cur
while 1 = 1 begin
fetch next from @cur into @Weight
if @@FETCH_STATUS <> 0 break
update Customer set Weight_RunningTotal = @Weight_RunningTotal where current of @cur
set @Weight_RunningTotal += @Weight
end
close @cur
deallocate @cur
select * from Customer
Скрипт 5
Способ 2. Я условно назову его sql-ex. На сайте SQL Exercises курсорами пользоваться нельзя. Там обожают упаковывать все в один запрос, с непринужденностью теоретиков жонглируя множествами. Сделаем треугольное произведение таблицы саму на себя:
select t2.CustomerID ID, t1.CustomerID ID_Preceding, t1.Weight from Customer t1 right join Customer t2 on t1.CustomerID < t2.CustomerID
Скрипт 6
В колонке ID содержится текущая запись, а в ID_Preceding - все, ей предшествующие. Сумма нарастающим итогом получается как сумма по группам ID:
select t2.CustomerID ID, sum(isnull(t1.Weight, 0)) s from Customer t1 right join Customer t2 on t1.CustomerID < t2.CustomerID group by t2.CustomerID
Cкрипт 7
Способ 3. В лоб. Что слышится - "в n-ю запись вставляется сумма весов n-1 предыдущих" - то и пишется:
select CustomerID, CompanyName, Weight, (select SUM(Weight) from Customer t2 where t2.CustomerID < t1.CustomerID) from Customer t1
Скрипт 8
Ни один из приведенных способов не свободен от недостатков. Курсор, по определению, медленный, перемножение - затраты растут как квадрат числа записей, тоже очевидные тормоза на больших объемах, вложенный подзапрос - аналогично. Идеальным был бы линейный способ, при котором таблица пробегается один раз а ля курсор, но без курсора. Что-нибудь вроде
declare @Weight_RunningTotal float = 0
update Customer set Weight_RunningTotal = @Weight_RunningTotal, @Weight_RunningTotal += Weight
select * from Customer
Скрипт 9
Как мы видим, невзирая на указанный в запросе порядок локальная переменная инкрементится раньше поля, поэтому нарастающий итог получается по, а не до текущей записи. Но это еще полбеды. Проблема в том, что мы вообще не можем гарантировать порядок, в котором записи будут перебираться в ходе выполнения запроса, и SQL Server тут не при чем, т.к. язык SQL оперирует с множествами, а классические множества неупорядочены. В данном случае нам повезло, и записи перебирались в нужном нам порядке, т.е. в соответствии с ключом CustomerID, но никто не гарантирует, что так будет всегда. Если в один прекрасный раз оптимизатору покажется лучше по каким-нибудь соображениям изменить порядок сканирования, Скр��пт 9 возвратит чушь. Ушлый народ пытался обеспечить порядок хинтами оптимизатору WITH(INDEX(1),TABLOCKX), OPTION (MAXDOP 1) и другими уловками, однако еще пару лет назад Ицик Бен-Ган в своей статье "Ordered UPDATE and Set-Based Solutions to Running Aggregates" в журнале SQL Server Magazine подверг их разбору и в результате пришел к неутешительному выводу - in SQL Server 2008 and earlier versions, I haven’t yet found a pure set-based technique for running totals that SQL Server’s optimizer handles very efficiently with large partition sizes. Наиболее надежным, он отметил, способом могла бы стать реализация порядка в оконных предикатах, прописанная, кстати, в стандарте SQL-2003. Imagine how great it would be if one day we’d be able to express a running total like this: … SUM(Weight) over (order by CustomerID).
Мне очень приятно сообщить, что этот день настал:
select CustomerID, CompanyName, Weight, SUM(Weight) over (order by CustomerID) from Customer
Скрипт 10
Сумма нарастающим итогом, не включая текущую запись:
select CustomerID, CompanyName, Weight, SUM(Weight) over (order by CustomerID rows between unbounded preceding and 1 preceding) from Customer
Скрипт 11
Подробности про новый синтаксис оконных функций можно прочитать в BOL.
Примечание. Фишка появилась в СТР3. СТР2 и ранние версии не воспринимают order by внутри sum(), count(), avg() и др., ругаясь на Incorrect syntax near 'order'.
В качестве продолжения – сравнение производительности способов между собой.
Алексей Шуленин
blogs.technet.microsoft.com
