MySQL pivot table via a view

mariadbMySQLview

Suppose the following schema

CREATE TABLE `log_apache` (
  `date` datetime NOT NULL,
  `count` int(10) unsigned NOT NULL,
  `input` bigint(20) unsigned NOT NULL,
  `output` bigint(20) unsigned NOT NULL,
  `server` char(30) NOT NULL,
  `vhostid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`date`,`server`,`vhostid`),
  UNIQUE KEY `date` (`date`,`vhostid`,`input`,`output`)
);

INSERT INTO log_apache VALUES ('2014-04-01', 12, 19031, 1358358, 'server 1', 11);
INSERT INTO log_apache VALUES ('2014-04-01', 1, 41051, 151977, 'server 1', 15);
INSERT INTO log_apache VALUES ('2014-04-01', 22, 4051, 11115, 'server 1', 5);
INSERT INTO log_apache VALUES ('2014-04-01', 21, 5893, 81906, 'server 2', 5);
INSERT INTO log_apache VALUES ('2014-04-01', 13, 4051, 11115, 'server 5', 11);
INSERT INTO log_apache VALUES ('2014-04-02', 9, 8195, 331551, 'server 3', 115);
INSERT INTO log_apache VALUES ('2014-04-02', 12, 19031, 1358358, 'server 2', 12);
INSERT INTO log_apache VALUES ('2014-04-02', 12, 19031, 1358358, 'server 3', 2);
INSERT INTO log_apache VALUES ('2014-04-03', 12, 19031, 1358358, 'server 4', 11);
INSERT INTO log_apache VALUES ('2014-04-01', 12, 19031, 1358358, 'server 4', 51);
INSERT INTO log_apache VALUES ('2014-04-02', 12, 19031, 1358358, 'server 1', 11);

I need to form a view of the table that looks more like this

-----------------------------------------------------------------------------------
|  Server  | 2014-04-01                                | 2014-04-02               |
----------------------------------------------------------------------------------|
| Server 1 | sum(`count`*input) | sum(`count`*output)  | input calc | output calc |
| Server 2 | sum(`count`*input) | sum(`count`*output)  | input calc | output calc |
| Server 3 | sum(`count`*input) | sum(`count`*output)  | input calc | output calc |
| Server 4 | sum(`count`*input) | sum(`count`*output)  | input calc | output calc |
| Server 5 | sum(`count`*input) | sum(`count`*output)  | input calc | output calc | 
-----------------------------------------------------------------------------------

But not just with one day but for every day.

Ideally I'd have a where condition for the view so I could isolate vhostid and date range. I believe this requires a pivot table but I have very little experience with them and am not sure how to begin constructing such a query. If you could help with this and provide and explanation as to how and why the query should be that way that would be fantastic.

PS I came up with this query but clearly the result set it wrong.

SELECT t0.server, t1.`date_in`, t1.`date_out`, t2.`date_in`, t2.`date_out`
  FROM (select distinct(server) as server from log_apache order by server) as t0
  LEFT JOIN (SELECT server as Server, 
                    sum(`count`*input) as date_in, 
                    sum(`count`*output) as date_out
               FROM log_apache
              WHERE vhostid = 11
                AND `date` = '2014-04-01'
               GROUP BY server) as t1
    ON t0.server = t1.server
  LEFT JOIN (SELECT server as server, 
                    sum(`count`*input) as date_in, 
                    sum(`count`*output) as date_out
               FROM log_apache
              WHERE vhostid = 11
                AND `date` = '2014-04-02'
              GROUP BY server) as t2
    ON t0.server = t2.server

Best Answer

May be you need much more simple table? 

----------------------------------------------------
|  Server  | Date       |                          |
---------------------------------------------------|
| Server 1 | 2014-04-01 | input calc | output calc |
| Server 2 | 2014-04-01 | input calc | output calc |
| Server 3 | 2014-04-01 | input calc | output calc |
| Server 4 | 2014-04-01 | input calc | output calc |
| Server 5 | 2014-04-01 | input calc | output calc |
| Server 1 | 2014-04-02 | input calc | output calc |
| Server 2 | 2014-04-02 | input calc | output calc |
| Server 3 | 2014-04-02 | input calc | output calc |
| Server 4 | 2014-04-02 | input calc | output calc |
| Server 5 | 2014-04-02 | input calc | output calc |
| Server 1 | 2014-04-03 | input calc | output calc |
| Server 2 | 2014-04-03 | input calc | output calc |
| Server 3 | 2014-04-03 | input calc | output calc |
| Server 4 | 2014-04-03 | input calc | output calc |
| Server 5 | 2014-04-03 | input calc | output calc |
----------------------------------------------------

That table can be produced by:

SELECT z.server_name,
       w.date,
       SUM(w.input_calc)  AS input_calc,
       SUM(w.output_calc) AS output_calc
   FROM log_apache AS w
   JOIN servers    AS z ON z.id = w.server.id           -- server_id / server_name table
   WHERE w.date BETWEEN '2014-04-01' AND '2014-04-05'   -- for example only
     AND w.server_id IN (1,2,3,4,5)                     -- for example only
   GROUP BY w.server.id, w.date
   ORDER BY w.server.id, w.date;

Some suggestions about efficiency:

  1. Replace server char(30) NOT NULL with server_id INT NOT NULL. Grouping by INT way more faster than by CHAR() as far as no collation conversions needed.

  2. Create separate index (date, server_id) for GROUP BY.

  3. Precalculate count*input on insertion to spread the load.

Related Question