gpt4 book ai didi

mysql - 每日总用户数

转载 作者:行者123 更新时间:2023-11-29 15:11:58 25 4
gpt4 key购买 nike

问题

我想显示特定范围内的所有用户,例如从 2020-01-01从 2020-01-05

我有一个表user,其中保存了所有用户。想象一下下表:

user

id | createdAt
1 2020-01-01
2 2020-01-02
3 2020-01-03
4 2020-01-03
5 2020-01-05

结果应该是(与上面的用户表相关):

        date | count

2020-01-01 1
2020-01-02 2
2020-01-03 4
2020-01-04 4
2020-01-05 5

到目前为止我所拥有的

我有一个查询,根据需要返回上面的示例:

SELECT COALESCE((SELECT COUNT(*) FROM user u WHERE DATE(u.createdAt) <= DATE(dates.gen_date) AND u.role = 'USER' GROUP BY dates.gen_date ),0) AS y, dates.gen_date AS x FROM user u, (select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
(select 0 t0 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) t0,
(select 0 t1 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) t1,
(select 0 t2 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) t2,
(select 0 t3 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) t3,
(select 0 t4 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) t4) v
where gen_date between DATE(?) and DATE(?)) AS dates GROUP BY x;

但是由于用户数量多、时间范围大,这条语句的执行速度确实很慢。

我也想到了物化 View ,但这只是最后的出路。

使用的数据库:MySql 5.7

最佳答案

测试

SELECT (dates.mindate + INTERVAL (t0.n+10*t1.n+100*t2.n+1000*t3.n) DAY) `date`,
COUNT(users.id) `count`
FROM users,
(SELECT ? mindate, ? maxdate) dates,
(select 0 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) t0,
(select 0 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) t1,
(select 0 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) t2,
(select 0 n union select 1 union select 2) t3
WHERE users.createdAt <= (dates.mindate + INTERVAL (t0.n+10*t1.n+100*t2.n+1000*t3.n) DAY)
GROUP BY `date`
HAVING `date` <= dates.maxdate
ORDER BY `date`

一定要便宜一点。

关于mysql - 每日总用户数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/60000172/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com