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

Откуда:
Сообщений: 26
Приветствую всех!
Подскажите, пожалуйста, в SQL2016 появилась ли возможность создавать свои агрегатные функции, не прибегая к CLR?
Просто появилась необходимость написать функцию для вычисления разности краевых значений показаний водяных счетчиков в определенном временном интервале. Хотелось бы это все завернуть в красивую оболочку, чтобы можно было потом в коде оперативно заменить такую функцию на стардартную агрегатную типа AVG в случае смены типа измерения.
14 июн 18, 09:45    [21490083]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Const123,

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-2017
14 июн 18, 09:46    [21490085]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31354
Const123
Подскажите, пожалуйста, в SQL2016 появилась ли возможность создавать свои агрегатные функции, не прибегая к CLR?
Нет.
14 июн 18, 10:37    [21490235]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
msLex
Member

Откуда:
Сообщений: 8065
Const123
Приветствую всех!
Подскажите, пожалуйста, в SQL2016 появилась ли возможность создавать свои агрегатные функции, не прибегая к CLR?
Просто появилась необходимость написать функцию для вычисления разности краевых значений показаний водяных счетчиков в определенном временном интервале. Хотелось бы это все завернуть в красивую оболочку, чтобы можно было потом в коде оперативно заменить такую функцию на стардартную агрегатную типа AVG в случае смены типа измерения.


А в чем проблема использовать CLR агрегат? Ничего сложного в нем нет.
14 июн 18, 11:38    [21490480]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
Const123
Member

Откуда:
Сообщений: 26
msLex
А в чем проблема использовать CLR агрегат? Ничего сложного в нем нет.

Эх...мне бы скилетик какой-нить для примера как это правильно оформить.
А то я писал до этого только clr для чтения данных из текстовых файлов.
14 июн 18, 11:55    [21490555]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31354
Const123
msLex
А в чем проблема использовать CLR агрегат? Ничего сложного в нем нет.

Эх...мне бы скилетик какой-нить для примера как это правильно оформить.
А то я писал до этого только clr для чтения данных из текстовых файлов.
Так прямо в хелпе есть "скелетик", строковая агрегатная функция.
14 июн 18, 11:57    [21490560]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
msLex
Member

Откуда:
Сообщений: 8065
Const123
msLex
А в чем проблема использовать CLR агрегат? Ничего сложного в нем нет.

Эх...мне бы скилетик какой-нить для примера как это правильно оформить.
А то я писал до этого только clr для чтения данных из текстовых файлов.


https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-user-defined-functions/clr-user-defined-aggregate-invoking-functions?view=sql-server-2017
14 июн 18, 12:14    [21490604]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
Const123
Member

Откуда:
Сообщений: 26
Спасибо всем откликнувшимся.
Вот сделал по мотивам вышеуказанных материалов сборку, но вот в чем загвоздка: в результирующий набор попадает только последнее значение выборки, а первое - нет. Как можно их явно задать, чтобы все же посчитать разницу. Ниже код выборки:

using System;  
using System.Data;  
using System.Data.SqlClient;  
using System.Data.SqlTypes;  
using Microsoft.SqlServer.Server;  
  
[Serializable]  
[SqlUserDefinedAggregate(  
    Format.Native,  
    IsInvariantToDuplicates = false,  
    IsInvariantToNulls = true,  
    IsInvariantToOrder = true,  
    IsNullIfEmpty = true,  
    Name = "DIFF")]  
public struct DIFF  
{  
    /// <summary>  
    /// The variable that holds the first of all values
    /// </summary>  
    private double first;  

    /// <summary>  
    /// The variable that holds the last of all values
    /// </summary>  
    private double last;  
  
    /// <summary>  
    /// The variable that holds the counter of all values
    /// </summary>  
    private int count;  
  
    /// <summary>  
    /// Initialize the internal data structures  
    /// </summary>  
    public void Init()  
    {  
        first = 0.0;  
        last  = 0.0;  
        count = 0;  
    }  
  
