计算指定条件的总计百分比 (SQL Server)

Question

我正在处理一个查询，该查询应该返回将由报告使用的数据集。假设我有一个dbo.payments数据的表格

，如下所示：

-----------------------------------------------
Customer |Commission| Trade Date | Trade Type | 
-----------------------------------------------
AMIRALIS | 20.00    | 22/01/2018 |   SALE     |
BRUSSASH | 30.00    | 22/01/2018 |  PURCHASE  |
AMIRALIS | 10.00    | 22/01/2018 |   SALE     |
AKBMOBIL | 50.00    | 22/01/2018 |  PURCHASE  |
AMIRALIS | 10.00    | 23/01/2018 |  PURCHASE  |
BRUSSASH | 10.00    | 23/01/2018 |  PURCHASE  |
BRUSSASH | 30.00    | 23/01/2018 |   SALE     |
BRUSSASH | 10.00    | 23/01/2018 |  PURCHASE  |
AMIRALIS | 60.00    | 24/01/2018 |  PURCHASE  |
BRUSSASH | 10.00    | 24/01/2018 |   SALE     |

场景 #1：我想编写一个查询，该查询将为我提供如下所示的结果。请注意，无论 where 条款如何，该百分比都反对所有时间240.00 SUM(Commission)。

-----------------------------------------------------
Customer |Total Commission| Trade Date |%Commission | 
-----------------------------------------------------
AMIRALIS |     30.00      | 22/01/2018 |  0.125     |
BRUSSASH |     30.00      | 22/01/2018 |  0.125     |
AKBMOBIL |     50.00      | 22/01/2018 |  0.208     |
AMIRALIS |     10.00      | 23/01/2018 |  0.041     |
BRUSSASH |     50.00      | 23/01/2018 |  0.208     |
AMIRALIS |     60.00      | 24/01/2018 |  0.250     |
BRUSSASH |     10.00      | 24/01/2018 |  0.041     |

我

是一个SQL新手，我能走的最远的是：。

SELECT [TRADE DATE] AS DATE, CUSTOMER, SUM([COMMISSION]) AS TOTAL, ([COMMISSION] / (SELECT SUM([COMMISSION]) FROM DBO.PAYMENTS)) AS '%COMMISSION' FROM DBO.PAYMENTS GROUP BY [TRADE DATE], CUSTOMER, COMMISSION

我如何达到预期的结果，无论 WHERE 子句如何：即 WHERE [Trade Date] BETWEEN '21/01/2018' AND '24/01/2018'将总数减少到170.00

Answer 1

这可以使用SQL Server窗口函数来解决，例如：

SELECT DISTINCT
    t.Customer,
    SUM(t.Commission) OVER (PARTITION BY Customer, Trade_Date) Total_Commission,
    t.Trade_Date,
     SUM(t.Commission) OVER (PARTITION BY Customer, Trade_Date) / SUM(t.Commission) OVER() "%Commission"
FROM mytable t 
ORDER BY 1, 3

DB小提琴演示：

 客户 |Total_Commission |Trade_Date |%佣金 :------- |:--------------- |:------------------ |:---------- 阿克美孚 |50.00 |22/01/2018 00：00：00 |0.208333   阿米拉利斯 |30.00 |22/01/2018 00：00：00 |0.125000   阿米拉利斯 |10.00 |23/01/2018 00：00：00 |0.041666   阿米拉利斯 |60.00 |24/01/2018 00：00：00 |0.250000   布鲁萨什 |30.00 |22/01/2018 00：00：00 |0.125000   布鲁萨什 |50.00 |23/01/2018 00：00：00 |0.208333   布鲁萨什 |10.00 |24/01/2018 00：00：00 |0.041666  

PS ：如果需要按日期过滤输出，只需在查询中添加WHERE子句即可。这将过滤这两个计算。

Answer 2

可以使用聚合查询。 在这种情况下，您可以使用窗口函数来计算总佣金：

select Customer, TradeDate,
       sum(Commission) as total_Commission,
       sum(Commission) / sum(sum(Commission)) over () as percent_commission
from dbo.payments p
group by Customer, TradeDate