Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH basis as (
         SELECT 'A' AS KPI_KEY, 2 AS KPI_VALUE FROM dual
        union all
        SELECT 'A' AS KPI_KEY, 5 AS KPI_VALUE FROM dual
        union all
        SELECT 'A' AS KPI_KEY, 3 AS KPI_VALUE FROM dual
    ), group_counts AS (
      SELECT KPI_KEY, COUNT(*) as total_count
      FROM basis
      GROUP BY KPI_KEY
    )
    , numbered_rows AS (
      SELECT KPI_KEY, KPI_VALUE, ROW_NUMBER() 
        OVER (PARTITION BY KPI_KEY ORDER BY dbms_random.value) AS rn, total_count
      FROM basis
      JOIN group_counts USING (KPI_KEY)
    )
    , random_numbers AS (
      SELECT KPI_KEY, ceil(dbms_random.value(0, max(total_count))) AS rand_rn
      FROM group_counts
      GROUP BY KPI_KEY
      CONNECT BY LEVEL <= total_count AND PRIOR KPI_KEY = KPI_KEY AND PRIOR dbms_random.value IS NOT NULL
    )
    SELECT *
    FROM random_numbers rn
    ;


KPI_KEY
RAND_RN


A
2

A
3

A
1


WITH basis as (
         SELECT 'A' AS KPI_KEY, 2 AS KPI_VALUE FROM dual
        union all
        SELECT 'A' AS KPI_KEY, 5 AS KPI_VALUE FROM dual
        union all
        SELECT 'A' AS KPI_KEY, 3 AS KPI_VALUE FROM dual
    ), group_counts AS (
      SELECT KPI_KEY, COUNT(*) as total_count
      FROM basis
      GROUP BY KPI_KEY
    )
    , numbered_rows AS (
      SELECT KPI_KEY, KPI_VALUE, ROW_NUMBER() 
        OVER (PARTITION BY KPI_KEY ORDER BY dbms_random.value) AS rn, total_count
      FROM basis
      JOIN group_counts USING (KPI_KEY)
    )
    , random_numbers AS (
      SELECT KPI_KEY, ceil(dbms_random.value(0, max(total_count))) AS rand_rn
      FROM group_counts
      GROUP BY KPI_KEY
      CONNECT BY LEVEL <= total_count AND PRIOR KPI_KEY = KPI_KEY AND PRIOR dbms_random.value IS NOT NULL
    )
    SELECT rn.*, nr.*, ROWNUM
    FROM random_numbers rn
    FULL OUTER JOIN numbered_rows nr 
        ON rn.KPI_KEY = nr.KPI_KEY 
        and rn.rand_rn = nr.rn
  UNION ALL
    SELECT rn.*, NULL, NULL, NULL, NULL, ROWNUM
    FROM random_numbers rn
    ;


KPI_KEY
RAND_RN
KPI_KEY
KPI_VALUE
RN
TOTAL_COUNT
ROWNUM


A
3
A
2
3
3
1

null
null
A
3
2
3
2

null
null
A
5
1
3
3

A
3
null
null
null
null
1


EXPLAIN PLAN FOR
  WITH basis as (
         SELECT 'A' AS KPI_KEY, 2 AS KPI_VALUE FROM dual
        union all
        SELECT 'A' AS KPI_KEY, 5 AS KPI_VALUE FROM dual
        union all
        SELECT 'A' AS KPI_KEY, 3 AS KPI_VALUE FROM dual
    ), group_counts AS (
      SELECT KPI_KEY, COUNT(*) as total_count
      FROM basis
      GROUP BY KPI_KEY
    )
    , numbered_rows AS (
      SELECT KPI_KEY, KPI_VALUE, ROW_NUMBER() 
        OVER (PARTITION BY KPI_KEY ORDER BY dbms_random.value) AS rn, total_count
      FROM basis
      JOIN group_counts USING (KPI_KEY)
    )
    , random_numbers AS (
      SELECT KPI_KEY, ceil(dbms_random.value(0, max(total_count))) AS rand_rn
      FROM group_counts
      GROUP BY KPI_KEY
      CONNECT BY LEVEL <= total_count AND PRIOR KPI_KEY = KPI_KEY AND PRIOR dbms_random.value IS NOT NULL
    )
    SELECT *
    FROM random_numbers rn
    ;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());


