0

I have the below function.

CREATE OR REPLACE FUNCTION pick_values RETURN t1_table
    PIPELINED
IS
    TYPE t2_type IS
        TABLE OF t2%rowtype;
    t2_data t2_type;
BEGIN
  -- https://stackoverflow.com/a/67398434/1509264
  -- License: CC BY-SA 4.0
    SELECT
        *
    BULK COLLECT
    INTO t2_data
    FROM
        t2;

    FOR cur IN (
        SELECT
            *
        FROM
            t1
        ORDER BY
            r
    ) LOOP DECLARE
        a_freqs    int_list := int_list();
        cum_freq   INT := 0;
        taken      string_list := split_string(cur.an, ', ');
        idx        INT;
        c          t2.a%TYPE;
    BEGIN
        a_freqs.extend(t2_data.count);
        FOR i IN 1..t2_data.count LOOP IF ( t2_data(i).a = cur.ay AND t2_data(i).c > 0 ) OR ( cur.ay IS NULL AND t2_data(i).a NOT
        MEMBER OF taken AND t2_data(i).c > 0 ) THEN
            a_freqs(i) := cum_freq + t2_data(i).c;
            cum_freq := cum_freq + t2_data(i).c;
        ELSE
            a_freqs(i) := cum_freq;
        END IF;
        END LOOP;

        IF cum_freq > 0 THEN
            idx := floor(dbms_random.value(0, cum_freq));
            FOR i IN 1..t2_data.count LOOP IF idx < a_freqs(i) THEN
                c := t2_data(i).a;
                t2_data(i).c := t2_data(i).c - 1;
                EXIT;
            END IF;
            END LOOP;

        END IF;

        PIPE ROW ( t1_data(cur.vk, cur.ay, cur.an, cur.r, c) );

    END;
    END LOOP;

END;

However, I'm struggling to understand the below lines:

        a_freqs(i) := cum_freq + t2_data(i).c;
        cum_freq := cum_freq + t2_data(i).c;
    ELSE
        a_freqs(i) := cum_freq;

Could you please help me on what do these lines do? A line over the code with a brief explanation would do. Thanks!

PD: kindly let me know if you need additional info. However, there's the link to the original SO question in the function code.

Improve this question

1 Answer 1

Reset to default
1 
FOR i IN 1..t2_data.count LOOP
  IF ( t2_data(i).a = cur.ay AND t2_data(i).c > 0 )
     OR ( cur.ay IS NULL AND t2_data(i).a NOT MEMBER OF taken AND t2_data(i).c > 0 )
  THEN
    a_freqs(i) := cum_freq + t2_data(i).c;
    cum_freq := cum_freq + t2_data(i).c;
  ELSE
    a_freqs(i) := cum_freq;
  END IF;
END LOOP;

You have a collection (array) t2_data which may contain the values:

A C
A1 4
A2 10
A3 2
A4 10

Then you have an array of cumulative frequencies that you are populating and you loop through them and when the IF statement is matched then you increase the cumulative frequency and store it in the next index of the cumulative frequency array and when it is not matched you do not increment the cumulative frequency and store the same frequency that was stored in the previous array index.

Example 1:

If the cursor row you are processing has the data:

AY (yes, must be included) AN (no, must be excluded)
<NULL> A2,A4

Then the frequency array will be:

a_freqs Notes
4 A1 is not excluded, so the cumulative frequency increases by the corresponding value from t2_data
4 A2 is excluded, so the cum. freq. does not change.
6 A3 is not excluded, so the cum. freq. increases by the corresponding value from t2_data
6 A4 is excluded, so the cum. freq. does not change.

The next step of the algorithm will pick a random item up to the maximum cumulative frequency and decrement the corresponding C value from t2_data to indicate that that item has been allocated. So if the 3rd element was randomly picked then that would be in the 1st group which would mean A1 is decremented in the next stage of the algorithm

Example 2:

If the cursor row you are processing has the data:

AY (yes, must be included) AN (no, must be excluded)
A3 <NULL>

Then the frequency array would be:

a_freqs Notes
0 A1 is not in the list that must be included, so the cumulative frequency stays at 0
0 A2 is not in the list that must be included, so the cum. freq. stays at 0
2 A3 is in the list that must be included, so the cum. freq. increases by the corresponding value from t2_data
2 A4 is not in the list that must be included, so the cum. freq. stays at the previous value.

In this case, there is only one row where the cumulative frequency increased so it can only be A3 that will be allocated.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks a lot for the brilliant explanation, I managed to fully understand the bit of code!
Well, one more question: if there was another link variable in both tables needed for the assignation, would it be enough with changing lines IF ( t2_data(i).a = cur.ay AND t2_data(i).c > 0 ) OR ( cur.ay IS NULL AND t2_data(i).a NOT MEMBER OF taken AND t2_data(i).c > 0 ) to include that relation?
@jeiv I am not sure I understand the question you are asking.
Imagine I had another column in both table t1 and table t2 called PC (with values PC1, PC2...). And I had to run the assignation logic for each partition of PC. How could I do it?
Thats quite off topic for this question but db<>fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.