CODEWITHSUNDEEP
sqloracle-database
Nicolás Rivera
Nicolás Rivera

Reputation: 78

Generate series of integers with random start and end SQL Oracle

I am trying to generate a series of integers that go from a random start-point until a random (greater) end-point. Furthermore, I would like to do this as a window function, so I could use this in a OVER (PARTITION BY ) statement.

Basically I'm trying to select random hours (from 1-24), but in a random way and also consecutively, and do this for each client (which is why I suggest an OVER (PARTITION BY client) statement, but I'm open for other ideas.

I am trying to use:

SELECT
    T1.HOURS
FROM (
        SELECT
            LEVEL HOURS
        FROM DUAL
        CONNECT BY
            LEVEL <= 24
    ) T1,
     (
        SELECT
            INIT,
            LEAST(INIT + LENGTH, 24) FIN
        FROM (
            SELECT
                ROUND(DBMS_RANDOM.VALUE(1, 24)) INIT,
                ROUND(DBMS_RANDOM.VALUE(1, 24)) LENGTH
            FROM DUAL
        ) T0
    ) T2
WHERE
    T1.HOURS >= T2.INIT AND
    T1.HOURS <= T2.FIN;

But the result is unfortunately non-consecutive orders. |hours| |-----| |17| |18| |20| |24|

The code is simply nor working because the table T0 (which is the one that filters the initial INIT and final FIN value) is being replicated for each row.

This is a desired result:

client hours
1 4
1 5
1 6
1 7
2 14
2 15
3 13
3 14
3 15
3 16
3 17
3 18
3 19
3 20
3 21

In the desired result a consecutive list is selected for each client, with a random start and end point.

Upvotes: 0

Views: 69

Answers (1)

MT0
MT0

Reputation: 167972

In Oracle, you can use a correlated LATERAL join:

SELECT c.id,
       h.hours
FROM   ( SELECT id,
                FLOOR(DBMS_RANDOM.VALUE(1, 25)) AS bound1,
                FLOOR(DBMS_RANDOM.VALUE(1, 25)) AS bound2
         FROM   clients
       ) c
       CROSS JOIN LATERAL (
         SELECT LEAST(bound1, bound2) + LEVEL - 1 AS hours
         FROM   DUAL
         CONNECT BY LEVEL <= ABS(bound1 - bound2) + 1
       ) h;

Then, for the sample data:

CREATE TABLE clients (id) AS
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 3;

May (randomly) output:

ID HOURS
1 7
1 8
1 9
1 10
2 19
2 20
2 21
2 22
2 23
2 24
3 6
3 7
3 8
3 9
3 10
3 11
3 12
3 13
3 14
3 15
3 16

fiddle

Upvotes: 2

Related Questions