Geek asks and answers

How to count only the first 5 elements, and then group the remaining in the "other" bin?

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

In cases where there are many screens, but 90% of them are equal to one of 5 values.

Using a query like:

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

(thanks. How to count only the first occurrence of a value? )

I get a good score for the number of times the screen bus occurred, counting only once for each user.

Is there a way to count the most popular screen screens, and then collect all the others in a category called "others", that is, instead of a query above returning a lot of rows, it returns 6, the first 5 - the first 5 it returns now, the 6th called another with the sum of the remaining values?

+3
3

. script Rank MySQL

, . 2 CASE. , script 2 , Other. . 99999, .

, .

, 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;

:

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

:

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

:

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);

:

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/31

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

, ubiuquitous case: MySQL, , - , ...

. Inner Select screnwidth , ... ( screnwidth ). , .

. , "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 similar to the SQL Servers function Str(), you can use it to convert the case expression to a string, and then insteaed from 0 after else, you can use "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
0
source

More articles:


All Articles