# Thread: User defined aggregate function clr

1. Registered User
Join Date
Dec 2010
Posts
3

## Unanswered: User defined aggregate function clr

Hi guys,

Is it possible to create a uda that will return different vales for each row? I mean in a similair way to rank()

What I want is to write something like LessthanMax as below

Select deptID, x , max(x) over (partition by deptid ) - x lessthanmax1, LessthanMax(x) over (partition by deptid ) lessthanmax2 from t

this would return
deptid x lessthanmax1 lessthanmax2
1 9 0 0
1 4 5 5
1 2 7 7
2 12 0 0
2 8 4 4

Ive tried to do this with clr in c# but I can get it to return a different value for each row. It makes sense that aggregates work like that but is there a way around it?

My actual requirement is more complicated, but this is the main sticking point at the moment

bp

2. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
I don't see any difference between LessThanMax1 and LessThanMax2.

It would not seem necessary to use a function to do this. But if you insist on using a function it would not seem necessary to use an aggregate clr.

3. Registered User
Join Date
Dec 2010
Posts
3

Yeah, LessThanMax1 is what I want to wrap in the function lessthanMax2.

Like I said this is just a simplified example of what I really want. The actual formula is (x - min(x)) / (min(x) - max(x)). And I want to apply this to a 'window', i.e (x - min(x)) / (min(x) - max(x)) over (partition by something)

So if x is something complicated like a subquery or an expensive function you can imagine the benefits of wrapping the logic in a single function call.

I posted this problem in a different forum and spent most of the time trying to convince people that I did actually want to do this. So while I appreciate all help offered please take my word for it that I know what I want to achieve.

If you have any ideas Id be very grateful.

Thank you,
bp

4. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Originally Posted by bp77
So if x is something complicated like a subquery or an expensive function you can imagine the benefits of wrapping the logic in a single function call.
No, a subquery, CTE, or temporary table dataset will be faster than a UDF for something like this. The UDF will get called once for each row that is output, whereas a CTE or temporary table could be used to calculated all the required values in a single scan of the table.

5. Registered User
Join Date
Dec 2010
Posts
3
Oh man here we go again .

So if x is something like (select avg(y) from t inner join t1..... inner join t4 on blah = blah where t1.col1 = a and t2.col2 > b .... )

I currently need to write ((select avg(y) from t inner join t1..... inner join t4 where t1.col1 = a and t2.col2 > b .... ) - min ((select avg(y) from t inner join t1..... inner join t4 where t1.col1 = a and t2.col2 > b .... )) / (max((select avg(y) from t inner join t1..... inner join t4 where t1.col1 = a and t2.col2 > b .... ) - min((select avg(y) from t inner join t1..... inner join t4 where t1.col1 = a and t2.col2 > b .... )))) over (partition by somthing)

But I want to write MyFunction ((select avg(y) from t inner join t1..... inner join t4 where t1.col1 = a and t2.col2 > b .... )) over (partition by somthing)

I mention earlier that its used in data mining, so I will use this scaling formula many times in the same query.

A two stage process where we dump the data in to a temp table and then scale the cols is not viable due to reasons to boring to go into here.

Please take the following for granted; I have thought about this and know what I need to achieve. I have very good reasons for this requirement. Im not a nut case etc etc.

Back to basics. Is it possible to wrap 'x - max(x)' in a single function call that I can use in the form myFunction(x) over (partition by something)?

Thats my question, no extra information is necessary. Sorry if I sound rude, but I cant go through the same ordeal as the last time I posted this question.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•