在MySql中按日期查找新客户和回头客 [英] Find new and repeat customers datewise in MySql

问题描述

我有一个包含user_id和Visited_at列的访问表.我想查找本月的每一天中有多少新客户并回头客.

I have a visit table with user_id and visited_at columns. I would like to find how many are new and repeat customer on each day in this month.

user_id      visited_at                

--------------------------------------------------------------------------

1750        2015-01-28 12:38:59
1920        2015-01-19 17:20:20
1009        2015-01-17 18:20:12
1920        2015-01-17 15:10:10
1080        2015-01-13 20:18:41
1920        2014-04-04 10:31:15
1750        2013-10-04 10:39:20

2015年1月，用户1750和1920访问同一地方，因此重复用户总数为2.2015年4月，用户1750、1920和1080访问同一地方，因此重复顾客总数为3.输出应该是这样的

In January 2015, user 1750 and 1920 visited the same place so total repeated customers are 2. In April 2015, user 1750, 1920 and 1080 visited the same place so total repeated customers are 3. The output should be something like this

October 2013

Month               New        Repeat
----------------------------------------------
2013-10-04          1          0

April 2014

Month               New        Repeat
----------------------------------------------
2014-04-04          1          0

January 2015

Month               New        Repeat
----------------------------------------------
2015-01-13          1          0
2015-01-17          1          1
2015-01-19          0          1
2015-01-28          0          1

推荐答案

Chopra. . .实际上，我确实误解了您在评论中提出的问题.这与按月的结构非常相似，只是汇总按日期进行:

Chopra . . . I actually did misunderstand the question you posed in the comment. This is very similar to the structure by month, just the aggregation is by date instead:

select date(v.visited_at),
       count(case when v.visited_at = vv.minva then user_id end) as num_new_users,
       (count(distinct user_id) - count(case when v.visited_at = vv.minva then user_id end)
       ) as num_repeat_users
from visits v join
     (select user_id, min(visited_at) as minva
      from visits t
      group by user_id
     ) vv
     on v.user_id = vv.user_id
group by date(v.visited_at)
order by date(v.visited_at);

这篇关于在MySql中按日期查找新客户和回头客的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！