Re: Median with standard SQL
Date: Mon, 16 Jul 2001 23:00:32 -0400
Message-ID: <E3O47.69$RO.25496636_at_radon.golden.net>
Hi Jess,
In my mind, this problem resembles a two-way quota query with an average of the result. You could solve it as I have below by nesting two quota-like queries or you could solve it by taking the union of two quota queries. The result from either method just nests within a query that calculates the average.
My solution will depend on whether the database treats a division of the form:
( SELECT COUNT(*) FROM a_relation ) / 2
as a floating point division or an integer division. If it treats it as an integer division with truncation, you will have to add 1 to the count prior to dividing by two. The solution below works assuming floating point division or assuming 0.5 rounds to the next higher integer after the divide.
SELECT AVG(rate_col) as median_value
FROM (
SELECT key_col, rate_col
FROM (
SELECT r1.key as key_col, r1.rate_column as rate_col
FROM a_relation r1, a_relation r2
WHERE r2.rate_column < r1.rate_column
OR ( r2.rate_column = r1.rate_column AND r2.key <= r1.key )
GROUP BY r1.key, r1.rate_column
HAVING COUNT(*) >= ( SELECT COUNT(*) FROM a_relation ) / 2
), a_relation r3
WHERE r3.rate_column > rate_col
OR ( r2.rate_column = rate_col AND r3.key >= key_col )
GROUP BY key_col, rate_col
HAVING COUNT(*) >= ( SELECT COUNT(*) FROM a_relation ) / 2
)
Cheers,
Bob
Jess Likens wrote in message <_1H47.1335$oz3.17953_at_vixen.cso.uiuc.edu>...
>We haven't gotten the scores back yet. I think my solution may have been
>somewhat incorrect though because I don't think it works quite right for
>tables that have multiple occurrences of a given value.
>
>-Jess
>
>"Alan" <alanshein_at_erols.com> wrote in message
>news:9injpk$7qv$1_at_bob.news.rcn.net...
>> As others have said, you need to mention this in the message. Also, it is
>> always a plus if you post the things you tried.
>> Anyway, I'm glad to hear you were just looking to make what you did
better.
>> What did you get on the assignment?
>>
>> "Jess Likens" <likens_at_students.uiuc.edu> wrote in message
>> news:gk537.748$oz3.10473_at_vixen.cso.uiuc.edu...
>> > Well, seeing as how I turned in the homework on which this was a
question
>> > prior to posting to the newsgroup, I think perhaps your assumption was
a
bit
>> > uncalled for, no? I already had a solution, but it is (in my opinion)
>> > unwieldy and inelegant, so I was simply looking for some other
>> > possibilities.
>> >
>> > -Jess
>> >
>> > "Alan" <alanshein_at_erols.com> wrote in message
>> > news:9ihmd2$1jn$1_at_bob.news.rcn.net...
>> > > Your professor at the University of Illinois probably wants you to
really
>> > > think about this one and try lots of different things. Finding a
ready-made
>> > > answer in a newsgroup is not the best way to go in the long term. It
is
much
>> > > better to try to figure it out on your own, so here is a hint:
>> > >
>> > > The answer is constrained in the problem.
>> > >
>> > > "Jess Likens" <likens_at_students.uiuc.edu> wrote in message
>> > > news:wPT27.633$oz3.8465_at_vixen.cso.uiuc.edu...
>> > > > Well, I don't think that works for both even and odd numbers of
rows.
And
I
>> > > > don't think it works if there are multiple rows with identical
values.
But
>> > > > thanks.
>> > > >
>> > > > -Jess
>> > > >
>> > > > "Aakash Bordia" <a_bordia_at_hotmail.com> wrote in message
>> > > > news:9ida78$vi6$1_at_stlnews.stl.ibm.com...
>> > > > > Hint....
>> > > > > select column from table O where (select count(*) from table
where
>> > > > > column<O.column)=(select count(*)/2 from table)
>> > > > > Thanks
>> > > > > Aakash
>> > > > >
>> > > > > "Jess Likens" <likens_at_students.uiuc.edu> wrote in message
>> > > > > news:yqm27.364$oz3.4759_at_vixen.cso.uiuc.edu...
>> > > > > > Does anyone have a method to get the median of a relation using
only
the
>> > > > > > following SQL commands: SELECT, DISTINCT, WHERE, FROM, LIKE,
EXISTS,
IN,
>> > > > > > UNION, INTERSECT, EXCEPT/MINUS, ANY, ALL, COUNT, SUM, AVG, MAX,
MIN,
GROUP
>> > > > > > BY, HAVING, ORDER BY, JOIN? I can't seem to come up with
anything.
The
>> > > > > > median of n elements occurs at (n+1)/2 for an odd number of
elements
and
it
>> > > > > > is the average of n/2 and n/2 and (n+1)/2. Any help would be
much
>> > > > > > appreciated.
>> > > > > >
>> > > > > > -Jess
>> > > > > >
>> > > > > >
>> > > > >
>> > > > >
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>>
>
>
Received on Tue Jul 17 2001 - 05:00:32 CEST