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

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

Answer 1

To make it easier just take all people who visited totay and look for a visit in the past 7 days and if one is found return the last date of visit. i think this approach is easier to comprehend:

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();

• We join on a select that already consists of the information we need (latest visit per hash during the last 7 days).

The number of rows returned is the number of visitors who visited your page today and during the last week.

您还可以从您的选择中计算得到您要查找的数字作为查询结果:

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');