用于群组分析的 MySQL JOIN

Question

我有两个表需要使用，USERS、METRICS_DAILY

mysql> 描述用户；

+---------------------------+---------------------+------+-----+------------------------------------------+----------------+
    | Field                     | Type                | Null | Key | Default                                  | Extra          |
    +---------------------------+---------------------+------+-----+------------------------------------------+----------------+
    | USER_ID                   | int(11) unsigned    | NO   | PRI | NULL                                     | auto_increment |
    | CREATED                   | timestamp           | NO   |     | CURRENT_TIMESTAMP|
+---------------------------+---------------------+------+-----+------------------------------------------+----------------+

mysql> 描述 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       |       |
+---------+----------------------+------+-----+---------+-------+

我需要选择计数不同的 USER_ID，其中 DATE=DATE(NOW()) 和 (x,y,z) 中的事件和 USERS.CREATED 是 1 月、2 月、3 月、... CURRENT。

我几乎可以完全在 METRICS_DAILY 表中完成此操作，因为我的 DATE 对应于 USERS.CREATED，只是间接的。

创建是一个完整的时间戳。我需要按每月创建的时间将其分割为群组。

这是一个开始:

SELECT count(distinct METRICS_DAILY.USER_ID),CREATED,MONTH(CREATED),DATE FROM METRICS_DAILY LEFT JOIN USERS ON (METRICS_DAILY.USER_ID = USERS.USER_ID)，其中 EVENT > 10 且 DATE=DATE(NOW()) 分组依据月(创建)；

+---------------------------------------+---------------------+----------------+------------+
| 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 |
+---------------------------------------+---------------------+----------------+------------+

一组 2 行(0.00 秒)

Answer 1

类似这样的吗？

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)