SQL-запрос для 7-дневной прокрутки в SQL Server

У меня есть таблица ежечасного использования продукта (сколько раз продукт используется) -

ID (bigint)| ProductId (tinyint)| Date (int - YYYYMMDD) | Hour (tinyint)| UsageCount (int)
#|1 | 20140901 | 0 | 10
#|1 | 20140901 | 1 | 15
#|1 | 20140902 | 5 | 25
#|1 | 20140903 | 5 | 25
#|1 | 20140904 | 3 | 25
#|1 | 20140905 | 7 | 25
#|1 | 20140906 | 10 | 25
#|1 | 20140907 | 9 | 25
#|1 | 20140908 | 5 | 25
#|2 | 20140903 | 16 | 10
#|2 | 20140903 | 13 | 115

Аналогично, у меня есть данные об использовании для 4 разных продуктов (ProductId от 1 до 4), хранящихся в течение каждого часа в таблице product_usage. Как вы можете себе представить, он постоянно растет, поскольку ночной процесс ETL выгружает данные за весь предыдущий день. Если продукт не используется в любой час дня, запись в этот час не будет отображаться в этой таблице. Аналогичным образом, если продукт не используется в течение всего дня, в таблице не будет записи за этот день в таблице. Мне нужно создать отчет, который дает ежедневное использование и последние 7 дней скользящего среднего -

Например:

ProductId | Date | DailyUsage | RollingAverage
1 | 20140901 | sum of usages of that day | (Sum of usages from 20140901 through 20140826) / 7
1 | 20140901 | sum of usages of that day | (Sum of usages from 20140901 through 20140826) / 7
1 | 20140902 | sum of usages of that day | (Sum of usages from 20140902 through 20140827) / 7
2 | 20140902 | sum of usages of that day | (Sum of usages from 20140902 through 20140827) / 7

И так далее.. Я планирую создать индексированный просмотр на SQL-сервере 2014. Можете ли вы подумать об эффективном запросе SQL для этого?

Ответ 1

Try:

select x.*,
       avg(dailyusage) over(partition by productid order by productid, date rows between 6 preceding and current row) as rolling_avg
  from (select productid, date, sum(usagecount) as dailyusage
          from tbl
         group by productid, date) x

Скрипка:

http://sqlfiddle.com/#!6/f674a7/4/0

Замените "avg (dailusage) over...." с суммой (а не avg), если то, что вы действительно хотите, это сумма за прошедшую неделю. В своем названии вы говорите, что хотите среднего, но позже вы говорите, что хотите получить сумму. Запрос должен быть таким же, кроме этого, поэтому используйте то, что вам действительно нужно.

Как было указано Гордоном, это в основном среднее из последних 6 дат, в которых использовался продукт, что может быть больше, чем только последние 6 дней, если на столе есть дни без каких-либо строк для этого продукта, потому что он вообще не использовался. Чтобы обойти это, вы можете использовать таблицу дат и таблицу продуктов.

Ответ 2

Вы должны быть осторожны, если вы можете потерять данные в течение нескольких дней. Если я предполагаю, что каждый день есть данные для какого-либо продукта, то этот подход будет работать:

select p.productid, d.date, sum(usagecount),
       sum(sum(usagecount)) over (partition by p.productid order by d.date
                                  rows between 6 preceding and current row) as Sum7day
from (select distinct productid from hourly) p cross join
     (select distinct date from hourly) d left join
     hourly h
     on h.productid = p.productid and h.date = p.date
group by p.productid, d.date;

Ответ 3

XZZA ZA Z Az SACR2XC4WT VGY4TFC YYVGWTAAA WWWWWWWW Dssafffcgetgfdg sgfv g dfs df ef vdcsdtg tg rf sfs g ert rtdf g