{"id":1400,"date":"2009-11-03T12:44:13","date_gmt":"2009-11-03T10:44:13","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=1400"},"modified":"2009-11-03T14:15:18","modified_gmt":"2009-11-03T12:15:18","slug":"sql-multi-line-chart","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/sql-multi-line-chart","title":{"rendered":"SQL multi line chart"},"content":{"rendered":"

Time for another charting SQL query. I wish to present a single-query generated multi-line\/area chart. I’ll walk through some of the steps towards making this happen. By the end of this post I’ll present some real-data charts, area charts and colored charts.<\/p>\n

\n
+---------+-----------------------------------------------------------------------------------+\r\n| y_scale | chart\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+---------+-----------------------------------------------------------------------------------+\r\n| 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | ****---------#######----------------------------------------*******--------###### |\r\n| 0.88\u00a0\u00a0\u00a0 | ----***---###-------##-----------------------------------***-------***---##------ |\r\n| 0.75\u00a0\u00a0\u00a0 | -------**#------------##-------------------------------**-------------*##-------- |\r\n| 0.63\u00a0\u00a0\u00a0 | ------##-*--------------##----------------------------*--------------##**-------- |\r\n| 0.5\u00a0\u00a0\u00a0\u00a0 | -@@@@@@@@@@@@@@@----------#-----@@@@@@@@@@@@@@@@----**---------@@@@@@@@@@@@@@@@-- |\r\n| 0.38\u00a0\u00a0\u00a0 | ----#-------*--------------##----------------------*---------------#-------*----- |\r\n| 0.25\u00a0\u00a0\u00a0 | --##---------*---------------#--------------------*--------------##---------*---- |\r\n| 0.13\u00a0\u00a0\u00a0 | -#------------**--------------#-----------------**--------------#------------*--- |\r\n| 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | @---------------*--------------##--------------*---------------#--------------**- |\r\n| -0.12\u00a0\u00a0 | -----------------*---------------#------------*--------------##-----------------* |\r\n| -0.25\u00a0\u00a0 | ------------------*---------------#---------**--------------#-------------------- |\r\n| -0.37\u00a0\u00a0 | -------------------**--------------#-------*---------------#--------------------- |\r\n| -0.5\u00a0\u00a0\u00a0 | ----------------@@@@@@@@@@@@@@@@----##----*-----@@@@@@@@@@@@@@@----------------@@ |\r\n| -0.62\u00a0\u00a0 | ----------------------**--------------#-**--------------#------------------------ |\r\n| -0.75\u00a0\u00a0 | ------------------------**------------**#-------------##------------------------- |\r\n| -0.87\u00a0\u00a0 | --------------------------**-------***---###-------###--------------------------- |\r\n| -1\u00a0\u00a0\u00a0\u00a0\u00a0 | ----------------------------*******---------#######------------------------------ |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | v:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::v |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 # sin(x)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 * cos(x)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 @ sign(tan(x))\/2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+---------+-----------------------------------------------------------------------------------+<\/pre>\n<\/blockquote>\n
Requirements<\/h4>\n
We need a generic query, which returns at least these two columns: ordering_column<\/strong> and row_values<\/strong>, The latter being a comma-delimited list of values. For example, the following query will do:<\/p>\n
\n
SELECT\r\n value\/10 AS ordering_column,\r\n CONCAT(SIN(value\/10), ',', COS(value\/10), ',', SIGN(TAN(value\/10))\/2) AS row_values,\r\n @multi_line_chart_values_legend := 'sin(x),cos(x),sign(tan(x))\/2' AS legend\r\nFROM tinyint_asc\r\nLIMIT 81;\r\n+-----------------+---------------------------------------------+------------------------------+\r\n| ordering_column | row_values\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | legend\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-----------------+---------------------------------------------+------------------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.0000 | 0,1,0.0000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | sin(x),cos(x),sign(tan(x))\/2 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.1000 | 0.099833416646828,0.99500416527803,0.5000\u00a0\u00a0 | sin(x),cos(x),sign(tan(x))\/2 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.2000 | 0.19866933079506,0.98006657784124,0.5000\u00a0\u00a0\u00a0 | sin(x),cos(x),sign(tan(x))\/2 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.3000 | 0.29552020666134,0.95533648912561,0.5000\u00a0\u00a0\u00a0 | sin(x),cos(x),sign(tan(x))\/2 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.4000 | 0.38941834230865,0.92106099400289,0.5000\u00a0\u00a0\u00a0 | sin(x),cos(x),sign(tan(x))\/2 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.5000 | 0.4794255386042,0.87758256189037,0.5000\u00a0\u00a0\u00a0\u00a0 | sin(x),cos(x),sign(tan(x))\/2 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.6000 | 0.56464247339504,0.82533561490968,0.5000\u00a0\u00a0\u00a0 | sin(x),cos(x),sign(tan(x))\/2 |\r\n...<\/pre>\n<\/blockquote>\n
Don’t worry about the @multi_line_chart_values_legend<\/strong> variable; it will be used later on for presenting chart legend.<\/p>\n
Since I’ve already presented with how to create line charts in Auto scaling, scaled SQL graphs concluded<\/a>, we will concentrate on how to present a single chart column, which includes multiple values.<\/p>\n
Step 1<\/h4>\n
Let’s say we wish to present the values 12,5,16. We wish to eventually generate something like:<\/p>\n
\n
+------------------+\r\n| unwalked_bar\u00a0\u00a0\u00a0\u00a0 |\r\n+------------------+\r\n| ----Y------X---Z |\r\n+------------------+<\/pre>\n<\/blockquote>\n
Where X<\/strong> is in position 12, Y<\/strong> in position 5, and Z<\/strong> in position 16.<\/p>\n
We begin with having these values concatenated into one string:<\/p>\n
\n
SET @values := '12,5,16';\r\nSET @num_values := CHAR_LENGTH(@values)-CHAR_LENGTH(REPLACE(@values,',',''))+1;\r\nSELECT @num_values;\r\n+-------------+\r\n| @num_values |\r\n+-------------+\r\n| 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-------------+<\/pre>\n<\/blockquote>\n
The query above parses the string and correctly identified there are three tokens.<\/p>\n
Step 2<\/h4>\n
We now move on to tokenizing the text:<\/p>\n
\n
SELECT\r\n *,\r\n SUBSTRING_INDEX(SUBSTRING_INDEX(@row_values, ',', tinyint_asc.value), ',', -1) AS row_value\r\nFROM\r\n tinyint_asc,\r\n (SELECT @row_values := '12,5,16' AS row_values) AS sel_values,\r\n (SELECT @num_values := CHAR_LENGTH(@row_values)-CHAR_LENGTH(REPLACE(@row_values,',',''))+1 AS num_values) AS sel_num_values\r\nWHERE\r\n tinyint_asc.value BETWEEN 1 AND @num_values\r\n;\r\n+-------+------------+------------+-----------+\r\n| value | row_values | num_values | row_value |\r\n+-------+------------+------------+-----------+\r\n|\u00a0\u00a0\u00a0\u00a0 1 | 12,5,16\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 | 12\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0 2 | 12,5,16\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 | 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0 3 | 12,5,16\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 | 16\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-------+------------+------------+-----------+<\/pre>\n<\/blockquote>\n
Using a numbers table (tinyint_asc<\/a>), we walk the string and tokenize it. We also maintain an indicator per value.<\/p>\n
Step 3<\/h4>\n
We need to recognize the minimum and maximum values:<\/p>\n
\n
SELECT\r\n  value AS row_value_indicator,\r\n  row_value,\r\n  @min_row_value := LEAST(IFNULL(@min_row_value, row_value), row_value) AS min_row_value,\r\n  @max_row_value := GREATEST(IFNULL(@max_row_value, row_value), row_value) AS max_row_value\r\nFROM (\r\n  SELECT\r\n  *,\r\n  SUBSTRING_INDEX(SUBSTRING_INDEX(@row_values, ',', tinyint_asc.value), ',', -1)+0 AS row_value\r\n    FROM\r\n      tinyint_asc,\r\n      (SELECT @row_values := '12,5,16' AS row_values) AS sel_values,\r\n      (SELECT @num_values := CHAR_LENGTH(@row_values)-CHAR_LENGTH(REPLACE(@row_values,',',''))+1 AS num_values) AS sel_num_values,\r\n      (SELECT @min_row_value := NULL) AS sel_min_row_value,\r\n      (SELECT @max_row_value := NULL) AS sel_max_row_value\r\n    WHERE\r\n      tinyint_asc.value BETWEEN 1 AND @num_values\r\n  ) sel_row_values\r\n;\r\n+---------------------+-----------+---------------+---------------+\r\n| row_value_indicator | row_value | min_row_value | max_row_value |\r\n+---------------------+-----------+---------------+---------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16 |\r\n+---------------------+-----------+---------------+---------------+<\/pre>\n<\/blockquote>\n
The last line presents the correct values: 5 and 16 being min, max values respectively.<\/p>\n
Step 4<\/h4>\n
We now iterate from min value to max value, this being the graph range, and, per row value (token in row_values<\/strong> column), we indicate whether there’s a pixel on the graph.<\/p>\n
\n
SELECT\r\n  * ,\r\n  IF(tinyint_asc.value = row_value, row_value_indicator, '-') AS display_val\r\nFROM\r\n  tinyint_asc,\r\n  (SELECT\r\n    value AS row_value_indicator,\r\n    row_value,\r\n    @min_row_value := LEAST(IFNULL(@min_row_value, row_value), row_value) AS min_row_value,\r\n    @max_row_value := GREATEST(IFNULL(@max_row_value, row_value), row_value) AS max_row_value\r\n    FROM (\r\n    SELECT\r\n      *,\r\n      SUBSTRING_INDEX(SUBSTRING_INDEX(@row_values, ',', tinyint_asc.value), ',', -1)+0 AS row_value\r\n    FROM\r\n      tinyint_asc,\r\n      (SELECT @row_values := '12,5,16' AS row_values) AS sel_values,\r\n      (SELECT @num_values := CHAR_LENGTH(@row_values)-CHAR_LENGTH(REPLACE(@row_values,',',''))+1 AS num_values) AS sel_num_values,\r\n      (SELECT @min_row_value := NULL) AS sel_min_row_value,\r\n      (SELECT @max_row_value := NULL) AS sel_max_row_value\r\n    WHERE\r\n      tinyint_asc.value BETWEEN 1 AND @num_values\r\n    ) sel_row_values\r\n  ) AS sel_row_values_indicators\r\nWHERE\r\n  tinyint_asc.value BETWEEN 1 AND @max_row_value\r\n;\r\n+-------+---------------------+-----------+---------------+---------------+-------------+\r\n| value | row_value_indicator | row_value | min_row_value | max_row_value | display_val |\r\n+-------+---------------------+-----------+---------------+---------------+-------------+\r\n|     1 |                   1 |        12 |            12 |            12 | -           |\r\n|     1 |                   2 |         5 |             5 |            12 | -           |\r\n|     1 |                   3 |        16 |             5 |            16 | -           |\r\n|     2 |                   1 |        12 |            12 |            12 | -           |\r\n|     2 |                   2 |         5 |             5 |            12 | -           |\r\n|     2 |                   3 |        16 |             5 |            16 | -           |\r\n|     3 |                   1 |        12 |            12 |            12 | -           |\r\n|     3 |                   2 |         5 |             5 |            12 | -           |\r\n|     3 |                   3 |        16 |             5 |            16 | -           |\r\n|     4 |                   1 |        12 |            12 |            12 | -           |\r\n|     4 |                   2 |         5 |             5 |            12 | -           |\r\n|     4 |                   3 |        16 |             5 |            16 | -           |\r\n|     5 |                   1 |        12 |            12 |            12 | -           |\r\n|     5 |                   2 |         5 |             5 |            12 | 2           |\r\n|     5 |                   3 |        16 |             5 |            16 | -           |\r\n|     6 |                   1 |        12 |            12 |            12 | -           |\r\n|     6 |                   2 |         5 |             5 |            12 | -           |\r\n|     6 |                   3 |        16 |             5 |            16 | -           |<\/pre>\n<\/blockquote>\n
This table can get very long.<\/p>\n
Step 5<\/h4>\n
We now group the marks per graph-row value. If there’s no mark, we present with an empty space. If only one mark is present, we display that mark. If two lines collide on that point, we need to decide which mark to use. In the following example, we choose by order of appearance in the row_values<\/strong> column. Another way (used later on) is to choose the higher value.<\/p>\n
\n
SELECT\r\n  string_position,\r\n  LEFT(GROUP_CONCAT(bar_string_token ORDER BY bar_string_token DESC separator ''), 1) AS chosen_mark\r\nFROM\r\n  (SELECT\r\n    value AS string_position,\r\n    IF(tinyint_asc.value = row_value, row_value_indicator, ' ') AS bar_string_token\r\n  FROM\r\n    tinyint_asc,\r\n    (SELECT\r\n      value AS row_value_indicator,\r\n      row_value,\r\n      @min_row_value := LEAST(IFNULL(@min_row_value, row_value), row_value) AS min_row_value,\r\n      @max_row_value := GREATEST(IFNULL(@max_row_value, row_value), row_value) AS max_row_value\r\n      FROM (\r\n      SELECT\r\n        *,\r\n        SUBSTRING_INDEX(SUBSTRING_INDEX(@row_values, ',', tinyint_asc.value), ',', -1)+0 AS row_value\r\n      FROM\r\n        tinyint_asc,\r\n        (SELECT @row_values := '12,5,16' AS row_values) AS sel_values,\r\n        (SELECT @num_values := CHAR_LENGTH(@row_values)-CHAR_LENGTH(REPLACE(@row_values,',',''))+1 AS num_values) AS sel_num_values,\r\n        (SELECT @min_row_value := NULL) AS sel_min_row_value,\r\n        (SELECT @max_row_value := NULL) AS sel_max_row_value\r\n      WHERE\r\n        tinyint_asc.value BETWEEN 1 AND @num_values\r\n      ) sel_row_values\r\n    ) AS sel_row_values_indicators\r\n  WHERE\r\n    tinyint_asc.value BETWEEN 1 AND @max_row_value\r\n  ) AS sel_marked_row_values\r\nGROUP BY\r\n  string_position\r\n;\r\n+-----------------+-------------+\r\n| string_position | chosen_mark |\r\n+-----------------+-------------+\r\n|               1 |             |\r\n|               2 |             |\r\n|               3 |             |\r\n|               4 |             |\r\n|               5 | 2           |\r\n|               6 |             |\r\n|               7 |             |\r\n|               8 |             |\r\n|               9 |             |\r\n|              10 |             |\r\n|              11 |             |\r\n|              12 | 1           |\r\n|              13 |             |\r\n|              14 |             |\r\n|              15 |             |\r\n|              16 | 3           |\r\n+-----------------+-------------+<\/pre>\n<\/blockquote>\n
Step 6<\/h4>\n
It’s now just a matter of using GROUP_CONCAT to turn this into the following horizontal bar:<\/p>\n
\n
+------------------+\r\n| unwalked_bar\u00a0\u00a0\u00a0\u00a0 |\r\n+------------------+\r\n| ----2------1---3 |\r\n+------------------+<\/pre>\n<\/blockquote>\n
(I know I’m mixing spaces and dashes, it’s just for display purposes).<\/p>\n
Putting it all together<\/h4>\n
From this point on, we use the techniques shown on Auto scaling, scaled SQL graphs concluded<\/a> to generate the complete chart. For the curious, here’s what the complete query looks like. The text in bold<\/strong> is the only thing that needs to change; replace this with your own query.<\/p>\n
\n
SELECT\r\n  y_scale,\r\n  horizontal_bar as chart\r\n  FROM\r\n  (\r\n  SELECT\r\n    @multi_line_chart_row_number := @multi_line_chart_row_number+1,\r\n    CASE @multi_line_chart_row_number\r\n      WHEN 1 THEN ROUND(@multi_line_chart_max_value, @multi_line_chart_value_precision)\r\n      WHEN @multi_line_chart_graph_rows THEN ROUND(@multi_line_chart_min_value, @multi_line_chart_value_precision)\r\n      ELSE ROUND(@multi_line_chart_max_value-(@multi_line_chart_max_value-@multi_line_chart_min_value)*(@multi_line_chart_row_number-1)\/(@multi_line_chart_graph_rows-1), @multi_line_chart_value_precision)\r\n    END AS y_scale,\r\n    horizontal_bar,\r\n    @multi_line_chart_bar_length := IFNULL(@multi_line_chart_bar_length, CHAR_LENGTH(horizontal_bar))\r\n  FROM\r\n    (SELECT @multi_line_chart_row_number := 0) AS select_row\r\n    INNER JOIN\r\n    (\r\n    SELECT\r\n      GROUP_CONCAT(SUBSTRING(unwalked_bar, tinyint_asc.value, 1) ORDER BY ordering_column SEPARATOR '') AS horizontal_bar\r\n    FROM\r\n      tinyint_asc\r\n    INNER JOIN (\r\n      SELECT\r\n        ordering_column,\r\n        GROUP_CONCAT(bar_string_token ORDER BY string_position SEPARATOR '') AS unwalked_bar\r\n      FROM\r\n        (SELECT\r\n          ordering_column,\r\n          string_position,\r\n          scaled_string_position,\r\n          REPLACE(LEFT(GROUP_CONCAT(bar_string_token ORDER BY bar_string_token DESC SEPARATOR ''), 1), ' ', '-') AS bar_string_token\r\n        FROM\r\n          (SELECT\r\n            ordering_column,\r\n            @multi_line_chart_scaled_string_position := CONVERT((row_value-@multi_line_chart_min_value)*(@multi_line_chart_graph_rows-1)\/(@multi_line_chart_max_value-@multi_line_chart_min_value), UNSIGNED) AS scaled_string_position,\r\n            value AS string_position,\r\n            IF(tinyint_asc.value = @multi_line_chart_scaled_string_position+1, SUBSTRING(IF(@multi_line_chart_values_legend IS NULL, @multi_line_chart_graph_fallback_colors, @multi_line_chart_graph_colors), row_value_indicator, 1), ' ') AS bar_string_token\r\n          FROM\r\n            tinyint_asc,\r\n            (SELECT\r\n              ordering_column,\r\n              value AS row_value_indicator,\r\n              row_value\r\n            FROM (\r\n              SELECT\r\n                *,\r\n                @multi_line_chart_min_value := LEAST(IFNULL(@multi_line_chart_min_value, row_value), row_value) AS min_value,\r\n                @multi_line_chart_max_value := GREATEST(IFNULL(@multi_line_chart_max_value, row_value), row_value) AS max_value,\r\n                @multi_line_chart_min_range := LEAST(IFNULL(@multi_line_chart_min_range, ordering_column), ordering_column) AS min_range,\r\n                @multi_line_chart_max_range := GREATEST(IFNULL(@multi_line_chart_max_range, ordering_column), ordering_column) AS max_range\r\n              FROM\r\n                (SELECT\r\n                  *,\r\n                  SUBSTRING_INDEX(SUBSTRING_INDEX(row_values, ',', tinyint_asc.value), ',', -1)+0 AS row_value,\r\n                  @multi_line_chart_num_values := CHAR_LENGTH(row_values)-CHAR_LENGTH(REPLACE(row_values,',',''))+1 AS num_values\r\n                FROM\r\n                  (SELECT @multi_line_chart_values_legend := NULL) AS select_nullify_values_legend,\r\n                  (SELECT @multi_line_chart_bar_length := NULL) AS select_nullify_bar_length,\r\n                  tinyint_asc,\r\n                  (\r\n                    SELECT\r\n                      value\/10 AS ordering_column,\r\n                      CONCAT(SIN(value\/10), ',', COS(value\/10), ',', SIGN(TAN(value\/10))\/2) AS row_values,\r\n                      @multi_line_chart_values_legend := 'sin(x),cos(x),sign(tan(x))\/2'\r\n                    FROM tinyint_asc LIMIT 81<\/strong>\r\n                  ) AS sel_main_values,\r\n                  (SELECT @multi_line_chart_min_value := NULL) AS select_min,\r\n                  (SELECT @multi_line_chart_max_value := NULL) AS select_max,\r\n                  (SELECT @multi_line_chart_min_range := NULL) AS select_min_range,\r\n                  (SELECT @multi_line_chart_max_range := NULL) AS select_max_range,\r\n                  (SELECT @multi_line_chart_graph_colors := '#*@%o+x;m:') AS select_graph_colors,\r\n                  (SELECT @multi_line_chart_graph_fallback_colors := 'abcdefghij') AS select_graph_fallback_colors,\r\n                  (SELECT @multi_line_chart_value_precision := 2) AS select_value_precision,\r\n                  (SELECT @multi_line_chart_graph_rows := 17) AS select_graph_rows\r\n                ) sel_counted_values_main_values\r\n              WHERE\r\n                value BETWEEN 1 AND @multi_line_chart_num_values\r\n              ) sel_row_values\r\n            ) AS sel_row_values_indicators\r\n          WHERE\r\n            tinyint_asc.value BETWEEN 1 AND @multi_line_chart_graph_rows\r\n          ) AS sel_marked_row_values\r\n        GROUP BY\r\n          ordering_column, string_position\r\n        ) AS sel_walked_bar\r\n      GROUP BY\r\n        ordering_column\r\n    ) AS select_vertical\r\n    WHERE\r\n      tinyint_asc.value BETWEEN 1 AND CHAR_LENGTH(unwalked_bar)\r\n    GROUP BY\r\n      tinyint_asc.value\r\n    ORDER BY\r\n      tinyint_asc.value DESC\r\n    ) AS select_horizontal\r\n  ) AS select_horizontal_untitled\r\nUNION ALL\r\nSELECT '', CONCAT('v', REPEAT(':', @multi_line_chart_bar_length-2), 'v')\r\nUNION ALL\r\nSELECT '', CONCAT(@multi_line_chart_min_range, REPEAT(' ', @multi_line_chart_bar_length-CHAR_LENGTH(@multi_line_chart_min_range)-CHAR_LENGTH(@multi_line_chart_max_range)), @multi_line_chart_max_range)\r\nUNION ALL\r\nSELECT\r\n  '', CONCAT('    ', SUBSTRING(@multi_line_chart_graph_colors, value, 1), ' ', SUBSTRING_INDEX(SUBSTRING_INDEX(@multi_line_chart_values_legend, ',', value), ',', -1))\r\nFROM\r\n  tinyint_asc\r\nWHERE\r\n  value BETWEEN 1 AND @multi_line_chart_num_values\r\n  AND @multi_line_chart_values_legend IS NOT NULL\r\n;<\/pre>\n<\/blockquote>\n
Area charts<\/h4>\n
Sinus functions curves go nicely with ASCII art. But your everyday chart won’t have such nice curves. There is some erratic behavior to, say, your number of queries per hour. It doesn’t display well in the above graph.<\/p>\n
Which is why we can choose to present area graphs: not only display the line<\/em>, but also the area<\/em> beneath it.<\/p>\n
This leads to the problem of hiding: higher values can hide the lower values display. So we need to adjust the above logic and make sure we always bring to front the lower values.<\/p>\n
The following example shows the relation between the percentage of slow queries (per hour) and the percentage of full table scans (of total queries, per hour). It’s really fun to see the unmistakeable connection!<\/p>\n
\n
+---------+---------------------------------------------------------------+\r\n| y_scale | chart\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+---------+---------------------------------------------------------------+\r\n| 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | --------------------------------*--*------------------------- |\r\n| 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | -----------------------------****-*****------------------*--- |\r\n| 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | -----------------------------**********-----------------**-*- |\r\n| 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | ----------*------------------**********--------------*****-** |\r\n| 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | ------*--**-----------------************-*-----------******** |\r\n| 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | ------*******---------------****************--------********* |\r\n| 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | ------*******---------------******************------********* |\r\n| 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | ------*********-------------******************------********* |\r\n| 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | -----************--------*-*******************------********* |\r\n| 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | *----*****************--************************-**-********* |\r\n| 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | *----******************************************************** |\r\n| 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | *----******************************************************** |\r\n| 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | **--********************************************************* |\r\n| 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | ************************************************************* |\r\n| 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | ************************************************************* |\r\n| 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | ************************************************************* |\r\n| 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | *****************************#******************************* |\r\n| 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | *****************************##########*****************##*#* |\r\n| 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | ******#***#*****************###########**************######## |\r\n| 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | ******#######***************###############*********######### |\r\n| 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | #*****########**************##################******######### |\r\n| 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | #****##########*#*******##*###################******######### |\r\n| 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | #***######################################################### |\r\n| 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | #***######################################################### |\r\n| 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | ############################################################# |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | v:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::v |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 2009-10-22 20:00:00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-10-25 08:00:00 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 # slow queries percent\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 * select scan percent\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+---------+---------------------------------------------------------------<\/pre>\n<\/blockquote>\n
Next we look at the number of SELECT<\/strong>, INSERT<\/strong>, UPDATE<\/strong>, DELETE<\/strong> queries per sec – on a per hour basis.<\/p>\n
\n
+---------+---------------------------------------------------------------+\r\n| y_scale | chart\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+---------+---------------------------------------------------------------+\r\n| 135\u00a0\u00a0\u00a0\u00a0 | --##--------------------------------------------------------- |\r\n| 130\u00a0\u00a0\u00a0\u00a0 | -###--------------------------------------------------------- |\r\n| 125\u00a0\u00a0\u00a0\u00a0 | -###----------------------##--------------------------------- |\r\n| 120\u00a0\u00a0\u00a0\u00a0 | -###-------------------#####--------------------------------- |\r\n| 115\u00a0\u00a0\u00a0\u00a0 | -###-----------------#######--------------------------------- |\r\n| 110\u00a0\u00a0\u00a0\u00a0 | -###----------------########----------------------##--------- |\r\n| 104\u00a0\u00a0\u00a0\u00a0 | -###--------------##########--------------------####--------- |\r\n| 99\u00a0\u00a0\u00a0\u00a0\u00a0 | ####*--------#--############------------------######--------- |\r\n| 94\u00a0\u00a0\u00a0\u00a0\u00a0 | ####*--------#-#############---------------#########--------- |\r\n| 89\u00a0\u00a0\u00a0\u00a0\u00a0 | #####-----##################------------############--------# |\r\n| 84\u00a0\u00a0\u00a0\u00a0\u00a0 | ############################-----------#############--------# |\r\n| 79\u00a0\u00a0\u00a0\u00a0\u00a0 | ####%########################---####################---###### |\r\n| 74\u00a0\u00a0\u00a0\u00a0\u00a0 | ####%######################################################## |\r\n| 69\u00a0\u00a0\u00a0\u00a0\u00a0 | ####%*####################################################### |\r\n| 64\u00a0\u00a0\u00a0\u00a0\u00a0 | ####%*####################################################### |\r\n| 59\u00a0\u00a0\u00a0\u00a0\u00a0 | ##*#%*###############*####################################### |\r\n| 54\u00a0\u00a0\u00a0\u00a0\u00a0 | #**#%*#####*#*****###*****##*###########*#####*#####*#####*## |\r\n| 49\u00a0\u00a0\u00a0\u00a0\u00a0 | #***%%*#*********************##########****#*********#**##*** |\r\n| 44\u00a0\u00a0\u00a0\u00a0\u00a0 | #***%%*************************##**************************** |\r\n| 39\u00a0\u00a0\u00a0\u00a0\u00a0 | ****%%******************************************************* |\r\n| 33\u00a0\u00a0\u00a0\u00a0\u00a0 | ****%%**********************************************%******** |\r\n| 28\u00a0\u00a0\u00a0\u00a0\u00a0 | *%%*%%**********%******%%*%*%*******%***************%******** |\r\n| 23\u00a0\u00a0\u00a0\u00a0\u00a0 | %%%%o%**%**%%o%%%%%%%%%%%%%%o******%%*%%%%%%%%%%%%%%%%*****%% |\r\n| 18\u00a0\u00a0\u00a0\u00a0\u00a0 | %oooooooo%%oooooooooooooooooo%%oo%oooooooooooooooooooo%o%%ooo |\r\n| 13\u00a0\u00a0\u00a0\u00a0\u00a0 | ooooooooo%%oooooooooooooooooo%%%%%%ooooooooooooooooooo%o%%ooo |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | v:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::v |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 2009-10-22 20:00:00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-10-25 08:00:00 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 # com_select per second\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 * com_insert per second\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 o com_update per second\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 % com_delete per second\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+---------+---------------------------------------------------------------+<\/pre>\n<\/blockquote>\n
Here’s the code<\/a> to do that.<\/p>\n
Colored charts<\/h4>\n
Time to add colors! The previous charts can be easily improved by sending the terminal escape characters to instruct initiating color display (thanks to TheVoo<\/strong> for providing the idea and implementation for that on SQL pie chart<\/a>).<\/p>\n
I recommend using the following pager<\/strong> if you’re on unix-like OS:<\/p>\n
\n
pager awk '{sub(\/..$\/, \"\")} {sub(\/[ ]+$\/, \"\")} !\/^[+][-]\/ && NR > 3 { print }'<\/pre>\n<\/blockquote>\n
The following chart is similar to the above, but now utilizes colors. It presents the number of popular DML queries per second, on an hourly basis.<\/p>\n
\"Colored<\/a><\/p><\/blockquote>\n
Again, comparing slow queries with full table scans:<\/p>\n
\n
\"Slow<\/a>
Slow queries and full scans overlay<\/figcaption><\/figure><\/blockquote>\n
Comparing number of created temporary tables with number of created disk temporary tables:<\/p>\n
\n
\"temp<\/a>
temp tables vs disk temp tables<\/figcaption><\/figure><\/blockquote>\n
And, once again, the number of popular DML statements, zoomed in.<\/p>\n
\"color_multi_line_chart_dml\"<\/a><\/p><\/blockquote>\n
Here’s the code<\/a> for colored charts.<\/p>\n
Some of the techinuqes used in my charting series<\/a> of blogs can be used, pretty much in the same way, in order to generate Google charts<\/a>. But other techniques can be used, as well.<\/p>\n
More on this in future posts.<\/p>\n","protected":false},"excerpt":{"rendered":"
Time for another charting SQL query. I wish to present a single-query generated multi-line\/area chart. I’ll walk through some of the steps towards making this happen. By the end of this post I’ll present some real-data charts, area charts and colored charts. +———+———————————————————————————–+ | y_scale | chart\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | +———+———————————————————————————–+ | 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | ****———#######—————————————-*******——–###### | | […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[41,21],"class_list":["post-1400","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-graphs","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-mA","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1400","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=1400"}],"version-history":[{"count":38,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1400\/revisions"}],"predecessor-version":[{"id":1473,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1400\/revisions\/1473"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=1400"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=1400"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=1400"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}