根据日期范围，我如何判断有多少用户一周有3-5天处于活跃状态？

Question

假设您有一个表，如下所示：

CREATE TABLE `checkins` (
  `id` bigint(20) NOT NULL default '0',
  `userid` bigint(20) default NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `ind_userid` (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

根据日期范围，我如何判断一周3-5天有多少用户是活跃的。

就像这样

input - two months date range
output - 310 users were active 3-5 days a week

Answer 1

如果您创建了一个每周一行的日程表，您应该能够使用如下查询解决您的问题：

SELECT userid 
FROM   (SELECT userid, 
               YEARWEEK(TIMESTAMP)                  AS year_week, 
               COUNT(DISTINCT DAYOFWEEK(TIMESTAMP)) AS check_in_days 
        FROM   checkins 
        WHERE  1 = 1 -- This would be your date range filter 
        GROUP  BY userid, 
                  YEARWEEK(TIMESTAMP) 
        HAVING check_in_days BETWEEN 3 AND 5) AS user_weeks 
GROUP  BY userid 
HAVING COUNT(year_week) = (SELECT COUNT(*) 
                           FROM   year_week 
                           WHERE  1 = 1 -- This would be your date range filter 
                          ); 

(我的week表在2001年到2020年之间的每个星期都有一行。)

内部查询(user_weeks)为每个{user_id，week}返回一行，其中用户在该特定周内至少签到3天或至多5天。(每天签到的Nr无关紧要)。外部查询为每个{user_id}返回一行，以及满足3-5天签入要求的周数。外部select中的having子句过滤结果，使其仅包括与日期范围内的实际周数一样多的签到次数(周)的用户。这应该照顾到“连续”周的要求。

如果这对你有帮助，请告诉我。

编辑从函数week()更改为yearweek()。

Answer 2

这在oracle中是存在的，但我认为在mysql中也可以很容易地做到。

SELECT  year_week AS year_week,
        COUNT (year_week) AS days
FROM   (  SELECT   TO_CHAR (timestamp, 'D') AS day_of_week,
                      TO_CHAR (timestamp, 'YYYY')
                   || '-'
                   || TO_CHAR (timestamp, 'WW')
                      AS year_week
            FROM   checkins
        GROUP BY      TO_CHAR (timestamp, 'YYYY')
                   || '-'
                   || TO_CHAR (timestamp, 'WW'),
                   TO_CHAR (timestamp, 'D')
        ORDER BY   year_week)
GROUP BY  year_week order by year_week;    

TO_CHAR(timestamp, 'WW') = WEEKOFYEAR

TO_CHAR(timestamp, 'D') = DAYOFWEEK

Answer 3

被认为是多查询问题，其中：

• result是从第一个查询派生的
• secondResult是从第二个查询派生的
• minDate是范围内的最小日期，给定的等同于WEEKOFYEAR(minDate)
• maxDate是范围内的最大日期，给定的等同于userid和timestamp列名称userid和timestamp将始终保留

解决方案应该是这样的：

SELECT DISTINCT userid, timestamp from checkins WHERE WEEKOFYEAR(timestamp) >= minDate and WEEKOFYEAR(timestamp) <= maxDate GROUP BY userid,DAYOFWEEK(timestamp);
SELECT userid, timestamp FROM result GROUP BY userid,WEEKOFYEAR(timestamp) HAVING COUNT(timestamp) >= 3 AND COUNT(timestamp) <= 5;
SELECT COUNT(*) FROM secondResult GROUP BY userid HAVING COUNT(timestamp) = (WEEKOFYEAR(maxDate) - WEEKOFYEAR(minDate));

显然，在日历环绕的情况下，请确保添加52。我已经验证了这个解析，并且相当有信心可以从这个解析中得出正确的解决方案。