从温度和ID数据库创建Google Charts API数据表

Question

我想使用数据库中的水池温度数据创建一个注释图表。您可以查看这里的sqlfiddle或这里的rextester中的数据库结构，但为了节省您的时间，这是我正在使用的结构：

DROP TABLE IF EXISTS `temperatures`;
DROP TABLE IF EXISTS `pools`;

CREATE TABLE `pools` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `temperatures` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pool_id` int(10) unsigned NOT NULL,
  `temperature` double(8,1) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `temperatures_pool_id_foreign` (`pool_id`),
  CONSTRAINT `temperatures_pool_id_foreign` FOREIGN KEY (`pool_id`) REFERENCES `pools` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3173 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `pools` (`id`, `name`, `created_at`)
VALUES
    (1,'Pool #1','2017-04-08 22:48:03'),
    (2,'Pool #2','2017-04-08 22:48:03'),
    (3,'Pool #3','2017-04-08 22:48:03');

INSERT INTO `temperatures` (`id`, `pool_id`, `temperature`, `created_at`)
VALUES
    (31,1,100.1,'2017-04-09 02:44:56'),
    (32,2,104.2,'2017-04-09 02:44:56'),
    (33,3,97.0,'2017-04-09 02:44:56'),
    (34,1,100.1,'2017-04-09 03:00:04'),
    (35,2,98.4,'2017-04-09 03:00:04'),
    (36,3,96.6,'2017-04-09 03:00:04'),
    (37,1,100.1,'2017-04-09 03:37:13'),
    (38,2,101.8,'2017-04-09 03:37:13'),
    (39,3,96.4,'2017-04-09 03:37:13'),
    (40,1,100.1,'2017-04-09 04:00:04'),
    (41,2,101.8,'2017-04-09 04:00:04'),
    (42,3,96.5,'2017-04-09 04:00:04'),
    (43,1,100.1,'2017-04-09 05:00:04'),
    (44,2,101.8,'2017-04-09 05:00:04');

好的，基本上，我创建了一个控制器，它将返回格式正确的JSON，用于ajax和google.visualization.DataTable()，就像这样：

var jsonData = $.ajax({
    url: "/data/pool-temperature-timeline",
    dataType: "json",
    async: false
}).responseText;

data = new google.visualization.DataTable(jsonData);
chart.draw(data, options);

当然，查看文档，注释图表期望遵循以下格式：

var data = new google.visualization.DataTable();
data.addColumn('date', 'Date');
data.addColumn('number', 'Kepler-22b mission');
data.addColumn('string', 'Kepler title');
data.addColumn('string', 'Kepler text');
data.addColumn('number', 'Gliese 163 mission');
data.addColumn('string', 'Gliese title');
data.addColumn('string', 'Gliese text');
data.addRows([
    [new Date(2314, 2, 15), 12400, undefined, undefined,
                            10645, undefined, undefined],
    [new Date(2314, 2, 16), 24045, 'Lalibertines', 'First encounter',
                            12374, undefined, undefined],
    [new Date(2314, 2, 17), 35022, 'Lalibertines', 'They are very tall',
                            15766, 'Gallantors', 'First Encounter'],
    [new Date(2314, 2, 18), 12284, 'Lalibertines', 'Attack on our crew!',
                            34334, 'Gallantors', 'Statement of shared principles'],
    [new Date(2314, 2, 19), 8476, 'Lalibertines', 'Heavy casualties',
                            66467, 'Gallantors', 'Mysteries revealed'],
    [new Date(2314, 2, 20), 0, 'Lalibertines', 'All crew lost',
                            79463, 'Gallantors', 'Omniscience achieved']
]);

var chart = new google.visualization.AnnotationChart(document.getElementById('chart_div'));

好的，那么这是设置，现在来到问题。最佳方式是如何组织数据，以便1）始终存在池1、2和3的温度数据，同时有相同的日期时间戳（我担心给定时间戳的数据集可能不完整）？我应该从SQL层开始使用一些巧妙的查询来组织它吗？还是我在控制器中使用一堆foreach循环来组织它？这是我努力追求的目标：

$dataTable->addRow(['created_at', 
    'temperature1', 'title1', 'text1',
    'temperature2', 'title2', 'text2',
    'temperature2', 'title2', 'text2',
]);

我可以看出，聪明的查询将是一种不错的方式，避免在控制器中进行大量的逻辑和foreach循环。也许如果数据按列组织，就像：

created_at, pool_1_temperature, pool_2_temperature, pool_3_temperature
------------------------------------------------
2017-04-09 02:44:56, 100.1, 104.2, 97.0
2017-04-09 03:00:04, 100.1, 98.4, 96.6
2017-04-09 03:37:13, 100.1, 101.8, 96.4

然后我可以轻松地浏览并创建DataTable。但我不确定如何在MySQL中完成这个操作，甚至不知道这是否是一个好主意。

感谢您迄今为止花费的时间，并提前感谢您的任何帮助。我希望我表达清楚了。

附注：我想到目前为止最接近的东西是Mysql query to dynamically convert rows to columns。我会再尝试一下...

- dhildreth

Answer 1

只要X轴（第一列）是日期，您就不必担心...

在同一日期时间上，泳池1、2和3始终有温度数据

图表应该能够解决这个问题

因此，您可以使用类似以下查询的方式...

select
  created_at,
  case when
    pool_id = 1
  then
    temperature
  else
    null
  end pool_1,
  case when
    pool_id = 2
  then
    temperature
  else
    null
  end pool_2,
  case when
    pool_id = 3
  then
    temperature
  else
    null
  end pool_3
from
  temperatures

我无法使提供的任何 SQL 链接工作，因此我无法验证 SQL。

我不确定返回 null 是否可行。

- WhiteHat

---

希望这可以帮到你。一般来说，越多的处理能在服务器端完成而不是客户端，页面加载速度就会越快。建议尽可能将逻辑处理放入 SQL 而非 JavaScript 中。 - WhiteHat

非常感谢。这让我开始思考如何处理我的图表。你说得对，图表可以自己解决一些细节问题。我没有使用需要存储过程的疯狂查询来处理数据，而是使用循环和array_pad()函数进行了组织。很快我就会回答自己的问题...再次感谢！这让我走上了正确的轨道。 - dhildreth

Answer 2

为了确保数据是动态的，以防将来添加了另一个池，我决定使用array_pad()创建一个填充数组，并在遍历温度数据集时进行排序。我还使用了Lavacharts，因为它使得处理Google DataTables变得容易。所以，这是我的代码（注意，添加注释字段需要更多工作）：

$dataTable = \Lava::DataTable();
$dataTable->addDateTimeColumn('DateTime');

// Add data column for each pool
$pools = \App\Pool::get();
foreach($pools as $pool) {
    $p = "Pool $pool->id";
    $dataTable->addNumberColumn("$p Temp");

    // TODO:  Create annotate fields for min and max temperatures
    // For this, we'll need to do some clever padding using array_pad()
    // and more clever index incrementing in the for() loop below.
    // Perhaps it's best to calculate and prepare in the temperatures query?
    //$dataTable->addStringColumn("$p Title");
    //$dataTable->addStringColumn("$p Text");
}

// Gather all the temperature data we wish to display.  A year ought to be enough.
// At one hour updates, that makes for about 8,766 datapoints.
$temperatures = \App\Temperature::where('created_at', '>=', \Carbon\Carbon::now()->subYear())
    ->orderBy('created_at', 'desc')
    ->orderBy('pool_id', 'asc')->get();

// Grab all the timestamps and organize into an array
$created_ats = \App\Temperature::groupBy('created_at')->pluck('created_at');

// Let's go through each datetime field and collect all temperatures recorded on that datetime.
// Then, let's store those temperatures into the appropriate index of the data row.
foreach($created_ats as $created_at) {
    $dataRow = [$created_at]; // Start the array off by adding date to beginning
    $dataRow = array_pad($dataRow, 1 + count($pools), null); // +1 to account for $created_at column
    //$dataRow = array_pad($dataRow, 1 + (count($pools) * 3), null); // TODO: multiply by 3 for annotation fields

    // Start going through each temperature recording and assign to proper spot in dataRow array
    // If temperature is not found for the datetime, the array_pad() above already accounts for null
    // in that index.  Note, the created_at comparison only accounts for the hour, not seconds or minutes.
    // TODO: Implement min and max temperature annotations.
    //$maxTemperature = 0;
    //$minTemperature = 999;
    foreach($temperatures as $temperature) {
        // TODO: Implement min and max temperature annotations.
        //$maxTemperature = ($temperature->temperature >= $maxTemperature) ? $temperature->temperature : $maxTemperature;
        //$minTemperature = ($temperature->temperature <= $minTemperature) ? $temperature->temperature : $minTemperature;

        // Compare date and hour, then assign to appropriate index of the data row according to pool id.
        // ie.  Pool ID #1 needs to be placed in [1], Pool ID #2 in [2] and so forth. Remember, [0] is date.
        if ($temperature->created_at->format('Y-m-d H') == $created_at->format('Y-m-d H')) {
            for ($i = 1; $i <= count($pools); $i++) {
                if($temperature->pool_id == $i) {
                    $dataRow[$i] = $temperature->temperature;
                }
            }
        }
    }

    // We've gone through all temperatures for this created_at datetime.  
    // Add the resulting dataRow to the dataTable.
    $dataTable->addRow($dataRow);
}   

// What we're left with is a bunch of rows that look like this!
// TODO: Add annoation fields for min and max temperatures.
// $dataTable->addRow(['created_at', 
//  'temperature1',
//  'temperature2',
//  'temperature2'
//  ]);
$jsonData = $dataTable->toJson();

// At this point, return $jsonData for use with google.visualization.DataTable(jsonData);
// Or, cache it and then return it, or whatever.

我建议缓存数据，因为在视图中渲染时需要一些时间（约1.9秒）。也许这不是最快的方法，但对我来说已经足够了。有趣的是进一步挖掘并找到其他优化方法。目前，我对此感到满意。

- dhildreth