PostgreSQL 任意字段数组合 AND\OR 条件,指定返回结果条数,构造测试数据算法举例-阿里云开发者社区

开发者社区> 德哥> 正文

PostgreSQL 任意字段数组合 AND\OR 条件,指定返回结果条数,构造测试数据算法举例

简介: 标签 PostgreSQL , 构造测试数据 , 任意字段组合AND,OR查询 , 指定结果集大小 背景 在进行一些实际的POC测试时,需要根据业务提出的需求构造数据,比如按照任意字段数组合 AND\OR 条件,指定返回结果条数,构造测试数据。
+关注继续查看

标签

PostgreSQL , 构造测试数据 , 任意字段组合AND,OR查询 , 指定结果集大小


背景

在进行一些实际的POC测试时,需要根据业务提出的需求构造数据,比如按照任意字段数组合 AND\OR 条件,指定返回结果条数,构造测试数据。

需求

表记录数A

表字段数B

1、N个字段等值OR,命中M条记录

(两个条件无法同时满足)

2、X个字段等值AND,命中Y条记录

字段取值空间如何计算?

构造算法

1、N个字段等值OR,命中M条记录

单个字段单个VALUE的记录数 = M/N

单个字段取值个数 = A/(M/N)

2、X个字段等值AND,命中Y条记录

(仅适用于完全离散分布,优化器里最难估算的也是多个字段AND的选择性,所以PG 10增加了多列统计信息)

《PostgreSQL 10 黑科技 - 自定义统计信息》

X个字段的总取值空间 = A/Y

单个字段的取值空间 = X_/(A/Y) (开X根)

例子

1、表记录数1000万

2、表字段数64

字段取值空间如何计算?

1、16个字段等值OR,命中1000条记录

单个字段取值个数 = 10000000/(1000/16.0) = 160000

1、建表,64字段,根据要求填入每个字段的取值范围

do language plpgsql $$  
declare  
  sql text := 'create table test1 (id int, ';  
begin  
  for i in 1..64 loop  
    sql := sql||' c'||i||' int default random()*160000,';  -- 单个字段取值空间  
  end loop;  
  sql := rtrim(sql,',');  
  sql := sql||')';  
  execute sql;  
end;  
$$;  

根据前面提供的需求,写入1000万记录

insert into test1 select generate_series(1,10000000);  

根据要求生成查询SQL,16个字段组合OR

do language plpgsql $$  
declare  
  sql text := 'select count(*) from test1 where ';  
begin  
  for i in 1..16 loop  
    sql := sql||' c'||i||' ='||(random()*160000)::int||' or';   -- 16个字段 or 查询   
  end loop;  
  sql := rtrim(sql,'or');  
  raise notice '%', sql;  
end;  
$$;  

生成SQL

select count(*) from test1 where    
c1 =143477 or c2 =153395 or c3 =102052 or c4 =151143 or c5 =129060 or   
c6 =87519 or c7 =148787 or c8 =123117 or c9 =126622 or c10 =118215 or   
c11 =134245 or c12 =53791 or c13 =151020 or c14 =53076 or c15 =143204 or c16 =51640 ;  

SQL实际返回数

 count   
-------  
   905  
(1 row)  

与算法预期基本一致(1000)。

2、16个字段等值AND,命中20条记录

单个字段的取值空间 = 16_/(10000000/20) = 2.27

1、根据算法,得到取值空间,创建测试表

do language plpgsql $$  
declare  
  sql text := 'create table test2 (id int, ';  
begin  
  for i in 1..64 loop  
    sql := sql||' c'||i||' int default random()*1,';  -- 单个字段取值空间  
  end loop;  
  sql := rtrim(sql,',');  
  sql := sql||')';  
  execute sql;  
end;  
$$;  

写入1000万数据

insert into test2 select generate_series(1,10000000);  

生成测试SQL,16个字段,OR查询

do language plpgsql $$  
declare  
  sql text := 'select count(*) from test2 where ';  
begin  
  for i in 1..16 loop  
    sql := sql||' c'||i||' ='||(random()*1)::int||' and';  -- 16个字段 and 查询   
  end loop;  
  sql := rtrim(sql,'and');  
  raise notice '%', sql;  
end;  
$$;  

生成SQL

select count(*) from test2 where  c1 =1 and c2 =0 and c3 =0 and c4 =1 and   
c5 =1 and c6 =1 and c7 =0 and c8 =1 and c9 =0 and c10 =0 and c11 =0 and   
c12 =0 and c13 =0 and c14 =0 and c15 =1 and c16 =0;  

SQL实际返回数

 count   
-------  
   154  
(1 row)  

与算法预期基本一致(取值范围作了取舍2.27,降到了2)。

扩展问题

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
使用OData服务创建SAP C4C的Lead数据,必须指定Account字段
使用OData服务创建SAP C4C的Lead数据,必须指定Account字段
8 0
DL之Attention:基于ClutteredMNIST手写数字图片数据集分别利用CNN_Init、ST_CNN算法(CNN+SpatialTransformer)实现多分类预测(一)
DL之Attention:基于ClutteredMNIST手写数字图片数据集分别利用CNN_Init、ST_CNN算法(CNN+SpatialTransformer)实现多分类预测
40 0
图的单源最短路径,Floyd算法(数据结构c++)
这个算法结构很是简单,但是理解还是有一定的困难,一开始做的时候想不明白,跟着算法自己动手画画就知道这个算法具体是怎么回事了。 时间复杂度是O(N*3) 算法有点动态规划的意思,有两个数组,一个(dis[])是记录俩顶点之间的最短路径的长度的,一个[path]数组是记录俩结点的中间结点的。
787 0
MYSQL数据库字母数字混合字段排序问题
对MySQL数据表里的一个字符型字段排序,其内容格式为一位字母+顺序数字。数字没有前导零,长度不固定。这种含字母的数字序列,排序出来的结果和我们想要的结果是不一样的,因为它不是纯数字,只能按字符规则排。
1938 0
CVPR2021竞赛结果出炉,阿里淘系多媒体算法包揽3项国际冠军
在刚刚落下帷幕的计算机视觉与模式识别领域顶级会议 CVPR 2021 上,各项国际挑战赛的竞赛结果已全部揭晓。
168 0
关于mysql字段时间类型timestamp默认值为当前时间问题
今天把应用部署到AWS上发现后台修改内容提交后程序报错,经过排查发现是更新数据的时候,有张数据表中的一个timestamp类型的字段默认值变成了"0000-00-00 00:00:00.000000"格式,导致解析失败造成的。 在mysql该字段的创建语句如下 `XXX` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CU
1639 0
+关注
德哥
公益是一辈子的事, I'm digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载