先週の火曜日からのSQLローリング28日平均

SaintFrag

先週の火曜日からの曜日に基づいて、28日間のローリング平均を計算する必要があります。したがって、DateStampから、次の情報を取得できます。

(datestamp+1-datepart(weekday, datestamp+5)) as Tuesday
datepart(weekday,DateStamp) as WeekDayNum

以下がExcelのテーブルである場合、4番目の列の擬似コード(上記の2つが使用可能であると想定)は次のようになります。

=AVERAGEIFS([DailySales],[DateStamp],">="&[@Tuesday]-28,[DateStamp],"<"&[@Tuesday],[StoreID],[@StoreID],[WeekdayNum],[@WeekdayNum])

つまり、基本的に曜日ごとに、4つのデータポイントを平均しています。

サンプルデータセットは次のとおりです。

DateStamp StoreID DailySales
2020-12-1 0230 6978.18
2020-12-1 0231 2952.64
2020-12-2 0230 3346.44
2020-12-2 0231 1171.74
2020-12-3 0230 7253.61
2020-12-3 0231 2546.55
2020-12-4 0230 759.41
2020-12-4 0231 701.69
2020-12-5 0230 2709.39
2020-12-5 0231 884.5
2020-12-6 0230 1795.58
2020-12-6 0231 2529.76
2020-12-7 0230 3454.64
2020-12-7 0231 3378
2020-12-8 0230 2376.15
2020-12-8 0231 5530.28
2020-12-9 0230 667.42
2020-12-9 0231 742.53
2020-12-10 0230 1246.98
2020-12-10 0231 1390.54
2020-12-11 0230 2900.85
2020-12-11 0231 2975.06
2020-12-12 0230 505.24
2020-12-12 0231 4161.02
2020-12-13 0230 343.42
2020-12-13 0231 3673.57
2020-12-14 0230 971.15
2020-12-14 0231 3293.06
2020-12-15 0230 10732.5
2020-12-15 0231 2032.61
2020-12-16 0230 1435.91
2020-12-16 0231 2974.87
2020-12-17 0230 8782.72
2020-12-17 0231 6669.7
2020-12-18 0230 4844.52
2020-12-18 0231 4939.37
2020-12-19 0230 8414.46
2020-12-19 0231 4319.32
2020-12-20 0230 1623.25
2020-12-20 0231 10730.4
2020-12-21 0230 468.86
2020-12-21 0231 5573.96
2020-12-22 0230 970.43
2020-12-22 0231 14583.55
2020-12-23 0230 2550.53
2020-12-23 0231 961.46
2020-12-24 0230 541.19
2020-12-24 0231 480.22
2020-12-26 0230 2077.46
2020-12-26 0231 4444.48
2020-12-27 0230 500.02
2020-12-27 0231 5203.68
2020-12-28 0230 3001.62
2020-12-28 0231 2944.84
2020-12-29 0230 507.54
2020-12-29 0231 9980.34
2020-12-30 0230 3646.58
2020-12-30 0231 2280.9
2020-12-31 0230 818.78
2020-12-31 0231 1786.65
2021-1-2 0230 1174
2021-1-2 0231 3919.76
2021-1-3 0230 929.56
2021-1-3 0231 1728.31
2021-1-4 0230 1768.55
2021-1-4 0231 3510.57
2021-1-5 0230 2740.26
2021-1-5 0231 7827.08
2021-1-6 0230 5429.5
2021-1-6 0231 1415.73
2021-1-7 0230 2518.84
2021-1-7 0231 2497.84
2021-1-8 0230 1366.57
2021-1-8 0231 3815.87
2021-1-9 0230 628.16
2021-1-9 0231 1274.79
2021-1-10 0230 375.99
2021-1-10 0231 1038.9
2021-1-11 0230 1081.46
2021-1-11 0231 1466.84
2021-1-12 0230 1704.89
2021-1-12 0231 1937.37
2021-1-13 0230 2393.86
2021-1-13 0231 1888.64
2021-1-14 0230 2920.17
2021-1-14 0231 2022.57
2021-1-15 0230 1814.29
2021-1-15 0231 1001.96
2021-1-16 0230 6576.04
2021-1-16 0231 7485.34
2021-1-17 0230 814.81
2021-1-17 0231 4564.34
2021-1-18 0230 2869.94
2021-1-18 0231 1068.95
2021-1-19 0230 4603.54
2021-1-19 0231 2306.57
2021-1-20 0230 1339.23
2021-1-20 0231 4092.1
2021-1-21 0230 2145.86
2021-1-21 0231 12138.5
2021-1-22 0230 2418.59
2021-1-22 0231 7735.85
2021-1-23 0230 3772.35
2021-1-23 0231 1965.2
2021-1-24 0230 4534.03
2021-1-24 0231 2994.9
2021-1-25 0230 702.87
2021-1-25 0231 631.4
2021-1-26 0230 1632.65
2021-1-26 0231 2986.1

