获取每月访问全部4周的用户数 [英] Get the count of Users who has visited all the 4 weeks in a Month

问题描述

我们有一个用于保存用户日志的系统.我们现在无法更改数据的处理方式，只能使用SQL来读取数据.

输入

  CREATE TABLE user_visits(`user_id` VARCHAR(16)，"created_at"日期);插入到user_visits(`user_id`，`created_at`)价值/*应该返回用户1 */('1'，'2021-04-02')，("1"，"2021-04-08")，("1"，"2021-04-15")，("1"，"2021-04-21")，("1"，"2021-04-21")，("1"，"2021-04-22")，("1"，"2021-04-25")，/*应该返回用户2 *这样做会导致问题*/('2'，'2021-04-01')，('2'，'2021-04-07')，('2'，'2021-04-14')，/*应该返回用户2 */('2'，'2021-05-01')，('2'，'2021-05-07')，('2'，'2021-05-14')，('2'，'2021-05-20')，('2'，'2021-05-20')，('2'，'2021-05-21')，('2'，'2021-05-24')，/*用户3在4月仅访问4天，不返回*/('3'，'2021-04-07')，('3'，'2021-04-14')，("3"，"2021-04-20")，('3'，'2021-04-24')，/*用户4仅在4月访问2周，不返回*/('4'，'2021-04-01')，('4'，'2021-04-02')，('4'，'2021-04-03')，('4'，'2021-04-04')，('4'，'2021-04-05')，('4'，'2021-04-06')，('4'，'2021-04-07')，('4'，'2021-04-08')，('4'，'2021-04-09'); 

这是DBFiddle.

当我使用weekly_visits.user_id = v.user_id加入时，它仅考虑第二行，而不考虑第一行.

解决方案

  SELECT user_id，DATE_FORMAT(created_at，'％Y-％m')年和月，拜访了COUNT(DISTINCT周(created_at))来自user_visitsGROUP BY user_id，year_and_month到访周数>3 

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle= 3073e99eebd04cdb6f63fadad35f0dfc

根据需要为 WEEK()函数调整 mode 参数.

We've a system where we're saving the User Logs. We cannot change how the data is strcutred right now, we've only the access to read data using SQL.

Input

CREATE TABLE user_visits (
  `user_id` VARCHAR(16),
  `created_at` date
);

INSERT INTO user_visits
  (`user_id`, `created_at`)
VALUES
  /* user 1 should be returned */
  ('1', '2021-04-02'),
  ('1', '2021-04-08'),
  ('1', '2021-04-15'),
  ('1', '2021-04-21'),
  ('1', '2021-04-21'),
  ('1', '2021-04-22'),
  ('1', '2021-04-25'),

  /* user 2 should be returned * doing this will cause an issue */ 
  ('2', '2021-04-01'),
  ('2', '2021-04-07'),
  ('2', '2021-04-14'),

  /* user 2 should be returned */
  ('2', '2021-05-01'),
  ('2', '2021-05-07'),
  ('2', '2021-05-14'),
  ('2', '2021-05-20'),
  ('2', '2021-05-20'),
  ('2', '2021-05-21'),
  ('2', '2021-05-24'),

  /* user 3 only visits 4 days in April, don't return */
  ('3', '2021-04-07'),
  ('3', '2021-04-14'),
  ('3', '2021-04-20'),
  ('3', '2021-04-24'),

  /* user 4 only visits 2 weeks in April, don't return */
  ('4', '2021-04-01'),
  ('4', '2021-04-02'),
  ('4', '2021-04-03'),
  ('4', '2021-04-04'),
  ('4', '2021-04-05'),
  ('4', '2021-04-06'),
  ('4', '2021-04-07'),
  ('4', '2021-04-08'),
  ('4', '2021-04-09');

This is the DBFiddle. https://www.db-fiddle.com/f/g6tDEmwFRJPC2ALJzauWAm/0

Expected Output

num_eligible_users  month
2                   April
1                   May

Current Output
num_eligible_users  month
1                   April
1                   May     

issue // User 2 gets omitted because when Joining using 'JOIN weekly_visits ON weekly_visits.user_id = v.user_id' it is also including the 2nd set of User Data and is not able to Join properly.see pic:

When I join using weekly_visits.user_id = v.user_id it is only considering the 2nd row and not the first third one.

解决方案

SELECT user_id, 
       DATE_FORMAT(created_at, '%Y-%m') year_and_month,
       COUNT(DISTINCT WEEK(created_at)) weeks_visited
FROM user_visits
GROUP BY user_id, year_and_month
HAVING weeks_visited > 3

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3073e99eebd04cdb6f63fadad35f0dfc

Adjust mode parameter for WEEK() function if needed.

这篇关于获取每月访问全部4周的用户数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！