主页 / dba / 问题 / 97760
Accepted
agipamp
Asked: 2015-04-14 19:14:27 +0800 CST

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

  • 772

如果我的 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
redshift

1 个回答

  1. Best Answer

    INNER JOIN...ON至于vs子句的区别，这里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;
    • 4

相关问题