在准备面试时,我遇到了一道SQL问题,希望能够更好地回答它。
给出时间戳和用户ID,如何确定每天活跃的用户数量(一周内)?
这个问题非常简单,但就是这个问题摆在我的面前。
在准备面试时,我遇到了一道SQL问题,希望能够更好地回答它。
给出时间戳和用户ID,如何确定每天活跃的用户数量(一周内)?
这个问题非常简单,但就是这个问题摆在我的面前。
我将根据自己的理解和回答问题的方式来演示这个想法,假设数据集如下,我们将命名表为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()
),即可得到星期日的日期。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;
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次的用户,并删除日期列。
这有点长,但我认为它充满了想法,而且我认为它肯定会在工作面试中以有趣的方式帮助回答问题。 :)
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。
我在 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');
给定数据: userid
和 timestamp
;如何计算每周中每天的“活跃用户”数量?
问题在于可能一周内没有任何登录,或某些天没有登录,因此解决这种要求的基本方法是您必须有一系列日期来与登录进行比较。
有许多方法可以生成一周的日期,选择的方法取决于两个主要因素:
如果我需要经常这样做(我认为是真的),那么我会创建一个“日历表”,每天一行,适合一个相当长的时间段(比如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来说,在这里提供了一些选项:如何用一段日期范围填充一个表格?
这对我有效
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;
这个怎么样?我试过了,它可以工作。
select yearweek(ts) as yearwk, user_id,
count(user_id) as counts
from log
group by 1,2
having count(user_id) =7;
SELECT user_id, yearweek(ts) as yearwk FROM login GROUP by 1,2 HAVING count(distinct DATE(ts))=7
按周分组,并确保您每天都登录一次。
您当前的查询只是查看您是否登录了7次,这可能全部是同一天。 - zipp