Создайте функцию со целыми столбцами в качестве входных и выходных данных

У меня есть несколько программ, написанных на R которые теперь мне нужно перевести в T-SQL, чтобы доставить их клиенту. Я новичок в T-SQL, и мне приходится сталкиваться с некоторыми трудностями при переводе всех моих R функций.

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

Мое настоящее понимание:

  1. Я не могу использовать SP, потому что мне нужно будет использовать эти функции inline с помощью оператора select , например: SELECT Customer_ID, Date, Amount, derivative(Amount, Date) FROM Customer_Detail

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

  3. UDA принимает целую колонку, но, как сказано в названии …, они будут агрегировать столбец, например, sum или avg .

Если это правильно, какие другие методы позволят мне создать тип функции, который мне нужен? Примером встроенной функции SQL аналогичной той, для которой я являюсь, является square() который (по-видимому) занимает столбец и возвращает себя ^ 2. Моя цель – создать библиотеку функций, которые ведут себя как square , power и т. Д. Но внутренне это будет другая причина square и каждый скаляр считывается через строки. Я хотел бы знать, возможно ли, чтобы Пользователь Defied с помощью метода накопления (например, UDA) мог работать со всеми данными в конце импорта и затем возвращать столбец той же длины?

NB: На данный момент я на SQL-Server 2005, но мы скоро перейдем к 2012 году (или, возможно, 2014 через несколько месяцев), поэтому ответы на основе любой версии SQL Server 2005 версии 2005 года прекрасны.

EDIT: добавлен тег R для разработчиков R, которые, надеюсь, уже столкнулись с такими трудностями.

EDIT2: Добавлен тег CLR : я прошел через CLR пользовательскую совокупность, определенную в руководстве для программистов Pro t-sql 2005. Я уже сказал выше, что этот тип функции не соответствует моим потребностям, но стоит заглядывать в нее. 4 метода, которые необходимы UDA: Init , Accumulate , Merge и Terminate . Мой запрос потребует, чтобы все данные анализировались вместе одним и тем же экземпляром UDA . Таким образом, параметры, включая методы merge для группировки частичных результатов многоядерной обработки, не будут работать.

Я думаю, вы можете подумать о том, чтобы немного изменить свое мнение. Язык SQL очень хорош при работе с наборами данных, особенно с современными реализациями RDBMS (например, SQL Server 2012), но вы должны думать в наборах, а не в строках или столбцах. Хотя я еще не знаю ваших точных задач, давайте посмотрим – у SQL Server 2012 очень хороший набор функций окна + ранжирование + аналитические функции + общие выражения таблиц , поэтому вы можете писать практически любой запрос inline. Вы можете использовать цепочки общего табличного выражения, чтобы превращать ваши данные так, как вы хотите, вычислять текущие итоги, вычислять средние значения или другие агрегаты над окном и т. Д.

На самом деле, мне всегда нравился SQL, и когда я изучил функциональный язык (ML и Scala) немного, я думал, что мой подход к SQL очень похож на парадигму функционального языка – просто нарезать и нарезать данные, не сохраняя ничего в переменных , до тех пор, пока у вас не будет результатов.

Просто быстрый пример , вот вопрос от SO – Как получить среднее значение «средних» значений в группе? , Цель состояла в том, чтобы получить среднее значение для каждой группы средних значений 3:

 TEST_ID TEST_VALUE GROUP_ID 1 5 1 -+ 2 10 1 +- these values for group_id = 1 3 15 1 -+ 4 25 2 -+ 5 35 2 +- these values for group_id = 2 6 5 2 -+ 7 15 2 8 25 3 9 45 3 -+ 10 55 3 +- these values for group_id = 3 11 15 3 -+ 12 5 3 13 25 3 14 45 4 +- this value for group_id = 4 

