Sql Click house在数组中生成X行Y随机数

发布于05月04日

我想扩展下面的内容以返回多行，其中每一行都有不同的随机值数组

  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

当前查询的图像结果:

推荐答案

try 这样:

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 │
└─────────────────────────────────┴──────────────────┘
*/