    /// <summary>  
    /// Accumulate the next value, not if the value is null  
    /// </summary>  
    /// <param name="Value">Next value to be aggregated</param>  
    /// <param name="Weight">The weight of the value passed to Value parameter</param>  
    public void Accumulate(SqlDouble Value)  
    {  
        if (!Value.IsNull)  
        {  
            if (count <= 1) first = (double)Value;
            else last = (double)Value;
            count += (int)count;  
        }  
    }  
  
    /// <summary>  
    /// Merge the partially computed aggregate with this aggregate  
    /// </summary>  
    /// <param name="Group">The other partial results to be merged</param>  
    public void Merge(DIFF Group)  
    {  
//        first = Group.first;
//        last = Group.last;
//        count += Group.count;  
    }  
  
    /// <summary>  
    /// Called at the end of aggregation, to return the results of the aggregation.  
    /// </summary>  
    /// <returns>The weighted average of all inputed values</returns>  
    public SqlDouble Terminate()  
    {  
            double value = (double)(last - first);  
            return new SqlDouble(value);  
    }  
}  
15 июн 18, 08:24    [21492634]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
Alexander Titkin
Member

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

У вас в first попадают первое и второе value, так задумано? Покажите пример входных данных и что должно быть на выходе
15 июн 18, 09:44    [21492817]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
Const123
Member

Откуда:
Сообщений: 26
Alexander Titkin
Const123,

У вас в first попадают первое и второе value, так задумано? Покажите пример входных данных и что должно быть на выходе


Скорее всего написал так просто из-за выборки, можно исправить на
            if (count == 1) first = (double)Value;

Но это не меняет сути проблемы, мне в результате нужно получить Value_=24, т.е. 6795-6771. А получается на выходе 6795 при работе этой сборки, т.е. последнее значение.

Date_Time_ Value_
2018-03-29 09:29:02.533 0
2018-03-29 09:30:02.533 6771
2018-03-29 09:31:02.527 6772
2018-03-29 09:38:23.527 6773
2018-03-29 09:46:23.523 6774
2018-03-29 09:54:43.567 6775
2018-03-29 10:02:43.563 6776
2018-03-29 10:10:03.563 6777
2018-03-29 10:18:03.560 6778
2018-03-29 10:26:23.557 6779
2018-03-29 10:34:23.553 6780
2018-03-29 10:42:23.550 6781
2018-03-29 10:50:23.547 6782
2018-03-29 11:02:43.563 6783
2018-03-29 11:26:03.557 6784
2018-03-29 12:15:24.530 6785
2018-03-29 12:59:44.533 6786
2018-03-29 13:12:04.550 6787
2018-03-29 13:17:04.567 6788
2018-03-29 13:20:04.547 6789
2018-03-29 13:24:04.570 6790
2018-03-29 13:28:44.540 6791
2018-03-29 13:35:24.547 6792
2018-03-29 13:42:04.550 6793
2018-03-29 13:49:04.553 6794
2018-03-29 13:55:44.560 6795
15 июн 18, 11:55    [21493308]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
Const123
Member

Откуда:
Сообщений: 26
Еще в догонку хочу спросить как преобразовать выражение типа:
CREATE ASSEMBLY [xp_clrudp]
FROM 'C:\SQLT\clrudp.dll';  

в бинарную сборку типа:
CREATE ASSEMBLY [xp_clrudp]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004

чтобы в дальнейшем отказаться от файла dll?
15 июн 18, 12:01    [21493324]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Const123,

c 2012 есть FIRST_VALUE которого достаточно для ваших танцев
15 июн 18, 12:04    [21493329]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Const123,
Во-первых, фигня вышла, тебе нужно первое и последнее, при этом.
IsInvariantToOrder = true

Во-вторых, эта задача решается чисто на SQL.
15 июн 18, 14:33    [21493922]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
Const123
Member

Откуда:
Сообщений: 26
Руслан Дамирович
Const123,
Во-первых, фигня вышла, тебе нужно первое и последнее, при этом.
IsInvariantToOrder = true

Во-вторых, эта задача решается чисто на SQL.


