使用Sql生成测试数据
上一篇 / 下一篇 2008-05-31 08:02:39 / 个人分类:数据库
F2Z.L B7k1J o0作者:Alexander Kuznetsov
VOcxs8q0
无论您是在用原型证明某一概念,还是开发一个全新的应用程序,或者只是学习 SQL,您都需要在您的应用程序上运行测试数据。本文讨论了如何生成足够的、对测试有用的、具有期望的值分布和列间相关性的测试数据。 |
使用 SQL 生成大量测试数据 |
51Testing软件测试网RR4\|-d2Wd5a:k 无论您是在用原型证明某一概念,还是开发一个全新的应用程序,或者只是学习 SQL,您都需要在您的应用程序上运行测试数据。为了有效地测试应用程序的性能,您必须拥有足够的测试数据,以便暴露潜在的性能问题。只要可以得到,用实际数据来进行测试总是更可取一些。如果没有可用的实际数据,那么在许多情况下,也可以生成足够的假想数据。一般来说,从头开始构造大量数据是件很容易的工作,您自己就可以快速地独立完成。51Testing软件测试网"}.|h'B8Cm I g[ w` Hv^x0本文提供了一些如何利用 SQL 脚本来生成测试数据的示例,而这些脚本本身就是较好的 SQL 实践。并且还讨论了一些为了生成尽可能真实的数据而应该注意的问题。 7k!fZz9JaR7Y0 |
生成大量记录 |
即使数据库是新创建且仍然为空的,也总是会带有系统表和视图,因此,您可以按以下方法使用它们: |
CREATE TABLE DB2ADMIN.SALES +fv"M"B o6I0(CUSTOMER_ID INT NOT NULL, ITEM_ID INT NOT NULL,51Testing软件测试网^5Id8G;F|\\ FT SALE_QUANTITY SMALLINT NOT NULL, SALE_DATE DATE NOT NULL);51Testing软件测试网iSph n_ INSERT INTO SALES 6oM"q6U1wbg0SELECT51Testing软件测试网I}5C"CdAI SYSFUN.RAND()*500 + 1 AS CUSTOMER_ID, _*czMyL0SYSFUN.RAND()*100 + 1 AS ITEM_ID,51Testing软件测试网4{'R X;DDE%u 1 + SYSFUN.RAND()*10 AS SALE_QUANTITY,51Testing软件测试网D6J O*jIy.] DATE('01/01/2003') + (SYSFUN.RAND()*200) DAYS AS SALE_DATE U$Wx:?k5j^0FROM SYSCAT.COLUMNS; |
g bx)@n0SALES 表中的记录数就与 SYSCAT.COLUMNS 中的完全一样了。请注意,多个列都是用随机值来填充的。例如,SALE_QUANTITY 列中的所有值都是处于 1 到 10 之间,约 10% 的记录具有各不相同的值。如果您需要更多记录,就可以根据需要多次重复执行这条 INSERT 语句。您还可以像下面这样使用交叉连接(CROSS JOIN),以便每条语句获得更多记录: nd%f9}Wz$YQ0 |
51Testing软件测试网fqD"eHW 注意:本例中,表 T1 和 T2 的连接是不含任何条件的,因此,T1 中的每一行会匹配 T2 中的每一行。这种类型的连接称作交叉连接。51Testing软件测试网0t(BrJ,J r_ L&ir!Moj,] @0注意:这条 INSERT 语句所涉及的事务可能会相当大,以致于您的服务器无法加以处理。如果您遇到“log full”的情况(SQL0964C 数据库的事务日志已满),您可能需要增加日志空间,或者通过指定 T1 或 T2 或两者中的 WHERE 子句来获得一个较小的事务。 {j;\U"qGO"H$k051Testing软件测试网,G,z[nU y'x/v#Mvc!~您可以使用该方法来生成大量记录,然而,该方法有点过分简单了,因为所有的值都是均匀分布的,而且它们之间不存在相关性。 !_8S'fs,XwT^0 |
填充子表 |
您的数据库中很可能存在多对一的关系。下列示例展示了如何填充子表,以使每一条父记录都具有随机的多条子记录。 |
CREATE TABLE DB2ADMIN.PARENT_TABLE(PARENT_ID INT NOT NULL, NUM_CHILDREN INT NOT NULL); -ue5sF-S$].?,w0INSERT INTO DB2ADMIN.PARENT_TABLE51Testing软件测试网i+_ s5Q9Jh] SELECT ROW_NUMBER() OVER(), SYSFUN.RAND()*5 + 151Testing软件测试网%Y |T L9F FROM SYSCAT.TABLES;51Testing软件测试网)q1G;O_ |Z(j4DZ ALTER TABLE DB2ADMIN.PARENT_TABLE ADD PRIMARY KEY(PARENT_ID);51Testing软件测试网o}yuUl?| ~bD CREATE TABLE DB2ADMIN.CHILD_TABLE(PARENT_ID INT NOT NULL, CHILD_NUM INT NOT NULL); v4S*o"dQ!j5lA0|3s jRN0INSERT INTO DB2ADMIN.CHILD_TABLE51Testing软件测试网u?v9hM |
最后一条 INSERT 语句的结果是,每一条父记录有 1 到 6 条子记录。SEQUENCE_TABLE 是一个表表达式。 |
使用辅助表模仿数据倾斜 |
如果一列中的某些值所出现的频率比其他的要大很多,则该数据存在数据倾斜(data skew)。例如: |
SELECT CITY, COUNT(*) FROM CUSTOMER 4q#Cx%rsFU"Z)z x0GROUP BY CITY51Testing软件测试网/{W2u9I E"s|C ORDER BY COUNT(*) DESC51Testing软件测试网o"G s VGKz!QU CHICAGO 23651Testing软件测试网?kHY"k 279 rows selected Xq_dl,O9K6Tr0 |
每当您有理由期望在生产数据中出现数据倾斜时,您就可能需要在测试数据中再现数据倾斜,首先,在一个表中存储预计频率: |
CREATE TABLE COLOR_FREQUENCY(COLOR CHAR(10), FREQUENCY SMALLINT);51Testing软件测试网eI@
z.\2Q/z INSERT INTO COLOR_FREQUENCY VALUES 9r}-Oq8tx2p+x,T"}0('RED', 37), ('SILVER',12), ('AMBER', 3), ('GREEN', 3), ('WHITE',2),('BLACK', 1),('BLUE',1); |
接着,创建一个辅助表(更明确地说,是一个序列表)。 |
CREATE TABLE CONSECUTIVE_NUMBER(NUM INT NOT NULL);51Testing软件测试网%\m
dYl ?(G? INSERT INTO CONSECUTIVE_NUMBER j2FiJ&P mNM^yGm0SELECT ROW_NUMBER() OVER() AS NUM FROM SYSCAT.COLUMNS; |
注意:Joe Celko 的 SQL for Smarties 一书中有一章是关于辅助表的。现在,让我们连接这两个表: |
SELECT COLOR, FREQUENCY, NUM "E*t p2Y,T"?z0FROM COLOR_FREQUENCY JOIN CONSECUTIVE_NUMBER )U[9zT` Met0ON NUM BETWEEN 1 AND FREQUENCY ORDER BY FREQUENCY, COLOR;51Testing软件测试网\/s)Jr]*g] COLOR FREQUENCY NUM51Testing软件测试网`igvB |
正如我们所看到的,COLOR_FREQUENCY 表中的每一行都连接了 CONSECUTIVE_NUMBER 表中的 FREQUENCY 行。该示例生成了您需要用于获得所需值分布的确切内容: |
CREATE TABLE T_SHIRT(COLOR VARCHAR(30) NOT NULL, SIZE CHAR(1) NOT NULL);51Testing软件测试网Qs%G r0Lbg4z INSERT INTO T_SHIRT |7}!AVGYoB9l0SELECT COLOR, COUNT(*) FROM T_SHIRT GROUP BY COLOR;51Testing软件测试网)W4z9n:@'_ l.r 51Testing软件测试网5{wtNHw-w)f*i2SCOLOR 2 |
因此,T_SHIRT 表现在有 37+12+3+3+2+1+1 = 57 行。该表刚好具有所需的值分布。 |
为几个列生成具有给定值分布的数据 |
使用前一章中的所用表,您还可以为 SIZE 列指定值分布: |
CREATE TABLE SIZE_FREQUENCY(SIZE CHAR(1), FREQUENCY SMALLINT); |