从两个SQL查询的数字结果中计算百分比的更好方法是什么

Question

我有两个oracle(11g(SQL查询，每个查询给出一个数字结果。

SQL1：

select count(*) as count1 
from table_name1
where col1_number in (2400)
and (col2_varchar2 = '' or col2_varchar2 is null or col2_varchar2 = 'BLANK' or 
col2_varchar2 = 'TBA' or col2_varchar2 like 'B%')
and col3_varchar2 = 'app31'
and col4_date > (sysdate - 7)

SQL2：

select count(*) as count2 
from table_name1
where col1_number in (2400)
and col3_varchar2 = 'app31'
and col4_date > (sysdate - 7)

假设我从 SQL1 中得到 35，从 SQL2 得到 831 作为结果。然后我想编写一个 SQL，它将从 SQL1 和 SQL2 中获取这两个结果并计算 (35 * 100(/831。

我已经编写了一个SQL来做到这一点。

SQL3：

select round((count1 * 100)/count2,3) as percent_missing from
(
with temp_table1 as (
select count(*) as count1 
from table_name1
where col1_number in (2400)
and (col2_varchar2 = '' or col2_varchar2 is null or col2_varchar2 = 
'BLANK' or col2_varchar2 = 'TBA' or col2_varchar2 like 'B%')
and col3_varchar2 = 'app31'
and col4_date > (sysdate - 7)
), 
temp_table2 as (
select count(*) as count2 
from table_name1
where col1_number in (2400)
and col3_varchar2 = 'app31'
and col4_date > (sysdate - 7)
) 
select count1, count2 
from temp_table1, temp_table2
);

问：有没有更好的方法可以做到这一点？我想知道在这种情况下是否推荐任何其他/更好的方法。

Answer 1

我可以看到 SQL1 和 SQL2 使用相同的表和几乎相同的 where 子句，除了 SQL1 中额外的 AND 条件，所以我们为什么不像下面这样合并它们：

select round((a.count1 * 100)/a.count2,3) as percent_missing from (
select count(case when (col2_varchar2 = '' or col2_varchar2 is null or col2_varchar2 = 
'BLANK' or col2_varchar2 = 'TBA' or col2_varchar2 like 'B%') then 1 end ) as count1, 
count(1) as count2 
from table_name1 where
col1_number in (2400) and 
and col3_varchar2 = 'app31'
and col4_date > (sysdate - 7)
) as a

我无法对此进行测试，因为我没有表和它们的架构，请原谅我的编译错误，但你会在这里得到想法。