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

Откуда: Алматы, Казахстан
Сообщений: 53
Всем, привет!

Написал CLR-функцию для агрегации строковых значений:
+
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined,
    IsInvariantToNulls = true,
    IsInvariantToDuplicates = false,
    IsInvariantToOrder = false,
    MaxByteSize = -1)
]
public class StrConcat : IBinarySerialize
{
    private StringBuilder intermediateResult;
    private Int32 delimiterLength;

    public void Init()
    {
        this.intermediateResult = new StringBuilder();
        this.delimiterLength = 0;
    }

    public void Accumulate(SqlString value, SqlString delimiter)
    {
        // игнорируем NULL значения
        if (value.IsNull)
        {
            return;
        }

        // добавляем новое значение в конец строки
        this.intermediateResult.Append(value.Value);

        // добавляем разделитель, если он был указан
        if (!delimiter.IsNull & delimiter.Value.Length > 0)
        {
            // запоминаем длину разделителя - чтобы в Terminate() его обрезать
            this.delimiterLength = delimiter.Value.Length;
            this.intermediateResult.Append(delimiter.Value);
        }
    }

    public void Merge(StrConcat other)
    {
        // объединяем результат с другим результатом
        this.intermediateResult.Append(other.Terminate());
    }

    public SqlString Terminate()
    {
        string output = string.Empty;

        if (this.intermediateResult != null & this.intermediateResult.Length > 0)
        {
            if (this.delimiterLength > 0)
            {
                // возврат строки без последнего разделителя
                output = this.intermediateResult.ToString(0, this.intermediateResult.Length - this.delimiterLength);
            }
            else
            {
                // т.к. разделитель не был задан, делаем возврат всей строки
                output = this.intermediateResult.ToString();
            }
        }

        return new SqlString(output);
    }

    public void Read(BinaryReader r)
    {
        // 0 - считывем строку
        this.intermediateResult = new StringBuilder(r.ReadString());
        // 1 - считываем длину разделителя
        this.delimiterLength = r.ReadInt32();
    }

    public void Write(BinaryWriter w)
    {
        // 0 - запоминаем строку
        w.Write(this.intermediateResult.ToString());
        // 1 - запоминаем длину разделителя
        w.Write(this.delimiterLength);
    }
}

Подключение функции:
+
sp_configure 'clr enabled', 1;
GO

RECONFIGURE WITH OVERRIDE;
GO

IF OBJECT_ID('dbo.StrConcat') IS NOT NULL
    DROP Aggregate StrConcat
GO 

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'StrAggFunc') 
    DROP assembly StrAggFunc; 
GO

CREATE ASSEMBLY StrAggFunc
AUTHORIZATION dbo
FROM 'd:\temp\MSSQLFunctions.dll'
WITH PERMISSION_SET = SAFE;
GO

CREATE AGGREGATE StrConcat (@value nvarchar(MAX), @delimiter nvarchar(10)) RETURNS nvarchar(max)
EXTERNAL NAME StrAggFunc.StrConcat;
GO

Тестовые данные:
CREATE TABLE BookAuthors
(
   BookID int NOT NULL,
   AuthorName nvarchar(200)
);

INSERT BookAuthors VALUES(1,'Johnson'),(2,'Taylor'),(3,'Steven'),(2,'Mayler'),(3,'Roberts'),(3,'Michaels'),(4,'Michaels'),(5,NULL),(6,'Alex');


Теперь описание проблемы
Если функцию вызывать в одном запросе несколько раз (например, один раз с DISTINCT, второй раз без него), то получаем следующую проблему:
SELECT
  dbo.StrConcat(DISTINCT AuthorName,', '), -- Alex, Johnson, Mayler, Michaels, Roberts, Steven, Taylor
  dbo.StrConcat(AuthorName,', ') -- AlexJohnsonMaylerMichaels, MichaelsRobertsStevenTaylor
FROM BookAuthors


