使用GROUP BY查询计算百分比 [英] Calculating percentages with GROUP BY query

问题描述

 文件使用者评价（1-5）
 ------------------------------ 
 00001 1 3 
 00002 1 4 
 00003 2 2 
 00004 3 5 
 00005 4 3 
 00005 3 2 
 00006 2 3 
等等
  

我想要生成一个查询，输出以下内容（对于每个用户和评级，显示文件的数量以及文件的百分比）：

 用户评分数量百分比
 ----------------------- ------------ 
 1 1 3 .18 
 1 2 6 .35 
 1 3 8 .47 
 2 5 12 .75 
 2 3 4 .25 
  

使用Postgresql，我知道如何创建一个包含第一个3列使用以下查询，但我不知道如何计算GROUP BY内的百分比：

 选择
用户，
等级，
数量（*）
从
结果
 GROUP BY 
用户，等级
 ORDER BY 
用户，等级
  

这里我想要将百分比计算应用于每个用户/评级组。

解决方案

  WITH t1 AS 
（SELECT User，Rating，Count（*）AS n 
 FROM your_table 
 GROUP BY User，Rating）
 SELECT用户，评级，n，
（0.0 + n）/（COUNT（*）OVER（PARTITION BY用户）） - 没有整数除法！ 
 FROM t1; 
  

或

  SELECT User，Rating，Count（*）OVER w_user_rating AS n，
（0.0 + Count（*）OVER w_user_rating）/（Count（*）OVER（PARTITION BY User）） b FROM your_table 
 WINDOW w_user_rating AS（PARTITION BY User，Rating）; 
  

我会看看这些或其中一个是否会为您提供更好的查询计划RDBMS。

I have a table with 3 columns which looks like this:

File    User     Rating (1-5)
------------------------------
00001    1        3
00002    1        4
00003    2        2
00004    3        5
00005    4        3
00005    3        2
00006    2        3
Etc.

I want to generate a query that outputs the following (for each user and rating, display the number of files as well as percentage of files):

User    Rating   Count   Percentage
-----------------------------------
1       1         3      .18
1       2         6      .35
1       3         8      .47
2       5         12     .75
2       3         4      .25

With Postgresql, I know how to create a query that includes the first 3 columns using the following query, but I can't figure out how to calculate percentage within the GROUP BY:

SELECT
    User,
    Rating,
    Count(*)
FROM
    Results
GROUP BY
    User, Rating
ORDER BY
    User, Rating

Here I want the percentage calculation to apply to each user/rating group.

解决方案

WITH t1 AS 
 (SELECT User, Rating, Count(*) AS n 
  FROM your_table
  GROUP BY User, Rating)
SELECT User, Rating, n, 
       (0.0+n)/(COUNT(*) OVER (PARTITION BY User)) -- no integer divide!
FROM t1;

Or

SELECT User, Rating, Count(*) OVER w_user_rating AS n, 
        (0.0+Count(*) OVER w_user_rating)/(Count(*) OVER (PARTITION BY User)) AS pct
FROM your_table
WINDOW w_user_rating AS (PARTITION BY User, Rating);

I would see if one of these or the other yields a better query plan with the appropriate tool for your RDBMS.

这篇关于使用GROUP BY查询计算百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！