0

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
2

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.

  • I have some 50+ million rows, doing that would be pretty wasteful. – Mark D May 30 '14 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.