mysql - 平均而言，用户在首次购买之前来过多少次？

Question

id(pk)      user_id(int)    came_to_site(date_time)     purchases(int)
1           1               27-8-2016:10:12:23              0
2           2               27-8-2016:10:20:23              0
3           1               28-8-2016:10:12:23              1
4           3               29-8-2016:10:12:23              0
5           4               29-8-2016:11:40:23              0
6           4               30-8-2016:10:12:23              0
7           4               30-8-2016:12:12:23              1
8           1               30-8-2016:12:30:23              1

我有这张表，我想知道用户在第一次购买之前平均来了多少次。

• 我们可以忽略用户 2 和 3，因为他们从未进行过购买。
• 用户 1 在购买前来过 2 次。
• 用户 4 在购买前来过 3 次。

所以平均值为 (2 + 3)/2 = 2.5

知道如何编写这样的查询吗？

Answer 1

select avg(cnt)
from
(
    select user_id, 1 + count(*) as cnt
    from tablename t1
    where purchases = 0
      and exists (select 1 from tablename t2
                  where t2.user_id = t1.user_id
                    and t2.purchases = 1)
      and not exists (select 1 from tablename t3
                      where t3.user_id = t1.user_id
                        and t3.purchases = 1
                        and t3.came_to_site < t1.came_to_site)
    group by user_id
)

子查询对已进行购买的每个 user_id 进行计数 (EXISTS)，但不在当前行之前进行计数 (NOT EXISTS)。

在主级别，执行 AVG() 来获取平均数。

也许，根据 dbms，您需要执行 avg(cnt * 1.0) 以避免整数结果。