IsInvariantToOrder = false все равно не дает нужного результата.
Но почему эту задачу нельзя решить через CLR без усложнения конструкций запроса?
Ведь FIRST_VALUE отребует конструкции с OVER и partition by.
Как же тогда будет выглядеть запрос?
15 июн 18, 15:09    [21494066]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Const123
Как же тогда будет выглядеть запрос?

SELECT
  [delta] = FIRST_VALUE( [value] ) OVER ( PARTITION BY ... [, ... ] ORDER BY [date_time] DESC )
          - FIRST_VALUE( [value] ) OVER ( PARTITION BY ... [, ... ] ORDER BY [date_time] ASC )
FROM
  ...
WHERE
      [date_time] >= ...
  AND [date_time]  < ...
15 июн 18, 15:22    [21494124]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31354
Const123
Еще в догонку хочу спросить как преобразовать выражение типа:
http://tomeko.net/online_tools/file_to_hex.php
15 июн 18, 15:26    [21494135]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
Const123
Member

Откуда:
Сообщений: 26
И все-таки вопрос с CLR остается открытым, так как возможно придется решать эту задачу на MSSQL 2008
15 июн 18, 15:59    [21494264]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Const123
И все-таки вопрос с CLR остается открытым, так как возможно придется решать эту задачу на MSSQL 2008

Ой, вьюноша, и шо? Таки SQL2008 уже не торт, а CLR спасет мир?
Ну и молодежь, все лишь бы новомодные функции внедрить...
DECLARE @table TABLE ( [date_time] DATETIME, [value] INT )
INSERT INTO @table
VALUES 
  ( '2018-03-29 09:29:02.533', 0    ),
  ( '2018-03-29 09:30:02.533', 6771 ),
  ( '2018-03-29 09:31:02.527', 6772 ),
  ( '2018-03-29 09:38:23.527', 6773 ),
  ( '2018-03-29 09:46:23.523', 6774 ),
  ( '2018-03-29 09:54:43.567', 6775 ),
  ( '2018-03-29 10:02:43.563', 6776 ),
  ( '2018-03-29 10:10:03.563', 6777 ),
  ( '2018-03-29 10:18:03.560', 6778 ),
  ( '2018-03-29 10:26:23.557', 6779 ),
  ( '2018-03-29 10:34:23.553', 6780 ),
  ( '2018-03-29 10:42:23.550', 6781 ),
  ( '2018-03-29 10:50:23.547', 6782 ),
  ( '2018-03-29 11:02:43.563', 6783 ),
  ( '2018-03-29 11:26:03.557', 6784 ),
  ( '2018-03-29 12:15:24.530', 6785 ),
  ( '2018-03-29 12:59:44.533', 6786 ),
  ( '2018-03-29 13:12:04.550', 6787 ),
  ( '2018-03-29 13:17:04.567', 6788 ),
  ( '2018-03-29 13:20:04.547', 6789 ),
  ( '2018-03-29 13:24:04.570', 6790 ),
  ( '2018-03-29 13:28:44.540', 6791 ),
  ( '2018-03-29 13:35:24.547', 6792 ),
  ( '2018-03-29 13:42:04.550', 6793 ),
  ( '2018-03-29 13:49:04.553', 6794 ),
  ( '2018-03-29 13:55:44.560', 6795 )
;
WITH
t0 AS (
  SELECT
    [rn_a] = ROW_NUMBER() OVER ( /*PARTITION BY ...*/ ORDER BY [date_time] ASC ),
    [rn_d] = ROW_NUMBER() OVER ( /*PARTITION BY ...*/ ORDER BY [date_time] DESC ),
    [value]
  FROM
    @table
  WHERE
        [date_time] >= '2018-03-29 10:02:43.563'
    AND [date_time]  < '2018-03-29 13:17:04.567'
)
SELECT
  [first] = MAX( CASE WHEN [rn_a] = 1 THEN [value] END ),
  [last]  = MAX( CASE WHEN [rn_d] = 1 THEN [value] END ),
  [delta] = MAX( CASE WHEN [rn_d] = 1 THEN [value] END ) 
          - MAX( CASE WHEN [rn_a] = 1 THEN [value] END )
FROM
  t0
;
15 июн 18, 16:54    [21494504]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
Const123
Member

