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

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Возможно ли написать агрегатную функцию более с чем одним входным параметром.
Просто сигнатура то методов Init() Accumulate () ... вроде стандартная, МсСКЛ даже не даёт задеплоить агрегат с более чем одним входным параметром.

Не спорю что в поиске мог не найти, но пользовался.

Проблема в том что в текущей базе уже есть функция, которая агрегирует строки. Разделитель у неё задан жёско ",". Мне нужен параметр что бы я мог передавать туда то, что мне как надо разделитель.

Естественно я напишу функцию, которая будет работать вообще без разделителя, но нужна и функция в которой разделитель передаётся, потому что не хочется писать

select ... sumStr(field + '; '), а потом у последнего обрезать лишний '; '. Точно так же не хочется играться replace-ом и его аналогом с регулярным выражением.

Заранее спасибо за помощь и отсутствие вопросов а-ля "зачем тебе оно надо".
25 сен 08, 13:14    [6227399]     Ответить | Цитировать Сообщить модератору
 Re: Агрегатная функция с двумя параметрами CLR  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Вообщем приходится обрезать на СКЛ, причём для того что вы второй раз не вызывать агрегатку что бы узнать длину результата делаю это из подзапроса. И писать это прийдётся везде.
25 сен 08, 16:24    [6229105]     Ответить | Цитировать Сообщить модератору
 Re: Агрегатная функция с двумя параметрами CLR  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Понимаю что 3е сообщение уже моё, но не хотелось бы что бы тема превращалась в блог )

Вчера захотелось вернуться к этой теме и вот почему. Понадобилось мне удалить дублирующиейся строки. Причём я решил действовать по алгоритму "удалить все повторяющиейся записи по определённому набору полей, крому последнего" По какому принципц вибирается последний в принципе не важно в данный момент.
Что я сделал:
1) написал запрос с group by
2) вывел поторяющиеся записи и количество повторений

Теперь стала задача получить значение по порядку внутри с агрегированной строки. Был бы там int-вый идешник и мне надо было бы получить 1ую или последнюю строку, то я бы просто написал бы min, max. А потом я удалил бы все повторяющиеся строки, кроме того идешника.
(сделать это не сложно если у тебя есть список того что надо удалить, кроме одного идешника)

Но в том то и сложность что поле там не int-овое и пришлось писать приблизително такой запрос к уже кривой таблице (прошу не втыкать).

with repeatedRows as
	   (select sr.xID, count(sr.xID) as count_ID--, min(sr.xID) as min_id
		  from tbStatusReasons sr
		group by sr.xID
		having count(sr.xID) > 1),
	allDuplicatedRows as
		(select sr.rowID, sr.xID, row_number() over(partition by sr.xID order by rowID desc) as fn_row
		   from tbStatusReasons sr
		  inner
		   join repeatedRows t
		     on sr.xID = t.xID),
	lastRowsForDeleting as
		(select rowID from allDuplicatedRows where fn_row <> 1)
--select * from lastRowsForDeleting 	
delete 
  from tbStatusReasons
  from lastRowsForDeleting fd
 where tbStatusReasons.rowID = fd.rowID

Ну естественно мне такой подход не нравится (можно было и по другом написать, но тут чётко видно 3 этапа). Если в последнем запросе вместо fn_row <> 1 написать fn_row = 1, то просто в delete по-другому пишется условие.

У меня периодически возникают задачки когда надо залезть внутрь группы. И я бы без проблем бы это сделал своей агрегатной функцией. Правда там это была бы сортировка внутри и не СУБД-шная, а .NET-овская, работало бы это не быстро, при вызове функции второй раз (даже с идентичными параметрами) всё бы повторялось бы заново. Подобный подход в принципе штука не быстрая, но другого средства Ms SQL не предоставил. Это конечно возможно реализовать, если передавать данный строкой определённого формата (проще всего XML), но явно не хочется его использовать. Но работать будет!

Может быть у кого-нить есть идея как обратится к результату вычисления следующей(их)/предыдущей(их) строк. Тогда бы можно было написать полный аналог аналитических функций в Oralce и просто классно было бы работать с нарастающим итогом и т. п.
2 окт 08, 11:52    [6254931]     Ответить | Цитировать Сообщить модератору
 Re: Агрегатная функция с двумя параметрами CLR  [new]
