在准备面试时,我遇到了一个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()
(的想法,是星期日的日期。
例如:如果今天是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
将过滤掉重复项,计数将只返回数量。
综合起来,这变成:
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`
我们使用计数和分组来按日期获取列表,返回:
+-----------------+-------+
| 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`
将返回相同的列表,并计算每个用户拥有的登录次数。
最后: 选择user_id
FROM logincounts
-- 请参阅上一个子查询。 其中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。
给定数据为:userid
和 timestamp
;如何计算一周中每天的"活跃用户"数量?
当然,问题在于可能根本没有登录,或者在一周中的某些日子没有登录,因此此类要求的基本解决方案是您必须有一系列日期来比较登录
。生成一周日期的方法多种多样,选择的方法取决于 2 个主要因素:
- 我多久需要一次这些(或类似(结果?
- 我正在使用的平台。(例如,使用Postgres"生成序列"非常容易,但MySQL不提供这样的功能,而最近MariaDB引入了系列表来帮助解决此类需求。因此,了解平台的功能将影响您如何解决此问题。
如果我需要定期执行此操作(我认为这是真的(,那么我会创建一个"日历表",每天一行,在合理的广泛时间段(例如 10 年(内,只有大约 3652 行,其主键作为日期列。在此表中,我们还可以使用 week()
函数存储"week_number",这使得每周报告更简单(我们也可以在此表中添加其他列(。
因此,假设我已经构建了包含每个日期和周数的日历表,那么我们可以从今天的日期中获取周数,减去 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,这里有可用的选项:如何使用一系列日期填充表?
我在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');
我有用
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;