GENERATOR
声明
GENERATOR(N)
说明
该函数为 Table Function 框架中的一个内置函数，即允许在 Table Function 中调用它，最终返回 N 行数据。N 是一个大于等于 0 的 64 位正整数。
Table function 是一种在 SQL 语言中使用的函数，它能够返回一张数据表作为结果。与传统的 SQL 函数只能返回标量值不同，Table Function 可以返回多行、多列的数据集。
TABLE(GENERATOR(N)) 并不真的生成任何数据，它只会产生连续 N 个 “行”，如果需要生成数据，需要在 SELECT 后面添加具体的表达式，例如常量、变量、函数、UDF 等。TABLE(GENERATOR(N)) 可以用于并行执行，例如并行 SELECT、并行 DML 等，但是负责读取 TABLE(GENERATOR(N)) 生成行的线程只会有一个，也就是说，包含 TABLE(GENERATOR(N)) 的 DFO 的 DOP=1。
示例
如下示例为使用 GENERATOR() 函数生成符合条件的随机数。
SELECT COUNT(*) FROM TABLE(GENERATOR(100000));
+----------+
| COUNT(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.001 sec)
SELECT NORMAL(0, 1, RANDOM()) FROM TABLE(GENERATOR(5));
+------------------------+
| NORMAL(0, 1, RANDOM()) |
|------------------------|
| 0.227384164 |
| 0.9945290748 |
| -0.2045078571 |
| -1.594607893 |
| -0.8213296842 |
+------------------------+
5 rows in set (0.001 sec)
SELECT RANDSTR(1, ZIPF(1, 5, RANDOM())) str FROM TABLE(GENERATOR(5));
+------------------------+
| str |
|------------------------|
| A |
| D |
| A |
| A |
| C |
+------------------------+
5 rows in set (0.001 sec)
GENERATOR() 函数也可以和其它表做 Join，示例如下：
CREATE TABLE t1 (c1 BIGINT);
Query OK, 0 rows affected (0.140 sec)
INSERT INTO t1 VALUES (1), (2);
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
SELECT c1, RANDOM(1) FROM t1, TABLE(GENERATOR(3));
+------+----------------------+
| c1 | random(1) |
+------+----------------------+
| 1 | -6753783847308464280 |
| 2 | -6707106347154343346 |
| 1 | -899926183391115878 |
| 2 | -8835543475904200562 |
| 1 | -2750444335953844424 |
| 2 | 7588216632478230601 |
+------+----------------------+
6 rows in set (0.002 sec)
EXPLAIN SELECT c1, RANDOM(1) FROM t1, TABLE(GENERATOR(3));
+------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------+
| ================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------ |
| |0 |NESTED-LOOP JOIN CARTESIAN | |398 |14 | |
| |1 |├─FUNCTION_TABLE |FUNC_TABLE1|199 |1 | |
| |2 |└─MATERIAL | |2 |2 | |
| |3 | └─TABLE FULL SCAN |t1 |2 |2 | |
| ================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1], [RANDOM(1)]), filter(nil), rowset=256 |
| conds(nil), nl_params_(nil), use_batch=false |
| 1 - output(nil), filter(nil) |
| value(GENERATOR(3)) |
| 2 - output([t1.c1]), filter(nil), rowset=256 |
| 3 - output([t1.c1]), filter(nil), rowset=256 |
| access([t1.c1]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------+
19 rows in set (0.001 sec)
SELECT /*+ PARALLEL(2) */ c1, RANDOM(1) FROM t1, TABLE(GENERATOR(3));
+------+----------------------+
| c1 | random(1) |
+------+----------------------+
| 1 | -6753783847308464280 |
| 2 | -6707106347154343346 |
| 1 | -899926183391115878 |
| 2 | -8835543475904200562 |
| 1 | -2750444335953844424 |
| 2 | 7588216632478230601 |
+------+----------------------+
6 rows in set (0.001 sec)
EXPLAIN SELECT /*+ PARALLEL(2) */ c1, RANDOM(1) FROM t1, TABLE(GENERATOR(3));
+------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------+
| ================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------ |
| |0 |NESTED-LOOP JOIN CARTESIAN | |398 |13 | |
| |1 |├─FUNCTION_TABLE |FUNC_TABLE1|199 |1 | |
| |2 |└─MATERIAL | |2 |2 | |
| |3 | └─PX COORDINATOR | |2 |2 | |
| |4 | └─EXCHANGE OUT DISTR |:EX10000 |2 |2 | |
| |5 | └─PX BLOCK ITERATOR | |2 |1 | |
| |6 | └─TABLE FULL SCAN |t1 |2 |1 | |
| ================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1], [RANDOM(1)]), filter(nil), rowset=256 |
| conds(nil), nl_params_(nil), use_batch=false |
| 1 - output(nil), filter(nil) |
| value(GENERATOR(3)) |
| 2 - output([t1.c1]), filter(nil), rowset=256 |
| 3 - output([t1.c1]), filter(nil), rowset=256 |
| 4 - output([t1.c1]), filter(nil), rowset=256 |
| dop=2 |
| 5 - output([t1.c1]), filter(nil), rowset=256 |
| 6 - output([t1.c1]), filter(nil), rowset=256 |
| access([t1.c1]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------+
26 rows in set (0.002 sec)