相关背景

在做性能调查或POC验证的时候，为目标表快速随机生成大量数据显得非常重要。下边以HANA为例进行一个简要的介绍。其实，各种DBMS在“造”数据方面，有些大同小异。在PostgreSQL里头已经简要介绍了完整的生成方法。

下边的某些内容，可能又让你回到数学数字时代，对数字数值需要有那么一点了解。

1.随机数

SELECT current_schema FROM dummy;
SET SCHEMA dbadmin;

-- 生成100以内的随机整数
SELECT CAST (rand()*100 AS int) FROM dummy;
76

使用rand()即可表示一个1以内的随机小数。但是这个数值很可能为0啊。有的时候这个满足不了要求。比如我要生成1到26之间的随机整数，并且不能出界：可以用下边的round()函数来得到：

-- SELECT floor((rand()*26 + 0.5)) FROM dummy;
SELECT round((rand()*26 + 0.5)) FROM dummy;
-- SELECT ceil((rand()*26 - 0.5)) FROM dummy;

这种方法在任何一种数据库中都是通用的。说白了，这三个函数就是API，是标准化的。floor和ceil，一个是退1取值，一个是进1取值。而round函数则是真正的四舍五入。比较一下下边的几个值就很清楚了：

SELECT floor(-0.1), floor(0.5), floor(0.51), floor(0.6) FROM dummy;
SELECT CEIL(-0.5), CEIL (-0.1), CEIL(0.49), CEIL (0.5) FROM dummy;
SELECT round(-0.1), round(0.49), round(0.5), round(0.51) FROM dummy;

2. 随机整数

4字节的int, 用2*10的9次幂来控制范围。8字节的int, 用9*10的18次幂来控制范围。

select CAST(rand() * (2*power(10, 9)) as int) FROM dummy;
338102167

select CAST(rand() * (9*power(10, 18)) as bigint) FROM dummy;
2371456246530383872

3. 随机Numeric

其实前边已经近似列出。示例如下：

select CAST(rand() * 100 AS numeric(4,2)) FROM dummy;
-- 34.74

4. 随机长度的重复字符串

select rpad('ab', CAST (rand()*20 AS int) * 2, 'ab') FROM dummy;
-- ababababababababab

当然，你用lpad也一样是可以的。用它以实现跟PG中repeat类似的效果。

5. 随机长度的重复二进制串
使用与前边类似的机制生成。只不过用的是二进制串表示法。

SELECT X'00abcd' "binary string 1", x'dcba00' "binary string 2" FROM DUMMY;
SELECT rpad(X'00abcd', CAST (rand()*20 AS int) * 2, X'00abcd') FROM dummy;
-- 00ABCD00ABCD00AB

6. 随机长度的子串
比如，现在有一个定长的串，我们要取其中随机长度的一个子串。

select substring('abcdefghijiklmnopqrstuvwxyz', 1, round(rand()*26 + 0.5)) FROM dummy;
-- abcdefghijiklmnop

7. 随机任意长度的字符串
上边介绍的是固定串的随机子串。这样的内容随机性并不是很好。更常见的是要生成任意长度的字符串。看看下边的例子：

SELECT string_agg(SUBSTRING('abcdefghijklmnopqrstuvwxyz', round((rand() * 26 + 0.5)), 1), '') AS random_str FROM SERIES_GENERATE_DECIMAL(1, 0, 10);
-- ouchhlnzol

这里用到了生成序列的函数SERIES_GENERATE_DECIMAL以及列转行聚集函数。只是生成序列指定了只循环10次。我们如果把这个次数参数化，就可以达成目的了。

CREATE FUNCTION random_string (IN max_count int)
RETURNS val CLOB
AS
BEGIN
 SELECT string_agg(SUBSTRING('abcdefghijklmnopqrstuvwxyz', round((rand() * 26 + 0.5)), 1), '') INTO val  FROM SERIES_GENERATE_DECIMAL(1, 0, max_count);
END;

SELECT random_string(20) FROM dummy;
-- lsjnfkfwnyfuftnqzwck

8. 固定枚举值中的随机值

SELECT ARRAY ( 'Beijing', 'Shenzhen', 'Nanjing', 'Hangzhou' ) FROM DUMMY;
-- ['Beijing','Shenzhen','Nanjing','Hangzhou']

