I have the below table:
PC | A | MAXI |
---|---|---|
PC1 | A1 | 1 |
PC1 | A2 | 2 |
PC1 | A3 | 3 |
PC2 | A1 | 1 |
PC2 | A2 | 2 |
PC2 | A3 | 3 |
Is there a way to generate ‘MAXI’ entries for each group of ‘PC’ and ‘A’ in a random order (or at least a seemingly even distribution, even if not random)?
Desired output (count_pc would just be an order count over partition by PC):
PC | A | COUNT_PC |
---|---|---|
PC1 | A2 | 1 |
PC1 | A1 | 2 |
PC1 | A3 | 3 |
PC1 | A3 | 4 |
PC1 | A2 | 5 |
PC1 | A3 | 6 |
PC2 | A3 | 1 |
PC2 | A2 | 2 |
PC2 | A3 | 3 |
PC2 | A1 | 4 |
PC2 | A2 | 5 |
PC2 | A3 | 6 |
Advertisement
Answer
You can generate your rows using your favorite method, then use a random number generator for the last column (with row_number()
):
with cte(pc, a, maxi) as ( select pc, a, maxi from t union all select pc, a, maxi - 1 from cte where maxi > 1 ) select pc, a, row_number() over (partition by pc order by dbms_random.random) as count_pc from cte order by pc, count_pc;