周一的 TSQL 窗口功能

Question

我正在寻找一个查询，该查询的总和累积在列上，但它需要每周一重新启动，但似乎无法使用窗口函数进行这是我的代码：

    WITH t  (fecha, nombre_dia, distribuidor, monto)as (
SELECT CAST(t.fecha AS DATE)fecha
, CASE WHEN datename(dw,t.fecha)='Monday' then 1 else 2 end  nombre_dia
,d.NombreDistribuidor distribuidor
,sum(tr.Monto)monto
from HechosTransferencia tr inner join DimensionTiempo t on tr.DimensionTiempoId=t.DimensionTiempoId
inner join DimensionDistribuidor d on d.DimensionDistribuidorId=tr.DimensionDistribuidorId
WHERE        (t.Fecha BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, - 2, GETDATE()) - 2, 0) AND GETDATE())
group by CAST(t.fecha AS DATE)
, CASE WHEN datename(dw,t.fecha)='Monday' then 1 else 2 end ,d.NombreDistribuidor
)SELECT *, 
SUM(monto) OVER(PARTITION BY nombre_dia, distribuidor
                ORDER BY fecha, nombre_dia, distribuidor  ROWS UNBOUNDED PRECEDING) AS RunningTotal
 from t where distribuidor='XYZ'

一个简单的输出示例是

monday           XYZ    5      5
tuesday          XYZ    1      6
wednesday        XYZ    2      8
..
....
monday           XYZ    2       2         -- restart value every monday
tuesday          XYZ    2       4
..
...

Answer 1

我刚刚在rextester中设置了一个示例：http://rextester.com/MEXHM63945

与其尝试打破每个星期一的累积总和，不如简单地将星期一设置为一周的第一天，并按提供程序和 WeekOfMonth 进行分区 SUM（）。

我还没有测试过它，但我认为您可以在不使用 CTE 窗口功能的情况下获得它。（看看答案的结尾。

-- Set monday first day of week
SET DATEFIRST 1;
create table #data ([date] datetime, provider varchar(10), value int);
insert into #data values
('2017-01-02', 'XYZ', 1),
('2017-01-03', 'XYZ', 5),
('2017-01-04', 'XYZ', 3),
('2017-01-05', 'XYZ', 5),
('2017-01-06', 'XYZ', 4),
('2017-01-07', 'XYZ', 11),
('2017-01-08', 'XYZ', 11),
('2017-01-09', 'XYZ', 1),
('2017-01-10', 'XYZ', 5),
('2017-01-11', 'XYZ', 3),
('2017-01-12', 'XYZ', 5),
('2017-01-13', 'XYZ', 4),
('2017-01-14', 'XYZ', 11),
('2017-01-15', 'XYZ', 11);

SELECT provider, DATEPART(WEEK, date) as week_of_year, value,  
       sum(value) OVER (Partition by provider, DATEPART(WEEK, date) ORDER BY date, provider) Acm
FROM #data
ORDER BY date, provider;

结果如下：

+----------+--------------+-------+-----+
| provider | week_of_year | value | Acm |
+----------+--------------+-------+-----+
|    XYZ   |       2      |   1   |  1  |
+----------+--------------+-------+-----+
|    XYZ   |       2      |   5   |  6  |
+----------+--------------+-------+-----+
|    XYZ   |       2      |   3   |  9  |
+----------+--------------+-------+-----+
|    XYZ   |       2      |   5   |  14 |
+----------+--------------+-------+-----+
|    XYZ   |       2      |   4   |  18 |
+----------+--------------+-------+-----+
|    XYZ   |       2      |   11  |  29 |
+----------+--------------+-------+-----+
|    XYZ   |       2      |   11  |  40 |
+----------+--------------+-------+-----+
|    XYZ   |       3      |   1   |  1  |
+----------+--------------+-------+-----+
|    XYZ   |       3      |   5   |  6  |
+----------+--------------+-------+-----+
|    XYZ   |       3      |   3   |  9  |
+----------+--------------+-------+-----+
|    XYZ   |       3      |   5   |  14 |
+----------+--------------+-------+-----+
|    XYZ   |       3      |   4   |  18 |
+----------+--------------+-------+-----+
|    XYZ   |       3      |   11  |  29 |
+----------+--------------+-------+-----+
|    XYZ   |       3      |   11  |  40 |
+----------+--------------+-------+-----+

我认为这应该可以完成这项工作：

SELECT 
    CAST(t.fecha AS DATE) fecha
    , d.NombreDistribuidor distribuidor
    , SUM(tr.Monto) OVER (PARTITION BY d.NombreDistribuidor, DATEPART(week, CAST(t.fecha AS DATE))
                          ORDER BY CAST(t.fecha AS DATE), d.NombreDistribuidor) monto
FROM 
    HechosTransferencia tr 
    INNER JOIN DimensionTiempo t ON tr.DimensionTiempoId=t.DimensionTiempoId
    INNER JOIN DimensionDistribuidor d ON d.DimensionDistribuidorId=tr.DimensionDistribuidorId
WHERE        
    (t.Fecha BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, - 2, GETDATE()) - 2, 0) AND GETDATE())
GROUP BY 
    CAST(t.fecha AS DATE), d.NombreDistribuidor;

Answer 2

按照McNets提出的解决方案，我的问题的答案是这样的：

SET DATEFIRST 1;
WITH t  (fecha, distribuidor, monto)as (
SELECT CAST(t.fecha AS DATE)fecha
,d.NombreDistribuidor distribuidor
,sum(tr.Monto)monto
 from HechosTransferencia tr inner join DimensionTiempo t on tr.DimensionTiempoId=t.DimensionTiempoId
inner join DimensionDistribuidor d on d.DimensionDistribuidorId=tr.DimensionDistribuidorId
WHERE        (t.Fecha BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, - 2, GETDATE()) - 2, 0) AND GETDATE())
group by CAST(t.fecha AS DATE)
,d.NombreDistribuidor
)SELECT *
,sum(monto) OVER (Partition by distribuidor, DATEPART(WEEK, fecha) ORDER BY fecha, distribuidor)RunningTotal
 from t;