sql - 显示 MTD 的总计

Question

我的表(市场)中有三列:销售员、销售额和周数。我希望能够计算 MTD(本月至今)的总数。就我而言，我希望能够提供表中从 11-18-2016 到 12-18-2016 的数据。我不知道如何在我的表中制定 MTD 列。如有任何帮助，我们将不胜感激。

select * from Market:

Salesman             Sales                    Week
Joe                   40                     12-18-2016
Mike                  27                     12-11-2016 
Todd                  29                     12-04-2016
Robert                44                     11-27-2016
Tony                  15                     11-20-2016
Morgan                60                     11-13-2016
Edwin                 25                     11-06-2016 
Josh                  19                     10-30-2016
Travis                24                     10-23-2016
Wade                  35                     10-16-2016                    

我要求的结果:

Select Salesman, Sales as 'This Week', (some formula) as 'MTD' from Market
where Week = '12-18-2016'

Salesman             This Week                  MTD
Joe                    40                       40
Mike                    0                       27
Todd                    0                       29
Robert                  0                       44
Tony                    0                       15

Answer 1

您可以使用窗口函数来完成此操作。对于一个星期，应该这样做:

select m.*
from (Select Salesman, Sales as "This Week",
             sum(Sales) over (partition by SalesMan, date_trunc('month', week)
                              order by week) as MTD
      from Market m
     ) m
where Week = '2016-12-18';  -- assumes that the date is stored in a native date/time format

over 子句中的order by 只需要支持不是最新一周的周。