自定义金额计算

我有下表

Date      Value promo   item
01/01/2011  626 0   1230
01/02/2011  231 1   1230
01/03/2011  572 1   1230
01/04/2011  775 1   1230
01/05/2011  660 1   1230
01/06/2011  662 1   1230
01/07/2011  541 1   1230
01/08/2011  849 1   1230
01/09/2011  632 1   1230
01/10/2011  906 1   1230
01/11/2011  961 1   1230
01/12/2011  361 0   1230
01/01/2012  461 0   1230
01/02/2012  928 1   1230
01/03/2012  855 0   1230
01/04/2012  605 0   1230
01/05/2012  83  0   1230
01/06/2012  44  0   1230
01/07/2012  382 0   1230
01/08/2012  862 0   1230
01/09/2012  549 0   1230
01/10/2012  632 0   1230
01/11/2012  2   0   1230
01/12/2012  26  0   1230

我尝试计算平均总和 (SoldAt)/最小日期和最大日期之间的天数回滚前 28 行（4 周），其中 promo =1 by Article

无论当天折扣如何，平滑期均为 4 周。

也就是说，如果该商品在过去 4 周内促销了一周，则不考虑销售周的促销情况，平滑时间将超过 5 周。

如何计算 promo =1 的前 4 周/28 行数据按时间顺序？

I try

         CREATE TABLE #RollingTotalsExample
(
    [Date]     DATE 
    ,[Value]   INT
    ,promo float 
    ,item int 
);

INSERT INTO #RollingTotalsExample
SELECT '2011-01-01',626,1,1230
UNION ALL SELECT '2011-02-01',231,1,1230 UNION ALL SELECT '2011-03-01',572,1,1230
UNION ALL SELECT '2011-04-01',775,1,1230 UNION ALL SELECT '2011-05-01',660,1,1230
UNION ALL SELECT '2011-06-01',662,1,1230 UNION ALL SELECT '2011-07-01',541,1,1230
UNION ALL SELECT '2016-08-01',849,1,1230 UNION ALL SELECT '2016-09-01',632,1,1230
UNION ALL SELECT '2016-10-01',906,1,1230 UNION ALL SELECT '2016-11-01',961,1,1230
UNION ALL SELECT '2016-04-01',775,1,1230 UNION ALL SELECT '2016-05-01',660,1,1230
UNION ALL SELECT '2016-06-01',662,1,1230 UNION ALL SELECT '2016-07-01',541,1,1230
UNION ALL SELECT '2016-08-01',849,1,1230 UNION ALL SELECT '2016-09-01',632,1,1230
UNION ALL SELECT '2016-10-01',906,1,1230 UNION ALL SELECT '2016-11-01',961,1,1230
UNION ALL SELECT '2016-12-01',361,0,1230 UNION ALL SELECT '2012-01-01',461,0,1230
UNION ALL SELECT '2012-02-01',928,0,1230 UNION ALL SELECT '2012-03-01',855,0,1230
UNION ALL SELECT '2012-04-01',605,0,1230 UNION ALL SELECT '2012-05-01',83,0,1230
UNION ALL SELECT '2012-06-01',44,0,1230 UNION ALL SELECT '2012-07-01',382,0,1230
UNION ALL SELECT '2012-08-01',862,0,1230 UNION ALL SELECT '2012-09-01',549,0,1230
UNION ALL SELECT '2012-10-01',632,0,1230 UNION ALL SELECT '2012-11-01',2,0,1230
UNION ALL SELECT '2012-12-01',26,0,1230;
 SELECT * FROM #RollingTotalsExample;

    -- Rolling twelve month total by using INNER JOIN
    SELECT a.[Date]
        ,Value=MAX(CASE WHEN a.[Date] = b.[Date] THEN a.Value END)
        ,Rolling12Months=CASE
                            WHEN ROW_NUMBER() OVER (ORDER BY a.[Date]) < 12
                            THEN NULL
                            ELSE SUM(b.Value)
                            END
    FROM #RollingTotalsExample a
    JOIN #RollingTotalsExample b ON b.[Date] BETWEEN DATEADD(month, -11, a.[Date]) AND a.[Date]
    GROUP BY a.[Date]
    ORDER BY a.[Date];

现在如何修改查询以计算最小日期和最大日期之间的平均总和 (SoldAmt)/天数 回滚按时间排序的前 28 行数据，促销 =1 按文章

这是需要 SQL 2012 中提供的 LAG() 的替代方法，但请注意示例数据不包含每个日期之前的“2​​8 个不同天”。此外，所使用的实际数据类型未知（date/smalldatetime/datetime/datetime2），也不知道是否需要从日期截断时间。因此，有一些注意事项，此方法为 28 个不同日期创建了一系列日期范围（但如果数据未提供这些日期范围，则它们是 28 天）。这里是一个是一个sqlfiddle 演示

PostgreSQL 9.3 架构设置: （因为 SQL Server 不在 sqlfiddle 上运行）

CREATE TABLE Table1
    (theDate timestamp, Value int, promo int, item int)
;

INSERT INTO Table1
    (theDate, Value, promo, item)
