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

Откуда: Минск
Сообщений: 812
Потребовалось сделать класическую вещь - суммирование строк, написал функцию, сделал в ней всю работу и все было хорошо... Пока не пришли реальные данные всего лишь на 6000 строки и не затормозили Sempron 3000 под SQL Server 2005 секунд на 7. Начал разбираться и понял что проблема исключительно в трех таких функциях. (а также в использовании Scroll курсоров в них). Решил не заниматься оптимизацией функций (объем данных все равно будет расти еще в несколько раз и вполне возможно, что ситуация повторится, хотя если посдкажете надежный и быстрый(в пределах 100 000 записей) вариант подобной функции - буду очень благодарен), а сделать более кардинальный вариант - вьюшки, но тут потребовалась агрегативная функция, для 2005 нашел только один единственный вариант синтаксиса ее создания с использованием NET:
CREATE AGGREGATE [ schema_name . ] aggregate_name
        (@param_name <input_sqltype> )
RETURNS <return_sqltype>
EXTERNAL NAME assembly_name [ .class_name ]
Установил MS Visual # Express Edition и написал там простейший вариант:

using System;
using System.Collections.Generic;
using System.Text;

namespace alexey_public
{    
    public class StrSUM
    {
        static string GetStr(string[] s)
        {
            string sum;

            sum = "";

            foreach (string ss in s)
            {
                sum = sum + ", " + ss;
            }


            return sum;
        }
    }
}
Затем создал сборку (assembly) в базе, натравии ее на DLL-ку, и назвал ee StrSUM.
Теперь пытаюсь создать непосредственно саму агрегирующую функцию:
CREATE AGGREGATE StrSUM (@s nvarchar)
RETURNS nvarchar
EXTERNAL NAME StrSUM;
Не проходит, с таким сообщением:

Msg 6556, Level 16, State 1, Line 1
CREATE AGGREGATE failed because it could not find type 'StrSUM' in assembly 'StrSUM'.
Msg 6597, Level 16, State 2, Line 1
CREATE AGGREGATE failed.

К сожалению в самом help-e нет примера, а примеры, которые должны были быть в инсталляторе, у меня отсутствуют. Может кто-нибудь подкинет текст хотя бы функции из примера?
3 май 06, 19:05    [2626812]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Игорь Оробчук
Member

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

А агрегат пишется только для UDT (пользовательского типа).
3 май 06, 19:23    [2626871]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Алексей Вк.
Member

Откуда: Минск
Сообщений: 812
create assembly где - в SQL Server-e?
В сервере я это сделал.
А что значит для пользовательских типов - в HELP-e немного описан пример и там сказано что пример предназначен для работы именно со строками, и именно для аналогичной задачи.
3 май 06, 19:29    [2626891]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Игорь Оробчук
Member

Откуда:
Сообщений: 115
Как минимум обратитесь к классу полностью (укажите Ваш namespace alexey_public) в определении агрегата.
3 май 06, 19:39    [2626925]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Такова жизнь
Member

Откуда: Москва
Сообщений: 194
public class StrSUM
{
	static string GetStr(string[] s)
	{
		StringBuilder _stringBuilder = new StringBuilder();
		foreach (string ss in s)
		{
			_stringBuilder.Append(", ");
			_stringBuilder.Append(ss);
		}
		return _stringBuilder.ToString();
	}
}

ИМХО так функция должна работать быстрее. Причем, чем больше строк надо сцепить, тем больше должна быть заметна разница по сравнению с обычной конкатенацией.
3 май 06, 19:47    [2626945]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Алексей Вк.
Member

Откуда: Минск
Сообщений: 812
Такова жизнь Интресный вариант
Игорь
Для

CREATE AGGREGATE StrSUM (@s nvarchar)
RETURNS nvarchar
EXTERNAL NAME StrSUM.alexey_public.StrSUM;

Получаю такое:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.

Не проходит этот этап, но зато сборка создается без ошибок.
3 май 06, 19:51    [2626956]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Игорь Оробчук
Member

Откуда:
Сообщений: 115
Алексей Вк.

Для

CREATE AGGREGATE StrSUM (@s nvarchar)
RETURNS nvarchar
EXTERNAL NAME StrSUM.alexey_public.StrSUM;

EXTERNAL NAME StrSUM.[alexey_public.StrSUM].GetStr;
3 май 06, 19:54    [2626968]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Алексей Вк.
Member

Откуда: Минск
Сообщений: 812
Игорь Оробчук
Алексей Вк.

Для

CREATE AGGREGATE StrSUM (@s nvarchar)
RETURNS nvarchar
EXTERNAL NAME StrSUM.alexey_public.StrSUM;

EXTERNAL NAME StrSUM.[alexey_public.StrSUM].GetStr;


С тем же результатом.
3 май 06, 20:04    [2627000]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Алексей Вк.
Member

