meanof(var1,var2,var3) 2

[hvass]

I am trying to write a function that will take the average of a number of columns (rather than average of column in a number of rows)(trying to ghost a SAS function called mean)

select
meanof('var1,var2,var3')
from mytable

on data (var1,var2,var3)
2,4,6
6,8,10

would return
meannof(var1,var2,var3)
4
8

I started parsing the string 'var1,var2,var3' and put it in a varrray but struggling to know how to get it to substitute the column name for the column values tried execute immediate. Guess I could do it as a procedure but interested in writing this as a function becuase want to use it in statements like...

select
least(var1,var2,var3,var4),
meanof(var1,var2,var4,var4)
from mytable

Am I going the right way trying execute immediate, will I be able to write this as a function?

Any help / or steer gratefully acepted

 

[SantaMufasa]

HVass,

There are probably many ways to do this...here is one:

create or replace function meanof
       (a number default null
       ,b number default null
       ,c number default null
       ,d number default null) return number is
    total number;
    cnt   number;
begin
    if a is not null then total := a; cnt := 1; end if;
    if b is not null then total := total+b; cnt := cnt + 1; end if;
    if c is not null then total := total+c; cnt := cnt + 1; end if;
    if d is not null then total := total+d; cnt := cnt + 1; end if;
    return total/cnt;
end;
/

Function created.

select meanof(2,4,6) from dual;

MEANOF(2,4,6)
-------------
            4

If you want more columns possible, then add more columns to your function until you reach the maximum number you wish.

Let us know if this is what you wanted.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[hvass]

Thanks Mufasa

Realise my original post was not very clear. Yes want to do something very similar but rather than pass in constants to the function 2,4,6 I want to pass in column names col1,col2,col3

so
select * from mytable
returns
col1,col2,col3
2,4,6
6,8,10

select meanof(col1,col2,col3) as meanof from mytable
returns
meanof
------
4
8

 

[SantaMufasa]

I want to do something very similar but rather than pass in constants to the function 2,4,6 I want to pass in column names col1,col2,col3

Perhaps you were not aware that when you create a user-defined function, the incoming arguments for the function can be any expression that matches the data type of the argument declaration. Specifically, you can pass any of the following as a numeric argument of a function:[ul][li]a numeric literal[/li][li]a column whose contents are numeric[/li][li]a function whose result is numeric[/li][li]an arithmetic operation[/li][li]any combination of the above.[/li][/ul]Therefore, as a proof of concept, using your data example...

select * from mytable;

     COL1       COL2       COL3
--------- ---------- ----------
        2          4          6
        6          8         10

select meanof(col1,col2,col3) result from mytable;

    RESULT
----------
         4
         8

Let us know if this resolves your issue.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[hvass]

Mufasa

Many thanks great that answer is so simple - I thought I would have a problem generalising this to be any number of columns
meanof(col1,col2,col3)
meanof(col1,col2,col3,col4)
so was thinking I would have to pass in a string that I then parse it.

Thanks for sticking with me.

 

[ChrisHunt]

Just refining Santa's solution a little: if the function parameters are being pulled from database columns, there's the possibility that any of them might be null - so initialising the [tt]total[/tt] and [tt]cnt[/tt] variables only if parameter [tt]a[/tt] is not null may cause the function to fail.

Here's my version:

CREATE OR REPLACE FUNCTION mean (a IN NUMBER := NULL,
                                 b IN NUMBER := NULL,
                                 c IN NUMBER := NULL,
                                 d IN NUMBER := NULL) RETURN NUMBER IS
   cnt NUMBER := 0;
   tot NUMBER := 0;
   PROCEDURE include (xx IN NUMBER) IS
   BEGIN
      IF xx IS NOT NULL THEN
         cnt := cnt + 1;
         tot := tot + xx;
      END IF;
   END;
BEGIN
   include(a);
   include(b);
   include(c);
   include(d);
   
   IF cnt = 0 THEN
      RETURN NULL;
   ELSE
      RETURN tot/cnt;
   END IF;
END;

I've hived off the adding-and-incrementing bit into a local procedure - probably not necessary, but makes it easy to add further parameters with a minimum of fuss.

If you really want any number of columns, then you will need to parse them out of a string:

CREATE OR REPLACE FUNCTION mean2 (pars IN VARCHAR2) RETURN NUMBER IS
   cnt NUMBER := 0;
   tot NUMBER := 0;
   p VARCHAR2(32767); 

   FUNCTION next_term(pp IN OUT VARCHAR2) RETURN NUMBER IS
      x NUMBER;
      t NUMBER;
   BEGIN
      x := INSTR(pp,',');
      IF x > 0 THEN
         t := SUBSTR(pp,1,x-1);
         pp := SUBSTR(pp,x+1);
      ELSE
         t := pp;
         pp := NULL;
      END IF;
      RETURN TO_NUMBER(t);
   END;

   PROCEDURE include (xx IN NUMBER) IS
   BEGIN
      IF xx IS NOT NULL THEN
         cnt := cnt + 1;
         tot := tot + xx;
      END IF;
   END;
BEGIN
   p := pars;
   WHILE p IS NOT NULL LOOP
      include(next_term(p));
   END LOOP;
   
   IF cnt = 0 THEN
      RETURN NULL;
   ELSE
      RETURN tot/cnt;
   END IF;
END;

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[SantaMufasa]

Cool, Chris...Hava

for your cleverness !


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”