mysql查询计算特定日期范围内的用户保留率

tyu7yeag 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(294)

我正在尝试选择保留在某个日期范围内的用户数，并且使用此查询和下表成功地进行了选择：

+----------+-------------+
| Field    | Type        |
+----------+-------------+
| id       | varchar(17) |
| log_date | date        |
+----------+-------------+

SELECT last_day.log_date, COUNT(distinct last_day.id) as users_num
FROM (SELECT DISTINCT log_date, id
      FROM `userActivity`) this_day
JOIN (SELECT DISTINCT log_date, id
      FROM `userActivity`) last_day
ON this_day.id = last_day.id
AND this_day.log_date = "2018-10-01"
AND last_day.log_date BETWEEN "2018-10-01" AND "2018-10-30"
GROUP BY log_date;

但我面临的问题是，我希望假设日期范围内的每一天都是第0天(与以下示例类似）：

请注意，图片中的第一行是我需要计算的以下结果的平均值。有人知道如何增强我的查询以得到如图所示的结果吗？

mysql Join retention

来源：https://stackoverflow.com/questions/53875544/mysql-query-calculate-user-retention-in-a-certain-date-range

1条答案

按热度按时间

9wbgstp71#

此解决方案仅适用于mysql 8.x，因为它需要CTE（公共表表达式）：

with digits as (
  select 0 as n union select 1 union select 2 union select 3 union select 4
  union select 5 union select 6 union select 7 union select 8 union select 9
),
series as (
select d1.n * 100 + d2.n * 10 + d3.n as n -- number series from 0 to 999
from digits d1
cross join digits d2
cross join digits d3
)
SELECT last_day.log_date, COUNT(distinct last_day.id) as users_num,
       date_add("2018-10-01", interval s.n day) as current_start
FROM (SELECT DISTINCT log_date, id
      FROM `userActivity`) this_day
JOIN (SELECT DISTINCT log_date, id
      FROM `userActivity`) last_day
ON this_day.id = last_day.id
cross join series s
WHERE s.n <= 30
  AND this_day.log_date = date_add("2018-10-01", interval s.n day)
  AND last_day.log_date BETWEEN date_add("2018-10-01", interval s.n day) 
                            AND date_add("2018-10-30", interval s.n day)
GROUP BY log_date, date_add("2018-10-01", interval s.n day);

赞(0）回复(0）举报 2021-06-15

我来回答

mysql查询计算特定日期范围内的用户保留率

1条答案

相关问题

热门标签

最新问答