我正在尝试选择保留在某个日期范围内的用户数,并且使用此查询和下表成功地进行了选择:
+----------+-------------+
| 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天(与以下示例类似):
请注意,图片中的第一行是我需要计算的以下结果的平均值。有人知道如何增强我的查询以得到如图所示的结果吗?
1条答案
按热度按时间9wbgstp71#
此解决方案仅适用于mysql 8.x,因为它需要CTE(公共表表达式):