我可以使用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%
答
使用合计sum
的窗口sum
。
SELECT Fruit ,
SUM(Quantity) AS Quantity ,
SUM(Quantity)/SUM(SUM(Quantity)) OVER() * 100
FROM @fruitbasket
GROUP BY Fruit;
+0
这正是我正在寻找的!我想我开始更多地理解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;
+0
我喜欢这个答案,因为我现在开始更多地使用相关子查询。 – 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 *
GROUP BY
Fruit
,Quantity
)
SELECT Fruit, CAST(sumProducts AS INT) AS sumProducts, CAST(100 * sumProducts/totalProducts AS DECIMAL(5,2)) AS percentage
FROM cte
在您的OVER子句中添加ORDER BY水果 – Mihai
仍然失败。我应该删除什么? – eek142
错误是什么,可能会尝试在添加一些偏执之后与100相乘 – Mihai