欢迎光临
梦想从学习开始!

使用SQL生成测试数据| 小熊测试

本文主要介绍 使用SQL生成测试数据| 小熊测试,小熊希望对大家的学习或者工作具有一定的参考学习价值,在测试领域有所提升和发展。

  无论您是在用原型证明某一概念,还是开发一个全新的应用程序,或者只是学习 SQL,您都需要在您的应用程序上运行测试数据。为了有效地测试应用程序的性能,您必须拥有足够的测试数据,以便暴露潜在的性能问题。只要可以得到,用实际数据来进行测试总是更可取一些。如果没有可用的实际数据,那么在许多情况下,也可以生成足够的假想数据。一般来说,从头开始构造大量数据是件很容易的工作,您自己就可以快速地独立完成。

  本文提供了一些如何利用 SQL 脚本来生成测试数据的示例,而这些脚本本身就是较好的 SQL 实践。并且还讨论了一些为了生成尽可能真实的数据而应该注意的问题。

 
 生成大量记录

  即使数据库是新创建且仍然为空的,也总是会带有系统表和视图,因此,您可以按以下方法使用它们:

  CREATE TABLE DB2ADMIN.SALES

  (CUSTOMER_ID INT NOT NULL, ITEM_ID INT NOT NULL,

  SALE_QUANTITY SMALLINT NOT NULL, SALE_DATE DATE NOT NULL);

  INSERT INTO SALES

  SELECT

  SYSFUN.RAND()*500 + 1 AS CUSTOMER_ID,

  SYSFUN.RAND()*100 + 1 AS ITEM_ID,

  1 + SYSFUN.RAND()*10 AS SALE_QUANTITY,

  DATE('01/01/2003') + (SYSFUN.RAND()*200) DAYS AS SALE_DATE

  FROM SYSCAT.COLUMNS;

  SALES 表中的记录数就与 SYSCAT.COLUMNS 中的完全一样了。请注意,多个列都是用随机值来填充的。例如,SALE_QUANTITY 列中的所有值都是处于 1 到 10 之间,约 10% 的记录具有各不相同的值。如果您需要更多记录,就可以根据需要多次重复执行这条 INSERT 语句。您还可以像下面这样使用交叉连接(CROSS JOIN),以便每条语句获得更多记录:

  INSERT INTO SALES

  SELECT

  SYSFUN.RAND()*500 + 1 AS CUSTOMER_ID,

  SYSFUN.RAND()*100 + 1 AS ITEM_ID,

  1 + SYSFUN.RAND()*10 AS SALE_QUANTITY,

  DATE('01/01/2003') + (SYSFUN.RAND()*2000) DAYS AS SALE_DATE

  FROM SYSCAT.TABLES T1 JOIN SYSCAT.TABLES T2;

  注意:本例中,表 T1 和 T2 的连接是不含任何条件的,因此,T1 中的每一行会匹配 T2 中的每一行。这种类型的连接称作交叉连接。关于交叉连接的更多信息,请查阅 Joe Celko 的 SQL for Smarties一书。

  注意:这条 INSERT 语句所涉及的事务可能会相当大,以致于您的服务器无法加以处理。如果您遇到“log full”的情况(SQL0964C 数据库的事务日志已满),您可能需要增加日志空间,或者通过指定 T1 或 T2 或两者中的 WHERE 子句来获得一个较小的事务。

  您可以使用该方法来生成大量记录,然而,该方法有点过分简单了,因为所有的值都是均匀分布的,而且它们之间不存在相关性。

  填充子表

  您的数据库中很可能存在多对一的关系。下列示例展示了如何填充子表,以使每一条父记录都具有随机的多条子记录。

  CREATE TABLE DB2ADMIN.PARENT_TABLE(PARENT_ID INT NOT NULL, NUM_CHILDREN

  INT NOT NULL);

  INSERT INTO DB2ADMIN.PARENT_TABLE

  SELECT ROW_NUMBER() OVER(), SYSFUN.RAND()*5 + 1

  FROM SYSCAT.TABLES;

  ALTER TABLE DB2ADMIN.PARENT_TABLE ADD PRIMARY KEY(PARENT_ID);

  CREATE TABLE DB2ADMIN.CHILD_TABLE(PARENT_ID INT NOT NULL, CHILD_NUM INT

  NOT NULL);

  INSERT INTO DB2ADMIN.CHILD_TABLE

  SELECT PARENT_ID, SEQUENCE_TABLE.NUM

  FROM DB2ADMIN.PARENT_TABLE

  JOIN

  (SELECT ROW_NUMBER() OVER() AS NUM

  FROM SYSCAT.TABLES) AS SEQUENCE_TABLE

  ON AUXILIARY_TABLE.NUM<NUM_CHILDREN;

  最后一条 INSERT 语句的结果是,每一条父记录有 1 到 6 条子记录。SEQUENCE_TABLE 是一个表表达式。关于表表达式的更多信息,请查阅 Sheryl Larsen 的文章。

  使用辅助表模仿数据倾斜

  如果一列中的某些值所出现的频率比其他的要大很多,则该数据存在 数据倾斜(data skew)。例如:

  SELECT CITY, COUNT(*) FROM CUSTOMER

  GROUP BY CITY

  ORDER BY COUNT(*) DESC

  CHICAGO    236

  MILWAKEE  95

  ROCKFORD  4

  NAPERVILLE  3

  SPRINGFIELD  3

  (snip)

  279 rows selected

  每当您有理由期望在生产数据中出现数据倾斜时,您就可能需要在测试数据中再现数据倾斜,首先,在一个表中存储预计频率:

  CREATE TABLE COLOR_FREQUENCY(COLOR CHAR(10), FREQUENCY SMALLINT);

  INSERT INTO COLOR_FREQUENCY VALUES

  ('RED', 37), ('SILVER',12), ('AMBER', 3), ('GREEN', 3),

  ('WHITE',2),('BLACK', 1),('BLUE',1);

  接着,创建一个辅助表(更明确地说,是一个序列表)。

  CREATE TABLE CONSECUTIVE_NUMBER(NUM INT NOT NULL);

  INSERT INTO CONSECUTIVE_NUMBER

  SELECT ROW_NUMBER() OVER() AS NUM FROM SYSCAT.COLUMNS;

  注意:Joe Celko 的 SQL for Smarties 一书中有一章是关于辅助表的。现在,让我们连接这两个表:

  SELECT COLOR, FREQUENCY, NUM

  FROM COLOR_FREQUENCY JOIN CONSECUTIVE_NUMBER

  ON NUM BETWEEN 1 AND FREQUENCY ORDER BY FREQUENCY, COLOR;

  COLOR      FREQUENCY NUM        

  ———- ——— ———–

  BLACK              1           1

  BLUE               1           1

  WHITE              2           1

  WHITE              2           2

  AMBER              3           1

  AMBER              3           2

  AMBER              3           3

  (SNIP)

  正如我们所看到的,COLOR_FREQUENCY 表中的每一行都连接了 CONSECUTIVE_NUMBER 表中的 FREQUENCY 行。该示例生成了您需要用于获得所需值分布的确切内容:

  CREATE TABLE T_SHIRT(COLOR VARCHAR(30) NOT NULL, SIZE CHAR(1) NOT NULL);

  INSERT INTO T_SHIRT

  SELECT COLOR, 'M' AS SIZE

  FROM COLOR_FREQUENCY JOIN CONSECUTIVE_NUMBER

  ON NUM BETWEEN 1 AND FREQUENCY;

  SELECT COLOR, COUNT(*) FROM T_SHIRT GROUP BY COLOR;

  COLOR                          2          

  —————————— ———–

  AMBER                                    3

  BLACK                                    1

  BLUE                                     1

  GREEN                                    3

  RED                                     37

  SILVER                                  12

  WHITE                                    2 ;

  因此,T_SHIRT 表现在有 37+12+3+3+2+1+1 = 57 行。该表刚好具有所需的值分布。

  为几个列生成具有给定值分布的数据

  使用前一章中的所用表,您还可以为 SIZE 列指定值分布:

  CREATE TABLE SIZE_FREQUENCY(SIZE CHAR(1), FREQUENCY SMALLINT);

  INSERT INTO SIZE_FREQUENCY VALUES

  ('S', 5), ('M',7), ('L', 9);

  并使用两个表表达式来填充 T_SHIRT 表:

  INSERT INTO T_SHIRT

  SELECT COLOR, SIZE

  FROM

  (SELECT COLOR FROM COLOR_FREQUENCY JOIN CONSECUTIVE_NUMBER ON NUM

  BETWEEN 1 AND FREQUENCY) C,

  (SELECT SIZE FROM SIZE_FREQUENCY JOIN CONSECUTIVE_NUMBER ON NUM BETWEEN

  1 AND FREQUENCY) S

  第一个表表达式产生 57 行,而第二个表表达式则产生 5+7+9=21 行。由于我们没有指定任何连接条件,所以第一个结果集中的每一行将会连接第二个中的每一行,从而产生 57*21 行。

  注意:交叉连接可能会生成太多行。因此,该事务将太大,以致服务器无法处理。本例中,您可能需要几个较小一些的 INSERT 语句,例如在第一个 INSERT 中使用以下表表达式:

  (SELECT SIZE FROM SIZE_FREQUENCY JOIN CONSECUTIVE_NUMBER ON NUM BETWEEN

  1 AND FREQUENCY AND SIZE='L') S

  并且在第二个 INSERT 语句中将这个表表达式修改为:

  (SELECT SIZE FROM SIZE_FREQUENCY JOIN CONSECUTIVE_NUMBER ON NUM BETWEEN

  1 AND FREQUENCY AND SIZE<>'L') S

  生成具有相关列的数据

  假定我们需要生成几行记录来填充 CAR 表:

  CREATE TABLE CAR(

  MAKE VARCHAR(20) NOT NULL,

  MODEL VARCHAR(20) NOT NULL,

  OTHER_DATA VARCHAR(20));

  如果尝试前一章中的方法,我们最后将获得一些不可能的 MAKE/MODEL 组合,例如“TOYOTA METRO”和“GEO CAMRY”。该状况称作 MAKE 列和 MODEL 列之间的相关性。正确的方法是指定有效对(MAKE,MODEL)及其频率:

  CREATE TABLE MAKE_MODEL_FREQUENCY(MAKE VARCHAR(20), MODEL VARCHAR(20),

  FREQUENCY SMALLINT);

  INSERT INTO MAKE_MODEL_FREQUENCY VALUES

  ('TOYOTA','CAMRY', 40), ('HONDA','ACCORD',40), ('CHEVY', 'PRIZM', 5),

  ('GEO','PRIZM',  5),  ('CHEVY', 'METRO', 5), ('GEO', 'METRO', 10);

  一旦完成该工作,我们就可以按照前面一模一样的方法来连接 CONSECUTIVE_NUMBER 和 MAKE_MODEL_FREQUENCY 表了。

  操纵群集因子

  表的物理行次序将影响该表上几乎所有查询的性能。因此,所生成的数据具有理想的物理行次序是极其重要的。如果您期望一个索引具有较高的群集因子,就只要重组该索引上的表。相反,如果您期望该索引具有较低的群集因子,也可以容易地以随机次序来打乱该表的次序,从而使得该索引的群集因子接近于 0:

  CREATE TABLE  NAMES(

  FULL_NAME VARCHAR(50)  NOT NULL,

  ORDERED_ON INT);

  INSERT INTO NAMES(FULL_NAME, ORDERED_ON)

  SELECT TABNAME || ', ' || COLNAME AS FULL_NAME,

  SYSFUN.RAND() * 10000 AS ORDERED_ON

  FROM SYSCAT.COLUMNS;

  CREATE INDEX NAMES_FULL_NAME ON NAMES(FULL_NAME);

  CREATE INDEX NAMES_ORDER ON NAMES(ORDERED_ON);

  REORG TABLE DB2ADMIN.NAMES INDEX DB2ADMIN.NAMES_ORDER;

  RUNSTATS ON TABLE DB2ADMIN.NAMES AND DETAILED INDEXES ALL;

  在进行重组之后,索引 NAMES_FULL_NAME 将具有一个极低的群集因子(接近于 0),因为现在的行是以随机次序存储的。

  注意:还可以重组该表,以使索引 NAMES_FULL_NAME 的群集因子接近 0 到 1 之间的任何给定值,但是,该内容超出了本文的范围。

  结束语

  本文讨论了如何构建一个测试数据集,以使该数据集达到用于测试的规模,并且具有期望的值分布和列间相关性。

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持小熊分享邦(www.xxfxb.com),希望大家能坚持软件测试之路,谢谢。

赞(0) 打赏
未经允许不得转载:小熊分享邦 » 使用SQL生成测试数据| 小熊测试

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