我有下表：
| ％酒精 | 評分 | 數數 |
|---|---|---|
| 7 | 4 | 5 |
| 6 | 5 | 3 |
| 7 | 6 | 3 |
| 4 | 7 | 2 |
| 4 | 8 | 1 |
| 4 | 9 | 5 |
使用 SQL 命令我想計算 %alcohol 的磁區：
| ％酒精 | 評分 | 數數 | 百分比 |
|---|---|---|---|
| 4 | 7 | 2 | 25 |
| 4 | 8 | 1 | 12.5 |
| 4 | 9 | 5 | 62.5 |
| 6 | 5 | 3 | 100 |
| 7 | 4 | 5 | 62.5 |
| 7 | 6 | 3 | 37.5 |
我已經用下面的命令試過了，但沒有用
WITH number_of_rating AS
(
SELECT AlcoholPercentage, Rating, Count(*) AS number_of_rating_per_percentage
FROM Beer
GROUP BY Beer, Rating)
SELECT AlcoholPercentage, Rating, number_of_rating_per_percentage,
(0.0 number_of_rating_per_percentage)/(COUNT(*) OVER (PARTITION BY AlcoholPercentage)) AS Portion
FROM number_of_rating;
uj5u.com熱心網友回復：
大多數資料庫支持視窗函式：
select *,
"Count" * 1.0 / Sum("count") over(partition by "%alcohol") * 100 as Percentage
from Beer
uj5u.com熱心網友回復：
WITH CTE(ALCOHOL,RATING,COUNT)AS
(
SELECT 7 , 4 , 5 UNION ALL
SELECT 6 , 5 , 3 UNION ALL
SELECT 7 , 6, 3 UNION ALL
SELECT 4 , 7, 2 UNION ALL
SELECT 4 , 8, 1 UNION ALL
SELECT 4 , 9, 5
)
SELECT C.ALCOHOL,C.RATING,C.COUNT,
CAST((CAST(C.COUNT AS DECIMAL(20,2))/CAST(SUM(C.COUNT)OVER(PARTITION BY C.ALCOHOL)AS DECIMAL(20,2)))*100.00 AS DECIMAL(20,2)) AS PER_CENTAGE
FROM CTE AS C
享受你的啤酒
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/387411.html
標籤：sql
上一篇：雪花問題：在SQL中使用RANK()Windows函式-您要排序的列不是序數
下一篇：左外連接先聚合