需求驱动
为了使得表格更好看些,我需要把查询结果合在一起,这样看起来就是一体的了。主要是性能测试有需求:
1、查询当前库设备
2、查询新增设备
3、计算每秒鉴权数
这个对我来说比较简单,分开查询就可以了。但我对自己不能要求这么低,我要把它放一起。然后遇到一些小问题,这里记录一下。
分开查询
查询语句:
SELECT count(1) "在库设备" FROM "public"."t_cloud_device" WHERE "create_time" < '-03-03 20:30:00'
查询结果:
查询语句:
SELECT count(1) "新增设备" FROM "public"."t_cloud_device" WHERE "create_time" BETWEEN '-03-03 20:30:00' AND '-03-04 09:30:00'
查询结果:
查询语句:
SELECT count(1)/13/3600 "鉴权/每秒" FROM "public"."t_cloud_device" WHERE "create_time" BETWEEN '-03-03 20:30:00' AND '-03-04 09:30:00'
查询结果:
综合查询
原始方式一:
SELECT count(a) "在库设备", count(b) "新增设备", count(b)/13/3600 "鉴权/每秒" FROM (SELECT * FROM "public"."t_cloud_device" WHERE "create_time" < '-03-03 20:30:00') a , (SELECT * FROM "public"."t_cloud_device" WHERE "create_time" BETWEEN '-03-03 20:30:01' AND '-03-04 09:30:00') b;
这种方式查询很久,不能说不对,只是我数据量太大(千万条数据),因此查询时间久到离谱。
优化方式二:
SELECT a.c "在库设备", b.d "新增设备", b.d/13/3600 "鉴权/每秒" FROM (SELECT count(1) c FROM "public"."t_cloud_device" WHERE "create_time" < '-03-03 20:30:00') a , (SELECT count(1) d FROM "public"."t_cloud_device" WHERE "create_time" BETWEEN '-03-03 20:30:01' AND '-03-04 09:30:00') b;