mysql - 如何只计算前 5 个项目，然后将剩余的项目分组到 'other' bin 下？

Question

我有一张这样的 table ；

+----+---------+-------------+
| id | user_id | screenWidth |
+----+---------+-------------+
|  1 |       1 |        1366 |
|  2 |       1 |        1366 |
|  3 |       1 |        1366 |
|  4 |       1 |        1366 |
|  5 |       2 |        1920 |
|  6 |       2 |        1920 |
|  7 |       3 |        1920 |
|  8 |       4 |        1280 |
|  9 |       5 |        1280 |
| 10 |       6 |        1280 |
| 11 |       7 |        1890 |
| ...|   ...   |     ...     |
| ...|   ...   |     ...     |
| ...|   ...   |     ...     |
| 100|       6 |        1910 |
+----+---------+-------------+

有很多 screenWidths，但其中 90% 等于 5 个值之一。

使用如下查询:

SELECT      screenwidth
        ,   COUNT(DISTINCT user_id) AS screenwidthcount
FROM        screenwidth
GROUP BY    screenwidth
ORDER BY    screenwidthcount;

(感谢来自 How do I count only the first occurrence of a value? )

我对 screenWidth 出现的次数进行了很好的计数，每个用户只计数一次。

有没有一种方法可以计算最流行的屏幕宽度，然后将所有其他屏幕宽度收集到一个名为“其他”的类别中 - 也就是说，上面的查询不会返回行负载，而是返回 6，前 5 个是它当前返回的前 5 个，第 6 个被称为 other 与其余值的总和？

Answer 1

这是一种方法。以下脚本是根据此问题的答案创建的 Rank function in MySQL

查询为计算不同计数的所有行分配排名。我在 CASE 表达式中分配了一个值 2。这表示脚本将显示前 2 个屏幕宽度，其余的将合并为其他。您需要根据您的要求更改该值。我已将值 99999 硬编码为对所有其他行进行分组。

可能有更好的方法来做到这一点，但这是我可以让它发挥作用的方法之一。

Click here to view the demo in SQL Fiddle.

脚本:

CREATE TABLE screenwidth 
(
    id INT NOT NULL
  , user_id INT NOT NULL
  , screenwidth INT NOT NULL
);

INSERT INTO screenwidth (id, user_id, screenwidth) VALUES
  (1, 1, 1366),
  (2, 2, 1366),
  (3, 2, 1366),
  (4, 2, 1366),
  (5, 3, 1366),
  (6, 1, 1920),
  (7, 2, 1920),
  (8, 1, 1440),
  (9, 2, 1440),
  (10, 3, 1440),
  (11, 4, 1440),
  (12, 1, 1280),
  (13, 1, 1024),
  (14, 2, 1024),
  (15, 3, 1024),
  (16, 3, 1024),
  (17, 3, 1024),
  (18, 1, 1366);

SELECT screenwidth
    , SUM(screenwidthcount) AS screenwidth_count
FROM
(
    SELECT      CASE    
                    WHEN @curRank < 2 THEN screenwidth 
                    ELSE 'Other' 
                END AS screenwidth
            ,   screenwidthcount
            ,   @curRank := 
                (   CASE 
                        WHEN @curRank < 2 THEN @curRank + 1 
                        ELSE 99999
                    END
                ) AS rank
    FROM
    (
        SELECT      screenwidth
                ,   COUNT(DISTINCT user_id) AS screenwidthcount
        FROM        screenwidth
        GROUP BY    screenwidth
        ORDER BY    screenwidthcount DESC
    ) T1
                ,   (SELECT @curRank := 0) r
) T2
GROUP BY    screenwidth
ORDER BY    rank;

输出:

SCREENWIDTH SCREENWIDTH_COUNT
----------- -----------------
1440               4
1024               3
Other              6