SELECT CARDINALITY(ARRAY ( 'Beijing', 'Shenzhen', 'Nanjing', 'Hangzhou' ) )FROM DUMMY;
-- 4 ，得到数组的元素个数

SELECT MEMBER_AT(ARRAY ( 'Beijing', 'Shenzhen', 'Nanjing', 'Hangzhou' ), round((rand() * 4 + 0.5))) FROM DUMMY;
-- Shenzhen, 得到某一个随机位置的元素值
上边的示例一看就非常明白。

9. 日期类型的随机值

要用到函数: SERIES_GENERATE_TIMESTAMP

SELECT * FROM SERIES_GENERATE_TIMESTAMP('INTERVAL 1 SECOND', '1999-01-01 08:00:01.000', '1999-01-01 08:00:11.000');
1999-01-01 08:00:01.000    1999-01-01 08:00:02.000 1   0.1
1999-01-01 08:00:02.000    1999-01-01 08:00:03.000 2   0.1
1999-01-01 08:00:03.000    1999-01-01 08:00:04.000 3   0.1
1999-01-01 08:00:04.000    1999-01-01 08:00:05.000 4   0.1
1999-01-01 08:00:05.000    1999-01-01 08:00:06.000 5   0.1
1999-01-01 08:00:06.000    1999-01-01 08:00:07.000 6   0.1
1999-01-01 08:00:07.000    1999-01-01 08:00:08.000 7   0.1
1999-01-01 08:00:08.000    1999-01-01 08:00:09.000 8   0.1
1999-01-01 08:00:09.000    1999-01-01 08:00:10.000 9   0.1
1999-01-01 08:00:10.000    1999-01-01 08:00:11.000 10  0.1

生成随机的：从今年1月1号起的180天内的随机日期

SELECT ELEMENT_NUMBER, GENERATED_PERIOD_START FROM SERIES_GENERATE_TIMESTAMP('INTERVAL 1 DAY', '2023-01-01', '2023-12-31') WHERE ELEMENT_NUMBER = round((rand() * 180 + 0.5))

select ADD_DAYS('2023-01-01' , round((rand() * 180))) FROM dummy;
-- 2023-02-18

10. 生成序列

SELECT * FROM SERIES_GENERATE_DECIMAL(1,0,10);
SELECT element_number FROM  SERIES_GENERATE_DECIMAL(1,0,10);

这个可以详细参考函数 SERIES_GENERATE_DECIMAL的用法。它与PG中的generate_series()有异曲同工之妙。

11. 综合一下

建一个表，插入10天的数据。

CREATE TABLE TBIG(id int, col2 varchar(32), col3 timestamp);
INSERT INTO TBIG SELECT n.ELEMENT_NUMBER, random_string(32), n.GENERATED_PERIOD_START FROM SERIES_GENERATE_TIMESTAMP('INTERVAL 1 SECOND', '1999-01-01 08:00:01.000', '1999-01-10 08:00:11.000') AS n;

Updated Rows    777610
Query    INSERT INTO TBIG SELECT n.ELEMENT_NUMBER, random_string(32), n.GENERATED_PERIOD_START FROM SERIES_GENERATE_TIMESTAMP('INTERVAL 1 SECOND', '1999-01-01 08:00:01.000', '1999-01-10 08:00:11.000') AS n
Finish time    Tue Mar 07 22:51:42 GMT 2023

随机取几条记录看看：

SELECT top 5 * FROM TBIG ORDER BY rand();

534358    exehkptxtkwfvkeykhiepsufozuvapsz    1999-01-07 12:25:58.000
208860    pmxysenwszvgkmlvdrosuebxztycimxz    1999-01-03 18:01:00.000
542491    vuvpllthtefxrpixdmrvqpfqbppqqcfs    1999-01-07 14:41:31.000
718813    cvwhnilgaxsepdybecvzutnfqobgsbxt    1999-01-09 15:40:13.000
195289    xbbkuvexaxcbljzpldnmavrqpgbtoqhg    1999-01-03 14:14:49.000

有了上述方法，基本上可以构造任意随机和复杂长度的表数据。用起来还是非常方便的。

-- END --

ITPub博客

Hana中的大批量随机数据生成

最新文章