Невозможно передать нулевое значение в пользовательский агрегат

Question

После полудня,

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

Вы получаете эту ошибку...

Msg 6569, Level 16, State 1, Line 11 'Median' failed because parameter 1 is not allowed to be null.

C#:

 namespace SQLMedianAggregate
{
    [System.Serializable]
    [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
   Microsoft.SqlServer.Server.Format.UserDefined,
   IsInvariantToDuplicates = false, // duplicates may change results
   IsInvariantToNulls = true,      // receiving a NULL is handled later in code 
   IsInvariantToOrder = true,       // is sorted later
   IsNullIfEmpty = true,            // if no values are given the result is null
        MaxByteSize = -1,
   Name = "Median"                 // name of the aggregate
)]

    public struct Median : IBinarySerialize
    {
        public double Result { get; private set; }

        public bool HasValue { get; private set; }

        public DataTable DT_Values { get; private set; } //only exists for merge essentially

        public static DataTable DT_Final { get; private set; } //Need a static version so its accesible within terminate

        public void Init()
        {
            Result = double.NaN;
            HasValue = false;
            DT_Values = new DataTable();
            DT_Values.Columns.Add("Values", typeof(double));
            DT_Final = new DataTable();
            DT_Final.Columns.Add("Values", typeof(double));
        }

        public void Accumulate(double number)
        {

            if (double.IsNaN(number))
            {
                //skip
            }
            else
            {
                //add to tables
                DataRow NR = DT_Values.NewRow();
                NR[0] = number;
                DT_Values.Rows.Add(NR);
                DataRow NR2 = DT_Final.NewRow();
                NR2[0] = number;
                DT_Final.Rows.Add(NR2);
                HasValue = true;
            }
        }

        public void Merge(Median group)
        {
            // Count the product only if the other group has values
            if (group.HasValue)
            {
                DT_Final.Merge(group.DT_Values);
                //DT_Final = DT_Values;
            }
        }

        public double Terminate()
        {
            if (DT_Final.Rows.Count == 0) //Just to handle roll up so it doesn't crash (doesnt actually work
            {
                DataRow DR = DT_Final.NewRow();
                DR[0] = 0;
                DT_Final.Rows.Add(DR);
            }
            //Sort Results
            DataView DV = DT_Final.DefaultView;
            DV.Sort = "Values asc";
            DataTable DTF = new DataTable();
            DTF = DV.ToTable();

            ////Calculate median and submit result
            double MiddleRow = (DT_Final.Rows.Count -1.0) / 2.0;
            if (MiddleRow % 2 != 0)
            {

                double upper =  (double)(DT_Final.Rows[Convert.ToInt32(Math.Ceiling(MiddleRow))]["Values"]);
                double lower =  (double)(DT_Final.Rows[Convert.ToInt32(Math.Floor(MiddleRow))]["Values"]);
                Result = lower + ((upper - lower) / 2);

            } else
            {
                Result = (double)(DT_Final.Rows[Convert.ToInt32(MiddleRow)]["Values"]);
            }
            return Result;
        }

        public void Read(BinaryReader SerializationReader)
        {
            //Needed to get this working for some reason
        }

        public void Write(BinaryWriter SerializationWriter)
        {
            //Needed to get this working for some reason
        }

    }
}

SQL:

DROP AGGREGATE dbo.Median
DROP ASSEMBLY MedianAggregate
CREATE ASSEMBLY MedianAggregate
AUTHORIZATION dbo
FROM 'C:\Users\#######\Documents\Visual Studio 2017\Projects\SQLMedianAggregate\SQLMedianAggregate\bin\Debug\SQLMedianAggregate.dll'
WITH PERMISSION_SET = UNSAFE;


CREATE AGGREGATE dbo.Median (@number FLOAT) RETURNS FLOAT
EXTERNAL NAME [MedianAggregate]."SQLMedianAggregate.Median";

Любые идеи о том, что настройки или код, который я пропускаю, позволит это. Я просто хочу, чтобы он игнорировал нули.

Версия SQL является SQL2008 R2 между прочим

Answer 1

Проблема в вашем типе данных. Вам нужно использовать Sql* типы для параметров SQLCLR, возвращаемых значений и столбцов набора результатов. В этом случае вам необходимо изменить:

Accumulate(double number)

в:

Accumulate(SqlDouble number)

Затем вы получаете доступ к double значение с помощью Value свойство, которое все Sql* типы имеют (т.е. number.Value в этом случае).

А потом, в начале Accumulate метод, вам нужно проверить NULL с использованием IsNull имущество:

if (number.IsNull)
{
  return;
}

Кроме того, для получения дополнительной информации об использовании SQLCLR в целом см. Серию статей, которые я пишу на эту тему по SQL Server Central: Лестница в SQLCLR (для чтения содержимого на этом сайте требуется бесплатная регистрация, но это того стоит:-).

И, поскольку мы говорим о медианных вычислениях здесь, см. Статью, которую я написал (также в SQL Server Central), на тему UDA и UDT, в которой в качестве примера используется Median: получение максимальной отдачи от UDT и UDA SQL Server 2005. Пожалуйста, имейте в виду, что статья была написана для SQL Server 2005 с жестким ограничением в 8000 байт памяти для UDT и UDA. Этот предел был снят в SQL Server 2008, поэтому вместо использования метода сжатия, показанного в этой статье, вы можете просто установить MaxByteSize в SqlUserDefinedAggregate в -1 (как вы делаете в настоящее время) или SqlMetaData.MaxSize (или что-то очень близкое к этому).

Также, DataTable является немного жестким для этого типа операции. Все, что вам нужно, это простой List<Double>:-).

---

Относительно следующей строки кода (разбито на 2 строки, чтобы избежать необходимости прокрутки):

public static DataTable DT_Final { get; private set; }
   //Need a static version so its accesible within terminate

Это огромное недопонимание того, как работают UDA и UDT. Пожалуйста, НЕ используйте статические переменные здесь. Статические переменные являются общими для всех сессий, поэтому ваш текущий подход не является потокобезопасным. Таким образом, вы либо получите ошибки об уже объявленном объявлении, либо различные сеансы изменят значение, неизвестное другим сеансам, так как все они будут использовать один экземпляр DT_Final, А ошибки и / или странное поведение (т.е. ошибочные результаты, которые вы не можете отладить) могут произойти в одном сеансе, если используется параллельный план.

UDT и UDA сериализуются в двоичное значение, хранящееся в памяти, а затем десериализуются, что сохраняет их состояние без изменений. Это причина Read а также Write методы, и почему вам нужно, чтобы те работали.

Опять же, вам не нужно (или хотите) DataTables здесь, поскольку они чрезмерно усложняют работу и занимают больше памяти, чем идеально. Пожалуйста, ознакомьтесь со статьей UDA и UDT, на которую я ссылался выше, чтобы узнать, как должна работать операция Median (и UDA в целом).