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

问题描述

假设我有一张桌子:

+------------+---------+
|    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   |
+------------+---------+

我想使用每个组的前2个元素按组BUT求平均值.
示例:

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   |
+------------+---------+

预期的答案:

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

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

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

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

如果您可以帮助我制作SQL，请感激.我还将使用Linq，以防万一你们中的一些人知道.

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

推荐答案

FOR Mysql:-

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;

您可以在oracle中使用rownum函数.并且row_number()在sql server中起作用.

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

这篇关于通过在每个组中使用前N个元素来对SQL组进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！