php - Mysql GROUP BY 和 COUNT 用于多个 WHERE 子句

Question

简化表结构:

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 JOINS 和不应影响问题的额外列。

MAX(active_date) 对于特定日期的所有记录都是相同的，并且应该始终选择最近的一天，或者允许与 NOW() 的偏移量。 (这是一个 UNIXTIME 字段)

我想要两个计数:(status != 'OK' AND status != 'Repaired')

AND 的逆...计数:(status = 'OK' OR status = 'Repaired')

然后将第一个答案除以第二个，得到“percentage_dead”(可能在后期处理中同样快)

对于最近一天或偏移量(- 86400 表示昨天，等等)

表包含大约 50 万条记录，并且每天增长大约 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”(引用组函数)

编辑:解决了不同的部分。以下有效并允许我ORDER BY percentage_dead

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

Answer 1

据我了解，您希望在上次事件的日期计算主机名的 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;

这是我在 StackOverflow 上的 SQL 问题中经常看到的“每组最多 n 个”问题的变体。

首先只想选择每个主机名具有最新事件日期的行，我们可以通过对具有相同主机名和更大 active_date 的行执行外部连接来实现。在我们找不到此类匹配项的地方，我们已经拥有每个给定主机名的最新行。

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

这就是今天日期的解决方案(假设 future 没有任何行具有 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; 

关于正常主机名和损坏主机名之间的比率，我建议只在您的 PHP 代码中计算它。 SQL 不允许您在其他选择列表表达式中引用列别名，因此您必须将上面的内容包装为子查询，这比在这种情况下的值(value)更复杂。

---

我忘了您说过您使用的是 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;