MySQL top-N排名，然后将同一组的其余部分求和 [英] MySQL top-N ranking and sum the rest of same group

问题描述

我大部分时间都在研究这个主题，但是我没有一个有效，完美的答案，可以将使用sum（）进行分组和汇总的MySQL表排名（前3名）到其余表中。

I've researched most of the time with this topic, however I couldn't get a efficient and perfect answer regarding ranking (top 3) a MySQL table with group and aggregate using sum() to the rest.

数据如下：

TS         | Name     | Count
=============================
1552286160 | Apple    | 7
1552286160 | Orange   | 8
1552286160 | Grape    | 8
1552286160 | Pear     | 9
1552286160 | Kiwi     | 10
...
1552286100 | Apple    | 10
1552286100 | Orange   | 12
1552286100 | Grape    | 14
1552286100 | Pear     | 16
1552286100 | Kiwi     | 9
...
1552286040 | Apple    | 4
1552286040 | Orange   | 2
1552286040 | Grape    | 3
1552286040 | Pear     | 7
1552286040 | Kiwi     | 9
...

使用此数据集，我想将每一个形成前三TS组，其中一行包含其余组的sum（Count），如下所示：

With this dataset, I would like to form Top 3 by each TS group, and 1 row with sum(Count) of the rest that group, like following:

TS         | Name     | Count
=============================
1552286160 | Kiwi     | 10
1552286160 | Pear     | 9
1552286160 | Grape    | 8
1552286160 | Other    | 8 + 7
...
1552286100 | Pear     | 16
1552286100 | Grape    | 14
1552286100 | Orange   | 12
1552286100 | Other    | 10 + 9
...
1552286040 | Kiwi     | 9
1552286040 | Pear     | 7
1552286040 | Apple    | 4
1552286040 | Other    | 3 + 2
...

最接近的提示实际上是通过 http://www.silota.com/docs/recipes/ sql-top-n-aggregate-rest-other.html 但是，该解决方案仅适用于单个组。

The closest hint is actually provided via http://www.silota.com/docs/recipes/sql-top-n-aggregate-rest-other.html However, the solution was just for a single group.

我已经完成的SQL Fiddle准备好的文件位于： http://sqlfiddle.com/#!9/3cedd0/10

The SQL Fiddle that I've prepared is located here: http://sqlfiddle.com/#!9/3cedd0/10

感谢是否有解决方案。

推荐答案

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(ts INT NOT NULL
,name VARCHAR(12) NOT NULL
,count INT NOT NULL
,PRIMARY KEY(ts,name)
);

INSERT INTO my_table VALUES
(1552286160,'Apple' , 7),
(1552286160,'Orange', 8),
(1552286160,'Grape' , 8),
(1552286160,'Pear'  , 9),
(1552286160,'Kiwi'  ,10),
(1552286100,'Apple' ,10),
(1552286100,'Orange',12),
(1552286100,'Grape' ,14),
(1552286100,'Pear'  ,16),
(1552286100,'Kiwi'  , 9),
(1552286040,'Apple' , 4),
(1552286040,'Orange', 2),
(1552286040,'Grape' , 3),
(1552286040,'Pear'  , 7),
(1552286040,'Kiwi'  , 9);

SELECT ts
     , CASE WHEN i>3 THEN 'other' ELSE name END name
     , SUM(count) count
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev=ts THEN @i:=@i+1 ELSE @i:=1 END i
            , @prev:=ts 
         FROM my_table x
            , (SELECT @prev:=null,@i:=0) vars 
        ORDER 
           BY ts
            , count DESC
            , name
     ) a
 GROUP
    BY ts
     , CASE WHEN i>3 THEN 'other' ELSE name END;

+------------+--------+-------+
| ts         | name   | count |
+------------+--------+-------+
| 1552286040 | Apple  |     4 |
| 1552286040 | Kiwi   |     9 |
| 1552286040 | other  |     5 |
| 1552286040 | Pear   |     7 |
| 1552286100 | Grape  |    14 |
| 1552286100 | Orange |    12 |
| 1552286100 | other  |    19 |
| 1552286100 | Pear   |    16 |
| 1552286160 | Grape  |     8 |
| 1552286160 | Kiwi   |    10 |
| 1552286160 | other  |    15 |
| 1552286160 | Pear   |     9 |
+------------+--------+-------+

这篇关于MySQL top-N排名，然后将同一组的其余部分求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！