SQL值除以总和，groupwise

Question

我有一张桌子，像：

user_id  operation   amount  
1        purchase    10  
1        sale        40
2        purchase    100
2        sale        20
2        conversion  15
3        sale        70
4        conversion  40  

由SQL查询提供：

SELECT  
  user_id,
  operation,
  COUNT(item_num) AS amount
FROM MyTable
GROUP BY user_id, operation

我想为每个用户计算每个操作总金额的百分比，最好把它们放在列中(实际上除以数字)：

user_id  purchase   sale      conversion
1        10  /50    40 /50    0  /50
2        100 /135   20 /135   15 /135
3        0   /70    70 /70    0  /70
4        0   /40    0  /40    40 /40

编辑：感谢在回答中给出的直觉，我找到了最适合我的解决方案

WITH result
AS
(
    SELECT
        [user_id],
        [operation],
        CAST(COUNT([item_num]) AS float) AS amount,
        SUM(COUNT([item_num])) over(partition by [user_id])  AS total_amount

    FROM Mytable
    GROUP BY user_id, operation
)

SELECT
    [user_id],
    ROUND(ISNULL([purchase], 0) / total_amount, 2)   AS purchase,
    ROUND(ISNULL([sale], 0) / total_amount, 2)       AS sale,
    ROUND(ISNULL([conversion], 0) / total_amount, 2) AS conversion
FROM result

PIVOT
(
    MAX(amount)
    FOR operation IN ([purchase], [sale], [conversion])
) x

ORDER BY user_id

Answer 1

这个代码会对你有用的。您将需要在列中使用CTE和计算每个user_id的总总和的sum() over()。有了这些信息，您所需要做的就是传递结果并给出所需的格式。如果您需要分割，而不只是显示它的划分，则删除形成和连接。

WITH Sum_Over AS

(

    select  user_id,operation, amount,sum(amount) over(partition by user_id) AS Total_Sum
    from #test
)

SELECT user_id

,CAST(ISNULL([purchase],0) AS VARCHAR(5))+'/'+CAST(Total_Sum  AS VARCHAR(5)) AS [purchase]

,CAST(ISNULL([sale],0) AS VARCHAR(5))+'/'+CAST(Total_Sum  AS VARCHAR(5)) AS [sale]

,CAST(ISNULL([conversion],0) AS VARCHAR(5))+'/'+CAST(Total_Sum  AS VARCHAR(5)) AS [conversion]

FROM Sum_Over

PIVOT (
max(amount)
FOR operation IN ([purchase],[sale],[conversion])
)x

ORDER BY user_id

Answer 2

杰德的回答更干净。这一次使用Group的方法是：

With result As
(
SELECT  
  user_id,
  Sum(amount) as tot,
    Case When operation = 'purchase' Then 
    convert(nvarchar(5),sum(amount)) + '/' +  convert(nVarChar(50),(Select sum(amount) From MyTable t Where t.user_id = x.user_id))
    End As [purchase],

    Case When operation = 'sale' Then 
    convert(nvarchar(5),sum(amount)) + '/' +  convert(nVarChar(50),(Select sum(amount) From MyTable t Where t.user_id = x.user_id))
    End As [sale],

    Case When operation = 'conversion' Then 
    convert(nvarchar(5),sum(amount)) + '/' +  convert(nVarChar(50),(Select sum(amount) From MyTable t Where t.user_id = x.user_id))
    End As [conversion]


FROM MyTable x
GROUP BY user_id, operation
)
Select user_id,
isnull(Max(purchase),'0/' + convert(nVarChar(50),sum(tot))) As purchase,
isnull(Max(sale),'0/' + convert(nVarChar(50),sum(tot))) As sale,
isnull(Max(conversion),'0/' + convert(nVarChar(50),sum(tot))) As conversion
From result
Group By user_id