多个WHERE子句的Mysql GROUP BY和COUNT

Question

多个WHERE子句的Mysql GROUP BY和COUNT

5

简化的表结构：

CREATE TABLE IF NOT EXISTS `hpa` (
  `id` bigint(15) NOT NULL auto_increment,
  `core` varchar(50) NOT NULL,
  `hostname` varchar(50) NOT NULL,
  `status` varchar(255) NOT NULL,
  `entered_date` int(11) NOT NULL,
  `active_date` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `hostname` (`hostname`),
  KEY `status` (`status`),
  KEY `entered_date` (`entered_date`),
  KEY `core` (`core`),
  KEY `active_date` (`active_date`)
)

为此，我有以下SQL查询，它简单地总结了所有具有定义状态的记录。

SELECT core,COUNT(hostname) AS hostname_count, MAX(active_date) AS last_active
          FROM `hpa`
          WHERE 
          status != 'OK' AND status != 'Repaired'
          GROUP BY core
          ORDER BY core

为了简化查询，已经去掉了与无关数据和额外列的INNER JOIN，并且不应该影响问题。

MAX（活动日期）对于特定日期的所有记录都是相同的，应该始终选择最近的一天或允许从NOW（）偏移。（它是一个UNIX时间字段）

我想要两个计数：（状态！= 'OK' AND 状态！= 'Repaired'）

以及反之...计数：（状态= 'OK' OR 状态= 'Repaired'）

第一个答案除以第二个答案，得到“percentage_dead”（可能在后处理中更容易实现）

针对最近一天或偏移量（- 86400表示昨天等）

该表包含约500k条记录，每天增加约5000条记录，因此单个SQL查询而不是循环将非常好...

我想象一些有创意的IF可以做到这一点。感谢您的专业知识。

编辑：我可以使用不同的SQL查询来获取今天的数据或来自偏移量的数据。

编辑：查询有效并且速度足够快，但目前我不能让用户按百分比列（从坏和好的计数派生而来）进行排序。这不是一个停止器，但我允许他们对其他所有内容进行排序。这个查询的ORDER BY：

SELECT h1.core, MAX(h1.entered_date) AS last_active, 
SUM(CASE WHEN h1.status IN ('OK', 'Repaired') THEN 1 ELSE 0 END) AS good_host_count,  
SUM(CASE WHEN h1.status IN ('OK', 'Repaired') THEN 0 ELSE 1 END) AS bad_host_count 
FROM `hpa` h1 
LEFT OUTER JOIN `hpa` h2 ON (h1.hostname = h2.hostname AND h1.active_date < h2.active_date) 
WHERE h2.hostname IS NULL 
GROUP BY h1.core 
ORDER BY ( bad_host_count / ( bad_host_count + good_host_count ) ) DESC,h1.core

给我： #1247 - 引用“bad_host_count”不受支持（对组函数的引用）

编辑：针对不同部分已解决。以下内容可行，并允许我按百分比死亡排序。

SELECT c.core, c.last_active, 
SUM(CASE WHEN d.dead = 1 THEN 0 ELSE 1 END) AS good_host_count,  
SUM(CASE WHEN d.dead = 1 THEN 1 ELSE 0 END) AS bad_host_count,
( SUM(CASE WHEN d.dead = 1 THEN 1 ELSE 0 END) * 100/
( (SUM(CASE WHEN d.dead = 1 THEN 0 ELSE 1 END) )+(SUM(CASE WHEN d.dead = 1 THEN 1 ELSE 0 END) ) ) ) AS percentage_dead
FROM `agent_cores` c 
LEFT JOIN `dead_agents` d ON c.core = d.core
WHERE d.active = 1
GROUP BY c.core
ORDER BY percentage_dead

- Daren Schwenke

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Bill Karwin · Accepted Answer

如果我理解正确，您想获取最后一次活动日期上OK和not OK主机名状态的计数。是这样吗？然后应该按核心分组。

SELECT core, MAX(active_date)
  SUM(CASE WHEN status IN ('OK', 'Repaired') THEN 1 ELSE 0 END) AS OK_host_count,
  SUM(CASE WHEN status IN ('OK', 'Repaired') THEN 0 ELSE 1 END) AS broken_host_count
FROM `hpa` h1 LEFT OUTER JOIN `hpa` h2 
  ON (h1.hostname = h2.hostname AND h1.active_date < h2.active_date)
WHERE h2.hostname IS NULL
GROUP BY core
ORDER BY core;

这是一个“最大-n-每组”问题的变体，我在StackOverflow上看到了很多关于SQL问题的解答。

首先，我们需要选择每个主机名最新活动日期的行，可以通过外连接相同主机名和较大的active_date的行来实现。当我们找不到这样的匹配时，我们已经拥有了每个给定主机名的最新行。

然后按核心分组，并按状态计算行数。

这是今天的解决方案（假设没有行的active_date在未来）。要将结果限制为N天前的行，您必须限制两个表。

SELECT core, MAX(active_date)
  SUM(CASE WHEN status IN ('OK', 'Repaired') THEN 1 ELSE 0 END) AS OK_host_count,
  SUM(CASE WHEN status IN ('OK', 'Repaired') THEN 0 ELSE 1 END) AS broken_host_count
FROM `hpa` h1 LEFT OUTER JOIN `hpa` h2 
  ON (h1.hostname = h2.hostname AND h1.active_date < h2.active_date
  AND h2.active_date <= CURDATE() - INTERVAL 1 DAY)
WHERE h1.active_date <= CURDATE() - INTERVAL 1 DAY AND h2.hostname IS NULL
GROUP BY core
ORDER BY core;

关于OK和broken主机名之间的比率，我建议在您的PHP代码中计算。 SQL不允许您在其他select-list表达式中引用列别名，因此您必须将上述内容包装为子查询，而这在这种情况下比它值得更加复杂。

我忘了你说你正在使用UNIX时间戳。像这样做：

SELECT core, MAX(active_date)
  SUM(CASE WHEN status IN ('OK', 'Repaired') THEN 1 ELSE 0 END) AS OK_host_count,
  SUM(CASE WHEN status IN ('OK', 'Repaired') THEN 0 ELSE 1 END) AS broken_host_count
FROM `hpa` h1 LEFT OUTER JOIN `hpa` h2 
  ON (h1.hostname = h2.hostname AND h1.active_date < h2.active_date
  AND h2.active_date <= UNIX_TIMESTAMP() - 86400)
WHERE h1.active_date <= UNIX_TIMESTAMP() - 86400 AND h2.hostname IS NULL
GROUP BY core
ORDER BY core;