通过使用每组中的前N个元素进行SQL分组[英] SQL Group by using the First N elements in each group

本文是小编为大家收集整理的关于通过使用每组中的前N个元素进行SQL分组的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

假设我有下表:

+------------+---------+
|    MovieId |  rating  |
+------------+---------+
|      1     |     4   |
|      1     |     3   |
|      1     |     2   |
|      1     |     4   |
|      1     |     5   |
|      2     |     3   |
|      2     |     4   |
|      2     |     2   |
|      3     |     1   |
|      3     |     2   |
|      3     |     3   |
|      3     |     5   |
|      4     |     4   |
|      4     |     2   |
+------------+---------+

我想按组获得平均值,但使用每个组上的前两个元素.
示例:

+------------+---------+
|    MovieId |  rating  |
+------------+---------+
|      1     |     4   |
|      1     |     3   |
|      2     |     3   |
|      2     |     4   |
|      3     |     1   |
|      3     |     2   |
|      4     |     4   |
|      4     |     2   |
+------------+---------+

回答预期:

+------------+---------+
|    MovieId |  AVG    |
+------------+---------+
|      1     |     3.5 |
|      2     |     3.5 |
|      3     |     1.5 |
|      4     |     3   |
+------------+---------+

这是我必须获得所有电影的SQL查询.但是正如我所说,我只想为每个组使用前两个元素.

SELECT movieid, AVG(cast(rating as DECIMAL(10,2))) AS AVG FROM ratings group by movieid

如果您可以帮助我使SQL我感激.我还将使用linq,以防万一你知道.

推荐答案

在SQL DBMS中 - 如在关系模型中 - 没有"第一个".您的意思是每部电影或两个最高评分或其他东西的任意2行吗?

如果您无法定义订单,则查询是毫无意义的.

如果您可以定义订单,请按照我在我的规范示例中的其他推荐答案

用于mysql: -

select id, avg(rating) 
from (SELECT a.*, @num := @num + 1 rownum,
        (select count(*) 
         from movies m 
         where m.id<=a.id) last_count,

        (select count(*) 
         from movies m1 
         where a.id=m1.id) grp_count 
      from movies a, (SELECT @num := 0) d) f  
where grp_count-(last_count-rownum)<=2 
group by id;

您可以在Oracle中使用rownum函数. SQL Server中的功能.

其他推荐答案

这是SQL

中的解决方案
Create table #tempMovie (movieId int ,rating int)

INSERT INTO #tempMovie
Select  *  from table where movieidid=1 Limit 2
Union all
Select *  from table where movieidid=2 Limit 2
Union all
Select *  from table where movieidid=3 Limit 2
Union all
Select  *  from table where movieidid=4 Limit 2

临时表#tempmovie表将包含这样的数据

+------------+---------+
|    MovieId |  rating  |
+------------+---------+
|      1     |     4   |
|      1     |     3   |
|      2     |     3   |
|      2     |     4   |
|      3     |     1   |
|      3     |     2   |
|      4     |     4   |
|      4     |     2   |
+------------+---------+

然后通过

应用组
Select movieId, AVG(rating)
from #tempMovie
Group by movieId

Drop table #tempmovie

本文地址:https://www.itbaoku.cn/post/1557088.html

问题描述

Suppose I have the next table:

+------------+---------+
|    MovieId |  rating  |
+------------+---------+
|      1     |     4   |
|      1     |     3   |
|      1     |     2   |
|      1     |     4   |
|      1     |     5   |
|      2     |     3   |
|      2     |     4   |
|      2     |     2   |
|      3     |     1   |
|      3     |     2   |
|      3     |     3   |
|      3     |     5   |
|      4     |     4   |
|      4     |     2   |
+------------+---------+

I would like to get the average by group BUT using the first 2 elements on each group.
Example:

+------------+---------+
|    MovieId |  rating  |
+------------+---------+
|      1     |     4   |
|      1     |     3   |
|      2     |     3   |
|      2     |     4   |
|      3     |     1   |
|      3     |     2   |
|      4     |     4   |
|      4     |     2   |
+------------+---------+

answer expected:

+------------+---------+
|    MovieId |  AVG    |
+------------+---------+
|      1     |     3.5 |
|      2     |     3.5 |
|      3     |     1.5 |
|      4     |     3   |
+------------+---------+

This is the SQL query I have to get the AVG for all of the movies. But as I said, I would like to use just the first 2 elements for each group.

SELECT movieid, AVG(cast(rating as DECIMAL(10,2))) AS AVG FROM ratings group by movieid

If you can help me to make the SQL I appreciate. I will also use Linq just in case some of you know it.

推荐答案

In a SQL DBMS -- as in the relational model -- there is no "first". Do you mean any arbitrary 2 rows for each movie, or the two highest ratings, or something else?

If you can't define an order, then the query is meaningless.

If you can define an order, join the table to itself as I show in my canonical example to create a ranking, and select where RANK < 3.

其他推荐答案

FOR Mysql:-

select id, avg(rating) 
from (SELECT a.*, @num := @num + 1 rownum,
        (select count(*) 
         from movies m 
         where m.id<=a.id) last_count,

        (select count(*) 
         from movies m1 
         where a.id=m1.id) grp_count 
      from movies a, (SELECT @num := 0) d) f  
where grp_count-(last_count-rownum)<=2 
group by id;

you can use rownum function in oracle. And row_number() function in sql server.

其他推荐答案

This is a solution in SQL

Create table #tempMovie (movieId int ,rating int)

INSERT INTO #tempMovie
Select  *  from table where movieidid=1 Limit 2
Union all
Select *  from table where movieidid=2 Limit 2
Union all
Select *  from table where movieidid=3 Limit 2
Union all
Select  *  from table where movieidid=4 Limit 2

Temporary table #tempmovie table will contain data like this

+------------+---------+
|    MovieId |  rating  |
+------------+---------+
|      1     |     4   |
|      1     |     3   |
|      2     |     3   |
|      2     |     4   |
|      3     |     1   |
|      3     |     2   |
|      4     |     4   |
|      4     |     2   |
+------------+---------+

then apply group by

Select movieId, AVG(rating)
from #tempMovie
Group by movieId

Drop table #tempmovie