RE: Standard Deviation

From: Larry Elkins <elkinsl_at_flash.net>
Date: Mon, 26 Nov 2001 10:34:08 -0800
Message-ID: <F001.003CCF7A.20011126100019@fatcity.com>

Ok, it sounds like you already know about the stddev function (since you mentioned it) and its 2 cousins. So it seems like what you are looking for is the best way to apply it to the data in your PL/SQL table, right? Here are some links to suggestions and workarounds for selecting from a "PL/SQL" table. You can use a function against a "real" PL/SQL table, or, you can create an SQL type, not PL/SQL type, and do some neat things. You can search for more examples while there.

http://asktom.oracle.com/pls/ask/f?p=4950:8:285838::NO::F4950_P8_DISPLAYID:6 66224436920
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:195057541 7033

And, if performance isn't absolutely critical, you could define an object type of number and hit the table again, returning each of the 48 columns as a separate row and applying the STDDEV function. Here is an example for transposing the columns into "rows" so that you can apply the function:

SQL> select * from sdev
2 /

A1 A2 A3
---------- ---------- ----------

1 2 3

SQL> create or replace type numlistt as table of number 2 /

Type created.

SQL> select a.column_value foo
2 from the (select cast( numlistt(A1,A2,A3) as numlistt) from sdev) A 3 /

FOO

         1
         2
         3

1 select stddev(a.column_value) foo
2* from the (select cast( numlistt(A1,A2,A3) as numlistt) from sdev) A SQL> / FOO

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781
-----Original Message-----
Peter.McLarty_at_mincom.com
Sent: Monday, November 26, 2001 6:05 AM
To: Multiple recipients of list ORACLE-L

Hi
I asked this earlier over on the ODTUG-SQLPLUS-L list, not much response

I am working on a problem where I have rows of data and the structure includes 48 elements of data in each row on which i need to calculate the standard deviation for the 48 elements in that row.

I currently have that data in a PL/SQL table in the form of one element to each row in that table as part of the overall package and could use this to calculate it.

Can I use stddev on a pl/sql table and if so what column name do I use in the stddev function. The column type in the PL/SQL table is Number. or
Do I need to write it out to a temp table and get it from that.

TIA

Peter McLarty                   E-mail: Peter.Mclarty_at_Mincom.com
Technical Consultant        WWW: http://www.Mincom.com
APAC Technical Services     Phone: +61 (0)7 3303 3461
Brisbane,  Australia                Mobile: +61 (0)402 094 238
----------------------                    Facsimile: +61 (0)7 3303 3048

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Larry Elkins
INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Nov 26 2001 - 12:34:08 CST