1

For each group of TS, there's random number of rows that having different Name and it's Count. I would like to do the following:

  1. Get latest group of TIMESTAMP with NAME order desc by Count. For more than 3 rows in the latest group, the rest sum by Count and rename as "Other".
  2. For each of the TIMESTAMP group, find rows having NAME in step 1.
  3. If not found, 0 padding as Count for the pair in specific group.

Have a look at the illustration:

TS         | Name     | Count
=============================
1552286160 | Apple    | 7
1552286160 | Orange   | 8
1552286160 | Grape    | 8
1552286160 | Pear     | 9
1552286160 | Kiwi     | 10
1552286160 | Berry    | 3
1552286160 | Carrot   | 2

1552286100 | Apple    | 10
1552286100 | Orange   | 12
1552286100 | Grape    | 14
1552286100 | Pear     | 16
1552286100 | Kiwi     | 9

1552286040 | Apple    | 4

1552285980 | Peach    | 8

With this dataset, I would like to have the following:

TS         | Name     | Count
=============================
1552286160 | Kiwi     | 10
1552286160 | Pear     | 9
1552286160 | Grape    | 8
1552286160 | Other    | 8 + 7 + 3 + 2

1552286100 | Kiwi     | 9
1552286100 | Pear     | 16
1552286100 | Grape    | 14
1552286100 | Other    | 10 + 9

1552286040 | Kiwi     | 0
1552286040 | Pear     | 0
1552286040 | Grape    | 0
1552286040 | Other    | 4

1552286040 | Kiwi     | 0
1552286040 | Pear     | 0
1552286040 | Grape    | 0
1552286040 | Other    | 8

This is actually an extended solution came from MySQL top-N ranking and sum the rest of same group However, the solution does not group each TS group with the same rows from the latest TS group, in this case TS=1552286160.

The SQL Fiddle that I've prepared is located here: http://sqlfiddle.com/#!9/4e13004/1

Thanks if you have any idea.

CheeHow
  • 875
  • 4
  • 12
  • 27
  • I would strongly recommend using MySQL 8+ or later if you think you will have to write queries like this. – Tim Biegeleisen Mar 18 '19 at 05:37
  • @TimBiegeleisen the previous answer was deleted? No worries, I will not flag down anyone that provides a suggestion even it's wrong, a good discussion is much valuable than a solution. – CheeHow Mar 18 '19 at 06:07
  • Yes, I deleted it, because it is somewhat far from what you need. I don't see a very clean way of doing this, because you might have to generate the "others" records in the result set. – Tim Biegeleisen Mar 18 '19 at 06:09

0 Answers0