php - MySQL 从特定周/月的最后 2 天选择条目

Question

我有 2 列，'create_time' 是帐户注册的时间，'last_play' 是帐户最后一次登录的时间。我想选择在特定周/月注册并在该特定周/月的最后 2 天内活跃的帐户。

这是我在不考虑 last_play(作品)的情况下选择上周所有条目的方法:

SELECT COUNT(id) FROM account.account 
WHERE WEEKOFYEAR(create_time) = WEEKOFYEAR(NOW()) - 1 
AND YEAR(create_time) = YEAR(NOW());

这是我上周的当前查询，但无效:

SELECT COUNT(id) FROM account.account 
WHERE WEEKOFYEAR(create_time) = WEEKOFYEAR(NOW()) - 1 
AND YEAR(create_time) = YEAR(NOW()) 
AND DATE(last_play) BETWEEN 
    ADDDATE(DATE(DATE_SUB(NOW(), INTERVAL 1 WEEK)), 
    INTERVAL 1 - DAYOFWEEK(DATE(NOW())) DAY) 
    AND DATE(NOW());

Answer 1

根据您的第一个工作查询，您可以使用 MySQL 函数 WEEKDAY 来识别周六和周日:

SELECT COUNT(id) FROM account.account 
WHERE WEEKOFYEAR(create_time) = WEEKOFYEAR(NOW()) - 1 
AND YEAR(create_time) = YEAR(NOW())
AND WEEKOFYEAR(last_play) = WEEKOFYEAR(create_time)  //last_play is in the same week as create_time
AND WEEKDAY(last_play) IN (5,6);  //wekkday is saturday or sunday

这会为您提供在他们注册的同一周的周六或周日活跃的条目。

编辑: 几个月来，您基本上做同样的事情，但将 WEEKOFYEAR 替换为 MONTH 和 WEEKDAY通过 DAYOFMONTH。您可以通过检查所有可能的情况手动查找给定月份的最后两天:

SELECT COUNT(id) FROM account.account 
WHERE MONTH(create_time) = MONTH(NOW()) - 1 
AND YEAR(create_time) = YEAR(NOW())
AND MONTH(last_play) = MONTH(create_time)  //last_play is in the same MONTH as create_time
AND 
   (DAYOFMONTH(last_play) IN (30,31) AND MONTH(last_play) IN (1,3,5,7,8,10,12)
    OR DAYOFMONTH(last_play) IN (29,30) AND MONTH(last_play) IN (4,6,9,11)
    OR DAYOFMONTH(last_play) IN (27,28) AND MONTH(last_play) IN (2))

别介意闰年 ;-)。或者自己手动重新合并。