PLAN_TABLE_OUTPUT


Plan hash value: 448364244

 

--------------------------------------------------------------------------------------

| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |      |     3 |    48 |     7  (15)| 00:00:01 |

|   1 |  VIEW                         |      |     3 |    48 |     7  (15)| 00:00:01 |

|*  2 |   CONNECT BY WITHOUT FILTERING|      |       |       |            |          |

|   3 |    VIEW                       |      |     3 |    48 |     7  (15)| 00:00:01 |

|   4 |     HASH GROUP BY             |      |     3 |     9 |     7  (15)| 00:00:01 |

|   5 |      VIEW                     |      |     3 |     9 |     6   (0)| 00:00:01 |

|   6 |       UNION-ALL               |      |       |       |            |          |

|   7 |        FAST DUAL              |      |     1 |       |     2   (0)| 00:00:01 |

|   8 |        FAST DUAL              |      |     1 |       |     2   (0)| 00:00:01 |

|   9 |        FAST DUAL              |      |     1 |       |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("KPI_KEY"=PRIOR "KPI_KEY")

       filter("TOTAL_COUNT">=LEVEL AND PRIOR "DBMS_RANDOM"."VALUE"() IS NOT

              NULL)


EXPLAIN PLAN FOR
WITH basis as (
         SELECT 'A' AS KPI_KEY, 2 AS KPI_VALUE FROM dual
        union all
        SELECT 'A' AS KPI_KEY, 5 AS KPI_VALUE FROM dual
        union all
        SELECT 'A' AS KPI_KEY, 3 AS KPI_VALUE FROM dual
    ), group_counts AS (
      SELECT KPI_KEY, COUNT(*) as total_count
      FROM basis
      GROUP BY KPI_KEY
    )
    , numbered_rows AS (
      SELECT KPI_KEY, KPI_VALUE, ROW_NUMBER() 
        OVER (PARTITION BY KPI_KEY ORDER BY dbms_random.value) AS rn, total_count
      FROM basis
      JOIN group_counts USING (KPI_KEY)
    )
    , random_numbers AS (
      SELECT KPI_KEY, ceil(dbms_random.value(0, max(total_count))) AS rand_rn
      FROM group_counts
      GROUP BY KPI_KEY
      CONNECT BY LEVEL <= total_count AND PRIOR KPI_KEY = KPI_KEY AND PRIOR dbms_random.value IS NOT NULL
    )
    SELECT rn.*, nr.*, ROWNUM
    FROM random_numbers rn
    FULL OUTER JOIN numbered_rows nr 
        ON rn.KPI_KEY = nr.KPI_KEY 
        and rn.rand_rn = nr.rn;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());


PLAN_TABLE_OUTPUT


Plan hash value: 1952896670

 

-----------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                         |                            |     2 |    96 |    17  (18)| 00:00:01 |

|   1 |  TEMP TABLE TRANSFORMATION               |                            |       |       |            |          |

|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6B7F_972B2F7 |       |       |            |          |

|   3 |    UNION-ALL                             |                            |       |       |            |          |

|   4 |     FAST DUAL                            |                            |     1 |       |     2   (0)| 00:00:01 |

|   5 |     FAST DUAL                            |                            |     1 |       |     2   (0)| 00:00:01 |

|   6 |     FAST DUAL                            |                            |     1 |       |     2   (0)| 00:00:01 |

|   7 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6B80_972B2F7 |       |       |            |          |

|   8 |    HASH GROUP BY                         |                            |     1 |     3 |     3  (34)| 00:00:01 |

