mysql - 基于 group by 子句的 SQL 百分比聚合

Question

我有一个表，给出了以下 sql 查询输入。

select server, count(*) from ServerNames where server is not null and server != '' and timestamp >= '2015-03-18'
and timestamp <= '2015-04-19' group by server;

Server | Count(*)
_________________
Server1    1700  
Server2    1554

select server, ip_address, count(*) from serverNames where server is not null and server != '' and ip_address is not null and ip_address != '' and timestamp >= '2015-03-18'
and timestamp <= '2015-04-19'  group by server, ip_address;

Server |    ip_address      | count(*)
______________________________________
Server1  Sample_ip_1            14
Server2  Sample_ip_2           209
Server1  Sample_ip_2           100
Server1  Sample_ip_1            50

我发现编写计算组内百分比的查询很困难。因此，例如在我的示例中，输出应该是。

Server | ip_address | Count(*) | percent
________________________________________________
Server1  Sample_ip_1  14         0.82% (14/1700)
Server2  Sample_ip_2 209         13.44%(209/1554)
Server1  Sample_ip_2 100         5.88%(100/1700)
Server2  Sample_ip_1  50         3.217(15/1554)

如何编写查询来执行此操作？

Answer 1

您只需将两个查询的结果连接在一起，然后将一堆内容连接在一起即可获得您要查找的百分比值。我认为这应该可以做到。

select q2.server, q2.ip_address, concat(round((q2.c / q1.c) * 100, 2), '%(', q2.c, '/', q1.c, ')') as percent 
  from
    (
      select server, count(*) c 
        from ServerNames 
        where server is not null 
          and server != '' 
          and timestamp >= '2015-03-18'
          and timestamp <= '2015-04-19' 
        group by server
     ) q1
     inner join
     (
       select server, ip_address, count(*) c  
         from serverNames 
         where server is not null 
           and server != '' 
           and ip_address is not null 
           and ip_address != '' 
           and timestamp >= '2015-03-18'
           and timestamp <= '2015-04-19'  
         group by server, ip_address
     ) q2
     on q1.server = q2.server

demo here