计算指定条件下总计的百分比(SQL Server) [英] Calculate percentage against total for a specified condition (SQL Server)

问题描述

我正在处理一个查询，该查询应该返回将由报表使用的数据集.假设我有一个表 dbo.payments ，其数据如下所示:

I am working on a query that's supposed to return a data set that will be consumed by a report. Suppose i have a table dbo.payments with data as shown below:

-----------------------------------------------
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).

Scenario #1: I want to write a query that will give me the results as shown below. Please not that the percentage is against the SUM(Commission) of all time 240.00 regardless of the where clause.

-----------------------------------------------------
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新手，我能走的最远的是:.

Am a SQL newbie, the furthest i could go was:.

选择[交易日期]作为日期，客户，总计SUM([COMMISSION])作为总计，([COMMISSION]/(SELECT DUM.[COMMISSION])来自DBO.PAYMENTS))作为'％COMMISSION'来自DBO.PAYMENTS GROUP BY [交易日期]，客户，委员会

无论WHERE子句如何，我如何达到期望的结果:在'21/01/2018'和'24/01/2018'之间的[交易日期] 将总数减少为 170.00

How to i achieve the desired result, regardlessor the WHERE clause:i.e. WHERE [Trade Date] BETWEEN '21/01/2018' AND '24/01/2018' which reduces the total to 170.00

推荐答案

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

This could be solved using SQL Server window functions, like :

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

数据库小提琴上的演示 :

 Customer | Total_Commission | Trade_Date          | %Commission
 :------- | :--------------- | :------------------ | :----------
 AKBMOBIL | 50.00            | 22/01/2018 00:00:00 | 0.208333   
 AMIRALIS | 30.00            | 22/01/2018 00:00:00 | 0.125000   
 AMIRALIS | 10.00            | 23/01/2018 00:00:00 | 0.041666   
 AMIRALIS | 60.00            | 24/01/2018 00:00:00 | 0.250000   
 BRUSSASH | 30.00            | 22/01/2018 00:00:00 | 0.125000   
 BRUSSASH | 50.00            | 23/01/2018 00:00:00 | 0.208333   
 BRUSSASH | 10.00            | 24/01/2018 00:00:00 | 0.041666   

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

PS : if you need to filter the output by date, you can simply add a WHERE clause to the query. This will filter both computations.

这篇关于计算指定条件下总计的百分比(SQL Server)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！