Так, пописать вышел :)
Guest
В 2005 нельзя сделать агрегатную функцию с несколькими параметрами. Кроме того, экземпляр класса, который реализует вашу функцию не должен занимать более 8кб. Для конкатенации это весьма существенное ограничение.

Если интересно, в 2008 можно и несколько параметров, и размер > 8кб
2 окт 08, 12:13    [6255124]     Ответить | Цитировать Сообщить модератору
 Re: Агрегатная функция с двумя параметрами CLR  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Так, пописать вышел :)
В 2005 нельзя сделать агрегатную функцию с несколькими параметрами. Кроме того, экземпляр класса, который реализует вашу функцию не должен занимать более 8кб. Для конкатенации это весьма существенное ограничение.

Если интересно, в 2008 можно и несколько параметров, и размер > 8кб


Спасибо за ответ. Про 8кб я и так знал, а про 2008 и несколько параметров нет. Само ограничение не особо напрягает. Я в крайнем случае отаброжу <строка 8кб - 3 байта> + '...'. Правда в юникоде тут поменьше в два раза, но не важно. Для отображения в списке строк достаточно. Если надо значение для одной строки отобразить в какой-нить Memo и более, то тут уже можно обойтись без агрегатки.

У меня больше проблема:
1) Я не могу придумать как передать несколько параметров в 2005 (а на 2008 планируется и будет, но не сейчас), кроме как ХМЛ-ником или строкой определённого формата (грёбанный вэбовский подход прям);

2) То что если я захочу работать внутри с агрегированных значений, то:
- работа с массивом на C#
- написать агрегатку, которая вернёт XML-ник в виде таблицы (аналогично как для строки) и обращатся к нему на уровне базы что бы достать нужный элекмент (тут уже 8кб предел);

3) Что бы обратится к агреггированным предыдущим и последующим значениям прийётся использовать подзапросы, иногда рекурсивный CTE (для нарастающего итога).

4) Что бы меньше нагружать сжарать ресурсов при написании запроса где в одной колонке я получаю 2ое значение внутри сагрегированной строки, а в другой 5ое значение, что бы функция по полной не вызывалась второй раз опять таки использовать подзапрос в к полю в котором хранится таблица в XML-е, из которой я потом уже достану и 1, 2, 3 ...10 значения. Т. е. все рассуждения сводятся к тому, что бы первично написать функцию, которая сформирует колонку в виде XML как дочернюю таблицу, но тут опять грёбанной ограничение 8кб становится важным (даже о длине тэга задумываешься).

По крайней мере я так для себя формулирую проблемы. И беспокоит то, что изначально свои агрегатные/аналитические функции хочется написать что бы повысить быстродействие, а .NET хоть и будет выполнятся на хосте MsSQL - всё равно останется .NET.
2 окт 08, 12:43    [6255421]     Ответить | Цитировать Сообщить модератору
 Re: Агрегатная функция с двумя параметрами CLR  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Сделал пример для тестирования. strSum - агрегатка для строки. Если ваша агрегатка не добавляет лишних символов можете использовать её. Иначе copy-past-ый, опять же откуда-то скопипащенный код после запросов.

Что больше всего напрягает - понимание того что не будет работать дюже быстро:
1) агрегатка на .НЕТ
2) работа с ХМЛ
3) подзапросы в списке полей - вообще ненавижу (хоть будут только для одной группы)
4) много повторяющихся частей когда повторно вызывается функция, каждый раз запрос к ХМЛ при использовании ещё одного столбца;
5) Ограничение 8кб может создать проблему (можно решить если на уровне .NET возвращать результат), но о каком коэфициенте использования кода можно говорить тогда?


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

Буду благодарен за идеи, особенно если оин будут работать быстрее

declare @tbStatusReasons table(xID int, rowID varchar(36))

insert
  into @tbStatusReasons
select 1, 'abcd'
union
select 1, 'babcd'
union
select 1, 'cabcd'
union
select 2, '1abcd'
union
select 2, '2abcd'
union
select 3, 'xabcd'



