我可以使用PARTITION BY和GROUP BY子句吗？ SQL Server 2012

问题描述：

我是新来的窗口函数。这是有4种水果的原始桌子。我可以使用PARTITION BY和GROUP BY子句吗？ SQL Server 2012

fruit quantity 
orange 100 
banana 27 
banana 20 
orange 5 
melon 5 
apple 1 
banana 10 
banana 4 
banana 36 
banana 86 
banana 47 
apple 32 
banana 7 
banana 5 
banana 3

是否有可能将这个变成每个水果构成一切总量的百分比？下面是我想：

fruit  total  percentage 
apple  33   9% 
banana  245   63% 
orange  105   27% 
melon  5   1%

这里是我想出来的代码，但它给我一个错误：

SELECT fruit 
, SUM(quantity)/SUM(quantity) OVER() * 100 
FROM fruit_inventory 
GROUP BY fruit

如果我撤消GROUP BY并删除第一个SUM(quantity)然后我得到看起来像这样的多个记录：

fruit quantity percentage 
apple 1   0% 
apple 32   8% 
banana 27   7% 
banana 20   5% 
banana 10   3% 
banana 4   1% 
banana 36   9% 
banana 86   22% 
banana 47   12% 
banana 7   2% 
banana 5   1% 
banana 3   1% 
melon 5   1% 
orange 100   26% 
orange 5   1%

在您的OVER子句中添加ORDER BY水果 – Mihai

仍然失败。我应该删除什么？ – eek142

错误是什么，可能会尝试在添加一些偏执之后与100相乘 – Mihai

答

使用合计sum的窗口sum。

SELECT Fruit , 
     SUM(Quantity) AS Quantity , 
     SUM(Quantity)/SUM(SUM(Quantity)) OVER() * 100 
FROM @fruitbasket 
GROUP BY Fruit;

这正是我正在寻找的！我想我开始更多地理解OVER的用法。非常感谢。 – eek142

答

我相信这是你在找什么：

declare @fruitbasket table 
    (
    Fruit nvarchar(50), 
    Quantity decimal(19, 5) 
    ); 

insert into @fruitbasket 
     (Fruit, Quantity) 
values (N'orange', 100), 
     (N'banana', 27), 
     (N'banana', 20), 
     (N'orange', 5), 
     (N'melon', 5), 
     (N'apple', 1), 
     (N'banana', 10), 
     (N'banana', 4), 
     (N'banana', 36), 
     (N'banana', 86), 
     (N'banana', 47), 
     (N'apple', 32), 
     (N'banana', 7), 
     (N'banana', 5), 
     (N'banana', 3); 

select Fruit, 
     sum(Quantity) as Quantity, 
     sum(Quantity)/(
         select sum(Quantity) as Total 
         from @fruitbasket 
         ) * 100 as PercentageOfTotal 
from @fruitbasket 
group by Fruit;

我喜欢这个答案，因为我现在开始更多地使用相关子查询。 – eek142

答

使用CTE（公用表表达式），您可以得到您想要的结果。总体CTE的确具有更好的性能，并且具有更好的可读性。

WITH cte AS 
(
    SELECT DISTINCT 
     Fruit 
     ,SUM(Quantity) OVER (PARTITION BY fruit ORDER BY fruit) AS sumProducts 
     ,SUM(SUM(Quantity)) OVER() AS totalProducts 
    FROM stackOverflow 
    GROUP BY 
     Fruit 
     ,Quantity 
) 
SELECT Fruit, CAST(sumProducts AS INT) AS sumProducts, CAST(100 * sumProducts/totalProducts AS DECIMAL(5,2)) AS percentage 
FROM cte

我可以使用PARTITION BY和GROUP BY子句吗？ SQL Server 2012

相关推荐