Re: SQL*PLUS question

On Tue, 16 Mar 1999 22:37:20 -0500, "Hemei" <Hemei_at_yahoo.com> wrote:

>I have one table, table_a which has two attributes group_id and rate. What I
>want is to retrieve the medium value for each group_id. For example,
> table_a:
> group_id rate
> ------------ ------
> 01 1.0
> 01 1.1
> 01 1.2
> 02 2.1
> 03 3.1
> 03 3.4
> 03 4.0
> 03 4.1
> 03 5.0
>what I want is something like:
> group_id rate
> ------------ ------
> 01 1.1
> 02 2.1
> 03 4.0
>Is there a easy way to do this with sql*plus? Thanks.
>
>Peng Nie
>
>

Well, I doubt you'd be asking this question if you were after just the average. I believe you are seeking the median value. There is no really easy way that I know of in Oracle to get this value. Oracle is not very robust in the statistical calculation area, from what I've seen. The only way I can think of doing this is sort-of complex, but I think it should work. If anyone can improve upon this or has a better way altogether, I too would be interested to see it.

I am not checking syntax here - just giving a general idea of what I would try:

select group_id, get_median(group_id) from table_a order by group_id;

You would need to create this get_median function something like this:

create function get_median ( p_groupid	table_a.group_id%TYPE)
	return number is

	working_val	number;
	target		number;
	loop_counter	number := 1;
	average_targets	boolean := FALSE;
	cursor rates is select rate from table_a
		where group_id = p_groupid
		order by rate;
begin
	select ((count(group_id) + 1)/2) into target from table_a
		where group_id = p_groupid;

	if target != floor(target) then
		target := floor(target);
		average_targets := TRUE;
	end if;

	open rates;
	loop
		if loop_counter = target then
		   working_val := rates.rate;
		end if;
		if loop_counter = target + 1 then
		   working_val := (working_val + rates.rate)/2;
		end if;

		if average_targets = TRUE
		   exit when loop_counter = target + 1;
		else
		   exit when loop_counter = target;
		end if;
		loop_counter := loop_counter + 1;
	end loop;
	return working_val; 		

Hope this works/helps.

-Jeff Guttadauro Received on Wed Mar 17 1999 - 10:22:58 CST