I have a table with VARRAY field and want to calculate its count and percentage.
DATE_START DATE_END O3 COUNT PERCENTAGE
2005/1/1 2005/1/4 (1, 2, 3, 4, 5)
2005/1/3 2005/1/11 (2, 3, 4, 5, 6)
2005/1/10 2005/1/13 (3, 4, 5, 6, 7)
2005/1/5 2005/1/8 (4, 5, 6, 7, 8)
2005/1/7 2005/1/14 (5, 6, 7, 8, 9)
I will calculate the count of how many elements in VARRAY O3 greater than or equal to 5 (A), and find out what percentage of A.
for example, the second-row show VARRAY O3 is (1, 2, 3, 4, 5). so A = 1, percentage of A is 1/5 = 20%.
the last-row show VARRAY O3 is (5, 6, 7, 8,9). so A = 5, percentage of A is 5/5 = 100%.
what I expected is
DATE_START DATE_END O3 COUNT PERCENTAGE
2005/1/1 2005/1/4 (1, 2, 3, 4, 5) 1 0.2
2005/1/3 2005/1/11 (2, 3, 4, 5, 6) 2 0.4
2005/1/10 2005/1/13 (3, 4, 5, 6, 7) 3 0.6
2005/1/5 2005/1/8 (4, 5, 6, 7, 8) 4 0.8
2005/1/7 2005/1/14 (5, 6, 7, 8, 9) 5 1.0
thanks in advance
CREATE OR REPLACE TYPE NUMBER_VARRAY_5 AS VARRAY (5) OF NUMBER (8,2) NOT NULL;
create table TEST_3
(
date_start DATE,
date_end DATE,
o3 NUMBER_VARRAY_5
);
--
Insert into TEST_3
(DATE_START, DATE_END, O3)
Values
(TO_DATE('2005/1/1', 'YYYY/MM/DD'), TO_DATE('2005/1/4', 'YYYY/MM/DD'), NUMBER_VARRAY_5(1,2,3,4,5));
Insert into TEST_3
(DATE_START, DATE_END, O3)
Values
(TO_DATE('2005/1/3', 'YYYY/MM/DD'), TO_DATE('2005/1/11', 'YYYY/MM/DD'), NUMBER_VARRAY_5(2,3,4,5,6));
Insert into TEST_3
(DATE_START, DATE_END, O3)
Values
(TO_DATE('2005/1/10', 'YYYY/MM/DD'), TO_DATE('2005/1/13', 'YYYY/MM/DD'), NUMBER_VARRAY_5(3,4,5,6,7));
Insert into TEST_3
(DATE_START, DATE_END, O3)
Values
(TO_DATE('2005/1/5', 'YYYY/MM/DD'), TO_DATE('2005/1/8', 'YYYY/MM/DD'), NUMBER_VARRAY_5(4,5,6,7,8));
Insert into TEST_3
(DATE_START, DATE_END, O3)
Values
(TO_DATE('2005/1/7', 'YYYY/MM/DD'), TO_DATE('2005/1/14', 'YYYY/MM/DD'), NUMBER_VARRAY_5(5,6,7,8,9));
COMMIT;