I have a SQL table user_game
which contains the games that a user owns:
我有一个SQL表user_game,其中包含用户拥有的游戏:
| id | user_id | game_id |
|----|---------|---------|
| 83 | 1 | 1 |
| 84 | 1 | 2 |
| 85 | 1 | 3 |
| 86 | 2 | 2 |
| 87 | 2 | 3 |
| 88 | 2 | 4 |
| 89 | 3 | 2 |
I am trying to count the number of users which have 1 game, 2 games, 3 games.. etc.
我试图计算拥有1场比赛,2场比赛,3场比赛的用户数量等。
User 1 has 3 games, User 2 has 3 games, and User 3 has 1 game. Therefore these are the results I want to achieve:
用户1有3个游戏,用户2有3个游戏,用户3有1个游戏。因此,这些是我想要实现的结果:
| no_of_games | COUNT(no_of_games) |
|-------------|--------------------|
| 1 | 1 |
| 2 | 0 |
| 3 | 2 |
COUNT(no_of_games)
is the number of users that have that number of games.
COUNT(no_of_games)是具有该游戏数量的用户数。
I can individually get the number of users for each no_of_games
with this query:
我可以使用此查询单独获取每个no_of_games的用户数:
-- Select no. of users with 1 game
SELECT no_of_games, COUNT(no_of_games)
FROM
(
-- Select no. of games each user has
SELECT user_id, COUNT(1) as no_of_games
FROM user_game
GROUP BY user_id
) as A
WHERE no_of_games = 1;
which gives the results:
给出了结果:
| no_of_games | COUNT(no_of_games) |
|-------------|--------------------|
| 1 | 1 |
However I have to change the no_of_games = 1
to 2, 3, 4... manually and UNION
them with this solution and I can't do it for ~60 cases.
但是我必须手动将no_of_games = 1更改为2,3,4 ...并使用此解决方案UNION它们,我不能做~60个案例。
Is there a simpler way to achieve this?
有没有更简单的方法来实现这一目标?
2 个解决方案
#1
3
Your problem is a bit tricky, because groups of games which do not appear in your data with a certain frequency (e.g. 2) will not appear in the result set just using your original table. In the query below, I use a second table called nums
which simply contains the sequence 1 through 10 representing counts of number of games. By using a LEFT JOIN
we can retain each game count in the final result set.
您的问题有点棘手,因为没有以特定频率出现在您的数据中的游戏组(例如2)将不会仅使用原始表格出现在结果集中。在下面的查询中,我使用了第二个名为nums的表,它只包含表示游戏数量的序列1到10。通过使用LEFT JOIN,我们可以在最终结果集中保留每个游戏计数。
SELECT t1.no_of_games,
COALESCE(t2.no_of_games_count, 0) AS no_of_games_count
FROM nums t1
LEFT JOIN
(
SELECT t.no_of_games, COUNT(*) AS no_of_games_count
FROM
(
SELECT COUNT(*) AS no_of_games
FROM user_game
GROUP BY user_id
) t
GROUP BY t.no_of_games
) t2
ON t1.no_of_games = t2.no_of_games
ORDER BY t1.no_of_games
And here is the definition I used for nums
:
以下是我用于nums的定义:
CREATE TABLE nums (`no_of_games` int);
INSERT INTO nums (`no_of_games`)
VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Demo here:
在这里演示:
SQLFiddle
#2
0
You can find count of games for each user and then find count of users for each count of games.
您可以找到每个用户的游戏数量,然后查找每个游戏数量的用户数量。
select cnt no_of_games, count(*) cnt_no_of_games
from(
select user_id, count(*) cnt
from your_table
group by user_id
) t group by cnt;
#1
3
Your problem is a bit tricky, because groups of games which do not appear in your data with a certain frequency (e.g. 2) will not appear in the result set just using your original table. In the query below, I use a second table called nums
which simply contains the sequence 1 through 10 representing counts of number of games. By using a LEFT JOIN
we can retain each game count in the final result set.
您的问题有点棘手,因为没有以特定频率出现在您的数据中的游戏组(例如2)将不会仅使用原始表格出现在结果集中。在下面的查询中,我使用了第二个名为nums的表,它只包含表示游戏数量的序列1到10。通过使用LEFT JOIN,我们可以在最终结果集中保留每个游戏计数。
SELECT t1.no_of_games,
COALESCE(t2.no_of_games_count, 0) AS no_of_games_count
FROM nums t1
LEFT JOIN
(
SELECT t.no_of_games, COUNT(*) AS no_of_games_count
FROM
(
SELECT COUNT(*) AS no_of_games
FROM user_game
GROUP BY user_id
) t
GROUP BY t.no_of_games
) t2
ON t1.no_of_games = t2.no_of_games
ORDER BY t1.no_of_games
And here is the definition I used for nums
:
以下是我用于nums的定义:
CREATE TABLE nums (`no_of_games` int);
INSERT INTO nums (`no_of_games`)
VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Demo here:
在这里演示:
SQLFiddle
#2
0
You can find count of games for each user and then find count of users for each count of games.
您可以找到每个用户的游戏数量,然后查找每个游戏数量的用户数量。
select cnt no_of_games, count(*) cnt_no_of_games
from(
select user_id, count(*) cnt
from your_table
group by user_id
) t group by cnt;