SQL column Inversion

Original: SQL column inversion

The row and column of SQL is not new knowledge, but in the Bo Master's technical consulting, still found that many people do not understand this piece, so write a blog in this special record. This article will take MySQL as an example, and take data acquisition indicator information to obtain as an example. In the following example, you can run in Sqlfiddle.

First we need to create a database schema:

  CREATE TABLE Chart (' createtime ' DateTime, ' KPI ' varchar (+), ' field ' varchar (+), ' VA    Lue ' Double); INSERT into Chart (' createtime ', ' KPI ', ' field ', ' Value ') VALUES ("2015-02-01 12:00:00", ' Disk ', ' Disk ', 2 0), ("2015-02-01 12:15:00", ' Disk ', ' disk ', ' + '), ("2015-02-01 12:20:00", ' Disk ', ' disk ', 25), ("2015- 02-01 12:30:00 ", ' Disk ', ' Disk '," ("2015-02-01 12:35:00", ' Disk ', ' disk ', 25), ("2015-02-01 12:40:00", ' Disk ', ' disk ', +), ("2015-02-01 12:00:00", ' Disk ', ' Disk-all ', '), ("2015-02-01 12:20:00", ' Disk ', ' disk-a        ll ', (), ("2015-02-01 12:25:00", ' Disk ', ' Disk-all ', '), ("2015-02-01 12:30:00", ' Disk ', ' Disk-all ', 25), ("2015-02-01 12:35:00", ' Disk ', ' Disk-all ', '), ("2015-02-01 12:40:00", ' Disk ', ' Disk-all ', 25), ("2 015-02-01 12:40:00 ", ' CPU ', ' Cpu-all '," ("2015-02-01 12:40:00", ' CPU ', ' CPU ');  

Here the fields are represented: createtime = Data acquisition time, KPI = Data acquisition indicator, field = small class as indicator (a KPI can contain multiple fields), value = Collected data

When we create the data structure, the following because we want to get out of all the fixed time range of the specific KPI's, note that because there may be more than one field in a KPI, but some fields are leaking part of the time of the data, so here we need to supplement the anomaly Point 0. And because of this kind of Echart chart library, we want to enter the horizontal and vertical axes as two independent array object representations. So we need the following:

option = {    ....    xAxis : [        {            type : ‘category‘,            boundaryGap : false,            data : [‘周一‘,‘周二‘,‘周三‘,‘周四‘,‘周五‘,‘周六‘,‘周日‘]        }    ],    yAxis : [        {            type : ‘value‘,            axisLabel : {                formatter: ‘{value} °C‘            }        }    ],    series : [        {            ....            data:[11, 11, 15, 13, 12, 13, 10]        },        {           ....            data:[11, 11, 15, 13, 12, 13, 10]        }    ]};

It is easier to remove the horizontal axis, as follows:

SELECT createTime,kpi, field, value FROM Chart WHERE kpi = ‘disk‘ and (createTime BETWEEN ‘2015-02-01 12:00:00‘ AND ‘2015-02-01 12:25:00‘);

But the longitudinal axis if we take out in the same way, there may be a need for our automatic program compensation, and we need to ensure that each data and horizontal axis corresponding, so our program processing will be more complex, as follows:

SELECT createTime,kpi, field, value FROM Chart WHERE kpi = ‘disk‘ and (createTime BETWEEN ‘2015-02-01 12:00:00‘ AND ‘2015-02-01 12:25:00‘);

The result is:

createTime  kpi field   valueFebruary, 01 2015 12:00:00  disk    disk    20February, 01 2015 12:15:00  disk    disk    30February, 01 2015 12:20:00  disk    disk    25February, 01 2015 12:00:00  disk    disk-all    20February, 01 2015 12:20:00  disk    disk-all    30February, 01 2015 12:25:00  disk    disk-all    25

Is there any other option better? Of course that's what this article says about SQL inversion, if we can convert the return data to the following:

field   ‘2015-02-01 12:00:00’   ‘2015-02-01 12:15:00’   ‘2015-02-01 12:20:00’   ‘2015-02-01 12:25:00’disk         20                            30                     25                       0disk-all     20                             0                     30                       25

Then the procedure will be handled very well. We have been able to take all the horizontal data and sort it out, and then we will be able to do it very simply: the following:

SELECT field,SUM(IF(createTime = ‘2015-02-01 12:00:00‘, value, 0)) as ‘2015-02-01 12:00:00‘,SUM(IF(createTime = ‘2015-02-01 12:15:00‘, value, 0)) as ‘2015-02-01 12:15:00‘,SUM(IF(createTime = ‘2015-02-01 12:20:00‘, value, 0)) as ‘2015-02-01 12:20:00‘,SUM(IF(createTime = ‘2015-02-01 12:25:00‘, value, 0)) as ‘2015-02-01 12:25:00‘ FROM ChartWHERE kpi = ‘disk‘ and (createTime BETWEEN ‘2015-02-01 12:00:00‘ AND ‘2015-02-01 12:25:00‘)GROUP BY field

This returns data to meet our needs.

Now let's analyze This SQL,

1. First we use the ' if (createtime = ' 2015-02-01 12:00:00 ', value, 0) ' to process the interpolation, and to convert each row of data to time-based data, and can use if to fill ' 0 ', will be as follows:

Sql:

SELECT field,IF(createTime = ‘2015-02-01 12:00:00‘, value, 0) as ‘2015-02-01 12:00:00‘,IF(createTime = ‘2015-02-01 12:15:00‘, value, 0) as ‘2015-02-01 12:15:00‘,IF(createTime = ‘2015-02-01 12:20:00‘, value, 0) as ‘2015-02-01 12:20:00‘,IF(createTime = ‘2015-02-01 12:25:00‘, value, 0) as ‘2015-02-01 12:25:00‘ FROM ChartWHERE kpi = ‘disk‘ and (createTime BETWEEN ‘2015-02-01 12:00:00‘ AND ‘2015-02-01 12:25:00‘);

The result is:

field   ‘2015-02-01 12:00:00’   ‘2015-02-01 12:15:00’   ‘2015-02-01 12:20:00’   ‘2015-02-01 12:25:00’disk               20                       0                       0                       0disk                0                       30                      0                       0disk                0                       0                       25                      0disk-all            20                      0                       0                       0disk-all            0                       0                       30                      0disk-all            0                       0                       0                       25

1. This allows us to aggregate data rows using the aggregate function of SQL, SUM and GROUP by:

Sql:

SELECT field,SUM(IF(createTime = ‘2015-02-01 12:00:00‘, value, 0)) as ‘2015-02-01 12:00:00‘,SUM(IF(createTime = ‘2015-02-01 12:15:00‘, value, 0)) as ‘2015-02-01 12:15:00‘,SUM(IF(createTime = ‘2015-02-01 12:20:00‘, value, 0)) as ‘2015-02-01 12:20:00‘,SUM(IF(createTime = ‘2015-02-01 12:25:00‘, value, 0)) as ‘2015-02-01 12:25:00‘ FROM ChartWHERE kpi = ‘disk‘ and (createTime BETWEEN ‘2015-02-01 12:00:00‘ AND ‘2015-02-01 12:25:00‘)GROUP BY field

Effect as above.

For SQL row and column transpose can be briefly divided into two parts:

1. Take advantage of conditional logic (mysql:if, SQL Server:case ... when (SQL Server 2005 starts supporting pivot tables) ...) Change the data you want to invert into columns.
2. Take advantage of aggregate functions (SUM, max, min ...) Group by merges data. It is important to note that Max and Min need to be aware of the bounds of the data, such as a negative number and a default value of 0, then Max has a problem, so the general sum is the safest (no numbers plus 0 will change the result), but for a particular scenario Max, Min is also a security scenario.

SQL column Inversion