如何编辑我的查询以获得更准确的用户留存结果？

Question

我的目标是找出在过去7天内进入我的网站的访客中，有多少人今天也访问了。也就是说，如果有人在05-02访问了我的网站并且今天也访问了，那么它会在05-02计数。

我的查询问题在于，如果有人在05-01和05-02访问了我的网站并且今天也访问了，那么它只会在05-01计数。但我希望查询结果也能在05-02计数。

如何编辑下面的查询以实现此目的？

SELECT COUNT( DISTINCT v.`hash` ) hashCount, DATE( v.`timestamp` ) AS views
FROM audience v
INNER JOIN behaviour b ON v.`hash` = b.`hash` 
WHERE v.timestamp
BETWEEN SUBDATE( CURDATE( ) , 7 ) 
AND CURDATE( ) 
AND DATE( b.timestamp ) = CURDATE( ) 
GROUP BY views

- villoui

---

在http://mysqlfiddle.com上设置一些示例数据会很有帮助。 - miken32

不知道表格包含什么内容，很难修复您的查询，因此请添加表格描述、一些示例数据（只需使用1、2、3等作为“哈希”，并为两个表格添加一些时间戳），以及预期结果。如果我要猜测：audience=首次访问列表，behaviour=这些用户的页面访问？那么您的group by views将采用注册/首次访问的日期（因此不会区分访问日期）-您可能只需要在distinct中添加b.timestamp即可。但请添加缺失的信息，这样我们就不必猜测了。 - Solarflare

你好，您对我的回答满意吗？我非常愿意听取您的反馈。 - iLikeMySql

Answer 1

为了让事情变得更简单，只需将今天访问过的所有人都找出来，然后查找过去7天内的访问记录，如果找到一个，则返回最近的访问日期。我认为这种方法更容易理解。

select b.`hash` ,  
       audience.last_visit 
from behaviour b
 inner join (select v.`hash`, max(v.timestamp) as last_visit from audience v
  where DATE(v.timestamp) between date_sub(current_date, interval 7 day) and 
  date_sub(current_date, interval 1 day)  
  group by v.`hash`) as audience 
 on(b.`hash` = audience.`hash`)
where DATE(b.timestamp) = CURDATE();

我们加入的选择已经包含了我们需要的信息（最近7天内每个哈希的最新访问记录）。

返回的行数是今天和过去一周访问您页面的访客数量。

您还可以从您的选择中计算得出您要查询的数量作为查询结果。

select count(*) from    
(select b.`hash` ,  
       audience.last_visit 
from behaviour b
 inner join (select v.`hash`, max(v.timestamp) as last_visit from audience v
  where DATE(v.timestamp) between date_sub(current_date, interval 7 day) and 
  date_sub(current_date, interval 1 day)  
  group by v.`hash`) as audience 
 on(b.`hash` = audience.`hash`)
where DATE(b.timestamp) = CURDATE() ) as my_visitors;

- 测试数据

    drop table if exists your_schema.behaviour;
    create table your_schema.behaviour(`hash` varchar(255), `timestamp` timestamp) ;

    insert into your_schema.behaviour
    values ('ab','2016-05-23'),('ac','2016-05-23');

    drop table if exists your_schema.audience;
    create table your_schema.audience (`hash` varchar(255), `timestamp` timestamp) ;
insert into your_schema.audience
values ('ab','2016-05-01'),('ab','2016-05-02'),('ab','2016-05-03'),('ab','2016-05-04'),('ab','2016-05-21'),('ab','2016-05-23'),
       ('ac','2016-05-01'),('ac','2016-05-02'),('ac','2016-05-03'),('ac','2016-05-04'),('ac','2016-05-21'),('ac','2016-05-23'),
       ('ad','2016-05-01'), ('ad','2016-05-02'), ('ad','2016-05-03'),('ad','2016-05-04'),('ad','2016-05-21'),('ad','2016-05-23');

- iLikeMySql

Answer 2

您也可以使用DATE_SUB来实现，可以查看以下查询语句

SELECT COUNT(DISTINCT v.`hash`) AS hashCount, DATE(v.`timestamp`) AS views
FROM audience v
INNER JOIN behaviour b ON v.`hash` = b.`hash` 
WHERE views >= DATE_SUB(Now(),INTERVAL 7 DAY)
GROUP BY views

获取更多信息，请访问以下网址

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

- chetan varma

Answer 3

你可以简单地使用DATE - INTERVAL ? DAY函数。请查看下面的示例：

SELECT COUNT(DISTINCT v.`hash`) AS hashCount, DATE(v.`timestamp`) AS views
FROM audience v
INNER JOIN behaviour b ON v.`hash` = b.`hash` 
WHERE views >= DATE(NOW()) - INTERVAL 7 DAY
GROUP BY views

请点击此处获取更多日期和时间函数。

- rhavendc