mysql - 按用户聚合时间戳月份

Question

我在 MySQL 表中有许多行，每行都有一个用户名(字符串)和日期(日期时间)列。我试图按月和年聚合行 - 所以我想要的输出是这样的:

      Jan-17  Feb-17 ... Dec-18
User1      5       7          2
User2      3      10          6

我之前汇总日期(包括用户名之前)的方式是使用求和，如下所示:

SUM(CASE WHEN YEAR(DATE_ADD(createdon, INTERVAL 6 month))="2017" AND MONTH(createdon)="7" THEN 1 ELSE 0 END) "Jul-17"

(date_add 是从 7 月开始的财政年度开始的)- 但是这只会返回一个值，所以当我在查询结束时尝试 GROUP BY username 时出错。

如果能帮助我实现预期的输出，我将不胜感激!

Answer 1

使用 FROM_UNIXTIME 函数有一个更简单的方法:

我的架构 SQL 源:

CREATE TABLE `myTable` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `timestamp` varchar(255),
  `userid` mediumint default NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;

INSERT INTO `myTable` (`timestamp`,`userid`) VALUES ("1485732487",4),("1485801735",5),("1485133400",5),("1485147453",5),("1485811775",5),("1485579678",2),("1485657366",5),("1485457462",4),("1485868162",4),("1485708628",1);
INSERT INTO `myTable` (`timestamp`,`userid`) VALUES ("1485433560",2),("1485499798",2),("1485799205",3),("1485781057",3),("1485623782",4),("1485332601",4),("1485352246",3),("1485391179",3),("1485088297",2),("1485715532",2);
INSERT INTO `myTable` (`timestamp`,`userid`) VALUES ("1485679973",5),("1485880759",2),("1485919188",4),("1485310769",1),("1485334857",5),("1485874185",3),("1485737013",4),("1485626859",4),("1485711272",5),("1485615066",5);
INSERT INTO `myTable` (`timestamp`,`userid`) VALUES ("1485431634",2),("1485395131",4),("1485865892",3),("1485608615",2),("1485814718",5),("1485246275",3),("1485684463",2),("1485817191",1),("1485598599",1),("1485780025",2);
INSERT INTO `myTable` (`timestamp`,`userid`) VALUES ("1485335277",1),("1485889093",2),("1485877980",4),("1485491321",2),("1485589418",1),("1485178400",1),("1485093496",5),("1485551672",2),("1485137077",2),("1485418586",3);
INSERT INTO `myTable` (`timestamp`,`userid`) VALUES ("1485875911",2),("1485859088",5),("1485177508",3),("1485636084",4),("1485181669",5),("1485382618",5),("1485871330",4),("1485312836",3),("1485855890",1),("1485272206",2);
INSERT INTO `myTable` (`timestamp`,`userid`) VALUES ("1485595984",5),("1485696218",1),("1485292388",3),("1485442982",4),("1485680209",4),("1485591351",1),("1485409914",5),("1485124892",5),("1485836867",3),("1485607163",1);
INSERT INTO `myTable` (`timestamp`,`userid`) VALUES ("1485852764",4),("1485815496",3),("1485254943",1),("1485216648",4),("1485389964",2),("1485876777",5),("1485265749",1),("1485789682",5),("1485078698",2),("1485917921",5);
INSERT INTO `myTable` (`timestamp`,`userid`) VALUES ("1485621171",5),("1485083221",3),("1485473782",2),("1485315997",1),("1485745357",1),("1485266659",3),("1485605084",1),("1485148324",5),("1485783207",5),("1485246009",3);
INSERT INTO `myTable` (`timestamp`,`userid`) VALUES ("1485718788",1),("1485355391",5),("1485660212",3),("1485131000",3),("1485153001",2),("1485115212",4),("1485426966",3),("1485155245",2),("1485719262",3),("1485662864",1);

我的聚合查询:

SELECT
  DATE_FORMAT(FROM_UNIXTIME(`timestamp`), '%b-%Y') AS 'month_year',
  userid,
  SUM(userid) AS 'sum'
FROM 
  myTable
GROUP BY
  DATE_FORMAT(FROM_UNIXTIME(`timestamp`), '%b-%Y'), userid
ORDER BY
  month_year, userid

结果:

month_year  userid  sum
Feb-2017    4   4
Feb-2017    5   5
Jan-2017    1   18
Jan-2017    2   42
Jan-2017    3   60
Jan-2017    4   64
Jan-2017    5   115

SQL fiddle :http://sqlfiddle.com/#!9/ca60cd/1