Results 1 to 3 of 3

Thread: AVG of an ARRAY

  1. #1
    Join Date
    Sep 2013
    Posts
    1

    Unanswered: AVG of an ARRAY

    Type Specification :
    CREATE OR REPLACE TYPE ArrayCounterSum AS OBJECT
    (
    -- AUTHOR : CLIVE.GREGORY
    -- CREATED : 03-04-2010 14:44:02 14:44:02
    -- Modified : S. Glass - Removed read function to increase performance
    -- PURPOSE : USER DEFINED SUMMARY ON ARRAY COUNTERS
    -- ATTRIBUTES

    gCount NUMBER,
    gNumArrayCounter arraycounter_T,

    -- MEMBER FUNCTIONS AND PROCEDURES
    STATIC FUNCTION ODCIAggregateInitialize(actx IN OUT ArrayCounterSum) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT ArrayCounterSum,
    AC_Array IN VARCHAR2) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT ArrayCounterSum,
    ctx2 IN ArrayCounterSum) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateTerminate(self IN ArrayCounterSum,
    ReturnValue OUT VARCHAR2,
    flags IN number) RETURN NUMBER
    );


    Type Body :
    TYPE BODY ArrayCounterSum AS
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(ACTX IN OUT ARRAYCOUNTERSUM) RETURN NUMBER IS
    27 BEGIN
    28
    29
    30
    31
    32 ACTX := ARRAYCOUNTERSUM(-1,
    33 ARRAYCOUNTER_T(
    34 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    35 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    36 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    37 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    38 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    39 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    40 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    41 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    42 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    43 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    44 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    45 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    46 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    47 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    48 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    49 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    50 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    51 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    52 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    53 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    54 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    55 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    56 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    57 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    58 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    59 NULL,NULL,NULL,NULL,NULL,NULL,
    60 NULL)
    61 );
    62
    63
    64 RETURN ODCICONST.SUCCESS;
    65
    66 EXCEPTION
    67 WHEN OTHERS THEN
    68 RETURN ODCICONST.ERROR;
    69 END;
    70
    71
    72
    73
    74
    75
    76
    77 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT ARRAYCOUNTERSUM,
    78 AC_ARRAY IN VARCHAR2) RETURN NUMBER IS
    79 GOTELEMENT BOOLEAN;
    80 PSEP VARCHAR2(1) := '_';
    81 VINDEX PLS_INTEGER;
    82 STARTPOS PLS_INTEGER;
    83 ENDPOS PLS_INTEGER;
    84 CURPOS PLS_INTEGER;
    85 LENARRAY PLS_INTEGER;
    86 VELEMENT NUMBER;
    87 BEGIN
    88
    89
    90 IF AC_ARRAY IS NULL OR LENGTH(AC_ARRAY) = 0 THEN
    91 RETURN ODCICONST.SUCCESS;
    92 END IF;
    93
    94
    95 GOTELEMENT := FALSE;
    96 VINDEX := 0;
    97
    98 LENARRAY := LENGTH(AC_ARRAY);
    99 STARTPOS := 1;
    100 ENDPOS := 1;
    101
    102 FOR I IN 1 .. LENARRAY
    103 LOOP
    104
    105 CURPOS := I;
    106
    107 IF SUBSTR(AC_ARRAY, I, 1) = PSEP THEN
    108
    109 GOTELEMENT := TRUE;
    110 VINDEX := VINDEX + 1;
    111
    112
    113 ENDPOS := I - 1;
    114
    115
    116 IF ENDPOS < STARTPOS THEN
    117 STARTPOS := I + 1;
    118 END IF;
    119
    120 END IF;
    121
    122
    123 IF GOTELEMENT THEN
    124 VELEMENT := TO_NUMBER(SUBSTR(AC_ARRAY, STARTPOS, ENDPOS - STARTPOS + 1));
    125 SELF.GNUMARRAYCOUNTER(VINDEX) := NVL(NVL(SELF.GNUMARRAYCOUNTER(VINDEX)+VELEMENT,SEL F.GNUMARRAYCOUNTER(VINDEX)), VELEMENT);
    126 GOTELEMENT := FALSE;
    127
    128 STARTPOS := I + 1;
    129
    130 END IF;
    131
    132 END LOOP;
    133
    134
    135 IF CURPOS = LENARRAY THEN
    136 VINDEX := VINDEX + 1;
    137
    138 IF SUBSTR(AC_ARRAY, LENARRAY, 1) = PSEP THEN
    139 SELF.GNUMARRAYCOUNTER(VINDEX) := NULL;
    140
    141
    142 ELSE
    143 VELEMENT := TO_NUMBER(SUBSTR(AC_ARRAY, STARTPOS, LENARRAY - STARTPOS + 1));
    144 SELF.GNUMARRAYCOUNTER(VINDEX) := NVL(NVL(SELF.GNUMARRAYCOUNTER(VINDEX)+VELEMENT,SEL F.GNUMARRAYCOUNTER(VINDEX)), VELEMENT);
    145
    146 END IF;
    147
    148 END IF;
    149
    150
    151
    152 IF SELF.GCOUNT < VINDEX THEN
    153 SELF.GCOUNT := VINDEX;
    154 END IF;
    155
    156 RETURN ODCICONST.SUCCESS;
    157
    158 EXCEPTION
    159 WHEN OTHERS THEN
    160 RETURN ODCICONST.ERROR;
    161
    162 END;
    163
    164
    165
    166
    167
    168
    169 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT ARRAYCOUNTERSUM,
    170 CTX2 IN ARRAYCOUNTERSUM) RETURN NUMBER IS
    171 BEGIN
    172
    173
    174 FOR I IN 1 .. SELF.GCOUNT
    175 LOOP
    176 SELF.GNUMARRAYCOUNTER(I) := SELF.GNUMARRAYCOUNTER(I) + CTX2.GNUMARRAYCOUNTER(I);
    177 END LOOP;
    178
    179 RETURN ODCICONST.SUCCESS;
    180
    181 EXCEPTION
    182 WHEN OTHERS THEN
    183 RETURN ODCICONST.ERROR;
    184
    185 END;
    186
    187
    188
    189
    190
    191
    192 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN ARRAYCOUNTERSUM,
    193 RETURNVALUE OUT VARCHAR2,
    194 FLAGS IN NUMBER) RETURN NUMBER IS
    195 VAC_ARRAY VARCHAR2(4000);
    196 BEGIN
    197
    198
    199 IF SELF.GCOUNT < 1 THEN
    200 RETURNVALUE := NULL;
    201 ELSE
    202
    203 VAC_ARRAY := TO_CHAR(SELF.GNUMARRAYCOUNTER(1));
    204 FOR I IN 2 .. SELF.GCOUNT
    205 LOOP
    206 VAC_ARRAY := VAC_ARRAY || '_' || TO_CHAR(SELF.GNUMARRAYCOUNTER(I));
    207 END LOOP;
    208 RETURNVALUE := VAC_ARRAY;
    209 END IF;
    210
    211 RETURN ODCICONST.SUCCESS;
    212
    213 EXCEPTION
    214 WHEN OTHERS THEN
    215 RETURN ODCICONST.ERROR;
    216
    217 END;
    218
    219 END;

    My Above code is Doing Sum of each individual number and return an array.

    For e.g.
    I have these values in my column x.
    Row1 : 1_2_3_4_5
    Row2 : 2_1_3_4_5
    Row3 : 3_7_9_5_4

    So the output will be : 6_10_15_13_14 (Sum of all rows and return as an array)

    My Goal is to AVG the ROWS and retrun it into array. so what change should I make in above code in order to get the AVG of all records.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    why don't you adhere to data normalization standards & only store single value in a column?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Briefly looking your code,
    array(what you mentioned) might be not the array datatype, it might be a string(with a format having numbers separated by a separaer("_")).

    If so,
    your issue might be easy to undersand(and easy to solve) by breaking down the issue into some streps, like...
    (1) Decompose the values in column x into numbers in separate rows(including row number namely vindex?), by using CONNECT BY syntax(or recursive query).
    (2) Sum the rows GROUP(ing) BY vindex, by using SUM function.
    (3) Aggregate the result of (2) into one row, by using LISTAGG function.
    Last edited by tonkuma; 09-06-13 at 20:08.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •