logo标签列表

在MySQL中使用登录时间戳计算活跃用户数

mysqlsql
5

在准备面试时，我遇到了一道SQL问题，希望能够更好地回答它。

给出时间戳和用户ID，如何确定每天活跃的用户数量（一周内）？

这个问题非常简单，但就是这个问题摆在我的面前。

表结构，示例输入和期望输出 - amdixon
在周开始和周结束之间，按user_id和date分组，HAVING COUNT(DISTINCT something) = 7；或者排除至少有一个缺失日期的数据。 - Strawberry
@Strawberry 你会在某个东西里放什么？我认为这是最难的部分，因为每个日期都有许多时间戳相对应，写下每个7天并不高效。 - user3362840
是的，那是正确的 - 但你只对日期部分感兴趣。 - Strawberry
@Strawberry 抱歉，我修改了之前的评论。 - user3362840
6个回答
7

我将根据自己的理解和回答问题的方式来演示这个想法，假设数据集如下，我们将命名表为logins:

+---------+---------------------+
| user_id |   login_timestamp   |
+---------+---------------------+
|       1 | 2015-09-29 14:05:05 |
|       2 | 2015-09-29 14:05:08 |
|       1 | 2015-09-29 14:05:12 |
|       4 | 2015-09-22 14:05:18 |
|   ...   |          ...        |
+---------+---------------------+

可能还有其他列，但我们不关心这些。
首先，我们应该确定该周的边界，为此我们可以使用ADDDATE()。结合今天的日期-今天的星期几（MySQL的DAYOFWEEK()），即可得到星期日的日期。
例如：如果今天是10号的星期三，Wed - 3 = Sun，因此10 - 3 = 7，我们可以预计星期日是第7天。
我们可以通过以下方式获取WeekStart和WeekEnd时间戳：
SELECT
DATE_FORMAT(ADDDATE(CURDATE(), INTERVAL 1-DAYOFWEEK(CURDATE()) DAY), "%Y-%m-%d 00:00:00") WeekStart, 
DATE_FORMAT(ADDDATE(CURDATE(), INTERVAL 7-DAYOFWEEK(CURDATE()) DAY), "%Y-%m-%d 23:59:59") WeekEnd;

注意：在PostgreSQL中有一个DATE_TRUNC()函数，它返回指定时间单位的开头，给定日期，例如周开始，月份，小时等。 但是MySQL中没有这个功能。
接下来，让我们利用WeekStart和weekEnd来切割数据集，本例中我将只展示如何使用硬编码日期进行过滤：
SELECT *
FROM `logins`
WHERE login_timestamp BETWEEN '2015-09-29 14:05:07' AND '2015-09-29 14:05:13'

这应该返回我们的数据集切片，只包含相关结果：
+---------+---------------------+
| user_id |   login_timestamp   |
+---------+---------------------+
|       2 | 2015-09-29 14:05:08 |
|       1 | 2015-09-29 14:05:12 |
+---------+---------------------+

我们可以将结果集减少到仅包含user_id，并过滤掉重复项。然后进行计数，方法如下：
SELECT COUNT(DISTINCT user_id)
FROM `logins`
WHERE login_timestamp BETWEEN '2015-09-29 14:05:07' AND '2015-09-29 14:05:13'

DISTINCT将过滤掉重复的内容，而count函数将只返回数量。

综合起来，可以这样写：

SELECT COUNT(DISTINCT user_id)
FROM `logins`
WHERE login_timestamp 
    BETWEEN DATE_FORMAT(ADDDATE(CURDATE(), INTERVAL 1- DAYOFWEEK(CURDATE()) DAY), "%Y-%m-%d 00:00:00") 
        AND DATE_FORMAT(ADDDATE(CURDATE(), INTERVAL 7- DAYOFWEEK(CURDATE()) DAY), "%Y-%m-%d 23:59:59")

将CURDATE()替换为任何时间戳，以获取该周用户的登录次数。
但是你想要将其分解为每天，没问题！以下是方法：
首先，让我们将过于详细的时间戳转换为仅日期数据。我们添加DISTINCT，因为我们不介意同一用户在同一天登录两次。我们计算用户数量，而不是登录次数，对吗？（请注意，我们在此处后退）：
SELECT DISTINCT user_id, DATE_FORMAT(login_timestamp, "%Y-%m-%d")
FROM `logins`

这将产生：
+---------+-----------------+
| user_id | login_timestamp |
+---------+-----------------+
|       1 | 2015-09-29      |
|       2 | 2015-09-29      |
|       4 | 2015-09-22      |
|   ...   |        ...      |
+---------+-----------------+

我们将用第二个查询来包装这个查询，以便计算每个日期的出现次数：

SELECT `login_timestamp`, count(*) AS 'count'
FROM (SELECT DISTINCT user_id, DATE_FORMAT(login_timestamp, "%Y-%m-%d") AS `login_timestamp` FROM `logins`) `loginsMod`
GROUP BY `login_timestamp`