Откуда: Минск
Сообщений: 812
Думаю проблема все же в самом классе - что-то там не так описано, я уже кое-что нашел в Help-e, но не все. Поэтому и хотелось бы увидеть работающий пример для подобной функции.
3 май 06, 20:08    [2627017]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Такова жизнь
Member

Откуда: Москва
Сообщений: 194
Алексей Вк.
Думаю проблема все же в самом классе - что-то там не так описано, я уже кое-что нашел в Help-e, но не все. Поэтому и хотелось бы увидеть работающий пример для подобной функции.


Похоже на то. Вот ссылка: Invoking CLR User-Defined Aggregate Functions
3 май 06, 20:31    [2627071]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Алексей Вк.
Member

Откуда: Минск
Сообщений: 812
Такова жизнь
Алексей Вк.
Думаю проблема все же в самом классе - что-то там не так описано, я уже кое-что нашел в Help-e, но не все. Поэтому и хотелось бы увидеть работающий пример для подобной функции.


Похоже на то. Вот ссылка: Invoking CLR User-Defined Aggregate Functions

Да - это оно, надо будет завтра заняться.
3 май 06, 20:43    [2627084]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Алексей Вк.
Member

Откуда: Минск
Сообщений: 812
Нашел главную причину - нельзя спользовать namespace - это вообще не предусмотрено почему-то, иначе ничего в сборке не находится.
Ну и конечно методы надо было называть по-другому, но это уже поправимо.
4 май 06, 19:36    [2631595]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Алексей Вк.
Member

Откуда: Минск
Сообщений: 812
Кстати, а может ли эта функция иметь более одно параметра, для управления логикой своей работы?
4 май 06, 20:19    [2631719]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Алексей Вк.
Member

Откуда: Минск
Сообщений: 812
Нет - все-таки namespace использовать можно, толкьо немного по-другому:
EXTERNAL NAME [StringUtilities].[Microsoft.Samples.SqlServer.Concatenate];
4 май 06, 20:26    [2631742]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Алексей Вк.
Member

Откуда: Минск
Сообщений: 812
Нет - больше одного параметра иметь нельзя, жаль - это очень большое упущение MS - а то можно было бы...
А теперь придется писать на каждый вариант соединения строк свой класс (или два параметра в одной переменной, что тоже не выход, но зато рабоче...).
4 май 06, 20:30    [2631753]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Игорь Оробчук
Member

Откуда:
Сообщений: 115
Алексей Вк.
Нет - больше одного параметра иметь нельзя, жаль - это очень большое упущение MS - а то можно было бы...
А теперь придется писать на каждый вариант соединения строк свой класс (или два параметра в одной переменной, что тоже не выход, но зато рабоче...).

Простите, а как сиквел должен использовать такой агрегат? Разве есть встроенные агрегативные функции с несколькими параметрами?
А обычная udf может иметь сколько угодно параметров.
5 май 06, 10:11    [2632895]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Алексей Вк.
Member

Откуда: Минск
Сообщений: 812
А так - простой пример - тебе надо объединить строки, добавив после каждой например какой-то набор символов, это могло бы выглядеть так:
 select id, StrSUM(name,';')
 from table_1

захотел разделить не ; а '*' :
 select id, StrSUM(name,'*')
 from table_1

И все - просто и аккуратно. Между прочим можно было бы даже и поля передавать:
 select id, StrSUM(name1, int_2, и т.д.)
 from table_1

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

Нет, есть конечно возможность вызывать несколько функций агрегирования в одном запросе, но это совсем другой случай.
5 май 06, 10:30    [2633009]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Алексей Вк.
Member

Откуда: Минск
Сообщений: 812
А теперь мне придется писать несколько классов и определять по ним ункции каждый раз, когда мне придется менять алгоритм соединения строк, неудобно мягко говоря.
Вот поэтому и говорю что MS пока что пропустила великолепную возможность значительно упростить работу программистам.
Хотя есть возможность сначал вызывать функцию обработки строки, а потом уже объединять их... Да способов выкрутиться немало, но это как-то все не то... И неудобно.
5 май 06, 10:34    [2633040]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Игорь Оробчук
Member

Откуда:
Сообщений: 115
Алексей Вк.
А так - простой пример - тебе надо объединить строки, добавив после каждой например какой-то набор символов, это могло бы выглядеть так:
 select id, StrSUM(name,';')
 from table_1

захотел разделить не ; а '*' :
 select id, StrSUM(name,'*')
 from table_1

И все - просто и аккуратно. Между прочим можно было бы даже и поля передавать:
 select id, StrSUM(name1, int_2, и т.д.)
 from table_1

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

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

Что-то в этом есть..
Но, если в запросе есть агрегаты, то выборка еще и полей, что не в агрегатах требует group by.

Кроме того возможность выкрутиться все равно есть. Вот они и сделали минимум.