select sr.xID, count(sr.xID) as count_ID,
	   cast(dbo.strSum('<row><xID>' + cast(sr.rowID as varchar(36)) + '</xID></row>') as xml).value('(row/xID)[1]', 'varchar(36)') value
  from @tbStatusReasons sr
 group by sr.xID
--having count(sr.xID) > 1
	   

select t.xID, 	   
	   --Первая
	   (select /*top 1*/ t2.xID
	      from (select IDS.ID.value('.','varchar(36)') as xID,
					   row_number() over (order by IDS.ID.value('.','varchar(36)')) as row_order
				  from t.valueXML.nodes('/row/xID')  IDS(ID)) t2
		  where row_order = 1
		) as value_first,
	   
	   --Предпоследняя	   
	   (select /*top 1*/ t2.xID
	      from (select IDS.ID.value('.','varchar(36)') as xID,
					   row_number() over (order by IDS.ID.value('.','varchar(36)')) as row_order
				  from t.valueXML.nodes('/row/xID')  IDS(ID)) t2
		  where row_order = t.count_ID - 1
		) as value_before_last
		
  from (select sr.xID, count(sr.xID) as count_ID,
			   cast(dbo.strSum('<row><xID>' + cast(sr.rowID as varchar(36)) + '</xID></row>') as xml) valueXML
		  from @tbStatusReasons sr
		 group by sr.xID
		/*having count(sr.xID) > 1*/) t

Берём dll-ку и атачим к базе скриптом

if object_id('dbo.strSum') is not null
DROP AGGREGATE [dbo].[strSum]
go

if exists (select 1 from sys.assemblies where [name] = 'sampleLibary')
	drop assembly [sampleLibary]
go

create assembly [sampleLibary] from 'd:\MyDocs\sampleLibary.dll'
go

CREATE AGGREGATE [dbo].[strSum]
(@value [nvarchar](4000))
RETURNS[nvarchar](4000)
EXTERNAL NAME [sampleLibary].[strSum]
GO


К сообщению приложен файл (sampleLibary.dll - 7Kb) cкачать
2 окт 08, 16:48    [6257845]     Ответить | Цитировать Сообщить модератору
 Re: Агрегатная функция с двумя параметрами CLR  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Такой подход помог отсортировать строковые значения внути агрегатки.
Например, у нас фамилии 'Иванов, Абрамов, Баранов' получены агрегаткой.
Для стандартных sum, count, avg ... порядок не важен.
А мне надо получить 'Абрамов, Баранов, Иванов'

Ну на самом деле получилось реализовать запрос возвращающий в одном столбце список email-ов на которые надо посылать сообщения, а в другом сообщения (отформатированные прям внутри агрегатки как надо, с отображением/не отображением и прочим в зависимости от того какой результат полученный после агрегации).

Но блин отправлять мыло всё равно приходится в цикле - а я их ненавижу !
3 окт 08, 18:23    [6263994]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Агрегатная функция с двумя параметрами CLR  [new]
Домер
Member

Откуда:
Сообщений: 1
кому если надо:
    [Serializable]
    [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = -1)]
    public struct Concat : IBinarySerialize
    {
        private string _concatString;

        public void Init()
        {
            _concatString = string.Empty;
        }

        public void Accumulate(SqlString value, SqlString ch)
        {
            _concatString += value.Value + ch.Value;
        }

        public void Merge (Concat @group)
        {
            _concatString += @group._concatString;
        }

        public SqlString Terminate ()
        {
            return new SqlString (_concatString);
        }

        public void Read(BinaryReader r)
        {
            _concatString = r.ReadString();
        }

        public void Write(BinaryWriter w)
        {
            w.Write(_concatString);
        }
    }
12 сен 13, 15:13    [14831813]     Ответить | Цитировать Сообщить модератору
 Re: Агрегатная функция с двумя параметрами CLR  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Ох ты ё... :)
Лучше поздно чем никогда, но я всё ещё жив! :)

То времена SQL 2005 были.
12 сен 13, 18:21    [14832988]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить