基于mysql行的计算。 [英] mysql row based calculations.

问题描述

< TD> 名称

ReqColumns |	hostid |	|值
RAM	10093	总内存	15.970000
RAM	10093	总内存[免费]	12.420000
RAM	10093	总内存[已使用]	3.550000
RAM	10094	总内存	3.920000
RAM	10094	总内存[免费]	1.970000
RAM	10094	总记忆[已使用]	1.950000

以上输出我使用此查询获得...

ReqColumns|	hostid|	name	|Value
RAM	10093	Total Memory	15.970000
RAM	10093	Total Memory [Free]	12.420000
RAM	10093	Total Memory [used]	3.550000
RAM	10094	Total Memory	3.920000
RAM	10094	Total Memory [Free]	1.970000
RAM	10094	Total Memory [used]	1.950000

The above output I am getting by using this query...

SELECT ReqColumns,hostid,name , AVG(value_max) AS Value from
(
select sc.ReqColumns,
    i.name,
    TRUNCATE((tu.value_min)/(1024*1024*1024),2) AS value_min,
    TRUNCATE((tu.value_avg)/(1024*1024*1024),2) AS value_avg,
    TRUNCATE((tu.value_max)/(1024*1024*1024),2) AS value_max,
    i.hostid,
    ci.NewInterfaceName,
    lm.LocationName,
    bfm.BusinessFunctionCode,
    bm.BuildingCode,
    FROM_UNIXTIME(tu.clock,'%Y-%m-%d') Date from trends_uint tu

INNER JOIN items i ON i.itemid=tu.itemid
INNER JOIN graphs_items gi ON gi.itemid=i.itemid
  INNER JOIN graphs g ON g.graphid=gi.graphid
INNER JOIN hosts h ON h.hostid=i.hostid

  LEFT JOIN flip_linkutilisation.categorisedinterfaces ci on ci.graphid=g.graphid
  LEFT JOIN flip_linkutilisation.buildingmaster bm ON bm.BuildingID=ci.BuildingID
  LEFT JOIN flip_linkutilisation.businessfunctionmaster bfm ON bfm.BusinessFunctionID=bm.BusinessFunctionID
  LEFT JOIN flip_linkutilisation.locationmaster lm ON lm.LocationID=bm.LocationID
  LEFT JOIN flip_linkutilisation.servercolumns sc ON sc.ColumnDetails=g.name
where tu.itemid IN(SELECT itemid FROM items  WHERE  name in ('Total Memory','Total Memory [Free]', 'Total Memory [used]') )
) as x group by hostid,name

这里我需要一行一个hostid，值列计算将是（总内存[使用] /总内存）* 100





这意味着我的输出应该是这样的

ReqColumns |	主机标识|	名称	|价值 - ％
RAM	10093	内存详情	22.22
RAM	10094	内存详细信息	49.74

请帮我解决这个问题..



谢谢提前

Here i need one row for one hostid and the value column calculation willl be the (Total Memory[used]/Total Memory)*100


That means my output should be like this

ReqColumns|	hostid|	name	|Value-%
RAM	10093	Memory Details	22.22
RAM	10094	Memory Details	49.74

Please help me to solve this..

Thanks in advance

推荐答案

SELECT
	ReqColumns, hostid, name, (TotalUsed/TotalMemory)* 100 AS Value
FROM
	(
		SELECT
			sc.ReqColumns, i.hostid, i.name, 
			/*TRUNCATE((tu.value_min) / (1024 * 1024 * 1024), 2) AS value_min, 
			TRUNCATE((tu.value_avg) / (1024 * 1024 * 1024), 2) AS value_avg,
			TRUNCATE((tu.value_max) / (1024 * 1024 * 1024), 2) AS value_max,*/
			SUM(IF(i.name='Total Memory [used]',TRUNCATE((tu.value_max) / (1024 * 1024 * 1024), 2) ,0)) AS TotalUsed,
			SUM(IF(i.name='Total Memory',TRUNCATE((tu.value_max) / (1024 * 1024 * 1024), 2) ,0)) AS TotalMemory
			 
		FROM
			trends_uint tu
			INNER JOIN items i
				ON   i.itemid = tu.itemid
			INNER JOIN graphs_items gi
				ON   gi.itemid = i.itemid
			INNER JOIN graphs g
				ON   g.graphid = gi.graphid
			INNER JOIN HOSTS h
				ON   h.hostid = i.hostid
                        LEFT JOIN flip_linkutilisation.servercolumns sc 
                                ON   sc.ColumnDetails = g.name
		WHERE
			i.name IN ('Total Memory', 'Total Memory [Free]', 
			              	        'Total Memory [used]')
		GROUP BY sc.ReqColumns, i.hostid, i.name
	) AS x

well我想这会解决你的问题吗？



注意我删除不必要的连接和列并减少读取次数

well i think this will solve your problem?

watch out i remove the unnecessary joins and columns and reduce the number of reads

这篇关于基于mysql行的计算。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！