Неправильным получился второй результат - AlexJohnsonMaylerMichaels, MichaelsRobertsStevenTaylor.

Если вызывать по отдельности, то данной проблемы не возникает:
-- ALL
SELECT
  dbo.StrConcat(AuthorName,', ') -- Johnson, Taylor, Steven, Mayler, Roberts, Michaels, Michaels, Alex
FROM BookAuthors

-- DISTINCT
SELECT
  dbo.StrConcat(DISTINCT AuthorName,', ') -- Alex, Johnson, Mayler, Michaels, Roberts, Steven, Taylor
FROM BookAuthors


Может кто сталкивался с такой проблемой и знает как ее можно решить?

Версия MS SQL - 2014 - 12.0.2269.0 (X64) Developer Edition (64-bit)
15 дек 15, 11:41    [18561687]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с агрегатной CLR-функцией  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
Leran2002
знает как ее можно решить?
Подумать на тем как работает Merge и почему при этом пропадает разделитель.
15 дек 15, 13:07    [18562224]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с агрегатной CLR-функцией  [new]
Leran2002
Member

Откуда: Алматы, Казахстан
Сообщений: 53
invm
Подумать на тем как работает Merge и почему при этом пропадает разделитель.


Большое спасибо, помогли!
+
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined,
    IsInvariantToNulls = true,
    IsInvariantToDuplicates = false,
    IsInvariantToOrder = false,
    MaxByteSize = -1)
]
public class StrConcat : IBinarySerialize
{
    private StringBuilder intermediateResult;
    private string delimiter;

    public void Init()
    {
        this.intermediateResult = new StringBuilder();
        this.delimiter = string.Empty;
    }

    public void Accumulate(SqlString value, SqlString delimiter)
    {
        // игнорируем NULL значения
        if (value.IsNull)
        {
            return;
        }

        // добавляем новое значение в конец строки
        this.intermediateResult.Append(value.Value);

        // добавляем разделитель, если он был указан
        if (!delimiter.IsNull & delimiter.Value.Length > 0)
        {
            // запоминаем разделитель - чтобы использовать в Merge() и Terminate()
            this.delimiter = delimiter.Value;
            // добавляем разделитель в конце строки
            this.intermediateResult.Append(this.delimiter);
        }
    }

    public void Merge(StrConcat other)
    {
        // объединяем результат с другим результатом
        this.intermediateResult.Append(other.Terminate());

        if (other.delimiter.Length > 0)
        {
            // запоминаем разделитель для использования в Terminate() этого класса
            this.delimiter = other.delimiter;
            // добавление разделителя после объединением
            this.intermediateResult.Append(other.delimiter);
        }
    }

    public SqlString Terminate()
    {
        string output = string.Empty;

        if (this.intermediateResult != null & this.intermediateResult.Length > 0)
        {
            if (this.delimiter.Length > 0)
            {
                // возврат строки без последнего разделителя
                output = this.intermediateResult.ToString(0, this.intermediateResult.Length - this.delimiter.Length);
            }
            else
            {
                // т.к. разделитель не был задан, делаем возврат всей строки
                output = this.intermediateResult.ToString();
            }
        }

        return new SqlString(output);
    }

    public void Read(BinaryReader r)
    {
        // 0 - считывем строку
        this.intermediateResult = new StringBuilder(r.ReadString());
        // 1 - считываем разделитель
        this.delimiter = r.ReadString();
    }

    public void Write(BinaryWriter w)
    {
        // 0 - запоминаем строку
        w.Write(this.intermediateResult.ToString());
        // 1 - запоминаем разделитель
        w.Write(this.delimiter);
    }
}
15 дек 15, 13:33    [18562423]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с агрегатной CLR-функцией  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Комментарии хоть и не везде, но там, где есть понравились, функциональны, взвешены


 // игнорируем NULL значения
        if (value.IsNull)
15 дек 15, 13:42    [18562496]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить