Question

我正在用卡格格尔对MySQL的Netflix数据集开会。数据集包含的领域包括国家、分区、年龄、性别、月刊等。在Subscription_Type一栏,可能的返回场是基本、标准、保险费。我正试图为每个国家找到最普通的订阅类型,因此,理想的做法是每个国家都有一个结果。

SELECT * 
FROM (
    SELECT Country,
            Subscription_Type, 
            COUNT(*) as count_subscription, 
            DENSE_RANK() OVER (PARTITION BY Country ORDER BY (SELECT COUNT(*) as count_subscription FROM netflix) ) as `rnk`
    FROM netflix
    GROUP BY Country, Subscription_Type
) as subscription_rank
WHERE rnk = 1;

问题一是,Dense_Rank在每行1级,因此,我最终取得每个国家的3个结果。我怎么能够确定,以便我能够获得每个国家最受欢迎的订阅类型?

Answer 1

The subquery is nonsense: it s not correlated so returns the same result on every row.

此外,这没有必要。窗口职能如下:在<><>><>>>>后,即:直接使用<>条码>。

SELECT * 
FROM (
    SELECT nf.Country,
           nf.Subscription_Type, 
           COUNT(*) as count_subscription, 
           DENSE_RANK() OVER (PARTITION BY nf.Country ORDER BY COUNT(*)) as rnk
    FROM netflix nf
    GROUP BY
      nf.Country,
      nf.Subscription_Type
) as subscription_rank
WHERE rnk = 1;

我可能错了,但也许你希望<条码>COUNT(*) DESC。。

友情链接