我有两个表要处理,USERS, METRICS_DAILY
mysql> description USERS;
+---------------------------+---------------------+------+-----+------------------------------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+---------------------+------+-----+------------------------------------------+----------------+
| USER_ID | int(11) unsigned | NO | PRI | NULL | auto_increment |
| CREATED | timestamp | NO | | CURRENT_TIMESTAMP|
+---------------------------+---------------------+------+-----+------------------------------------------+----------------+
mysql> description METRICS_DAILY;
+---------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| USER_ID | int(11) unsigned | NO | PRI | NULL | |
| EVENT | tinyint(3) unsigned | NO | PRI | NULL | |
| DATE | date | NO | PRI | NULL | |
| COUNT | smallint(5) unsigned | NO | | 0 | |
+---------+----------------------+------+-----+---------+-------+
我需要选择count distinct USER_ID where where DATE=DATE(NOW()) and EVENT in (x,y,z) and USERS。创建的是month1, month2, month3,…monthCURRENT .
我几乎可以完全在METRICS_DAILY表中完成,因为我有对应于USERS的DATE。是间接创造的。
Created是一个完整时间戳。我需要按月创建的群组来划分。
开头:
SELECT count(distinct METRICS_DAILY. user_id),CREATED,MONTH(CREATED),DATE FROM METRICS_DAILY。USER_ID = USERS.USER_ID) where EVENT> 10 and DATE=DATE(NOW()) group by MONTH(CREATED);
+---------------------------------------+---------------------+----------------+------------+
| count(distinct METRICS_DAILY.USER_ID) | CREATED | MONTH(CREATED) | DATE |
+---------------------------------------+---------------------+----------------+------------+
| 3 | 2013-01-14 09:35:16 | 1 | 2013-01-18 |
| 16 | 2012-12-15 07:07:49 | 12 | 2013-01-18 |
+---------------------------------------+---------------------+----------------+------------+
像这样?
SELECT MONTHNAME(u.CREATED), YEAR(u.CREATED), COUNT(*) AS UserCount
FROM METRICS_DAILY md
JOIN USERS u
ON u.USER_ID = md.USER_ID
WHERE md.DATE = NOW() AND md.EVENT in (x,y,z)
GROUP BY MONTHNAME(u.CREATED), YEAR(u.CREATED)
SELECT DATE(`METRICS_DAILY`.`DATE`) AS `Day of DATE`, `METRICS_DAILY`.`EVENT` AS `EVENT`, COUNT(DISTINCT `METRICS_DAILY`.`USER_ID`) AS `Number of unique User Id`
FROM METRICS_DAILY
JOIN USERS ON `METRICS_DAILY`.`USER_ID` = `USERS`.`USER_ID`
WHERE (`METRICS_DAILY`.`EVENT` >= "31") AND (`METRICS_DAILY`.`DATE` BETWEEN "2012-12-01" AND "2012-12-31") AND (`USERS`.`CREATED` BETWEEN CAST("2012-12-01" AS DATETIME) AND CAST(CONCAT("2012-12-31", " 23:59:59") AS DATETIME))
GROUP BY DATE(`METRICS_DAILY`.`DATE`), `METRICS_DAILY`.`EVENT`
ORDER BY `Day of DATE` ASC, `EVENT` ASC;