Where is the one place that you should definitely NOT ...
-
I meant like creating a new aggregate function MEDIAN.
SELECT MIN(Col1), MAX(Col1), STDEV(Col1), AVG(Col1), MEDIAN(Col1) FROM Table1
N 56°04'39.16"
E 12°55'05.25" -
A 'proper' median function native to T-SQL is still on the to-do list.
(There is one in MDX, however)
Ofc you can 'hack' your own, but that isn't SQL per definition.
As I understand it, the 'problem' with a MEDIAN is that it contradicts what relational theory is about.
Very short - in order to find the median, you depend on ordering, and as soon as you order something, it's no longer a 'table' (as per the theory). Part of the optimizations within the engine, is to use 'the relational' way in intermediate steps when processing a query, if possible. This is one reason why you can't rely on nested ORDER BY's in a predictable way, because the engine doesn't feel it absolutely *must* stick to those sortings, if a queryplan says that skipping the sort would be more efficient. The only sort that is always honored, is the outermost one - the one that is displayed in the actual result.
I mean, the only thing you really need to do, is to sort your list with a rownumber, and get the middle one (or the average of the two middle one of row# are even) - that's where the median is. (not considering the different stat/math/finance etc variations of medians).
The most 'straightforward' could be as easy as pouring the ordered list into a temptable with some sort of rownumber, find maxrow#, go to the middle and read the value(s) there. However, this isn't what we want in SQL. We want a relational setbased solution, and this isn't it.
..seems we have strayed from the original subject quite a bit now
/Kenneth
-
Yep... strayed just a bit... I think Peter meant "would a CLR be an appropriate place to build a function"? If not, that's the way I took it and I think the answer might be "Yes".
So far as a Median function not being "relational", I'd have to say that a good number of the intrinsic functions aren't relational either (just to stray a bit more
)--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
..in order to keep us 'strayed' then...
Yes, some functions would be very well suited as CLR implementations, the 'recommended kind' is those where you can take advantage of stuff not available in T-SQL - eg crypto libraries for a decrypt/uncrypt function just to name one possible use.
/Kenneth
-
Kenneth,
SP is a part of T-SQL functionality, CLR is not, it's another programming language. As well as VB, C, Java, etc.
SP's are the part of T-SQL functionality, so it's naturally cannot be used for something not related to "STRUCTURED QUERIES LANGUAGE".
And if "CLR" is an answer for the question of the topic then "installing Windows" and "fixing a car" must be considered as right answers as well.
_____________
Code for TallyGenerator
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply