MySQL query question, I can not get it

Question

Hope someone can help me. I have a table that logs our import jobs. I need a query that will create a matrix with table names on the vertical axis, import dates on the horizontal axis and the total number of records imported for this table on this date in the matrix cell. I don't care if I need to create a temporary table, but all this needs to be done in MySQL.

The following is a simplified example of an event log table. Not only does this have many more feeds, but we import many more tables. Therefore, the solution should consider querying table names. You will notice that data can be imported into the table more than once a day, as in records 5 and 6.

id table_name import_date num_recs ----+-----------+--------------------+------- 0 customer 2010-06-20 00:00:00 10 1 order 2010-06-20 00:00:00 15 2 customer 2010-06-21 00:00:00 5 3 order 2010-06-21 00:00:00 6 4 customer 2010-06-22 00:00:00 1 5 order 2010-06-22 00:00:00 6 6 order 2010-06-22 00:00:00 1 

We are looking for a result something like this. It doesn't have to be for sure.

 table_name 06-20 06-21 06-22 ------------+-----+-----+------ customer | 10 | 5 | 1 order | 15 | 6 | 7 

Answer 1

How about form output:

 table_name date imports ------------+-------+-------- customer | 06-20 | 10 customer | 06-21 | 5 order | 06-20 | 15 order | 06-21 | 6 

This way you can do it with a simple GROUP BY :

 SELECT table_name, DATE(import_date) AS date, SUM(*) AS imports FROM yourTable GROUP BY table_name, date; 

Otherwise, your request will be very unpleasant.

Answer 2

MySQL cannot execute summary queries, but you can do this in two queries, using the result of the first query as SQL for the following query:

 SELECT 'SELECT table_name' UNION SELECT CONCAT(', SUM(IF(import_date = "',import_date,'", num_recs,0)) AS "',DATE_FORMAT(import_date, "%m-%d"),'"') FROM event_log GROUP BY import_date UNION SELECT 'FROM event_log GROUP BY table_name' 

Then run the output of this query to get the final results, for example. for your example you will get:

 SELECT table_name , SUM(IF(import_date = "2010-06-20", num_recs,0)) AS "06-20" , SUM(IF(import_date = "2010-06-21", num_recs,0)) AS "06-21" , SUM(IF(import_date = "2010-06-22", num_recs,0)) AS "06-22" FROM event_log GROUP BY table_name 

You can either write a stored procedure to concatenate, prepare and execute the results of the first query, or if it is all run from a shell script, you can commit the results of the first query, then return the results to mysql.

Answer 3

I think Ben C is on the right track. I wanted to offer what I could here if it helps anyone who knows. Source source

Here is a method to take two arbitrary dates and divide them into time blocks, and then performs some function of aggregation by other data in each block. In your case, this block should probably be one day, the start date will probably be 30 days before the current day, and the end date will probably be the current day. Each block can be returned with some aggregate metric of interest. In your case, it will most likely be SUM('imports')

SELECT t1.table_name AS table_name, t1.imports AS imports FROM (SELECT SUM(`imports`) AS imports, CEIL( (UNIX_TIMESTAMP('<now>') - UNIX_TIMESTAMP(`import_date`))/ (<one day in ?seconds, i think?>) ) AS RANGE FROM `<your table>` WHERE `import_date` BETWEEN '<now minus 30 days>' AND '<now>' GROUP BY RANGE ORDER BY RANGE DESC) AS t1;

This may not help at all, but if so, then it’s good. It is easily modified to return the start day for each range as a date column. To be clear, this does the same thing that Ben S offers, but it will work if all of your dates are not 00:00:00, while this will cause its GROUP BY to fail in the date column

To see how the opposite will look, see Ben S's answer and mentally delete the date column. However, as I said, this column can easily be added back to this query. FWIW, I used this method for tables with more than 4 million rows and still works in <1 second, which was enough for my purposes.

Hamy