- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有一个有趣的情况。我将发布 SQL 然后解释。
SELECT
`c`.`clusterName` AS `clusterName`,
DATE_FORMAT(`p`.`insertedTS`, '%Y-%m-%d') AS `insertedDate`,
MAX(`p`.`maxMemory`) AS `peakMaxMemory`,
MIN(`p`.`maxMemory`) AS `minMaxMemory`,
MAX(`p`.`usedMem`) AS `peakUsedMem`,
MIN(`p`.`usedMem`) AS `minUsedMem`,
MAX(`p`.`availMem`) AS `peakAvailMem`,
MIN(`p`.`availMem`) AS `minAvailMem`,
MAX(`p`.`numPoweredOnVms`) AS `peakPoweredOnVms`,
MIN(`p`.`numPoweredOnVms`) AS `minPoweredOnVms`,
(MAX(`p`.`maxMemory`) - MIN(`p`.`maxMemory`)) AS `deltaMaxMemory`,
(MAX(`p`.`usedMem`) - MIN(`p`.`usedMem`)) AS `deltaUsedMem`,
(MAX(`p`.`availMem`) - MIN(`p`.`availMem`)) AS `deltaAvailMem`,
(MAX(`p`.`numPoweredOnVms`) - MIN(`p`.`numPoweredOnVms`)) AS `deltaPoweredOnVms`
FROM
(`vtrend`.`clusters` `c`
JOIN `vtrend`.`capacityDataRawPOSH` `p` ON ((`c`.`clusterID` = `p`.`clusterID`)))
GROUP BY `c`.`clusterName` , DATE_FORMAT(`p`.`insertedTS`, '%Y-%m-%d')
ORDER BY `c`.`clusterName`
结果如下所示:
clusterName,insertedDate,peakMaxMemory,minMaxMemory,peakUsedMem,minUsedMem,peakAvailMem,minAvailMem,peakPoweredOnVms,minPoweredOnVms,deltaMaxMemory,deltaUsedMem,deltaAvailMem,deltaPoweredOnVms
Some-Cluster,2016-09-20,221,221,111,111,111,111,17,17,0,0,0,0
Some-Cluster,2016-09-21,221,221,111,111,111,111,17,17,0,0,0,0
Some-Cluster,2016-09-22,221,221,111,111,111,111,17,17,0,0,0,0
Some-Cluster,2016-09-23,221,221,111,111,111,111,17,17,0,0,0,0
Some-Cluster,2016-09-24,221,221,111,111,111,111,17,16,0,0,0,1
Some-Cluster,2016-09-25,221,221,111,111,111,111,17,17,0,0,0,0
Some-Cluster,2016-09-26,221,221,111,111,111,111,17,17,0,0,0,0
Some-Cluster,2016-09-27,221,221,111,111,111,111,17,17,0,0,0,0
Some-Cluster,2016-09-28,221,221,111,111,111,111,17,15,0,0,0,2
Some-Cluster,2016-09-29,221,221,111,111,111,111,17,17,0,0,0,0
因此,我想要完成(但失败)的是在末尾添加一列,该列将显示 numPoweredOnVms 计数在一天中增加或减少。由于上面的每条记录代表一整天的分组,每 15 分钟输入一次数据,因此我希望能够查看 numPoweredOnVms 中的第一条记录和当天,并从中减去当天的最后一条记录然后得到一个正数(增加)或负数(减少)计数,我可以使用 IF 语句来呈现“增加”或“减少”文本。
这是原始数据的示例:
idcapacityDataRaw,insertedTS,clusterID,totalRamGb,maxMemory,availMem,usedMem,actualUsageRamGb,totalCpuMhz,maxMhz,usedMhz,numPoweredOnVms
739,"2016-09-20 16:50:12",29,384,221,111,111,111,268032,178688,3394,17
976,"2016-09-20 21:50:42",29,384,221,111,111,111,268032,178688,4072,17
1074,"2016-09-20 22:13:07",29,384,221,111,111,111,268032,178688,4683,17
1172,"2016-09-20 22:35:36",29,384,221,111,111,111,268032,178688,3916,17
1270,"2016-09-20 22:58:01",29,384,221,111,111,111,268032,178688,3857,17
1365,"2016-09-20 23:21:06",29,384,221,111,111,111,268032,178688,7867,17
1463,"2016-09-20 23:43:37",29,384,221,111,111,111,268032,178688,3971,17
1575,"2016-09-21 00:11:05",29,384,221,111,111,111,268032,178688,8196,17
2149,"2016-09-21 19:13:33",29,384,221,111,111,111,268032,178688,3758,17
2495,"2016-09-21 21:01:48",29,384,221,111,111,111,268032,178688,3927,16
所以基本上我希望能够从当天的第一条记录和最后一条记录中提取 numPoweredOnVms 并减去它们。现在有趣的部分是如何在多天内对多个集群执行此操作?我想我可能需要对原始数据进行单独的 View 并按记录计算该记录,然后使用该 View 作为此查询的支持,只是不确定。
最佳答案
使用原始查询作为子查询,并让它返回每天的第一个和最后一个时间戳。然后连接回表以获取每次该列的值。
SELECT c.clusterName, p.*,
CASE SIGN(plast.numPoweredOnVms > pfirst.numPoweredOnVms)
WHEN -1 THEN 'Decrease'
WHEN 0 THEN 'No change'
ELSE 'Increase'
END AS changePoweredOnVms
FROM vtrend.clusters AS c
JOIN (
SELECT
clusterID,
DATE(`insertedTS`) AS `insertedDate`,
MAX(`maxMemory`) AS `peakMaxMemory`,
MIN(`maxMemory`) AS `minMaxMemory`,
MAX(`usedMem`) AS `peakUsedMem`,
MIN(`usedMem`) AS `minUsedMem`,
MAX(`availMem`) AS `peakAvailMem`,
MIN(`availMem`) AS `minAvailMem`,
MAX(`numPoweredOnVms`) AS `peakPoweredOnVms`,
MIN(`numPoweredOnVms`) AS `minPoweredOnVms`,
(MAX(`maxMemory`) - MIN(`maxMemory`)) AS `deltaMaxMemory`,
(MAX(`usedMem`) - MIN(`usedMem`)) AS `deltaUsedMem`,
(MAX(`availMem`) - MIN(`availMem`)) AS `deltaAvailMem`,
(MAX(`numPoweredOnVms`) - MIN(`numPoweredOnVms`)) AS `deltaPoweredOnVms`
MIN(insertedTS) AS firstTS,
MAX(insertedTS) AS lastTS
FROM `vtrend`.`capacityDataRawPOSH`
GROUP BY clusterID, insertedDate) AS p ON p.clusterID = c.clusterID
JOIN vtrend.capacityDataRawPOSH AS pfirst ON pfirst.clusterID = p.clusterID AND pfirst.insertedTS = p.firstTS
JOIN vtrend.capacityDataRawPOSH AS plast ON plast.clusterID = p.clusterID AND plast.insertedTS = p.lastTS
此外,使用 DATE(insertedTS)
而不是 DATE_FORMAT(insertedTS, '%Y-%m-%d')
将时间戳转换为日期。 %Y-%m-%d
是显示日期时使用的默认格式。
关于MySQL 较大记录集中的组求和,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/39797751/
我正在通过 PHP 将 .csv 中的两行插入到表中。 我还会跟踪任何错误,如果发生错误,我不会提交事务。插入表后,我检索结果行的 ID(全部在一个事务中提交),并且 csv 的第一行对应于第二个 I
一个应用程序托管一个具有三个接口(interface)的 Web 服务,用于三个单独且独立的操作,所有这些操作都在应用程序的不同组件中实现,彼此独立,例如在不同的包等中,所以他们对彼此了解不多,只共享
我希望在单击特定表格数据单元格时同时选中单选按钮和单选按钮单击事件。我已经使用以下方法实现了这一点: $(document).ready(function() { $("td").click(
JSFiddle:https://jsfiddle.net/oyp1zxaq/ 本质上,我只是想在较大的 div 中放置四个具有定义宽度的较小 div,但我希望它们在其中间隔开。 我想知道是否有一种方
我在一个布局中有两个 View 。我将分别称它们为 View A 和 View B。 ┌──────┐ │┌─┐┌─┐│ ││A││B││ │└─┘└─┘│ └──────┘ 父布局(包括View A
我是一名优秀的程序员,十分优秀!