Select data from MySQL for multi line chart

Question

I'm struggling for some time now with constructing of data to a model that Google Chart would accept.

Chart requires data like this (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      ],
]

So it should be extracted in similar manner. There are 3 options - construct all data in PHP/JS or select it with MySQL and then just cleanup.

MySQL database table 'ping_history' looks like this:

|--------|-----------|-------------|---------------------|
| 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 |
|--------|-----------|-------------|---------------------|

Records are grouped by HOUR(created_at), ping selected as MAX(ping).

How to make those grouped url rows appear as column titles or first row and all records for particular url make to go vertically... In case no value for date - we use null.

Maybe I just don't know how this method is called and can't google it. Something like rotating results set :) Please help!

search for Pivot Tables. https://stackoverflow.com/questions/1241178/mysql-rows-to-columns — Ravinder Reddy, Dec 06 '17 at 19:11

score 0 · Answer 1 · answered Dec 07 '17 at 05:41

Try the following

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")

score 0 · Accepted Answer · answered Dec 07 '17 at 10:35

Thanks to @ravinder-reddy I was led to the right direction and managed to build what I need. @leran2002 answer is good when you have known items array. In my case it's unknown so had to make dynamic selection. I ended up with a stored procedure. Here it is:

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

Also credit goes to @mihai because his answer https://stackoverflow.com/a/28284999/2470912 led to this solution.

Select data from MySQL for multi line chart

2 Answers2