这两种SQL样式之间有什么区别？

Question

如果我在Redshift/Postgresql中有一个名为: conn_log的简单表

在视图的性能方面，这两个命令有什么不同？

select t1.wifi_name, (t2.sconn*100)::numeric/t1.ttconn
from (select wifi_name, count(*) as ttconn 
      from conn_log 
      group by wifi_name) t1, 
     (select wifi_name, count(*) as sconn 
      from conn_log 
      where success = 1 
      group by wifi_name) t2
where t1.wifi_name = t2.wifi_name;

第二个查询：

select t1.wifi_name, (t2.sconn*100)::numeric/t1.ttconn
from (select wifi_name, count(*) as ttconn 
      from conn_log 
      group by wifi_name) t1
join
     (select wifi_name, count(*) as sconn 
      from conn_log 
      where success = 1 
      group by wifi_name) t2
on t1.wifi_name = t2.wifi_name 

Answer 1

关于INNER JOIN...ON和WHERE子句的区别，这里有一个很好的答案。这里有几个答案，而被接受的答案几乎概括了这一切。

但是，我不得不指出，您的查询可以重写以显着地提高性能，如下所示：

select wifi_name
      ,sum(case when success = 1 then 1 else 0 end)*100/count(*) as success_rate
from conn_log 
group by wifi_name;

在PostgreSQL 9.4+中，它甚至更简单：

select
    wifi_name, 
    count(*) filter (where success = 1) / count(*) * 100 as success_rate
from conn_log 
group by wifi_name;