计数不同时的 MySQL 案例

Question

我正在尝试按收视率等级获取不同视频的计数。我有下表:

vid_id  views
1       6
1       10
1       900
2       850
2       125000
3       1010
3       12239
3       150000

我尝试使用这段代码来获得我想要的输出:

SELECT
    CASE
        WHEN views < 1000 THEN '< 1K Views'
            WHEN views >= 1000 AND views < 10000 THEN '1K to 10K Views'
            WHEN views >= 10000 AND views < 100000 THEN '10K to 100K Views'
            WHEN views >= 100000 AND views < 1000000 THEN '100K to 1M Views'
            ELSE '1M+ Views'
    END AS tier,
    COUNT(distinct vid_id)
FROM
    test
GROUP BY
    tier

因为每个 vid_id 都有多个条目, 我想使用每个 vid_id 的最大观看次数对每一层的视频计数进行分组.所以我的输出应该是:

tier                COUNT(views)
< 1K Views          1 
100K to 1M Views    2 

视频 #1 的观看次数最多为 900，所以它在 < 1K Views 中等级。视频 2 和 3 的观看次数分别达到了 125,000 和 150,000。所以他们在 100K to 1M Views层级。

Answer 1

一个解决方案是分两步进行:

• 首先，使用聚合子查询计算每部电影的最大观看次数
• 然后，将上述信息分层

SQL:

SELECT
    CASE
        WHEN views < 1000 THEN '< 1K Views'
        WHEN views >= 1000 AND views < 10000 THEN '1K to 10K Views'
        WHEN views >= 10000 AND views < 100000 THEN '10K to 100K Views'
        WHEN views >= 100000 AND views < 1000000 THEN '100K to 1M Views'
        ELSE '1M+ Views'
    END AS tier,
    COUNT(*) as distinct_videos
FROM (
    SELECT vid_id, MAX(views) views FROM mytable GROUP BY vid_id
) x
GROUP BY tier

Demo on DB Fiddle :

| tier             | distinct_videos |
| ---------------- | --------------- |
| 100K to 1M Views | 2               |
| < 1K Views       | 1               |