javascript - 来自 MySQL 数组的 Google 图表中的多行

Question

我正在尝试将我的 MySQL 查询传递到 Google 折线图中，但在将其转换为正确格式时遇到了一些问题。该图表用于显示计算机上 RAM 利用率的百分比。

MySQL 查询:

SELECT DATE_FORMAT(date, 'new Date(%Y, %c, %d, %H, %i, %s)') AS date, percentComittedInUse, machineName FROM stsdata.hyperv_perf_memory

表格结果(缩短):

+-----------------------------------+----------------------+-------------+
|               date                | percentComittedInUse | machineName |
+-----------------------------------+----------------------+-------------+
| new Date(2018, 8, 17, 17, 09, 03) |                   53 | STSWKS15    |
| new Date(2018, 8, 17, 17, 32, 43) |                   57 | STSWKS15    |
| new Date(2018, 8, 17, 18, 02, 53) |                   55 | STSWKS15    |
| new Date(2018, 8, 17, 18, 33, 00) |                   56 | STSWKS15    |
| new Date(2018, 8, 17, 19, 03, 05) |                   57 | STSWKS15    |
| new Date(2018, 8, 20, 14, 02, 30) |                   57 | STSWKS15    |
| new Date(2018, 8, 20, 14, 32, 46) |                   57 | STSWKS15    |
| new Date(2018, 8, 20, 15, 02, 56) |                   59 | STSWKS15    |
| new Date(2018, 8, 20, 15, 33, 08) |                   60 | STSWKS15    |
| new Date(2018, 8, 17, 17, 32, 43) |                   50 | FAKE_VHOST8 |
| new Date(2018, 8, 17, 18, 02, 53) |                   48 | FAKE_VHOST8 |
| new Date(2018, 8, 17, 18, 33, 00) |                   49 | FAKE_VHOST8 |
| new Date(2018, 8, 17, 19, 03, 05) |                   50 | FAKE_VHOST8 |
| new Date(2018, 8, 17, 19, 33, 27) |                   49 | FAKE_VHOST8 |
| new Date(2018, 8, 17, 20, 02, 33) |                   49 | FAKE_VHOST8 |
| new Date(2018, 8, 17, 20, 32, 41) |                   50 | FAKE_VHOST8 |
+-----------------------------------+----------------------+-------------+

我在使用 PHP 的结果中回显:

<?php
$row = mysqli_fetch_assoc($VMMemoryPerf);
if ($VMMemoryPerf->num_rows > 0) {
    while ($row = mysqli_fetch_assoc($VMMemoryPerf)) {
        echo "[".$row['date'].", '".$row['machineName']."', '".$row['percentComittedInUse']."'],";
        }
    }
?>

结果按预期工作，它将返回:[新日期(2018, 8, 17, 17, 32, 43), 'STSWKS15', '57'], [新日期(2018, 8, 17, 18, 02, 53), 'STSWKS15', '55'],

但我有多个计算机名称，并且希望在图表中为每个名称划一条线。

我正在探索一种在 MySQL 中操作数据的方法，但如果我必须在 PHP 中这样做也可以。

图表的 Javascript:

