mysql - 如何获取前20条SQL中某列的百分比？

Question

我正在尝试查找评分低于 20 的 pageType 的百分比。

我的 table 看起来像:

requestId   rank    pageType    
MMCVS       0       HOME        
MMCVS       1       MOBILE      
MMCVS       2       HOME 
BBVSS       0       HOME        
BBVSS       11      MOBILE      
BBVSS       12      HOME 

到目前为止，我尝试过:

 select pageType, Top20, Top20/count(DISTINCT requestId) as Percentage
    from (
     SELECT 
        pageType, requestId,
        SUM(CASE WHEN rank <= 20 THEN 1 ELSE 0 END) as Top20  
    FROM 
        tempTable 
    group by pageType, requestId) tempTable group by pageType

但是出现错误:

 expression 'Top20' is neither present in the group by, nor is it an aggregate function. 

Answer 1

是的，您需要将任何非聚合字段添加到按字段分组列表中。即使您不这样做，MySQL 也不会给您错误。

对于这个问题，您可以将 Top20 添加到 group by 中，或者从 select 中删除 Top20:

SELECT pageType, Top20/count(DISTINCT requestId) as Percentage
FROM (
  SELECT pageType, requestId,
    SUM(CASE WHEN rank <= 20 THEN 1 ELSE 0 END) as Top20  
  FROM tempTable 
  GROUP BY pageType, requestId
) tempTable
GROUP BY pageType

http://sqlfiddle.com/#!9/e7eb89/4