我们使用count和分组，以按日期获取列表，返回以下内容：
+-----------------+-------+
| login_timestamp | count |
+-----------------+-------+
| 2015-09-29      | 1     +
| 2015-09-22      | 2     +
+-----------------+-------+

经过所有的辛勤工作，最终结合在一起:

SELECT `login_timestamp`, COUNT(*)
FROM (
SELECT DISTINCT user_id, DATE_FORMAT(login_timestamp, "%Y-%m-%d") AS `login_timestamp`
FROM `logins`
WHERE login_timestamp BETWEEN DATE_FORMAT(ADDDATE(CURDATE(), INTERVAL 1- DAYOFWEEK(CURDATE()) DAY), "%Y-%m-%d 00:00:00") AND DATE_FORMAT(ADDDATE(CURDATE(), INTERVAL 7- DAYOFWEEK(CURDATE()) DAY), "%Y-%m-%d 23:59:59")) `loginsMod`
GROUP BY `login_timestamp`;

本周每天登录次数的详细统计将由我提供。同样，您可以替换CURDATE()以获取不同的一周。

至于登录的用户本身，让我们按不同顺序结合相同的内容：

SELECT `user_id`
FROM (
    SELECT `user_id`, COUNT(*) AS `login_count`
    FROM (
        SELECT DISTINCT `user_id`, DATE_FORMAT(`login_timestamp`, "%Y-%m-%d")
        FROM `logins`) `logins`
    GROUP BY `user_id`) `logincounts`
WHERE `login_count` > 6

我有两个内部查询，第一个是logins：

SELECT DISTINCT `user_id`, DATE_FORMAT(`login_timestamp`, "%Y-%m-%d")
FROM `logins`

将提供用户列表以及他们登录的日期，无重复。

然后我们有logincounts:

SELECT `user_id`, COUNT(*) AS `login_count`
FROM `logins` -- See previous subquery.
GROUP BY `user_id`) `logincounts`

将返回相同的列表，并计算每个用户登录次数。
最后： SELECT user_id FROM logincounts -- 参见前面的子查询。 WHERE login_count > 6
过滤掉那些没有登录7次的用户，并删除日期列。
这有点长，但我认为它充满了想法，而且我认为它肯定会在工作面试中以有趣的方式帮助回答问题。 :)
23:59:59并不是一天的结束。时间戳可以具有更精细的亚秒精度。请停止使用23:59:59，因为它将在某个时候导致错误。由于这个原因，我建议您避免使用between，参考Bad habits to kick : mis-handling date / range queries。 - Paul Maxwell
5
create table fbuser(id integer, date date); 
insert into fbuser(id,date)values(1,'2012-01-01'); 
insert into fbuser(id,date)values(1,'2012-01-02'); 
insert into fbuser(id,date)values(1,'2012-01-01'); 
insert into fbuser(id,date)values(1,'2012-01-01'); 
insert into fbuser(id,date)values(1,'2012-01-01'); 
insert into fbuser(id,date)values(1,'2012-01-01'); 
insert into fbuser(id,date)values(1,'2012-01-02'); 
insert into fbuser(id,date)values(1,'2012-01-03'); 
insert into fbuser(id,date)values(1,'2012-01-04'); 
insert into fbuser(id,date)values(1,'2012-01-05'); 
insert into fbuser(id,date)values(1,'2012-01-06'); 
insert into fbuser(id,date)values(1,'2012-01-07');
insert into fbuser(id,date)values(4,'2012-01-08');
insert into fbuser(id,date)values(4,'2012-01-08');
insert into fbuser(id,date)values(1,'2012-01-08');
insert into fbuser(id,date)values(1,'2012-01-09');
select * from fbuser;
id |    date    
----+------------
 1 | 2012-01-01
 1 | 2012-01-02
 1 | 2012-01-01
 1 | 2012-01-01
 1 | 2012-01-01
 1 | 2012-01-01
 1 | 2012-01-02
 1 | 2012-01-03
 1 | 2012-01-04
 1 | 2012-01-05
 1 | 2012-01-06
 1 | 2012-01-07
 2 | 2012-01-07
 3 | 2012-01-07
 4 | 2012-01-07
 4 | 2012-01-08
 4 | 2012-01-08
 1 | 2012-01-08
 1 | 2012-01-09

select id,count(DISTINCT date) from fbuser 
where date BETWEEN '2012-01-01' and '2012-01-07' 
group by id having count(DISTINCT date)=7

 id | count 
----+-------
  1 |     7
(1 row)

查询用户在给定时期内登录的唯一日期计数，并返回发生7次的ID。如果时间充足，您还可以使用date_format。

我喜欢这个答案。一个观察是OP的问题陈述中有时间戳而不是日期，因此对于任何其他查看此答案的人，只需将日期替换为DATE（login_timestamp），并给COUNT一个别名，这样您就不必在HAVING子句中再次编写它。 - Nic Scozzaro
0

我在 Teradata 中尝试了这个操作，以下是 SQL 语句。首先，获取每个用户在某个日期的唯一标识，然后检查该用户是否存在 7 天。

SELECT src.USER_ID
  ,COUNT(*) CNT