Sorry, there's probably a better way to have done that, but I wanted to make it as simple as possible.

I can do a BASIC rolling 28-day average with the windowing function:

select *
    ,Rolling28 = avg(DailySales) over (Partition by StoreID order by datestamp rows between 28 preceding and current row)
From ytd_sales
order by storeid, datestamp

However, I'm stuck on getting from there to something that looks like this (shortened) result:

DateStamp StoreID Past_28_AVG
2021-1-24 0230 655.1
2021-1-24 0231 3133.81
2021-1-25 0230 2180.39
2021-1-25 0231 2247.8
2021-1-26 0230 2389.06
2021-1-26 0231 5512.84
Sander

Using a couple cross apply joins to reach a solution.

  1. First fetch the previous Tuesday (or the current date if it is a Tuesday).
  2. Then select the 4 dates based on the previous result.

Sample data

create table Sales
(
  DateStamp date,
  StoreId nvarchar(4),
  DailySales money
);

insert into Sales (DateStamp, StoreID, DailySales) values
('2020-12-1'    ,'0230',    6978.18),
('2020-12-1'    ,'0231',    2952.64),
('2020-12-2'    ,'0230',    3346.44),
('2020-12-2'    ,'0231',    1171.74),
('2020-12-3'    ,'0230',    7253.61),
('2020-12-3'    ,'0231',    2546.55),
('2020-12-4'    ,'0230',    759.41),
('2020-12-4'    ,'0231',    701.69),
('2020-12-5'    ,'0230',    2709.39),
('2020-12-5'    ,'0231',    884.5),
('2020-12-6'    ,'0230',    1795.58),
('2020-12-6'    ,'0231',    2529.76),
('2020-12-7'    ,'0230',    3454.64),
('2020-12-7'    ,'0231',    3378),
('2020-12-8'    ,'0230',    2376.15),
('2020-12-8'    ,'0231',    5530.28),
('2020-12-9'    ,'0230',    667.42),
('2020-12-9'    ,'0231',    742.53),
('2020-12-10'   ,'0230',    1246.98),
('2020-12-10'   ,'0231',    1390.54),
('2020-12-11'   ,'0230',    2900.85),
('2020-12-11'   ,'0231',    2975.06),
('2020-12-12'   ,'0230',    505.24),
('2020-12-12'   ,'0231',    4161.02),
('2020-12-13'   ,'0230',    343.42),
('2020-12-13'   ,'0231',    3673.57),
('2020-12-14'   ,'0230',    971.15),
('2020-12-14'   ,'0231',    3293.06),
('2020-12-15'   ,'0230',    10732.5),
('2020-12-15'   ,'0231',    2032.61),
('2020-12-16'   ,'0230',    1435.91),
('2020-12-16'   ,'0231',    2974.87),
('2020-12-17'   ,'0230',    8782.72),
('2020-12-17'   ,'0231',    6669.7),
('2020-12-18'   ,'0230',    4844.52),
('2020-12-18'   ,'0231',    4939.37),
('2020-12-19'   ,'0230',    8414.46),
('2020-12-19'   ,'0231',    4319.32),
('2020-12-20'   ,'0230',    1623.25),
('2020-12-20'   ,'0231',    10730.4),
('2020-12-21'   ,'0230',    468.86),
('2020-12-21'   ,'0231',    5573.96),
('2020-12-22'   ,'0230',    970.43),
('2020-12-22'   ,'0231',    14583.55),
('2020-12-23'   ,'0230',    2550.53),
('2020-12-23'   ,'0231',    961.46),
('2020-12-24'   ,'0230',    541.19),
('2020-12-24'   ,'0231',    480.22),
('2020-12-26'   ,'0230',    2077.46),
('2020-12-26'   ,'0231',    4444.48),
('2020-12-27'   ,'0230',    500.02),
('2020-12-27'   ,'0231',    5203.68),
('2020-12-28'   ,'0230',    3001.62),
('2020-12-28'   ,'0231',    2944.84),
('2020-12-29'   ,'0230',    507.54),
('2020-12-29'   ,'0231',    9980.34),
('2020-12-30'   ,'0230',    3646.58),
('2020-12-30'   ,'0231',    2280.9),
('2020-12-31'   ,'0230',    818.78),
('2020-12-31'   ,'0231',    1786.65),
('2021-1-2'     ,'0230',    1174),
('2021-1-2'     ,'0231',    3919.76),
('2021-1-3'     ,'0230',    929.56),
('2021-1-3'     ,'0231',    1728.31),
('2021-1-4'     ,'0230',    1768.55),
('2021-1-4'     ,'0231',    3510.57),
('2021-1-5'     ,'0230',    2740.26),
('2021-1-5'     ,'0231',    7827.08),
('2021-1-6'     ,'0230',    5429.5),
('2021-1-6'     ,'0231',    1415.73),
('2021-1-7'     ,'0230',    2518.84),
('2021-1-7'     ,'0231',    2497.84),
('2021-1-8'     ,'0230',    1366.57),
('2021-1-8'     ,'0231',    3815.87),
('2021-1-9'     ,'0230',    628.16),
('2021-1-9'     ,'0231',    1274.79),
('2021-1-10'    ,'0230',    375.99),
('2021-1-10'    ,'0231',    1038.9),
('2021-1-11'    ,'0230',    1081.46),
('2021-1-11'    ,'0231',    1466.84),
('2021-1-12'    ,'0230',    1704.89),
('2021-1-12'    ,'0231',    1937.37),
('2021-1-13'    ,'0230',    2393.86),
('2021-1-13'    ,'0231',    1888.64),
('2021-1-14'    ,'0230',    2920.17),
('2021-1-14'    ,'0231',    2022.57),
('2021-1-15'    ,'0230',    1814.29),
('2021-1-15'    ,'0231',    1001.96),
('2021-1-16'    ,'0230',    6576.04),
('2021-1-16'    ,'0231',    7485.34),
('2021-1-17'    ,'0230',    814.81),
('2021-1-17'    ,'0231',    4564.34),
('2021-1-18'    ,'0230',    2869.94),
('2021-1-18'    ,'0231',    1068.95),
('2021-1-19'    ,'0230',    4603.54),
('2021-1-19'    ,'0231',    2306.57),
('2021-1-20'    ,'0230',    1339.23),
('2021-1-20'    ,'0231',    4092.1),
('2021-1-21'    ,'0230',    2145.86),
('2021-1-21'    ,'0231',    12138.5),
('2021-1-22'    ,'0230',    2418.59),
('2021-1-22'    ,'0231',    7735.85),
('2021-1-23'    ,'0230',    3772.35),
('2021-1-23'    ,'0231',    1965.2),
('2021-1-24'    ,'0230',    4534.03),
('2021-1-24'    ,'0231',    2994.9),
('2021-1-25'    ,'0230',    702.87),
('2021-1-25'    ,'0231',    631.4),
('2021-1-26'    ,'0230',    1632.65),
('2021-1-26'    ,'0231',    2986.1);

