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

问题描述

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