mysql 通过GROUP BY和JOINS获取MAX值的数据行

gwo2fgha 于 2024-01-05 发布在 Mysql

关注(0)|答案(2)|浏览(306)

我正在使用ml100k数据集编写一个查询，可以为我获取每个年龄段最受欢迎的电影。
下面是我的表是如何定义的。

用户

第一个月

收视率

userid | movieid | rating | ts个
到目前为止我所做的
1.由于没有显式定义计数，所以我编写了一个查询来查找它。

SELECT age, movieid, COUNT(*) AS mcount
   FROM ratings
   JOIN users ON id = userid
   GROUP BY age, movieid

字符串
这给了我每个年龄段每部电影的计数。

型
1.找出每个年龄段的最大值

SELECT age, MAX(mcount) AS mc
    FROM (
        SELECT age, movieid, COUNT(*) AS mcount
        FROM ratings
        JOIN users ON id = userid
        GROUP BY age, movieid
    ) t1
    GROUP BY age

的字符串
这给了我年龄和最大计数。然而，我还想要相应的电影ID，这是我一直卡住的地方。我的想法是将这些结果与第一个表连接起来，但它不起作用。有没有其他的替代方法可以尝试？这是我使用的查询。

SELECT users.age, ratings.movieid, count(*) as mc2
FROM ratings JOIN users ON id = userid 
INNER JOIN
(
    SELECT age, MAX(mcount) AS mc
    FROM (
        SELECT age, movieid, COUNT(*) AS mcount
        FROM ratings
        JOIN users ON id = userid
        GROUP BY age, movieid
    ) t1
    GROUP BY age
)t2
ON t2.age = users.age
WHERE mc2=t2.mc
GROUP BY users.age, ratings.movieid;

型

mysql

来源：https://stackoverflow.com/questions/77626029/get-row-of-data-for-max-value-over-group-by-and-joins

2条答案

按热度按时间

vh0rcniy1#

你可以这样做：

SELECT t.age, t.movieid, t.mcount
FROM (
   SELECT age, movieid, COUNT(*) AS mcount
   FROM ratings
   JOIN users ON id = userid
   GROUP BY age, movieid
) t
LEFT JOIN (
   SELECT age, movieid, COUNT(*) AS mcount
   FROM ratings
   JOIN users ON id = userid
   GROUP BY age, movieid
) t2
ON t.age = t2.age AND
   t.movieid <> t2.movieid AND
   t.mcount < t2.mcount
WHERE t2.age IS NULL

字符串
说明：

第一次子选择
连接ratings和users
onusers的id
按age和movieid分组
来找到
选择您需要的字段
生成所有组
第二次子选择
与第一实施例相同
我们LEFT JOIN两组，
匹配age
不同的movieid（因此我们将进行合理的计数比较）
并且第一mcount小于第二mcount
因为我们想找到第一组的物品
没有更高计数的匹配
所以我们搜索匹配的次数
在WHERE子句中，我们排除了在第二组中找到的匹配计数高于第一组的情况

小提琴：http：//sqlfiddle.com/#！9/f8 defe/6

展开查看全部

赞(0）回复(0）举报 2024-01-05

p1iqtdky2#

8.2.0 - vnk

WITH cte AS (
        SELECT age, 
               movieid, 
               COUNT(*) AS mcount, 
               RANK() OVER (PARTITION BY age, ORDER BY COUNT(*) DESC) rnk
        FROM ratings
        JOIN users ON id = userid
        GROUP BY 1, 2
        )
SELECT age, 
       GROUP_CONCAT(movieid) movie_ids, 
       mcount
FROM cte
WHERE rnk = 1
GROUP BY 1, 3

字符串
多部电影可能具有相同的最高评级。因此，将向外部查询添加聚合。如果您希望在许多行中（每行一部电影）而不是在一个输出行中接收这些重复内容，请删除外部聚合。
如果您只需要一部电影，那么您必须定义额外的排序标准（在窗口定义中展开ORDER BY），以便从所有可能的电影中仅选择一部电影。
或者，如果你需要在任何不确定/随机的，然后简单地删除外部聚合，并使用ROW_NUMBER（）而不是RANK（）。

展开查看全部

赞(0）回复(0）举报 2024-01-05

我来回答

mysql 通过GROUP BY和JOINS获取MAX值的数据行

2条答案

相关问题

热门标签

最新问答