如何只计算前5个项目，然后将剩下的5个项目分组?

I have a table like this;

我有一张这样的桌子;

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

Where there are lots of screenWidths, but 90% of them are equal to one of 5 values.

屏幕宽度很多，但90%的都等于5个值中的一个。

Using a query like:

使用查询:

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

(Thanks from How do I count only the first occurrence of a value?)

(我怎么才能算出一个值的第一个出现呢?)

I get a nice count for the number of times a screenWidth has occurred, counting only once per user.

我得到了屏幕宽度发生的次数的一个很好的计数，每个用户只计数一次。

Is there a way to count the most popular screenWidths, then collect all the others in a category called "other" - that is to say, instead of the query above returning loads of rows, it returns 6, the first 5 being the first 5 it returns currently, the 6th being called other with the sum of the rest of the values?

有最受欢迎的screenWidths计数,然后收集所有其他类别称为“其他”——也就是说,而不是上面的查询返回的行,它返回6,第一个5是第一个5它返回目前,6日被称为其他剩下的值的总和?

3 个解决方案

#1

Here is one way to do it. Following script was created based on the answer to this question Rank function in MySQL

这里有一个方法。下面的脚本是基于MySQL中这个问题等级函数的答案创建的

The query assigns a ranking to all the rows for which distinct count has been computer. I have assigned a value of 2 in the CASE expressions. This denotes that the script will display the top 2 screen widths and the remaining will be clubbed into Other. You need to change the value according to your requirement. I have hard coded the value 99999 to group all the other rows.

该查询将一个排序分配给计算机中不同计数的所有行。我在CASE表达式中赋值了2。这表示脚本将显示前两个屏幕宽度，其余部分将被合并到另一个中。您需要根据您的需求更改值。我已经硬编码了99999值来将所有其他行分组。

There might be a better way to do this but this is one of the ways I could make it to work.

也许有更好的方法，但这是我可以让它工作的方法之一。

Click here to view the demo in SQL Fiddle.

点击这里查看SQL Fiddle。

Script:

脚本:

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;

Output:

输出:

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

#2

Try this:

试试这个:

select

  case when rank <= 5 then rank else 'Other' end as screenwidth, 

  sum(screenwidthcount) as screenwidthcount,

  least(rank,6) as LimitRank

from
(
  SELECT
  *, (@r := @r + 1) as rank
  FROM
  (
    SELECT      screenwidth
            ,   COUNT(DISTINCT user_id) AS screenwidthcount

    FROM        tbl

    GROUP BY    screenwidth
    ORDER BY    screenwidthcount desc, screenwidth desc
  ) AS X
  cross join (select @r := 0 as init ) rx
) as y

group by LimitRank

Data sample:

数据样本:

CREATE TABLE tbl
    (id int, user_id int, screenWidth int);

INSERT INTO tbl
    (id, user_id, screenWidth)
VALUES
    (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),
    (12, 9, 1890),
    (13, 9, 1890),
    (13, 9, 1024),
    (13, 9, 800),
    (100, 6, 1910);

Output:

输出:

SCREENWIDTH SCREENWIDTHCOUNT    LIMITRANK
1280        3                   1
1920        2                   2
1890        2                   3
1910        1                   4
1366        1                   5
Other       2                   6

Live test: http://www.sqlfiddle.com/#!2/c0e94/33

现场测试:http://www.sqlfiddle.com/ ! 2 / c0e94/33

---

Here's the uncapped results: http://www.sqlfiddle.com/#!2/c0e94/31

下面是无上限的结果:http://www.sqlfiddle.com/#!2/c0e94/31

SCREENWIDTH SCREENWIDTHCOUNT
1280        3
1920        2
1890        2
1910        1
1366        1
1024        1
800         1

#3

Yes, with the ubiuquitous case statement: I don't have MySQL, but this, or something like this, should work...

是的，有了ubiuquency case语句:我没有MySQL，但是这个或类似的东西应该可以……

A. Inner Select generates resultset of screnwidth, and the count of distinct users that have that screenwidth... (this effectively counts each screnwidth only once per user). Result set is limited to only those screenwidths used by five or more users.

A. Inner Select生成screnwidth的resultset，以及具有该屏幕宽度的不同用户的计数…(这有效地为每个用户计算一次screnwidth)。结果集仅限于5个或更多用户使用的屏幕宽度。

B. Then outer query joins the complete table to that resultset, grouping on an expression and summing "Cnt" that represents the number of users using each screenwidth.

然后，外部查询将完整的表连接到resultset，在一个表达式上分组并求和“Cnt”，该“Cnt”表示使用每个屏幕宽度的用户数量。

   Select case When Z.Cnt < 5 Then screnwidth, else 0 end
       Sum(Z.Cnt) screenwidthcount, 
   From screenwidth A
      Left Join (Select screenwidth, Count(Distinct User_ID) Cnt
                 From screenwidth
                 Group By screenwidth
                 Having count(*) > 4) Z
        On Z.screeenwidth = A.screeenwidth         
   Group By case When Z.Cnt < 5 Then screnwidth, else 0 end

C. If MySql has a function like SQL Servers Str() function, you can use that to convert the case expression to a string, then insteaed of the 0 after the else, you can use 'other'

C.如果MySql有SQL Servers Str()函数，您可以使用它将case表达式转换为字符串，然后在else后面安装0，您可以使用'other'

   Select case When Z.Cnt < 5 Then Str(screnwidth, 6,0) else 'other' end
       Sum(Z.Cnt) screenwidthcount, 
   From screenwidth A
      Left Join (Select screenwidth, Count(Distinct User_ID) Cnt
                 From screenwidth
                 Group By screenwidth
                 Having count(*) > 4) Z
        On Z.screeenwidth = A.screeenwidth         
   Group By case When Z.Cnt < 5 Then Str(screnwidth, 6,0) else 'other'  end