sql - 两个 group by 和一个 sum sql 的总百分比

Question

我有一个如下所示的表格:

id     Category     Spend
------------------------------
00001    Apple       10
00001    Oranges     4
00001    Apple       21
00001    Oranges     50
00002    Oranges     32
00002    Apple       2
00002    Oranges     31
Etc.

我希望生成一个显示以下内容的表格

id     Category     Spend_Ratio
------------------------------
00001    Apple       36%
00001    Oranges     64%
00002    Apple       3%
00001    Oranges     97%

逻辑是根据按 ID 分组的类别支出生成比率。

我已经做到了这一点:

SELECT id, category, SUM(spend)
FROM table
GROUP BY id, category;

完全不知道下一步要去哪里..

Answer 1

你可以使用这个:

WITH sum_category AS 
(
    SELECT id, category,
        SUM(spend) AS spend
    FROM table_name
    GROUP BY id, category
)
SELECT id, category,
    ROUND(100 * spend / SUM(spend) OVER (PARTITION BY id)) AS spend_ratio
FROM sum_category
ORDER BY id, category;

尝试 SqLFiddle