Create a function with whole columns as input and output

Question

I have several programs written in R that now I need to translate in T-SQL to deliver them to the client. I am new to T-SQL and I'm facing some difficulties in translating all my R functions.

An example is the numerical derivative function, which for two input columns (values and time) would return another column (of same length) with the computed derivative.

My current understanding is:

1. I can't use SP, because I'll need to use this functions inline with select statement, like: SELECT Customer_ID, Date, Amount, derivative(Amount, Date) FROM Customer_Detail

2. I can't use UDF, because they can take, as input parameter, only scalar. I'll need vectorised function due to speed and also because for some functions I have, like the one above, running row by row wouldn't be meaningful (for each value it needs the next and the previous)

3. UDA take whole column but, as the name says..., they will aggregate the column like sum or avg would.

If the above is correct, which other techniques would allow me to create the type of function I need? An example of SQL built-in function similar to what I'm after is square() which (apparently) takes a column and returns itself^2. My goal is creating a library of functions which behave like square, power, etc. But internally it'll be different cause square takes and returns each scalar is read through the rows. I would like to know if is possible to have User Defied with an accumulate method (like the UDA) able to operates on all the data at the end of the import and then return a column of the same length?

NB: At the moment I'm on SQL-Server 2005 but we'll switch soon to 2012 (or possibly 2014 in few months) so answers based on any 2005+ version of SQL-Server are fine.

EDIT: added the R tag for R developers who have, hopefully, already faced such difficulties.

EDIT2: Added CLR tag: I went through CLR user defined aggregate as defined in the Pro t-sql 2005 programmers guide. I already said above that this type of function wouldn't fit my needs but it was worth looking into it. The 4 methods needed by a UDA are: Init, Accumulate, Merge and Terminate. My request would need the whole data being analysed all together by the same instance of the UDA. So options including merge methods to group together partial results from multicore processing won't be working.

Answer 1

I think you may consider changing your mind a bit. SQL language is very good when working with sets of data, especially modern RDBMS implementations (like SQL Server 2012), but you have to think in sets, not in rows or columns. While I stilldon't know your exact tasks, let's see - SQL Server 2012 have very nice set of window functions + ranking functions + analytic functions + common table expressions, so you can write almost any query inline. You can use chains of common table expression to turn your data any way you want, to calculate running totals, to calculate averages or other aggregates over window and so on.

Actually, I've always liked SQL and when I've learned functional language (ML and Scala) a bit, my thought was that my approach to SQL is very similar to functional language paradigm - just slicing and dicing data without saving anything into variables, untils you have resultset your need.

Just quick example, here's a question from SO - How to get average of the 'middle' values in a group?. The goal was to get the average for each group of the middle 3 values:

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

For me, it's not an easy task to do in R, but in SQL it could be a really simple query like this:

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

You can also easily expand this query to get 5 values around the middle.

TAke closer look to analytical functions, try to rethink your calculations in terms of window functions, may be it's not so hard to rewrite your R procedures in plain SQL.

Hope it helps.

Answer 2

I would solve this by passing a reference to the record(s) you want to process, and use so called "inline table-valued function" to return the record(s) after processing the initial records.

You find the table-function reference here: http://technet.microsoft.com/en-en/library/ms186755.aspx

A Sample:

    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 would be the Primary-Key of the Records you want to process.

Table-Function can afterwards be joined to other Query results if you want to process more than one record at once.

Here is a Sample:

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

Using a normal Stored Procedure would do the same more or less, but it's a bit tricky to work with the results programmatically.

But keep one thing in mind: SQL-Server is not really good for "functional-programming". It's brilliant working with data and sets of data, but the more you use it as a "application server" the more you will realize it's not made for that.

Answer 3

I don't think this is possible in pure T-SQL without using cursors. But with cursors, stuff will usually be very slow. Cursors are processing the table row-by/row, and some people call this "slow-by-slow".

But you can create your own aggregate function (see Technet for more details). You have to implement the function using the .NET CLR (e.g. C# or R.NET).

For a nice example see here.

I think interfacing R with SQL is a very nice solution. Oracle is offering this combo as a commercial product, so why not going the same way with SQL Server.

When integrating R in the code using the own aggregate functions, you will only pay a small performance penalty. Own aggregate functions are quite fast according to the Microsoft documentation: "Managed code generally performs slightly slower than built-in SQL Server aggregate functions". And the R.NET solution seems also to be quite fast by loading the native R DLL directly in the running process. So it should be much faster than using R over ODBC.

Answer 4

ORIGINAL RESPONSE:

if you know already what are the functions you will need one of the approach I can think of is, creating one In-Line function for each method/operation you want to apply per table. what I mean by that? for example you mentioned FROM Customer_Detail table when you select you might want need one method "derivative(Amount, Date)". let's say second method you might need (I am just making up for explanation) is "derivative1(Amount1, Date1)". we create two In-Line Functions, each will do its own calculation inside function on intended columns and also returns remaining columns as it is. that way you get all columns as you get from table and also perform custom calculation as a set-based operation instead scalar operation. later you can combine the Independent calculation of columns in same function if make sense. you can still use this all functions and do JOIN to get all custom calculation in single set if needed as all functions will have common/unprocessed columns coming as it is. see the example below.

    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
                            ,totalqty+@newqty AS totalqty
                            ,uprice+@newuprice 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
                            ,totalqty+@newqty AS totalqty
                            ,uprice+@newuprice 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

also check the output as shown below.

EDIT2:

three point smoothing Algorithms

    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)

Answer 5

I think you may need to break you functionalities into two parts - into UDA which can work on scopes thank to OVER (...) clause and formulas which combine the result scalars.

What you are asking for - to define objects in such a way as to make it a aggregate/scalar combo - is probably out of scope of regular SQL Server's capabilities, unless you fall back into CLR code the effectively would be equivalent to cursor in terms of performance or worse.

Your best shot is to probably defined SP (I know you don't what that) that will produce the whole result. Like create [derivative] stored procedure that will take in parameters with table and column names as parameters. You can even expand on the idea but in the end that's not what you want exactly.

Answer 6

Since you mention you will be upgrading to SQL Server 2012 - SQL Server 2008 introduced Table Valued Parameters

This feature will do what you want. You will have to define a User Defined Type (UDT) in your DB which is like a table definition with columns & their respective types.

You can then use that UDT as a parameter type for any other stored procedure or function in your DB.

You can combine these UDTs with CLR integration to achieve what you require.

As mentioned SQL is not good when you are comparing rows to other rows, it's much better at set based operations where every row is treated as an independent entity. But, before looking at cursors & CLR, you should make sure it can't be done in pure TSQL which will almost always be faster & scale better as your table grows.

One method for comparing rows based on order is wrap your data in a CTE, adding a ranking function like ROW_NUMBER to set the row order, followed by a self-join of the CTE onto itself.

The join will be performed on the ordered field e.g. ROW_NUMBER=(ROW_NUMBER-1)

Look at this article for an example