问来自多个表的多个表连接和最新时间戳
EN

Stack Overflow用户

提问于 2019-08-05 23:02:41

回答 1查看 354关注 0票数 0

我尝试将多个字段聚合到一个查询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           |
+-----------+---------------+-------------------+---------------+

从我学到的每一件事中，我已经适应了不同的问题-谢谢你的时间，非常感谢！

mysql

timestamp

回答 1

Stack Overflow用户

发布于 2019-08-06 18: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。剩下的就是内联其余的桌子了。并最终使结果

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/57366997

复制

mysql单个表拆分成多个表

数据

一.横向拆分 create table 新表的名称 select * from 被拆分的表 order by id limit int1,int2 int1为其实位置,int2为几条注意:这样拆分后主键会失效手动让其主键生效即可所有要执行 alter table 新表的名称 modify 主键字段 int primary key auto_increment 二.纵向拆分 create table 新表的名称 select 需保留的字段 from 被拆分的表拆分后原表都要保存主要是把经常查的数据

小小咸鱼YwY

2020/06/19

3.8K0

VBA技巧：复制多个工作表