<?php include($_SERVER['DOCUMENT_ROOT']. "/inc/SQL_stsdata.php"); ?>
<script type="text/javascript">
	google.charts.load('current', {'packages':['corechart']});
	google.charts.setOnLoadCallback(chart_line_hyperv_memory);
	function chart_line_hyperv_memory() {
		var data = google.visualization.arrayToDataTable([
			[{ label: 'Date', type: 'datetime' }, { role: 'annotationText' }, { label: 'Memory % Comitted', type: 'number' }],
			<?php
			$row = mysqli_fetch_assoc($VMMemoryPerf);
			if ($VMMemoryPerf->num_rows > 0) {
				while ($row = mysqli_fetch_assoc($VMMemoryPerf)) {
					echo "[".$row['date'].", '".$row['machineName']."', '".$row['percentComittedInUse']."'],";
				}
			}
			?>
			]);
		var options = {
			title: 'VM Memory Utilization', fontSize: 10, fontName: 'Consolas',
			tooltip: { textStyle: { bold: false, color: '#000000', fontSize: 12 }, showColorCode: true, isHtml: true, ignoreBounds: false, text: 'both', trigger: 'hover', padding: '0px' },
			animation: { duration: 1000, startup: true, easing: 'inAndOut' },
			legend: { position: 'right' },
			chartArea: { height: '95%' },
			width: '100%', height: '100%',
			annotations: { textStyle: { fontName: 'Consolas', color: '#000000', }, highContrast: 'true', alwaysOutside: 'true', },
		};
		var chart = document.getElementById('chart_line_hyperv_memory'), x = (parseFloat(chart.getAttribute('data-x')) || 0), y = (parseFloat(chart.getAttribute('data-y')) || 0);
		var chartContainer = $(chart).parent();
		chart.style.width  = chartContainer.width() + 'px';
		chart.style.height = chartContainer.height() + 'px';
		x += chartContainer.left;
		y += chartContainer.top;
		chart.style.webkitTransform = chart.style.transform = 'translate(' + x + 'px,' + y + 'px)';
		chart.setAttribute('data-x', x);
		chart.setAttribute('data-y', y);
		var chart = new google.visualization.LineChart(document.getElementById('chart_line_hyperv_memory'));
		console.log(data);
		chart.draw(data, options);

	}
</script>

感谢您的审阅和对我的帮助!

更新:该图表需要一个表格输出，例如:

+-------------------------------------+----------+-------------+
|                date                 | STSWKS15 | FAKE_VHOST8 |
+-------------------------------------+----------+-------------+
| Date(year, date, day, hour, minute) | value    | value       |
| Date(year, date, day, hour, minute) | value    | value       |
| Date(year, date, day, hour, minute) | value    | value       |
| Date(year, date, day, hour, minute) | value    | value       |
| Date(year, date, day, hour, minute) | value    | value       |
+-------------------------------------+----------+-------------+

这样就可以构造出[Date(), value, value],等数据我对 MySQL 中的串联没有任何问题，只是将每台机器的结果翻转到它们自己的列中的查询。

请记住，如果将新机器添加到表中，查询可以动态获取它。

Answer 1

根据您在更新中添加的预期输出，我认为您基本上是在寻找数据透视表。

SELECT 
    DATE_FORMAT(date, 'new Date(%Y, %c, %d, %H, %i, %s)') AS date,
    SUM(IF(machineName = 'STSWKS15', percentComittedInUse, 0)) AS 'STSWKS15',
    SUM(IF(machineName = 'FAKE_VHOST8', percentComittedInUse, 0)) AS 'FAKE_VHOST8'
FROM stsdata.hyperv_perf_memory
GROUP BY `date`;

这会返回这样的东西:

date                              |STSWKS15 |FAKE_VHOST8 |
----------------------------------|---------|------------|
new Date(2018, 8, 21, 10, 53, 27) |15       |0           |
new Date(2018, 8, 21, 10, 53, 44) |25       |0           |
new Date(2018, 8, 21, 10, 53, 57) |57       |0           |
new Date(2018, 8, 21, 10, 54, 13) |60       |0           |
new Date(2018, 8, 21, 10, 54, 27) |0        |35          |
new Date(2018, 8, 21, 10, 54, 35) |0        |45          |

这假设您事先知道您的机器名称。如果没有，您可能需要做更多的工作。

=======================================

编辑:回复评论。

如果你想动态获取列名，你可以使用这样的动态查询。创建存储过程并调用以获取数据。

CREATE DEFINER=`root`@`localhost` PROCEDURE `getUsage`()
BEGIN
    SET @query = NULL;
    SELECT
          GROUP_CONCAT(
          DISTINCT CONCAT('SUM(IF (machineName = "', machineName, '", percentComittedInUse, 0)) AS ', CONCAT(clientName, '_', machineName))
          ) INTO @query
        FROM stsdata.hyperv_perf_memory;

    SET @query = CONCAT('SELECT `date`, ', @query, ' FROM stsdata.hyperv_perf_memory GROUP BY date, clientName');

    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

要从中获取数据，您可以简单地执行如下查询:

call getUsage();

希望对你有帮助!!如果您有任何疑问，请随时提出。