Откуда:
Сообщений: 26
Руслан Дамирович
Ой, вьюноша, и шо? Таки SQL2008 уже не торт, а CLR спасет мир?
Ну и молодежь, все лишь бы новомодные функции внедрить...

А вы, дедушка, похоже так и не поняли, к чему я клоню ;)
Дело в принципе - разобраться, почему это не работает именно в CLR-сборке, текст которой я привел выше.
К тому же колонка [Value], указанная мной в примере, всего лишь одна из ста в реальной таблице, которую необходимо будет обработать. Какой величины тогда получится текст функции/хранимки без использования CLR? И будет ли это быстро работать?
16 июн 18, 01:03    [21495634]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
dymka
Member

Откуда: г. Альметьевск (Татарстан)
Сообщений: 235
Const123,
    public void Accumulate(SqlDouble Value)  
    {  
        if (!Value.IsNull)  
        {  
            if (count == 0) <-- нужно же первое (с индексом 0) значение?
                first = (double)Value;
            else 
                last = (double)Value;
            count += 1; <-- тут все таки нужно делать приращение единицы?
        }  
    } 

Может счетчик проверять все-таки на ноль?
И потом инкремент у вас странный. Хотя там пойдет и признак - первый / не первый.
Далее, в вашей выборке первое значение равно 0, так что результат 6795 вполне корректен.
Еще есть момент - первое и последнее значение зависит от сортировки набора. Я вот не могу ручаться, что СУБД протащит через агрегат в том порядке, в котором вы задумали (этот момент просто не знаю). Для суммы то все равно в каком порядке пройдут значения. Для гарантии сортировки в агрегат нужно передавать значения поля, по которому будет осуществляться сортировка данных в окне. А в CLR мутить запоминания минимума и максимума.
16 июн 18, 11:46    [21495887]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
Const123
Member

Откуда:
Сообщений: 26
dymka
Может счетчик проверять все-таки на ноль?
И потом инкремент у вас странный. Хотя там пойдет и признак - первый / не первый.
Далее, в вашей выборке первое значение равно 0, так что результат 6795 вполне корректен.
Еще есть момент - первое и последнее значение зависит от сортировки набора. Я вот не могу ручаться, что СУБД протащит через агрегат в том порядке, в котором вы задумали (этот момент просто не знаю). Для суммы то все равно в каком порядке пройдут значения. Для гарантии сортировки в агрегат нужно передавать значения поля, по которому будет осуществляться сортировка данных в окне. А в CLR мутить запоминания минимума и максимума.

Спасибо большое за отклик (боялся, что местные старички-классики T-SQL продолжат закидывать меня ссаными тряпками), обязательно прислушаюсь к вашим рекомендациям, попробую покумекать на своим кодом :)
Есть еще вариант - завернуть в CLR динамический SQL и там попробовать его обыграть.
Собственно интерес к CLR-решению появился именно из-за невозможности выполнения sp_executesql с параметрами (да и без) в теле функции.
Не я первый с этим столкнулся и многие пытаются на форуме как-то разными путями выкручиваться в данной ситуации.
16 июн 18, 12:54    [21495988]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
Const123
боялся, что местные старички-классики T-SQL продолжат закидывать меня ссаными тряпками
Старички-классики, в отличие от вас, прекрасно знают цену решениям с SQLCLR и не пользуются ими без крайней нелобходимости.
Вы же пока не научились эффективно решать своли задачи на T-SQL. Об этом свидетельствует желание запихнуть sp_ececutesql в функцию и т.п.
Ваша хотелка
Const123
Хотелось бы это все завернуть в красивую оболочку, чтобы можно было потом в коде оперативно заменить такую функцию на стардартную агрегатную типа AVG в случае смены типа измерения.
Вполне может быть решена и без CLR. Например, продемонстриролванным далее способом.
+ Агрегатные функции
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native, Name = "SimpleDiff", IsInvariantToDuplicates = true, IsInvariantToNulls = true, IsInvariantToOrder = false, IsNullIfEmpty = true)]
public struct SimpleDiff
{
    private int _count;
    private int _first;
    private int _last;

    public void Init()
    {
        _count = _first = _last = 0;
    }

