在Ott平台上保留用户

Question

我有一张叫user_info的桌子

它有两列：

User_id
Date

如何到达下面提到的表格：

----------------------------------------------------------------
Date        |total_number_of_users | D2    |D5  |  D7 |   D14|
--------------------------------------------------------------
2020-07-01      1000                 700    500   200    150
2020-07-02      400                  300    250   200    100

例如，考虑我试图实现的表中的第一行：

total_number_of_users = Total number of users who have visited the site on 2020-07- 01

2020-07-01访问网站总用户数，2020-07-2访问总用户数D2=Out

D7=Out of total users who visited the site on 2020-07-01, visited on 2020-07-7

我已经尝试了以下方法，如何获得确切的解决方案：

SELECT user_id, week(login_date) AS login_week
    FROM user_info
    GROUP BY user_id,week(login_date);
SELECT user_id, min(week(login_date)) AS first_week
       FROM user_info
       GROUP BY user_id;
select a.user_id,a.login_week,b.first_week as first_week  from   
              (SELECT
                user_id,
                week(login_date) AS login_week
                FROM user_info
                GROUP BY user_id,week(login_date)) a,
              (SELECT
                user_id,
                min(week(login_date)) AS first_week
                FROM user_info
                GROUP BY user_id) b
        where a.user_id=b.user_id;

Answer 1

这看起来很痛苦，但您可以使用自连接和聚合：

select t.date,
       sum( t2.date = t.date) as total_number_of_users,
       sum( t2.date = t.date + interval 1 day ) as d2,
       sum( t2.date = t.date + interval 4 day ) as d5,
       sum( t2.date = t.date + interval 6 day ) as d7,
       sum( t2.date = t.date + interval 13 day ) as d14
from (select distinct date, user_id
      from t
     ) t1 left join
     (select distinct date, user_id
      from t
     ) t2
     on t1.user_id = t2.user_id and
        t2.date in (t1.date, t1.date + interval 1 day, t1.date + interval 4 day, t1.day + interval 6 day, t1.day + interval 13 day)
group by t.date;