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;