StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCreate a function with whole columns as input and output
text
Body
copied!<p>I have several programs written in <code>R</code> 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 <code>R</code> functions.</p> <p>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.</p> <p>My current understanding is:</p> <ol> <li><p>I can't use SP, because I'll need to use this functions inline with <code>select</code> statement, like: <code>SELECT Customer_ID, Date, Amount, derivative(Amount, Date) FROM Customer_Detail</code></p></li> <li><p>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)</p></li> <li>UDA take whole column but, as the name says..., they will aggregate the column like <code>sum</code> or <code>avg</code> would.</li> </ol> <p><strong>If the above is correct, which other techniques would allow me to create the type of function I need?</strong> An example of <code>SQL</code> built-in function similar to what I'm after is <code>square()</code> which (apparently) takes a column and returns itself^2. My goal is creating a library of functions which behave like <code>square</code>, <code>power</code>, etc. But internally it'll be different cause <code>square</code> takes and returns each scalar is read through the rows. I would like to know if <strong>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?</strong></p> <p>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.</p> <p>EDIT: added the <code>R</code> tag for R developers who have, hopefully, already faced such difficulties.</p> <p>EDIT2: Added <code>CLR</code> tag: I went through <code>CLR</code> 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: <code>Init</code>, <code>Accumulate</code>, <code>Merge</code> and <code>Terminate</code>. My request would need the whole data being analysed all together by the same instance of the <code>UDA</code>. So options including <code>merge</code> methods to group together partial results from multicore processing won't be working.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload