创建10个不同的随机数 [英] Create 10 different random numbers

问题描述

我想生成一些随机数据。约5万行

I want generate some random data. ~50k rows

每行具有ID自动数字和从1到90的10个数字。

Every row have ID autonumeric and 10 numeric values from 1 to 90.

+----+------+------+------+------+------+------+------+------+------+------+
| id | nr1  | nr2  | nr3  | nr4  | nr5  | nr6  | nr7  | nr8  | nr9  | nr10 |
+----+------+------+------+------+------+------+------+------+------+------+
| 1  | 1    | 39   | 19   | 23   | 28   | 80   | 3    | 42   | 60   | 32   |
+----+------+------+------+------+------+------+------+------+------+------+
| 2  | 43   | 18   | 3    | 24   | 29   | 33   | 15   | 1    | 61   | 80   |
+----+------+------+------+------+------+------+------+------+------+------+
| 3  | 11   | 25   | 33   | 2    | 30   | 3    | 1    | 44   | 62   | 78   |
+----+------+------+------+------+------+------+------+------+------+------+

所以每一行都需要有10个不同的值。

So each row need to have 10 different values.

我可以生成10个随机数，但是将它们之间的所有10个自身进行比较以确保

I could generate 10 random numbers but compare all 10 between them self to make sure there isn't duplicates values take a lot of time.

INSERT INTO test
SELECT trunc(random() * 89 + 1) as nr1,
       trunc(random() * 89 + 1) as nr2,
       trunc(random() * 89 + 1) as nr3,
       trunc(random() * 89 + 1) as nr4,
       trunc(random() * 89 + 1) as nr5,
       trunc(random() * 89 + 1) as nr6,
       trunc(random() * 89 + 1) as nr7,
       trunc(random() * 89 + 1) as nr8,
       trunc(random() * 89 + 1) as nr9,
       trunc(random() * 89 + 1) as nr10
FROM generate_series(1,50);

那么执行该任务的更有效方法是什么。

So what is the more efficient way to perform that task.

推荐答案

尝试：

-- INSERT INTO table( ...... )
CREATE TABLE test111 AS
SELECT 
       max( case when rn = 1 then nr end ) nr1,
       max( case when rn = 2 then nr end ) nr2,
       max( case when rn = 3 then nr end ) nr3,
       max( case when rn = 4 then nr end ) nr4,
       max( case when rn = 5 then nr end ) nr5,
       max( case when rn = 6 then nr end ) nr6,
       max( case when rn = 7 then nr end ) nr7,
       max( case when rn = 8 then nr end ) nr8,
       max( case when rn = 9 then nr end ) nr9,
       max( case when rn = 10 then nr end ) nr10
FROM generate_series(1,50000) id,
LATERAL ( 
  SELECT nr,
         row_number() over ( ORDER BY id * random() ) rn
  FROM generate_series(1,90) nr
)  x
GROUP BY id

此 CREATE TABLE 查询在我的PC上花费大约8-9秒：

查询正常，受影响的行数为50000（执行时间：8016秒；总时间：8016秒）

This CREATE TABLE query takes on my PC about 8-9 seconds:
Query OK, 50000 rows affected (execution time: 8,016 sec; total time: 8,016 sec)

编辑-PostgreSQL版本< ; = 9.2

CREATE TABLE test222 AS
SELECT        
       max( case when rn = 1 then nr end ) nr1,
       max( case when rn = 2 then nr end ) nr2,
       max( case when rn = 3 then nr end ) nr3,
       max( case when rn = 4 then nr end ) nr4,
       max( case when rn = 5 then nr end ) nr5,
       max( case when rn = 6 then nr end ) nr6,
       max( case when rn = 7 then nr end ) nr7,
       max( case when rn = 8 then nr end ) nr8,
       max( case when rn = 9 then nr end ) nr9,
       max( case when rn = 10 then nr end ) nr10
FROM (
    SELECT id, nr, row_number() over (partition by id order by random()) rn 
    FROM generate_series(1,50000) id
    CROSS JOIN (
      SELECT *  FROM generate_series(1,90) nr
    ) x
) y
WHERE rn <= 10
GROUP BY id

此版本在我的PC上大约需要7-8秒：< br>
查询确定，受影响的行数为50000（执行时间：7516秒；总时间：7,516秒）

This version takes about 7-8 seconds on my PC:
Query OK, 50000 rows affected (execution time: 7,516 sec; total time: 7,516 sec)

这篇关于创建10个不同的随机数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！