代码之家 › 专栏 › 技术社区 › Daren Schwenke

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

greatest-n-per-group group-by mysql php

Daren Schwenke · 技术社区 · 16 年前

简化的表格结构:

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`)
)

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

此查询已被简化,以删除不相关数据的内部联接和不应影响问题的额外列。

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

我想要两个计数:(状态!='“好的”和状态!='("")

而反过来。。。计数:(状态='OK'或状态='Repaired')

对于最近一天或抵消(-86400对于昨天等)

该表包含约500k条记录,每天增长约5000条,所以一个SQL查询而不是循环查询会非常好。。

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

编辑:我愿意对今天的数据或来自偏移量的数据使用不同的SQL查询。

编辑:查询有效,速度足够快,但我目前无法让用户按百分比列(从坏计数和好计数派生的列)排序。这不是一个表演的阻碍,但我允许他们对其他一切进行排序。命令如下:

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-不支持引用“坏主机计数”(引用组函数)

编辑:为不同的部分求解。下面的工作,并允许我 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

1 回复 | 直到 16 年前

Bill Karwin 16 年前

如果我理解,您希望获得上次活动日期的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个”问题的变体。

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

这就是今天日期的解决方案(假设将来没有行具有活动的_日期)。要将结果限制为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不允许您在其他选择列表表达式中引用列别名,因此您必须将上述内容包装为子查询,这比在本例中更复杂。

我忘了你说过你在使用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;