我可以在groupby子句中使用PARTITION BY吗?SQL 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