    public void Accumulate(SqlInt32 Value)
    {
        if (Value.IsNull)
            return;

        if (Value.Value < _first)
            _first = Value.Value;
        else
            if (Value.Value > _last)
            _last = Value.Value;

        _count += 1;
    }

    public void Merge (SimpleDiff Group)
    {
        if (Group._first < _first)
            _first = Group._first;
        else
            if (Group._last > _last)
            _last = Group._last;
    }

    public SqlInt32 Terminate ()
    {
        return (_count == 0)? new SqlInt32() : new SqlInt32(_last - _first);
    }
};

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native, Name = "SimpleDiffOrdered", IsInvariantToDuplicates = true, IsInvariantToNulls = true, IsInvariantToOrder = true, IsNullIfEmpty = true)]
public struct SimpleDiffOrdered
{
    private int _count;
    private int _first;
    private int _last;
    private int _firstvalue;
    private int _lastvalue;

    public void Init()
    {
        _count = _firstvalue = _lastvalue = 0;
    }

    public void Accumulate(SqlInt32 Value, SqlInt32 Order)
    {
        if (Value.IsNull || Order.IsNull)
            return;

        if (_count == 0)
        {
            _first = Order.Value;
            _firstvalue = Value.Value;
            _last = Order.Value;
            _lastvalue = Value.Value;
        }
        else
            if (Order.Value < _first)
            {
                _first = Order.Value;
                _firstvalue = Value.Value;
            }
            else
                if (Order.Value > _last)
                {
                   _last = Order.Value;
                   _lastvalue = Value.Value;
                };

        _count += 1;
    }

    public void Merge(SimpleDiffOrdered Group)
    {
        if (Group._count == 0)
            return;

        if (Group._first < _first)
        {
            _first = Group._first;
            _firstvalue = Group._firstvalue;
        }
        else
            if (Group._last > _last)
            {
                _last = Group._last;
                _lastvalue = Group._lastvalue;
            }
    }

    public SqlInt32 Terminate()
    {
        return (_count == 0) ? new SqlInt32() : new SqlInt32(_lastvalue - _firstvalue);
    }
}
+ Установка сборки
if exists(select 1 from sys.configurations where name = N'clr strict security' and value = 1)
 begin
  exec sp_configure N'clr strict security', 0;
  reconfigure with override;
 end;
go

use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

if object_id('dbo.aggSimpleDiff', 'AF') is not null
 drop aggregate dbo.aggSimpleDiff;

if object_id('dbo.aggSimpleDiffOrdered', 'AF') is not null
 drop aggregate dbo.aggSimpleDiffOrdered;

if exists(select 1 from sys.assemblies where name = N'CLRAggregfates')
 drop assembly CLRAggregfates;
go

if exists(select 1 from sys.assemblies where name = N'CLRAggregates')
 drop assembly CLRAggregates;
go

create assembly CLRAggregates from '...';
go

create aggregate dbo.aggSimpleDiff 
(
 @Value int
) 
returns int  
external name CLRAggregates.SimpleDiff;
go

create aggregate dbo.aggSimpleDiffOrdered
(
 @Value int,
 @Order int
) 
returns int  
external name CLRAggregates.SimpleDiffOrdered;
go
+ Небольшой тест
use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

create table dbo.t (id int identity primary key, v int, g int, o int);
insert into dbo.t
 (v, g, o)
 select top(2000000)
  rand(checksum(newid())) * 10000,
  rand(checksum(newid())) * 10,
  rand(checksum(newid())) * 1000000
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;
go

if object_id('dbo.fnAgg', 'IF') is not null
 drop function dbo.fnAgg;
go

