mysql - 扩展以根据数据范围选择

Question

我有以下 MySQL 查询:

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/user.php?id=1&user=',
              u.id,
              '&mode=alllogs">',
              u.firstname ,' ',
              u.lastname,'</a>') AS Username, 
       count(*) AS logins ,
       (SELECT count(*) 
        FROM mdl_log 
        WHERE userid = l.userid 
        GROUP BY userid) AS Activity 
FROM mdl_log AS l 
JOIN mdl_user AS u ON l.userid = u.id 
WHERE action LIKE '%login%' 
GROUP BY userid 
ORDER BY Activity DESC 
INTO OUTFILE '/tmp/Total_Logins_With_Total_Activity.txt';

我想扩展它，以便我可以根据数据范围(例如 365 天)得出结果。

所以我想我可以更改上面的查询以从 mdl_logs 表中选择“时间”字段并将其转换为天数(它是一个 unix 时间戳)，然后仅在时间在最后 365 天期间内选择.任何帮助表示赞赏。

mdl_log 表具有以下结构:

+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| time   | int(10) unsigned | NO   | MUL | 0       |                |
| userid | int(10) unsigned | NO   | MUL | 0       |                |
| ip     | varchar(15)      | NO   |     |         |                |
| course | int(10) unsigned | NO   | MUL | 0       |                |
| module | varchar(20)      | NO   |     |         |                |
| cmid   | int(10) unsigned | NO   | MUL | 0       |                |
| action | varchar(40)      | NO   | MUL |         |                |
| url    | varchar(100)     | NO   |     |         |                |
| info   | varchar(255)     | NO   |     |         |                |
+--------+------------------+------+-----+---------+----------------+

mdl_user 表如下所示:

+---------------+---------------------+------+-----+---------+----------------+
| Field         | Type                | Null | Key | Default | Extra          |
+---------------+---------------------+------+-----+---------+----------------+
| id            | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| auth          | varchar(20)         | NO   | MUL | manual  |                |
| confirmed     | tinyint(1)          | NO   | MUL | 0       |                |
| policyagreed  | tinyint(1)          | NO   |     | 0       |                |
| deleted       | tinyint(1)          | NO   | MUL | 0       |                |
| mnethostid    | bigint(10)          | NO   | MUL | 0       |                |
| username      | varchar(100)        | NO   |     |         |                |
| password      | varchar(32)         | NO   |     |         |                |
| idnumber      | varchar(255)        | NO   | MUL |         |                |
| firstname     | varchar(100)        | NO   | MUL |         |                |
| lastname      | varchar(100)        | NO   | MUL |         |                |
| email         | varchar(100)        | NO   | MUL |         |                |
| emailstop     | tinyint(1) unsigned | NO   |     | 0       |                |
| icq           | varchar(15)         | NO   |     |         |                |
| skype         | varchar(50)         | NO   |     |         |                |
| yahoo         | varchar(50)         | NO   |     |         |                |
| aim           | varchar(50)         | NO   |     |         |                |
| msn           | varchar(50)         | NO   |     |         |                |
| phone1        | varchar(20)         | NO   |     |         |                |
| phone2        | varchar(20)         | NO   |     |         |                |
| institution   | varchar(40)         | NO   |     |         |                |
| department    | varchar(30)         | NO   |     |         |                |
| address       | varchar(70)         | NO   |     |         |                |
| city          | varchar(20)         | NO   | MUL |         |                |
| country       | varchar(2)          | NO   | MUL |         |                |
| lang          | varchar(30)         | NO   |     | en_utf8 |                |
| theme         | varchar(50)         | NO   |     |         |                |
| timezone      | varchar(100)        | NO   |     | 99      |                |
| firstaccess   | int(10) unsigned    | NO   |     | 0       |                |
| lastaccess    | int(10) unsigned    | NO   | MUL | 0       |                |
| lastlogin     | int(10) unsigned    | NO   |     | 0       |                |
| currentlogin  | int(10) unsigned    | NO   |     | 0       |                |
| lastip        | varchar(15)         | NO   |     |         |                |
| secret        | varchar(15)         | NO   |     |         |                |
| picture       | tinyint(1)          | NO   |     | 0       |                |
| url           | varchar(255)        | NO   |     |         |                |
| description   | text                | YES  |     | NULL    |                |
| mailformat    | tinyint(1) unsigned | NO   |     | 1       |                |
| maildigest    | tinyint(1) unsigned | NO   |     | 0       |                |
| maildisplay   | tinyint(2) unsigned | NO   |     | 2       |                |
| htmleditor    | tinyint(1) unsigned | NO   |     | 1       |                |
| ajax          | tinyint(1) unsigned | NO   |     | 1       |                |
| autosubscribe | tinyint(1) unsigned | NO   |     | 1       |                |
| trackforums   | tinyint(1) unsigned | NO   |     | 0       |                |
| timemodified  | int(10) unsigned    | NO   |     | 0       |                |
| trustbitmask  | int(10) unsigned    | NO   |     | 0       |                |
| imagealt      | varchar(255)        | YES  |     | NULL    |                |
| screenreader  | tinyint(1)          | NO   |     | 0       |                |
+---------------+---------------------+------+-----+---------+----------------+

Answer 1

Mark 的回答很好，并为您提供了过去 365 天的信息，在间隔上添加上限是微不足道的，但是查询效率低于预期 - 从 mdl_log 读取两次:

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/user.php?id=1&user=',
          u.id,
          '&mode=alllogs">',
          u.firstname ,' ',
          u.lastname,'</a>') AS Username,
   SUM(IF(l.action LIKE '%login%', 1, 0)) AS logins,
   COUNT(*) AS Activity 
FROM mdl_log AS l 
  JOIN mdl_user AS u ON l.userid = u.id 
WHERE l.time BETWEEN 
  UNIX_TIMESTAMP(ADDDATE(now(),INTERVAL -200 day)
    AND UNIX_TIMESTAMP(ADDDATE(now(),INTERVAL -100 day)
GROUP BY userid
HAVING  SUM(IF(l.action LIKE '%login%', 1, 0))>0 
ORDER BY Activity DESC
INTO OUTFILE '/tmp/Total_Logins_With_Total_Activity.txt';