|   9 |     VIEW                                 |                            |     3 |     9 |     2   (0)| 00:00:01 |

|  10 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6B7F_972B2F7 |     3 |    48 |     2   (0)| 00:00:01 |

|  11 |   COUNT                                  |                            |       |       |            |          |

|  12 |    VIEW                                  | VW_FOJ_0                   |     2 |    96 |     8  (25)| 00:00:01 |

|* 13 |     HASH JOIN FULL OUTER                 |                            |     2 |    96 |     8  (25)| 00:00:01 |

|  14 |      VIEW                                |                            |     1 |    16 |     3  (34)| 00:00:01 |

|  15 |       HASH GROUP BY                      |                            |     1 |    16 |     3  (34)| 00:00:01 |

|* 16 |        CONNECT BY WITHOUT FILTERING      |                            |       |       |            |          |

|  17 |         VIEW                             |                            |     3 |    48 |     2   (0)| 00:00:01 |

|  18 |          TABLE ACCESS FULL               | SYS_TEMP_0FD9D6B80_972B2F7 |     3 |     9 |     2   (0)| 00:00:01 |

|  19 |      VIEW                                |                            |     1 |    32 |     5  (20)| 00:00:01 |

|  20 |       WINDOW SORT                        |                            |     1 |    22 |     5  (20)| 00:00:01 |

|* 21 |        HASH JOIN                         |                            |     1 |    22 |     4   (0)| 00:00:01 |

|  22 |         VIEW                             |                            |     3 |    18 |     2   (0)| 00:00:01 |

|  23 |          TABLE ACCESS FULL               | SYS_TEMP_0FD9D6B7F_972B2F7 |     3 |    48 |     2   (0)| 00:00:01 |

|  24 |         VIEW                             |                            |     3 |    48 |     2   (0)| 00:00:01 |

|  25 |          TABLE ACCESS FULL               | SYS_TEMP_0FD9D6B80_972B2F7 |     3 |     9 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

  13 - access("RN"."KPI_KEY"="NR"."KPI_KEY" AND "RN"."RAND_RN"="NR"."RN")

  16 - access("KPI_KEY"=PRIOR "KPI_KEY")

       filter("TOTAL_COUNT">=LEVEL AND PRIOR "DBMS_RANDOM"."VALUE"() IS NOT NULL)

  21 - access("BASIS"."KPI_KEY"="GROUP_COUNTS"."KPI_KEY")


WITH basis (kpi_key, kpi_value) as (
  SELECT 'A', 2 FROM dual union all
  SELECT 'A', 5 FROM dual union all
  SELECT 'A', 3 FROM dual
),
random_numbers AS (
  SELECT kpi_key,
         kpi_value,
         1 + FLOOR(DBMS_RANDOM.VALUE(0, COUNT(*) OVER (PARTITION BY kpi_key))) AS rand_rn,
         ROW_NUMBER() OVER (PARTITION BY kpi_key ORDER BY ROWNUM) AS rn
  FROM   basis
)
select original.kpi_key   AS o_kpi_key,
       original.kpi_value AS o_kpi_value,
       original.rn        AS o_rn,
       rand.kpi_key       AS r_kpi_key,
       rand.kpi_value     AS r_kpi_value,
       rand.rn            AS r_rn
from   random_numbers original
       INNER JOIN random_numbers rand
       ON original.rand_rn = rand.rn;


O_KPI_KEY
O_KPI_VALUE
O_RN
R_KPI_KEY
R_KPI_VALUE
R_RN


A
3
3
A
3
3

A
5
2
A
5
2

A
2
1
A
2
1
KPI_KEY	RAND_RN	KPI_KEY	KPI_VALUE	RN	TOTAL_COUNT	ROWNUM
A	3	A	2	3	3	1
null	null	A	3	2	3	2
null	null	A	5	1	3	3
A	3	null	null	null	null	1