我想用下面的Mysql表回答以下问题.
-
在过go 的几天里,用户在avg上活跃了多少天
-
我想按天显示用户平均值,
0表示用户未处于活动状态,1表示用户处于活动状态.
我还是SQL方面的新手,以下是我迄今为止try 过的.我不太确定我的答案.
SELECT u.username, COUNT(u.snapshot_date) as 'active_days', a.action
FROM actions a
JOIN users u
ON a.user_id = u.user_id
WHERE NOT (a.action = 0)
GROUP BY u.username;
我的输出
username active_days action
Albert 2 1
Paul 4 1
Ronaldo 2 1
Messi 1 1
用户表
user_id username snapshot_date
1 Albert 2022-01-10
2 Paul 2022-01-10
3 Blessing 2022-01-10
4 Ronaldo 2022-01-22
5 Messi 2022-01-01
动作表
action_id action snapshot_date user_id
1 0 2022-01-10 1
2 1 2022-01-10 2
3 0 2022-01-10 3
4 1 2022-01-22 4
5 1 2022-01-01 5
6 0 2022-01-10 2
7 0 2022-01-10 1
8 0 2022-01-10 3
9 0 2022-01-22 2
10 0 2022-01-01 4
11 0 2022-01-10 2
12 1 2022-01-10 1
13 0 2022-01-10 3
14 1 2022-01-22 2
15 1 2022-01-01 4
16 1 2022-01-10 2
17 1 2022-01-10 2
18 0 2022-01-10 1
19 1 2022-01-22 1
20 0 2022-01-01 5