В Вашем случае мне кажется смешаны две задачи - агрегат конкатенации и функция форматирования одной ячейки перед агрегированием. Возможно лучше это явно разделить. Тогда агрегат меняться не будет, а функцию форматирования напишите как-угодно (хоть по типу printf).
8 май 06, 16:08    [2643110]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Игорь Оробчук
Member

Откуда:
Сообщений: 115
Алексей Вк.
Хотя есть возможность сначал вызывать функцию обработки строки, а потом уже объединять их... Да способов выкрутиться немало, но это как-то все не то... И неудобно.

Проглядел.. Именно так лучше всего имхо.
8 май 06, 16:09    [2643115]     Ответить | Цитировать Сообщить модератору
 Re: Написание пользовательской агрегирующей функции  [new]
Алексей Вк.
Member

Откуда: Минск
Сообщений: 812
Согласен, но часто возникают ситуации, когда процесс соединения строк зависит от нескольких параметров, в первую очередь от значения остальных ячеек, и не только в текущей строчке. ИМХО в самом классе они сделали все, чтобы это реализовать, результат суммируется и анализируется отдельно от выдачи результата.
По поводу group by - согласен, тут сложности, вообще приверженность MS по этой позиции - обязательный group by для всех полей не всегда удобна (в том же Сache можно спокойно выбирать данные из других полей, не включая их в group by, неоднозначно конечно - но если структура данных позволяет, то это очень удобно,, запросы становятся меньше и проще). Впрочем это отдельная тема.
К тому же возможне вариант выборки нескольких полей таки -
select i1,sum(i2),sum(i3)
group by i1
Так что можно было бы даже в рамках существующей модели обработки даных использовать несколько параметров для агрегирующей функции.
10 май 06, 11:19    [2646461]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Написание пользовательской агрегирующей функции  [new]
Алексей Вк.
Member

Откуда: Минск
Сообщений: 812
Не прошло и три года :-)

В общем появилась проблема, мне нужно не просто суммировать, а еще и сортировать при суммировании, я решил переписать текст функции, но вот в чем проблема, сортирует везде, кроме одной строчки, в которой сортировка идет не так, как хотелось бы. Привожу текст сборки, что здесь может быть не так, я ее сделал на основе варианта от Microsoft.

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;



    [Serializable]
    [SqlUserDefinedAggregate(
        Format.UserDefined, //use clr serialization to serialize the intermediate result
        IsInvariantToNulls = true, //optimizer property
        IsInvariantToDuplicates = false, //optimizer property
        IsInvariantToOrder = false, //optimizer property
        MaxByteSize = 8000) //maximum size in bytes of persisted value
    ]

    public class StrSUM_Comma : IBinarySerialize
    {
        /// <summary>
        /// The variable that holds the intermediate result of the concatenation
        /// </summary>
        private StringBuilder intermediateResult;
        private SqlString PrevValue;
        private const string add_str = ", ";



        /// <summary>
        /// Initialize the internal data structures
        /// </summary>
        public void Init()
        {
            this.intermediateResult = new StringBuilder();
            this.PrevValue = string.Empty;
        }

        /// <summary>
        /// Accumulate the next value, not if the value is null
        /// </summary>
        /// <param name="value"></param>
        public void Accumulate(SqlString value)
        {
            if (value.IsNull)
            {
                return;
            };

            if (this.PrevValue.IsNull)
                this.intermediateResult.Append(value.Value).Append(add_str);
            else
                {
                    if (this.PrevValue.CompareTo(value.Value) < 0)
                        this.intermediateResult.Append(value.Value).Append(add_str);
                    else this.intermediateResult.Insert(0, add_str).Insert(0, value.Value);
                };

            this.PrevValue = value.Value;
        }

        /// <summary>
        /// Merge the partially computed aggregate with this aggregate.
        /// </summary>
        /// <param name="other"></param>
        public void Merge(StrSUM_Comma other)
        {
            string scurrent=this.intermediateResult.ToString();
            string sother=other.intermediateResult.ToString();

            if (scurrent.CompareTo(sother)>0)
                this.intermediateResult.Append(other.intermediateResult);
            else this.intermediateResult.Insert(0,other.intermediateResult);
        }

        /// <summary>
        /// Called at the end of aggregation, to return the results of the aggregation.
        /// </summary>
        /// <returns></returns>
        public SqlString Terminate()
        {
            string output = string.Empty;
            //delete the trailing comma, if any
            if (this.intermediateResult != null
                && this.intermediateResult.Length > add_str.Length)
            {
                output = this.intermediateResult.ToString(0, this.intermediateResult.Length - add_str.Length);
            }

            return new SqlString(output);
        }

        public void Read(BinaryReader r)
        {
            intermediateResult = new StringBuilder(r.ReadString());
        }

        public void Write(BinaryWriter w)
        {
            w.Write(this.intermediateResult.ToString());
        }
    }


Может это вообще в соседний раздел нужно было бы писать?
11 сен 09, 20:22    [7649890]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить