我想显示百分比评级产品评论表按比率编号表。
例如:
5 :71%
4 :0%
3 :0%
2 :28%
1 :0%
产品评论表中可能不存在任何费率号。
插入上表中的数据是:
Id CustomerId ProductId Rating
------- --------------- -------------- --------
39 14 57 2
42 18 57 5
56 19 57 5
我对显示百分比的查询是:
SELECT
pr.ProductId ,
pr.Rating,
percentage = AVG(pr.Rating) * 100 / SUM(AVG(pr.Rating)) OVER (PARTITION BY pr.ProductId)
FROM ProductReview pr
WHERE pr.ProductId = 57
GROUP BY
pr.ProductId,
pr.Rating
ORDER BY pr.Rating DESC
和结果我的查询是:
ProductId Rating percentage
----------- ----------- -----------
57 5 71
57 2 28
,但如果不存在为零百分比,我不会显示其他速率号。
感谢所有人。
如果您的原始数据没有每个产品的评分,那么您将必须以某种方式将此信息介绍到查询中。一个选项是日历表方法(因此命名是因为它用于涵盖数据集中缺少的日期)。在下面的第一个CTE中,我为您的ProductReview
表中的每个产品生成了所有评分,1至5。然后,我将其加入到您的原始查询中,将每个产品/评级与原始查询中的每个数据点相匹配。如果无法匹配,那么我们将百分比显示为零。
WITH cte AS (
SELECT t1.ProductId, t2.Rating
FROM (SELECT DISTINCT ProductId FROM ProductReview) t1
CROSS JOIN
(
SELECT 1 AS Rating UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
) t2
),
yourQuery AS (
SELECT
ProductId,
Rating,
AVG(pr.Rating) * 100 /
SUM(AVG(pr.Rating)) OVER (PARTITION BY pr.ProductId) AS percentage
FROM ProductReview
WHERE ProductId = 57
GROUP BY ProductId, Rating
)
SELECT
t1.ProductId,
t1.Rating,
COALESCE(t2.percentage, 0) AS percentage
FROM cte t1
LEFT JOIN yourQuery t2
ON t1.ProductId = t2.ProductId AND
t1.Rating = r2.Rating
ORDER BY
t1.ProductId,
t1.Rating;