Solution

select s.DateStamp,
    -- pt.DateStamp as PreviousTuesday,
       s.StoreId,
       ra.Avg28
from Sales s
cross apply ( select top 1 s2.DateStamp
              from Sales s2
              where s2.StoreId = s.StoreId
                and s2.DateStamp <= s.DateStamp
                and datepart(weekday, s2.DateStamp) = 3 -- Tuesday
              order by s2.DateStamp desc ) pt -- previous Tuesday
cross apply ( select avg(s3.DailySales) as Avg28
              from Sales s3
              where s3.StoreId = s.StoreId
                and datepart(weekday, s3.DateStamp) = datepart(weekday, s.DateStamp)
                and s3.DateStamp < pt.DateStamp
                and s3.DateStamp >= dateadd(day, -28, pt.DateStamp)) ra -- rolling average

Result

DateStamp   StoreId  Avg28
----------  -------  ---------
...         ...      ... 
2021-01-24  0230      655.095
2021-01-24  0231     3133.8075
2021-01-25  0230     2180.3925
2021-01-25  0231     2247.8
2021-01-26  0230     2389.0575
2021-01-26  0231     5512.84

Fiddle to see things in action.

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集
0

コメントを追加

0

関連記事

Related 関連記事

ホットタグ

アーカイブ