从MySQL中选择数据图表

Question

我现在努力挣扎了一段时间，将数据构造成Google图表所接受的模型。

图表需要这样的数据（JSON）：

[
['Time',               'url1', 'url2', 'url3'   ],
['2017-12-06T12:00:00', 1,      2,       4      ],
['2017-12-06T13:00:00', 3,      6,       5      ],
['2017-12-06T14:00:00', 2,      5,       2      ],
['2017-12-06T15:00:00', 7,      3,       1      ],
]

因此，应以类似的方式提取它。有3个选项 - 在PHP/JS中构建所有数据，或使用MySQL选择它，然后清理。

mySQL数据库表'ping_history'看起来像这样：

|--------|-----------|-------------|---------------------|
| ID     | url       | ping        | created_at          |
|--------|-----------|-------------|---------------------|
| 0      | url1      | 1           | 2017-12-06T12:00:00 |
|--------|-----------|-------------|---------------------|
| 1      | url2      | 2           | 2017-12-06T12:00:00 |
|--------|-----------|-------------|---------------------|
| 2      | url3      | 4           | 2017-12-06T12:00:10 |
|--------|-----------|-------------|---------------------|
| 3      | url1      | 3           | 2017-12-06T13:00:20 |
|--------|-----------|-------------|---------------------|
| 4      | url2      | 6           | 2017-12-06T13:00:30 |
|--------|-----------|-------------|---------------------|
| 5      | url3      | 5           | 2017-12-06T13:00:00 |
|--------|-----------|-------------|---------------------|
| 6      | url1      | 2           | 2017-12-06T14:00:40 |
|--------|-----------|-------------|---------------------|
| 7      | url2      | 5           | 2017-12-06T14:00:00 |
|--------|-----------|-------------|---------------------|
| 8      | url3      | 2           | 2017-12-06T14:00:00 |
|--------|-----------|-------------|---------------------|
| 9      | url1      | 7           | 2017-12-06T15:00:01 |
|--------|-----------|-------------|---------------------|
| 10     | url2      | 3           | 2017-12-06T15:00:02 |
|--------|-----------|-------------|---------------------|
| 11     | url3      | 1           | 2017-12-06T15:00:03 |
|--------|-----------|-------------|---------------------|

记录按小时分组（create_at），ping选择为max（ping）。

如何使这些分组的URL行以列标题或第一行出现，并且特定URL的所有记录都可以垂直进行...如果日期没有值 - 我们使用null。

也许我只是不知道该方法是如何调用的，也不能谷歌搜索。诸如旋转结果集之类的东西:)请帮助！

Answer 1

尝试以下

SELECT
    DATE_FORMAT(created_at, "%Y-%m-%dT%H:00:00") `Time`,
    MAX(CASE WHEN url='url1' THEN ping END) `url1`,
    MAX(CASE WHEN url='url2' THEN ping END) `url2`,
    MAX(CASE WHEN url='url3' THEN ping END) `url3`
FROM ping_history
GROUP BY DATE_FORMAT(created_at, "%Y-%m-%dT%H:00:00")

Answer 2

多亏了 @ravinder-reddy，我被带到了正确的方向，并设法构建了我的需求。@leran2002当您知道项目数组时，答案很好。就我而言，这是未知的，因此必须进行动态选择。我最终得到了一个存储过程。在这里是：

CREATE DEFINER=`%`@`%` PROCEDURE `get_chart_history_pings`(IN masterId INT, IN daysLimit INT)
BEGIN
    SET SESSION group_concat_max_len = 1000000;
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'ifnull(MAX(case when url = ''',
          url,
          ''' then ping end), null) AS `',
          url, '`'
        )
      ) INTO @sql
    FROM
      history
    WHERE 
      url IN (
        SELECT url FROM hosts WHERE id = masterId
      )
      OR url = ( SELECT url FROM masters WHERE id = masterId LIMIT 1 );
    SET @sql = CONCAT('SELECT date_format(created_at, "%Y%-%m%-%d% %H%:%i%:00" ) as time, ', @sql, ' 
                      FROM history
                      WHERE created_at > DATE_SUB(now(), INTERVAL ',daysLimit,' DAY)
                      GROUP BY date_format( created_at, "%Y%m%d%H" )
                      ORDER BY created_at ASC');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

也要归功于@mihai，因为他的答案https://stackoverflow.com/a/28284999/2470912导致了此解决方案。