oracle 按两个字段分组，并在第一个字段上使用 count()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18735595/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Group by two fields, and having count() on first field
提问by u1813888
I have a table that stored users play list, a video can be viewed by multiple users for multiple times. A records goes like this:
我有一个存储用户播放列表的表，一个视频可以被多个用户多次查看。A记录是这样的：
videoid, userid, time
123, abc , 2013-09-11
It means user(abc) has watched video(123) on 2013-09-11
表示用户（abc）在 2013-09-11 观看了视频（123）
Now I want to find distinct users watched video list (no duplication), and only show the users that have watched more than two videos.
现在我想找到不同的用户观看的视频列表（没有重复），并且只显示观看了两个以上视频的用户。
SELECT videoid, userid
FROM table_play_list
WHERE SOME CONDICTION
GROUP BY userid, videoid
The sql only select distinct users watchlist, I also want to filter users that have watched more than two different videos.
sql 只选择不同的用户观看列表，我还想过滤观看了两个以上不同视频的用户。
I know I have to google and read the documentation first, some said 'HAVING' could solve this, unfortunately, I could not make it.
我知道我必须先谷歌并阅读文档，有人说“HAVING”可以解决这个问题，不幸的是，我无法做到。
回答by Gordon Linoff
If I understand correctly, you are looking for users who watched more than two differentvideos. You can do this by using count(distinct)with a partition byclause:
如果我理解正确，您正在寻找观看两个以上不同视频的用户。您可以通过使用count(distinct)withpartition by子句来做到这一点：
select userid, videoid
from (SELECT userid, videoid, count(distinct videoid) over (partition by userid) as cnt
FROM table_play_list
WHERE <ANY CONDITION>
) t
where cnt > 2;
回答by Dba
Try like this,
试试这样，
SELECT userid, count(*)
FROM table_play_list
WHERE SOME CONDICTION
GROUP BY user_id
having count(*) >2;
Try this if you need to get the count based on userid and videoid(users who watch the same video more than two times).
如果您需要根据用户 ID 和视频 ID（观看同一视频两次以上的用户）获取计数，请尝试此操作。
SELECT userid, videoid, count(*)
FROM table_play_list
WHERE SOME CONDICTION
GROUP BY user_id, video_id
having count(*) >2;
回答by Vincent Malgrat
This is probably best handled with analytics. Without analytics you will probably need a self-join.
这可能最好通过分析来处理。如果没有分析，您可能需要自加入。
SQL> WITH table_play_list AS (
2 SELECT 123 videoid, 'a' userid FROM dual UNION ALL
3 SELECT 125 videoid, 'a' userid FROM dual UNION ALL
4 SELECT 123 videoid, 'b' userid FROM dual UNION ALL
5 SELECT 123 videoid, 'b' userid FROM dual UNION ALL
6 SELECT 123 videoid, 'c' userid FROM dual
7 )
8 SELECT videoid, userid,
9 COUNT(*) over(PARTITION BY userid) nb_video
10 FROM table_play_list;
VIDEOID USERID NB_VIDEO
---------- ------ ----------
123 a 2
125 a 2
123 b 2
123 b 2
123 c 1
This lists all user/video and the total number of videos watched by each user. As you can see user bhas watched the same video twice, I don't know if it's possible in your system.
这列出了所有用户/视频以及每个用户观看的视频总数。正如您所看到的，用户b已经观看了两次相同的视频，我不知道您的系统是否可行。
You can filter with a subquery:
您可以使用子查询进行过滤：
SQL> WITH table_play_list AS (
2 SELECT 123 videoid, 'a' userid FROM dual UNION ALL
3 SELECT 125 videoid, 'a' userid FROM dual UNION ALL
4 SELECT 123 videoid, 'b' userid FROM dual UNION ALL
5 SELECT 123 videoid, 'b' userid FROM dual UNION ALL
6 SELECT 123 videoid, 'c' userid FROM dual
7 )
8 SELECT *
9 FROM (SELECT videoid, userid,
10 COUNT(*) over(PARTITION BY userid) nb_video
11 FROM table_play_list)
12 WHERE nb_video > 1;
VIDEOID USERID NB_VIDEO
---------- ------ ----------
123 a 2
125 a 2
123 b 2
123 b 2
回答by DB_learner
The below will give users who have watched more than two different videos.
下面将提供观看了两个以上不同视频的用户。
SELECT userid, count(distinct video_id)
FROM table_play_list
WHERE SOME CONDICTION
GROUP BY user_id
having count(distinct video_id) >2;
回答by ethemsulan
- If you use Oracle PL/SQL you can use like this:
- 如果您使用 Oracle PL/SQL，您可以像这样使用：
SELECT column1, column2 FROM ( SELECT column1, column2, COUNT(column1) OVER (PARTITION BY column1) AS cnt FROM test GROUP BY column1, column2 ORDER BY column1 ) WHERE cnt > 2
SELECT column1, column2 FROM ( SELECT column1, column2, COUNT(column1) OVER (PARTITION BY column1) AS cnt FROM test GROUP BY column1, column2 ORDER BY column1 ) WHERE cnt > 2
- If you use standard SQL you can use like this:
- 如果您使用标准 SQL，您可以像这样使用：
SELECT column1, column2 FROM test WHERE column1 IN ( SELECT column1 FROM ( SELECT column1, column2 FROM test GROUP BY column1, column2 ORDER BY column1 ) GROUP BY column1 HAVING COUNT(column1) > 2 ) GROUP BY column1, column2 ORDER BY column1
SELECT column1, column2 FROM test WHERE column1 IN ( SELECT column1 FROM ( SELECT column1, column2 FROM test GROUP BY column1, column2 ORDER BY column1 ) GROUP BY column1 HAVING COUNT(column1) > 2 ) GROUP BY column1, column2 ORDER BY column1
