合并MySQL中的列

Question

I'm trying to visualize patterns in my database year-over-year. My DB is MySQL, my server-side language is PHP and the data visualizer is Morris.js.

I've exported my data year by year and have been trying to merge this data using PHP, but the order of efficiency is terrible and unintuitive, so I'm looking to merge columns in MySQL rather than after the fact in PHP.

I'm pulling out the data so that within each year January 7 represents Jan 1 + Jan 2 + .. + Jan 7 and December 31 represents Jan 1 + .. + Mar 25 + .. + Dec 31. This is all within one single year, so that the column 2012 is cumulative only for 2012.

For 2014, the data looks like,

+--------+-------+
| date   | books |
+--------+-------+
| Jan-01 |    17 |
| Jan-02 |    40 |
| Jan-03 |    99 |
| Jan-04 |   164 |
| Jan-05 |   307 |
| Jan-06 |   527 |
| Jan-07 |   744 |
| Jan-08 |   866 |
| Jan-09 |   941 |
| Jan-10 |   990 |
| Jan-11 |  1016 |
| Jan-12 |  1030 |
| Jan-13 |  1082 |
+--------+-------+

Right now I'm pulling out the data like so,

<?php

// the actual code includes data for 2010-2014

$sql2014Cumulative = "SELECT DATE_FORMAT(b.`date_added`, '%b-%d') AS date, COUNT(*) AS books
"
    . "FROM (SELECT DISTINCT date(`date_added`) `date_added` FROM `books` WHERE YEAR(`date_added`) = 2014) b
"
    . "JOIN `books` b2 ON b.`date_added` >= date(b2.`date_added`)
"
    . "WHERE YEAR(b2.`date_added`) = 2014
"
    . "GROUP BY b.`date_added`
"
    . "ORDER BY b.`date_added` ASC";

$sql2013Cumulative = "SELECT DATE_FORMAT(b.`date_added`, '%b-%d') AS date, COUNT(*) AS books
"
    . "FROM (SELECT DISTINCT date(`date_added`) `date_added` FROM `books` WHERE YEAR(`date_added`) = 2013) b
"
    . "JOIN `books` b2 ON b.`date_added` >= date(b2.`date_added`)
"
    . "WHERE YEAR(b2.`date_added`) = 2013
"
    . "GROUP BY b.`date_added`
"
    . "ORDER BY b.`date_added` ASC";

$result2014Cumulative = $mysqli->query($sql2014Cumulative);
$result2013Cumulative = $mysqli->query($sql2013Cumulative);

while($row2014Cumulative = $result2014Cumulative->fetch_array(MYSQLI_ASSOC))
{
    $rows2014Cumulative[] = array('date' => $row2014Cumulative['date'], '2014' => $row2014Cumulative['books']);
}

while($row2013Cumulative = $result2013Cumulative->fetch_array(MYSQLI_ASSOC))
{
    $rows2013Cumulative[] = array('date' => $row2013Cumulative['date'], '2013' => $row2013Cumulative['books']);
}

$mergedDataCumulative = array_replace_recursive($rows2013Cumulative, $rows2014Cumulative);

?>

<script>var booksYearOverYearCumulative = <?php echo json_encode($mergedDataCumulative); ?>;</script>

...

On a schema that looks like this,

+---------------+------------------+------+-----+---------------------+-----------------------------+
| Field         | Type             | Null | Key | Default             | Extra                       |
+---------------+------------------+------+-----+---------------------+-----------------------------+
| id            | int(10) unsigned | NO   | PRI | NULL                | auto_increment              |
| user_id       | int(10) unsigned | NO   | MUL | NULL                |                             |
| course_code   | varchar(9)       | NO   |     | NULL                |                             |
| for_sale      | tinyint(1)       | NO   |     | NULL                |                             |
| date_added    | datetime         | NO   |     | NULL                |                             |
| date_removed  | datetime         | NO   |     | 0000-00-00 00:00:00 |                             |
| date_modified | timestamp        | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+---------------+------------------+------+-----+---------------------+-----------------------------+

Then I'm taking each set of data from each year and merging them into an array and exporting into JSON for Morris.js. This is the tedious and messy part.

What would be easier is if this was all done in MySQL and the data was exported in one table and was displayed as such,

+--------+-------+-------+-------+
| date   | 2014  | 2013  | 2012  |
+--------+-------+-------+-------+
| Jan-01 |    17 |    12 |     0 |
| Jan-02 |    40 |    40 |    12 |
| Jan-03 |    99 |   102 |    18 |
| Jan-04 |   164 |   136 |    27 |
| Jan-05 |   307 |   144 |    45 |
| Jan-06 |   527 |   504 |    48 |
| Jan-07 |   744 |   893 |   189 |
| Jan-08 |   866 |  1002 |   567 |
| Jan-09 |   941 |  1100 |   890 |
| Jan-10 |   990 |  1430 |  1054 |
| Jan-11 |  1016 |  1435 |  1278 |
| Jan-12 |  1030 |  1545 |  1575 |
| Jan-13 |  1082 |  1604 |  1897 |
+--------+-------+-------+-------+

So rather than merging all the data using PHP, does anyone know how I can group cumulative data by day within each year and export it in one table?

Answer 1

With simple case when

select 
  b.date 'date',
  sum(b.y2014) '2014', 
  sum(b.y2013) '2013', 
  sum(b.y2012) '2012'
from (
  select
   date_format(date_added, '%b-%d') as 'date',
   case year(date_added) when 2014 then books else 0 end as 'y2014',
   case year(date_added) when 2013 then books else 0 end as 'y2013',
   case year(date_added) when 2012 then books else 0 end as 'y2012'
  from books 
) b
group by b.date;

---

With joins

select
  date_format(y14.date_added, '%b-%d') as 'date', 
  y14.books as '2014',
  y13.books as '2013',
  y12.books as '2012'
from 
  books y14
  left join books y13
     on   year(y13.date_added) = 2013
     and  month(y13.date_added) = month(y14.date_added)
     and  day(y13.date_added)   = day(y14.date_added)
  left join books y12
     on   year(y12.date_added) = 2012
     and  month(y12.date_added) = month(y14.date_added)
     and  day(y12.date_added)   = day(y14.date_added)
where
  year(y14.date_added) = 2014
;

You could use many different ways, union all, etc...

SQLFIDDLE

SQLFIDDLE