来自多个表的多个表连接和最新时间戳
来自多个表的多个表连接和最新时间戳
提问于 2019-08-06 07:02:41
我尝试将多个字段聚合到一个查询buy中，获取相关历史表中每个项目的最新时钟/时间戳值。这是用于墙壁仪表板显示的，内置的函数不足以获得干净整洁的输出，但它确实支持sql查询。
基于前面的问题(MySql Combine two queries (subselect or join or union))，我创建了以下内容，尽管它是功能性的，但数据是不正确的。我需要从时钟列中提取每一项的最后一个值。一旦我们将表分区和其他项放在一起，就会有第三个表需要添加，这只是一个最小的示例。
SELECT hosts.host,
max(case when items.name='DB2 CPU Usage' then history.value end) as ' DB2 CPU Usage',
max(case when items.name='DB2 Cache Hit Rate' then history.value end) as ' DB2 Cache Hit Rate',
max(case when items.name='DB2 Percent Rqst Waiting' then history.value end) as ' DB2 Percent Rqst Waiting',
max(case when items.name='DB2 Version' then history_text.value end) as ' DB2 Version'
from hosts
left join items on items.hostid = hosts.hostid
left join history on history.itemid = items.itemid
left join history_text on history_text.itemid = items.itemid
where items.name like 'DB2%'
group by hosts.host, items.name, history.value我已经尝试了一些这样的示例(Join tables and return row with latest timestamp和Retrieve latest timestamp row from table using INNER JOIN)，但是无法使用所有的表进行有效的查询。
这可以很好地工作，但是我不知道如何组合select itemid,max(clock) as max_clock from history group by history.itemid order by max_clock desc limit 1
以下是一些示例数据
hosts
+-----------+-------+
|host |hostid |
+-----------+-------+
|server01 |10001 |
|server02 |10002 |
+-----------+-------+
items
+-------+-------+-------------------+
|itemid |hostid |name |
+-------+-------+-------------------+
|9801 |10001 |DB2 CPU Usage |
|9936 |10001 |DB2 Cache Hit Rate |
|9783 |10001 |DB2 Version |
|9802 |10002 |DB2 CPU Usage |
|9937 |10002 |DB2 Cache Hit Rate |
|9784 |10002 |DB2 Version |
+-------+-------+-------------------+
history
+-------+-------+-----------+
|itemid |value |clock |
+-------+-------+-----------+
|9801 |86 |1565042153 |
|9801 |32 |1565042253 |
|9936 |12 |1565042120 |
|9936 |22 |1565042220 |
|9802 |41 |1565042153 |
|9802 |72 |1565042253 |
|9937 |99 |1565042120 |
|9937 |53 |1565042220 |
+-------+-------+-----------+
history_text
+-------+-------+-----------+
|itemid |value |clock |
|9783 |9.7 |1565042120 |
|9783 |9.7 |1565042320 |
|9784 |10.5 |1565042123 |
|9784 |10.5 |1565042324 |
+-------+-------+-----------+和所需的输出
+-----------+---------------+-------------------+---------------+
| |DB2 CPU Usage |DB2 Cache Hit Rate |DB2 Version |
+-----------+---------------+-------------------+---------------+
|server01 |32 |22 |9.7 |
|server02 |72 |53 |10.5 |
+-----------+---------------+-------------------+---------------+从我学到的每一件事中，我已经适应了不同的问题-谢谢你的时间，非常感谢！
回答 1
Stack Overflow用户
发布于 2019-08-07 02:58:44
如果您有两个历史表，其中一个包含第一个值，第二个包含后面的第二个值，那么您的数据库设计会更适合这一点。首先，这是数据的create版本，因此您可以使用dbfidddle或sqlfiddle快速尝试
CREATE TABLE hosts
(`host` varchar(8), `hostid` int)
;
INSERT INTO hosts
(`host`, `hostid`)
VALUES
('server01', 10001),
('server02', 10002)
;
CREATE TABLE items
(`itemid` int, `hostid` int, `name` varchar(18))
;
INSERT INTO items
(`itemid`, `hostid`, `name`)
VALUES
(9801, 10001, 'DB2 CPU Usage'),
(9936, 10001, 'DB2 Cache Hit Rate'),
(9783, 10001, 'DB2 Version'),
(9802, 10002, 'DB2 CPU Usage'),
(9937, 10002, 'DB2 Cache Hit Rate'),
(9784, 10002, 'DB2 Version')
;
CREATE TABLE history
(`itemid` int, `value` DECIMAL(5,1) , `clock` int)
;
INSERT INTO history
(`itemid`, `value`, `clock`)
VALUES
(9801, 86, 1565042153),
(9801, 32, 1565042253),
(9936, 12, 1565042120),
(9936, 22, 1565042220),
(9802, 41, 1565042153),
(9802, 72, 1565042253),
(9937, 99, 1565042120),
(9937, 53, 1565042220)
;
CREATE TABLE history_text
(`itemid` int, `value` DECIMAL(5,1) , `clock` int)
;
INSERT INTO history_text
(`itemid`, `value`, `clock`)
VALUES
(9783, 9.7, 1565042120),
(9783, 9.7, 1565042320),
(9784, 10.5, 1565042123),
(9784, 10.5, 1565042324)
;这是我对你的问题的解决方案
SELECT
host,
MAX(IF(name = 'DB2 CPU Usage',value,NULL)) 'DB2 CPU Usage',
MAX(IF(name = 'DB2 Cache Hit Rate',value,NULL)) 'DB2 Cache Hit Rate',
MAX(IF(name = 'DB2 Version',value,NULL)) 'DB2 Version'
FROM
(
SELECT i.itemid itemid, h.hostid hostid, name, value, h.host host FROM
(
SELECT MAX(value) value,h.itemid itemid FROM history h
INNER JOIN
(SELECT MAX(clock) clock,itemid FROM history
GROUP BY itemid
) hi
ON h.clock = hi.clock GROUP BY h.itemid
UNION
SELECT MAX(value) value,h.itemid itemid FROM history_text h
INNER JOIN
(SELECT MAX(clock) clock,itemid FROM history_text
GROUP BY itemid
) hi
ON h.clock = hi.clock GROUP BY h.itemid
) his
inner join items i on i.itemid = his.itemid
inner join hosts h on h.hostid = i.hostid
) res GROUP BY host;结果是
host DB2 CPU Usage DB2 Cache Hit Rate DB2 Version
server01 32.0 22.0 9.7
server02 72.0 53.0 10.5正如您所看到的，我从历史和历史文本开始。我找不到比这更漂亮的形式了。这并不复杂，您首先选择按itemid分组的最大日期，然后将其与同一个表连接。这样您就可以为每个itemid指定正确的值。然后我对历史文本做了同样的处理。把所有的东西结合起来，这样我们就有了所有需要的itemid。剩下的就是内联其余的桌子了。并最终使结果
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/57366997
复制相关文章
相似问题
腾讯云开发者