Для меня это непростая задача в R, но в SQL это может быть очень простой запрос:

 with cte as ( select *, row_number() over(partition by group_id order by test_value) as rn, count(*) over(partition by group_id) as cnt from test ) select group_id, avg(test_value) from cte where cnt <= 3 or (rn >= cnt / 2 - 1 and rn <= cnt / 2 + 1) group by group_id 

Вы также можете легко расширить этот запрос, чтобы получить 5 значений по середине.

TAke ближе к аналитическим функциям , попытайтесь переосмыслить свои вычисления с точки зрения функций окна, может быть, не так сложно переписать ваши R-процедуры в простом SQL.

Надеюсь, поможет.

Я бы решил это, передав ссылку на запись (записи), которую вы хотите обработать, и используйте так называемую «встроенную функцию, ориентированную на таблицу», чтобы вернуть запись (записи) после обработки исходных записей.

Вы найдете ссылку на таблицу-функцию здесь: http://technet.microsoft.com/en-en/library/ms186755.aspx

Образец:

  CREATE FUNCTION Sales.CustomerExtendedInfo (@CustomerID int) RETURNS TABLE AS RETURN ( SELECT FirstName + LastName AS CompleteName, DATEDIFF(Day,CreateDate,GetDate()) AS DaysSinceCreation FROM Customer_Detail WHERE CustomerID = @CustomerID ); GO 

StoreID будет основным ключом записей, которые вы хотите обработать.

После этого функция «Таблица» может быть присоединена к другим результатам запроса, если вы хотите обработать более одной записи одновременно.

Вот пример:

 SELECT * FROM Customer_Detail CROSS APPLY Sales.CustomerExtendedInfo (CustomerID) 

Использование обычной хранимой процедуры будет делать то же самое более или менее, но немного сложно работать с результатами программно.

Но имейте в виду: SQL-сервер не очень хорош для «функционального программирования». Это блестящая работа с данными и наборами данных, но чем больше вы используете ее как «сервер приложений», тем больше вы поймете, что это не сделано для этого.

Я не думаю, что это возможно в чистом T-SQL без использования курсоров. Но с курсорами материал обычно будет очень медленным. Курсоры обрабатывают таблицу row-by / row, и некоторые люди называют это «медленным замедленным».