FROM (SELECT USER_ID
      ,CAST(LOGIN_TIMESTAMP AS DATE FORMAT 'YYYY-MM-DD') AS LOGIN_DT
  FROM src_table
  WHERE LOGIN_TIMESTAMP BETWEEN '2017-11-12 00:00:00' AND '2017-11-18 23:59:59'
  GROUP BY 1,2
  )src GROUP BY 1 HAVING CNT = 7;
INSERT INTO src_table VALUES (1,'2017-11-12 10:10:10');
INSERT INTO src_table VALUES (1,'2017-11-13 10:10:10');
INSERT INTO src_table VALUES (1,'2017-11-13 11:10:10');
INSERT INTO src_table VALUES (1,'2017-11-13 12:10:10');
INSERT INTO src_table VALUES (1,'2017-11-14 10:10:10');
INSERT INTO src_table VALUES (1,'2017-11-15 10:10:10');
INSERT INTO src_table VALUES (1,'2017-11-16 10:10:10');
INSERT INTO src_table VALUES (1,'2017-11-17 10:10:10');
INSERT INTO src_table VALUES (1,'2017-11-18 10:10:10');

INSERT INTO src_table VALUES (2,'2017-11-12 01:10:10');
INSERT INTO src_table VALUES (2,'2017-11-13 13:10:10');
INSERT INTO src_table VALUES (2,'2017-11-14 14:10:10');
INSERT INTO src_table VALUES (2,'2017-11-15 12:10:10');


INSERT INTO src_table VALUES (5,'2017-11-12 01:10:10');
INSERT INTO src_table VALUES (5,'2017-11-13 02:10:10');
INSERT INTO src_table VALUES (5,'2017-11-14 03:10:10');
INSERT INTO src_table VALUES (5,'2017-11-15 04:10:10');
INSERT INTO src_table VALUES (5,'2017-11-16 05:10:10');
INSERT INTO src_table VALUES (5,'2017-11-17 06:10:10');

INSERT INTO src_table VALUES (8,'2017-11-12 04:10:10');
INSERT INTO src_table VALUES (8,'2017-11-13 05:10:10');
INSERT INTO src_table VALUES (8,'2017-11-14 06:10:10');
INSERT INTO src_table VALUES (8,'2017-11-15 01:10:10');
INSERT INTO src_table VALUES (8,'2017-11-16 02:10:10');
INSERT INTO src_table VALUES (8,'2017-11-17 03:10:10');
INSERT INTO src_table VALUES (8,'2017-11-18 03:10:10');
0

给定数据： userid 和 timestamp；如何计算每周中每天的“活跃用户”数量？

问题在于可能一周内没有任何登录，或某些天没有登录，因此解决这种要求的基本方法是您必须有一系列日期来与登录进行比较。

有许多方法可以生成一周的日期，选择的方法取决于两个主要因素：

  1. 我需要这些（或类似）结果的频率有多高？
  2. 我正在使用的平台。（例如，使用Postgres很容易“生成系列”，但MySQL不提供此功能，而最近MariaDB引入了系列表以帮助解决此类需求。因此，了解您的平台功能将影响您如何解决此问题。）

如果我需要经常这样做（我认为是真的），那么我会创建一个“日历表”，每天一行，适合一个相当长的时间段（比如10年），即仅约3652行，其主键为日期列。在这个表中，我们还可以使用week()函数存储“周数”，使逐周报告变得更简单（我们也可以在此表中添加其他列）。

因此，假设我已经建立了包含每个日期和周数的日历表，那么我们可以从今天的日期中取出周数，减去1，并收集所需的登录数据，如下：

select
      c.caldate, count(distinct l.userid) as user_logins
from calendar_table as c
left join login_table l on l.timestamp >= c.caldate and l.timestamp < date_add(c.caldate,INTERVAL 1 DAY)
where c.week_number = WEEK(curdate())-1
group by c.caldate

我是怎么创建日历表的？

如先前所说，有许多方法可供选择，对于MySQL来说，在这里提供了一些选项：如何用一段日期范围填充一个表格？

0

这对我有效

select a.user_id, count(a.user_id) as active_time_in_days
from
(
select user_id, login_time, lead(login_time) over (partition by user_id order by login_time asc )  as next_day
from dev.login_info
group by 1,2
order by user_id, login_time asc
)a where a.login_time + interval '1 day' = next_day
group by 1;
你的回答可以通过添加更多支持信息来改进。请编辑以添加进一步详细信息，例如引文或文档，以便他人可以确认您的回答是否正确。您可以在帮助中心找到有关编写良好答案的更多信息。 - Community
-1

这个怎么样？我试过了，它可以工作。

select yearweek(ts) as yearwk, user_id,
 count(user_id) as counts 
 from log
 group by 1,2
 having count(user_id) =7;
1
正确的查询语句： SELECT user_id, yearweek(ts) as yearwk FROM login GROUP by 1,2 HAVING count(distinct DATE(ts))=7 按周分组，并确保您每天都登录一次。 您当前的查询只是查看您是否登录了7次，这可能全部是同一天。 - zipp
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接