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

Откуда:
Сообщений: 6
Здравствуйте,

мне нужно сделать групповую функцию Coalesce. Аналог Group_Concat.
+ Для чего мне это нужно
В разных папках у меня лежат неполные наборы файлов к определенному документу xml. По документу xml я нахожу лежащие рядом с ним в одной папке и относящиеся к нему файлы. Я их все заношу в табличку вида:
file_stream.xml file_stream.xml.sig file_stream.pdffile_stream.pdf.sig
0x514684135481 NULL 0x35434318 NULL
0x514684135481 0x51238431 NULL NULL
0x514684135481NULLNULL0x1483434843
Получается несколько строк, относящихся к одному документу xml. Причем в некоторых строчках есть один документ, но нет другого. Групповой функцией Coalesce хочу собрать полный комплект документов.

+ Я попробовал сделать ее с помощью clr

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

namespace MSSQLServicesExtensionByFFFFF
{
	/// <summary> 
	/// Пользовательская агрегатная функция, которая выбирает первое встретившееся не null значение. Аналог Coalesce
	/// </summary> 
	[Serializable]
	[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined,
		IsInvariantToDuplicates = true,		//так как выбирается первое не null-значение, то не учитывает дубликаты
		IsInvariantToNulls = true,			//не учитывает null
		IsInvariantToOrder = false,			//используется первое встретившееся значение
		IsNullIfEmpty = true,                         //возвращает null, если накполенное значение отсутствует
		MaxByteSize = -1,
		Name = "Coalesced")]
	public struct Coalesced: IBinarySerialize
	{
		public void Init()
		{
			_firstNotNullVal = null;
			IsNull = true;
		}

		public void Accumulate(Object Value)
		{
			if (IsNull && Value != null)
			{
				_firstNotNullVal = Value;
				IsNull = false;
			}
		}

		public void Merge(Coalesced Group)
		{
			if (IsNull && !Group.IsNull)
			{
				_firstNotNullVal = Group._firstNotNullVal;
				IsNull = false;
			}
		}
	
		public Object Terminate()
		{
			return IsNull ? SqlString.Null : _firstNotNullVal;
		}

		public void Read(BinaryReader r)
		{
			IsNull = r.ReadBoolean();
			if(!IsNull)
				_firstNotNullVal = r.ReadString();
		}
		
		public void Write(BinaryWriter w)
		{
			w.Write(IsNull);
			if(!IsNull)
				w.Write(_firstNotNullVal.ToString());
		}

		public Object _firstNotNullVal;
		private bool IsNull;
	}
}



В какой-то степени clr-функция работает, но не возвращает NULL так как должна бы.
+ Пример запроса и результат

DECLARE @d DATE	 = CAST('20101010' AS DATE);

SELECT a AS Группа, Services.Coalesced(b) AS Объединение, NULL AS "Пример каким должен быть NULL"
FROM (VALUES(1,NULL),
			(2,NULL),
			(1,NULL),
			(2,NULL),
			(1,@d) 
			)AS D(a,b)
GROUP BY a;

Группа Объединение Пример каким должен быть NULL
110.10.2010 0:00:00 NULL
2NULL




Подскажите, пожалуйста, что я не так сделал.
23 окт 16, 22:46    [19813817]     Ответить | Цитировать Сообщить модератору
 Re: Group Coalesce  [new]
gma-FFFFF
Member

Откуда:
Сообщений: 6
Кстати, я в конце концов понял, что эту задачу можно попробывать решить с помощью функции MAX(), но люблю доводить дело до конца.
23 окт 16, 22:59    [19813842]     Ответить | Цитировать Сообщить модератору
 Re: Group Coalesce  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5146
gma-FFFFF
хочу собрать полный комплект документов.
вы опишите, что в итоге хотите получить... пока это выглядит как "ощупывание гланд через жо".
почему сразу не писать в таблицу так как потом удобно использовать?
- например, не писать каждый раз новую строку, а апдейтить уже существующую
- или писать все файлы в одно поле
23 окт 16, 23:07    [19813857]     Ответить | Цитировать Сообщить модератору
 Re: Group Coalesce  [new]
gma-FFFFF
Member

Откуда:
Сообщений: 6
Дедушка,

Суть проблемы в том, что нужные файлы собирались разными людьми, в разное время. Кто-то сохранял только xml файлы, кто-то оставлял доп. файлы. кто-то вообще оставлял только архив с этими файлами-исходный формат(об обработке архивов я пока не думал). Файлы могли дублироваться в разных местах на общей сетевой папке, т.к. централизованного хранилища нет.
Я решил покончить с эти безобразием и свести их все в БД. Sql server предлагает очень удобный инструмент -FileTable. С его поомщью я создаю 2 файловые таблицы-одну inbox, вторую - doc. В первую я планирую просто побросать все возможные файлы с xml документыми и с помощью sql servera перенести их системным образом в таблицу doc. В дальнейшем пользователи смогут попрежнему бессистемно скидывать фалы, но уже в FileTable inbox, а хранимая процедура будет находить новые xml документы(или недостающие) файлы и сохранять их системно в таблице doc.
Учитывая изложенное в таблице inbox файлы уже разложены по строкам, а апдейтить каждую строку можно, но тут выглядывают курсоры или внешние приложения. Я честно говоря не являюсь программистом вообще, да и база мальнькая чтобы я мог жаловаться на хоть какое-нибудь падение производительности, но хочется все сделать правильно, т.е. за счет реляционных запросов.
23 окт 16, 23:40    [19813906]     Ответить | Цитировать Сообщить модератору
 Re: Group Coalesce  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21495
max(field)
-- в крайнем случае
max(coalesce(field,''))
24 окт 16, 00:00    [19813944]     Ответить | Цитировать Сообщить модератору
 Re: Group Coalesce  [new]
gma-FFFFF
Member

Откуда:
Сообщений: 6
спасибо за совет. Сейчас я и сам вижу, что пошёл по глубоко ошибочному пути. Это все простота создания пользовательских агрегатов виновата-вместо того чтобы 15 минут потратить на обдумывание и поиск готовых решений, решил за 10 минут запилить свой велосипед. Хорошо хоть споткнулся о подводный камень.
Теперь я даже не уверен где вообще можно использовать групповой coalesce, разве, что так :
gr_coalesce("var") over (partition by "gr" order by "weight") чтобы выбрать не null значение в соответствии с его весом. И то не уверен, что это будет работать.
24 окт 16, 09:37    [19814379]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить