CODEWITHSUNDEEP
sqloracle12c
user6315807
user6315807

Reputation: 17

Iterating through columns in a VARRAY in ORACLE

I have a table in my DB that has a VARRAY in it. VARRAY size is 4788

CREATE OR REPLACE TYPE numbers AS VARRAY(4788) of NUMBER;

CREATE TABLE table1
    (
        id                      NUMBER(5)         NOT NULL,
        notes                   VARCHAR2(150),
        person_id               NUMBER(5),
        visit_id                NUMBER(5)         NOT NULL,
        device_id               NUMBER(5),
        vals                    numbers,
        CONSTRAINT PK_table1 PRIMARY KEY (id)
    );

Im wondering how i can iterate over the columns 1 at a time (There are 637 rows in the table, each row has a VARRAY of 4788 size. Im trying to do some stats to get a Cohens D's value out of the entire dataset. Basically what I want to say is ...

M_1 is calculated by taking the mean of column 1’s values from row 1-300. M_2 is calculated from taking the mean of column 1’s values from rows 301-337). We then subtract M_1 - M_2, then divide that value by the standard deviation of all of the values from column 1 for rows 1-637. I want to repeat this for all 4788 Columns.

Also, welcome to suggestions to improve as well.

Any help would be greatly appreciated.

Upvotes: 0

Views: 99

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132570

My suggestion is to create a separate table for the values. So the first table becomes:

CREATE TABLE table1
    (
        id                      NUMBER(5)         NOT NULL,
        notes                   VARCHAR2(150),
        person_id               NUMBER(5),
        visit_id                NUMBER(5)         NOT NULL,
        device_id               NUMBER(5),
        CONSTRAINT PK_table1 PRIMARY KEY (id)
    );

and the second (child) table is:

CREATE TABLE table2
    (
        id                      NUMBER            NOT NULL,
        table1_id               NUMBER(5)         NOT NULL,
        value                   NUMBER,
        CONSTRAINT PK_table2 PRIMARY KEY (id),
        CONSTRAINT FK_table2_table1 FOREIGN KEY (table1_id)
          REFERENCES table1 (id)
    );

You would insert 4788 rows into table2 for each row in table1.

Now you can use the Oracle AVG function to calculate the mean values something like:

select avg(value) 
from   table2
where  table1_id between 1 and 300; -- or however you identify the first 300 rows

Upvotes: 1

Related Questions