我可以在GROUP BY子句中使用分区吗？Server 2012

Question

我对加了窗口的函数不熟悉。这是四种水果的原始桌子。

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%

Answer 1

使用聚合sum的窗口sum。

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

Answer 2

我相信这就是你要找的：

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;

Answer 3

使用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