create function dbo.fnAgg
(
 /*
  0: avg
  1: max - min
  2: max - min ordered 
  3: last_value - first_value ordered
 */
 @AggType tinyint
)
returns table
as
return (
 select
  t.g, avg(t.v) as agg
 from
  dbo.t t
 where
  @AggType = 0
 group by
  t.g

 union all

 select
  t.g, max(t.v) - min(t.v)
 from
  dbo.t t
 where
  @AggType = 1
 group by
  t.g

 union all

 select
  t.g, max(cast((cast(t.o as binary(4)) + 0x00000000) | t.v as bigint)) - min(cast((cast(t.o as binary(4)) + 0x00000000) | t.v as bigint))
 from
  dbo.t t
 where
  @AggType = 2
 group by
  t.g

 union all

 select
  t.g, t.lv - t.fv
 from
  (
   select
    g,
    first_value(v) over (partition by g order by o),
    last_value(v) over (partition by g order by o),
    row_number() over (partition by g order by o)
   from
    dbo.t
   where
    @AggType = 3
  ) t(g, fv, lv, rn)
 where
  t.rn = 1
);
go

declare @g int, @agg int, @show_plan bit = 0, @create_index bit = 0;

if @create_index = 1
 create index IX_t__g__o on dbo.t (g, o) include (v);

if @show_plan = 1
 set statistics xml on;
set statistics time on;

select @g = g, @agg = agg from dbo.fnAgg(0/*avg*/) option (maxdop 1);
select @g = g, @agg = agg from dbo.fnAgg(1/*max - min*/) option (maxdop 1);
select @g = g, @agg = agg from dbo.fnAgg(2/*max - min ordered*/) option (maxdop 1);
select @g = g, @agg = agg from dbo.fnAgg(3/*last_value - first_value ordered*/) option (maxdop 1);

select @g = g, @agg = dbo.aggSimpleDiff(v) from dbo.t group by g option (maxdop 1);
select @g = g, @agg = dbo.aggSimpleDiffOrdered(v, o) from dbo.t group by g option (maxdop 1);
go

set statistics time off;
set statistics xml off;
go

drop table dbo.t;
go
16 июн 18, 15:55    [21496178]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
Const123
Member

Откуда:
Сообщений: 26
invm
Вполне может быть решена и без CLR. Например, продемонстриролванным далее способом.

Спасибо за ценные советы. Все что касается непосредственно кода - весьма поучительно.
invm
Старички-классики, в отличие от вас, прекрасно знают цену решениям с SQLCLR и не пользуются ими без крайней нелобходимости.
Вы же пока не научились эффективно решать своли задачи на T-SQL. Об этом свидетельствует желание запихнуть sp_ececutesql в функцию.

И какова же эта цена? Дыра в безопасности, утечка памяти или что-то еще? Может зря вообще Microsoft сделал эту возможность?
А вот самоутверждение предыдущих ораторов за счет менее опытных в определенных вопросах оппонентов есть признаки невзоровщины. У него лекции "как довести конкурента по бизнесу до инсульта" тоже очень дорого стоят как и ваше драгоценное время, потраченное на нас, убогих ;)
17 июн 18, 09:40    [21497115]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
stells2
Member

Откуда: Оклахома Пригород Колымы
Сообщений: 898
Const123,
Задачи вычислений в БД стояли задолго до того как появилась возможность прикручивать UDF, и эти задачи успешно решались и не только аналитическими функциями.
Просто есть два варианта: с помощью грубой силы, как школьник - нагромоздить многостраничное решение, или грамотно, часто это намного проще.
17 июн 18, 09:54    [21497124]     Ответить | Цитировать Сообщить модератору
 Re: Агрегантая функция пользователя  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
Const123
но вот в чем загвоздка: в результирующий набор попадает только последнее значение выборки, а первое - нет.
У меня такая же ошибка, как и у вас. Метод Accumulate должен выглядеть так:
    public void Accumulate(SqlInt32 Value)
    {
        if (Value.IsNull)
            return;

        if (_count == 0)
        {
            _first = Value.Value;
            _last = Value.Value;
        }
        else
            if (Value.Value < _first)
                _first = Value.Value;
            else
                if (Value.Value > _last)
                _last = Value.Value;

        _count += 1;
    }

Const123
И какова же эта цена?
Падение производительности.
В меньшей степени - отсутствие должной гибкости.
Const123
Может зря вообще Microsoft сделал эту возможность?
Для реализации того, что невозможно или весьма затруднительно/эффективно реализовать на TSQL.
17 июн 18, 10:54    [21497187]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить