sqlclickhouserecursive-query

Clickhouse generate X rows of Y random numbers in an array


I would like to extend the below to return multiple rows, where each row has a different array of random values

  with base as (
       -- makes a random number 0 to 50, adds up to 10 into an array
select groupArray(10)(rand()%50) as values 
       -- generates 10 rows
  from numbers(10) 
)
select values, arrayMax(base.values)
  from base

Imaged result of current query:

enter image description here


Solution

  • Try this way:

    WITH
        10 AS x,
        5 AS y,
        base AS
        (
            SELECT groupArray(rand() % 50) AS values
            FROM
            (
                SELECT toUInt32(number / x) AS row
                FROM system.numbers
                LIMIT x * y
            )
            GROUP BY row
        )
    SELECT
        values,
        arrayMax(base.values)
    FROM base
    
    /*
    ┌─values──────────────────────────┬─arrayMax(values)─┐
    │ [16,35,13,40,37,45,33,10,47,31] │               47 │
    │ [14,39,36,26,20,6,24,43,43,19]  │               43 │
    │ [41,45,24,18,20,28,21,45,3,36]  │               45 │
    │ [7,29,19,2,23,49,18,2,12,6]     │               49 │
    │ [0,17,4,5,27,22,7,3,44,43]      │               44 │
    └─────────────────────────────────┴──────────────────┘
    */