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)
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 ;
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 行,而第二个表表达式则产生
延伸阅读
文章来源于领测软件测试网 https://www.ltesting.net/