Results 1 to 5 of 5
  1. #1
    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

    Thanks for your tme

    bp

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2010
    Posts
    3
    Thank you for your reply.

    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. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by bp77 View Post
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    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.

    Its not so much about efficiency as about readability and ease of modification.

    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
  •