VALUES
    ('2011-01-01 00:00:00', 626, 0, 1230),
    ('2011-01-02 00:00:00', 231, 1, 1230),
    ('2011-01-03 00:00:00', 572, 1, 1230),
    ('2011-01-04 00:00:00', 775, 1, 1230),
    ('2011-01-05 00:00:00', 660, 1, 1230),
    ('2011-01-06 00:00:00', 662, 1, 1230),
    ('2011-01-07 00:00:00', 541, 1, 1230),
    ('2011-01-08 00:00:00', 849, 1, 1230),
    ('2011-01-09 00:00:00', 632, 1, 1230),
    ('2011-01-10 00:00:00', 906, 1, 1230),
    ('2011-01-11 00:00:00', 961, 1, 1230),
    ('2011-01-12 00:00:00', 361, 0, 1230),
    ('2012-01-01 00:00:00', 461, 0, 1230),
    ('2012-01-02 00:00:00', 928, 1, 1230),
    ('2012-01-03 00:00:00', 855, 0, 1230),
    ('2012-01-04 00:00:00', 605, 0, 1230),
    ('2012-01-05 00:00:00', 83, 0, 1230),
    ('2012-01-06 00:00:00', 44, 0, 1230),
    ('2012-01-07 00:00:00', 382, 0, 1230),
    ('2012-01-08 00:00:00', 862, 0, 1230),
    ('2012-01-09 00:00:00', 549, 0, 1230),
    ('2012-01-10 00:00:00', 632, 0, 1230),
    ('2012-01-11 00:00:00', 2, 0, 1230),
    ('2012-01-12 00:00:00', 26, 0, 1230)
;

Query 1:

select
      t1.item
    , ranges.theStart
    , ranges.theEnd
    , sum(t1.value)
    , sum(t1.value) / 28 avg
from (
      select
            coalesce(lag(theDay,28) over(order by theDay) , theDay - INTERVAL '28 DAYS') as theStart
          , theDay as theEnd
      from (
            select distinct cast(thedate as date) theDay from Table1
            ) days
      ) ranges
inner join table1 t1 on theDate between ranges.theStart and ranges.theEnd
group by
      t1.item
    , ranges.theStart
    , ranges.theEnd

Results:

| item |                   thestart |                    theend |  sum | avg |
|------|----------------------------|---------------------------|------|-----|
| 1230 | December, 04 2010 00:00:00 | January, 01 2011 00:00:00 |  626 |  22 |
| 1230 | December, 05 2010 00:00:00 | January, 02 2011 00:00:00 |  857 |  30 |
| 1230 | December, 06 2010 00:00:00 | January, 03 2011 00:00:00 | 1429 |  51 |
| 1230 | December, 07 2010 00:00:00 | January, 04 2011 00:00:00 | 2204 |  78 |
| 1230 | December, 08 2010 00:00:00 | January, 05 2011 00:00:00 | 2864 | 102 |
| 1230 | December, 09 2010 00:00:00 | January, 06 2011 00:00:00 | 3526 | 125 |
| 1230 | December, 10 2010 00:00:00 | January, 07 2011 00:00:00 | 4067 | 145 |
| 1230 | December, 11 2010 00:00:00 | January, 08 2011 00:00:00 | 4916 | 175 |
| 1230 | December, 12 2010 00:00:00 | January, 09 2011 00:00:00 | 5548 | 198 |
| 1230 | December, 13 2010 00:00:00 | January, 10 2011 00:00:00 | 6454 | 230 |
| 1230 | December, 14 2010 00:00:00 | January, 11 2011 00:00:00 | 7415 | 264 |
| 1230 | December, 15 2010 00:00:00 | January, 12 2011 00:00:00 | 7776 | 277 |
| 1230 | December, 04 2011 00:00:00 | January, 01 2012 00:00:00 |  461 |  16 |
| 1230 | December, 05 2011 00:00:00 | January, 02 2012 00:00:00 | 1389 |  49 |
| 1230 | December, 06 2011 00:00:00 | January, 03 2012 00:00:00 | 2244 |  80 |
| 1230 | December, 07 2011 00:00:00 | January, 04 2012 00:00:00 | 2849 | 101 |
| 1230 | December, 08 2011 00:00:00 | January, 05 2012 00:00:00 | 2932 | 104 |
| 1230 | December, 09 2011 00:00:00 | January, 06 2012 00:00:00 | 2976 | 106 |
| 1230 | December, 10 2011 00:00:00 | January, 07 2012 00:00:00 | 3358 | 119 |
| 1230 | December, 11 2011 00:00:00 | January, 08 2012 00:00:00 | 4220 | 150 |
| 1230 | December, 12 2011 00:00:00 | January, 09 2012 00:00:00 | 4769 | 170 |
| 1230 | December, 13 2011 00:00:00 | January, 10 2012 00:00:00 | 5401 | 192 |
| 1230 | December, 14 2011 00:00:00 | January, 11 2012 00:00:00 | 5403 | 192 |
| 1230 | December, 15 2011 00:00:00 | January, 12 2012 00:00:00 | 5429 | 193 |

注意：对于 SQL Server

• 代替theDay - INTERVAL '28 DAYS' use dateadd(天,-28,theDay)