Но вы можете создать свою собственную совокупную функцию (подробнее см. В разделе « Техника» ). Вы должны реализовать эту функцию с помощью .NET CLR (например, C # или R.NET ).

Для хорошего примера см. Здесь .

Я считаю, что взаимодействие R с SQL – очень приятное решение. Oracle предлагает эту комбо в качестве коммерческого продукта , поэтому почему бы не пойти аналогичным образом с SQL Server.

При интегрировании R в код с использованием собственных агрегатных функций вы будете платить только небольшое штрафное вознаграждение. Собственные совокупные функции довольно быстро соответствуют документации Microsoft: «Управляемый код обычно выполняет несколько медленнее, чем встроенные функции агрегации SQL Server» . И решение R.NET также должно быть довольно быстрым, загружая собственную R DLL непосредственно в текущем процессе . Поэтому он должен быть намного быстрее, чем использование R поверх ODBC.

ОРИГИНАЛЬНЫЙ ОТВЕТ:

если вы уже знаете, какие функции вам понадобятся, один из подходов, о котором я могу думать, – создание одной функции In-Line для каждого метода / операции, которые вы хотите применить для каждой таблицы. что я имею в виду? например, вы упомянули из таблицы Customer_Detail, когда вы выберете, вам может понадобиться один метод «производная (сумма, дата)». скажем, второй метод, который вам может понадобиться (я просто придумываю для объяснения), является «производным1 (сумма1, дата1)». мы создаем две встроенные функции, каждый из которых будет выполнять собственные вычисления внутри функции по назначенным столбцам, а также возвращает оставшиеся столбцы, как есть. таким образом вы получаете все столбцы по мере того, как получаете от таблицы, а также выполняете пользовательский расчет как операцию на основе набора вместо скалярной операции. позже вы можете совместить независимый расчет столбцов в одной и той же функции, если это имеет смысл. вы все равно можете использовать все эти функции и сделать JOIN, чтобы получить все пользовательские вычисления в одном наборе, если это необходимо, поскольку все функции будут иметь общие / необработанные столбцы, которые будут такими, какие есть. см. пример ниже.

  IF object_id('Product','u') IS NOT NULL DROP TABLE Product GO CREATE TABLE Product ( pname sysname NOT NULL ,pid INT NOT NULL ,totalqty INT NOT NULL DEFAULT 1 ,uprice NUMERIC(28,10) NOT NULL DEFAULT 0 ) GO INSERT INTO Product( pname, pid, totalqty, uprice ) SELECT 'pen',1,100,1.2 UNION ALL SELECT 'book',2,300,10.00 UNION ALL SELECT 'lock',3,500,15.00 GO IF object_id('ufn_Product_totalValue','IF') IS NOT NULL DROP FUNCTION ufn_Product_totalValue GO CREATE FUNCTION ufn_Product_totalValue ( @newqty int ,@newuprice numeric(28,10) ) RETURNS TABLE AS RETURN ( SELECT pname,pid,totalqty,uprice,totalqty*uprice AS totalValue FROM ( SELECT pname ,pid ,[email protected] AS totalqty ,[email protected] AS uprice FROM Product )qry ) GO IF object_id('ufn_Product_totalValuePct','IF') IS NOT NULL DROP FUNCTION ufn_Product_totalValuePct GO CREATE FUNCTION ufn_Product_totalValuePct ( @newqty int ,@newuprice numeric(28,10) ) RETURNS TABLE AS RETURN ( SELECT pname,pid,totalqty,uprice,totalqty*uprice/100 AS totalValuePct FROM ( SELECT pname ,pid ,[email protected] AS totalqty ,[email protected] AS uprice FROM Product )qry ) GO SELECT * FROM ufn_Product_totalValue(10,5) SELECT * FROM ufn_Product_totalValuepct(10,5) select tv.pname,tv.pid,tv.totalValue,pct.totalValuePct from ufn_Product_totalValue(10,5) tv join ufn_Product_totalValuePct(10,5) pct on tv.pid=pct.pid 

также проверьте выход, как показано ниже. введите описание изображения здесь

EDIT2:

Алгоритмы сглаживания трех точек

введите описание изображения здесь

  IF OBJECT_ID('Test3PointSmoothingAlgo','u') IS NOT NULL DROP TABLE Test3PointSmoothingAlgo GO CREATE TABLE Test3PointSmoothingAlgo ( qty INT NOT NULL ,id INT IDENTITY NOT NULL ) GO INSERT Test3PointSmoothingAlgo( qty ) SELECT 10 UNION SELECT 20 UNION SELECT 30 GO IF object_id('ufn_Test3PointSmoothingAlgo_qty','IF') IS NOT NULL DROP FUNCTION ufn_Test3PointSmoothingAlgo_qty GO CREATE FUNCTION ufn_Test3PointSmoothingAlgo_qty ( @ID INT --this is a dummy parameter ) RETURNS TABLE AS RETURN ( WITH CTE_3PSA(SmoothingPoint,Coefficients) AS --finding the ID of adjacent points ( SELECT id,id FROM Test3PointSmoothingAlgo UNION SELECT id,id-1 FROM Test3PointSmoothingAlgo UNION SELECT id,id+1 FROM Test3PointSmoothingAlgo ) --Apply 3 point Smoothing algorithms formula SELECT a.SmoothingPoint,SUM(ISNULL(b.qty,0))/3 AS Qty_Smoothed--this is a using 3 point smoothing algoritham formula FROM CTE_3PSA a LEFT JOIN Test3PointSmoothingAlgo b ON a.Coefficients=b.id GROUP BY a.SmoothingPoint ) GO SELECT SmoothingPoint,Qty_Smoothed FROM dbo.ufn_Test3PointSmoothingAlgo_qty(NULL) 

введите описание изображения здесь

Я думаю, вам может понадобиться разбить функциональность на две части – в UDA, которая может работать в облаках благодаря предложению OVER (...) и формулам, которые объединяют скаляры результата.

То, что вы запрашиваете, – определять объекты таким образом, чтобы сделать его совокупным / скалярным комбо, – вероятно, выходит за рамки обычных возможностей SQL Server, если вы не вернетесь в код CLR, это будет эффективно эквивалентно курсору в терминах производительности или хуже.

Ваш лучший шанс – это, вероятно, определить SP (я знаю, что вы не знаете, что это такое), который даст весь результат. Как создать хранимую процедуру [производная], которая будет принимать параметры с именами таблиц и столбцов в качестве параметров. Вы можете даже расширить идею, но в конце концов это не то, что вы хотите точно.

Поскольку вы упоминаете, что будете обновляться до SQL Server 2012 – SQL Server 2008 представил Табличные параметры

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

Затем вы можете использовать этот UDT в качестве типа параметра для любой другой хранимой процедуры или функции в вашей БД.

Вы можете объединить эти UDT с интеграцией CLR для достижения того, что вам нужно.

Как упоминалось, SQL не очень хорош, когда вы сравниваете строки с другими строками, гораздо лучше при работе на основе набора, где каждая строка рассматривается как независимая сущность. Но, прежде чем смотреть на курсоры и CLR, вы должны убедиться, что это невозможно сделать в чистом TSQL, который почти всегда будет быстрее и масштабироваться по мере роста вашей таблицы.

Один метод сравнения строк на основе порядка – это перенос ваших данных в CTE, добавление функции ранжирования, такой как ROW_NUMBER, для установки порядка строк, а затем самосоединение CTE на себя.

Соединение будет выполнено в упорядоченном поле, например ROW_NUMBER = (ROW_NUMBER-1)

Посмотрите на эту статью для примера

  • Как читать временные метки utc с сервера Sql с использованием RODBC в R?
  • Доступ к файловой системе CLR Sql Server из UDF
  • Службы SQL Server R - вывод данных в таблицу базы данных, производительность
  • Microsoft SQL и R, хранимая процедура и k-средства
  • Как передать две таблицы sql в качестве входного параметра для r-кодов в SQL Server
  • Невозможно добавить в таблицу SQL Server с помощью sqlSave в R
  • RevoScaleR: rxPredict, количество параметров не соответствует числу переменных
  • Расчет количества каждого продукта пары для каждого заказа. Разделенный на количество каждого продукта и вставка результата в таблицу корреляции.
  • RODBC вытягивает float как символ в SQL Server
  • Как создать таблицу SQL Server из dplyr-конвейера
  • Параметрированный SQL-запрос с помощью RODBC
  • Interesting Posts

    устаревший драйвер JTDS?

    Лучший способ сравнить содержимое двух плоских файлов

    Преобразование столбцов в строки из 2 таблиц

    freebcp: «Данные Unicode – это размер нечетного байта для столбца. Должен быть даже размер байта "

    SQL Server 2005 Express успешно установлен, но его нигде не найти!

    Как создать уникальный код в INSERT внутри MERGE?

    T-SQL-параметр «на лету» при вызове хранимой процедуры

    В результирующем наборе SQL XQuery существует ли способ заставить оператор «value» возвращать значение null для отсутствующих элементов?

    Как отображать дубликаты записей в SQL Server

    TSQL округляется до половины десятичного знака

    Совокупная функция с предложением where

    Преобразование даты в формате 12Hour SQL Server

    Горизонтальное разделение данных на «архив» в SQL Server на выполнение месяцев?

    Синтаксис SQL для обновления моей таблицы Date Dimension с кодами календарного года

    sql-запрос записывает пару из двух

    Давайте будем гением компьютера.