[SQL] SQL Server의 7 일 롤링 평균에 대한 SQL 쿼리

SQL Server의 7 일 롤링 평균에 대한 SQL 쿼리

나는 (제품 사용 횟수) 시간당 제품 사용 데이터의 테이블이 -

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

마찬가지로, 나는 product_usage 테이블에있는 모든 시간 동안 보관 (1 ~ 4에서 제품 ID) 4 개 가지 제품에 대한 사용 데이터를 가지고있다. 당신이 상상할 수 있듯이 야간 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 쿼리 생각할 수 SQL 서버 2014에서 인덱싱 된 뷰를 만들 계획입니까?

해결법

1. ==============================

   1.시험:

   시험:

   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

   교체 "평균 (dailusage) 이상 ...."당신이 정말 원하는 것은 지난 주에 합계 경우 (오히려 평균 이상) 액으로한다. 제목에서 당신은 당신이 평균을 원하지만 나중에 당신이 합계를 싶다고 말한다. 당신이 실제로 원하는 사용 중 있도록 쿼리는, 그 이외의 동일해야합니다.

   으로이 기본적으로는 년후 때문에 일 '테이블에 해당 제품에 대한 행을하지 않고있을 경우 더 다만 지난 6 일 이상이 될 수있는 제품을 사용하는 지난 6 일자의 평균 고든에 의해 지적되었다 t는 전혀 사용. 당신이 날짜 테이블과 제품 테이블을 사용할 수 있음을 해결하기 위해.

2. ==============================

   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;
   

from https://stackoverflow.com/questions/25922379/sql-query-for-7-day-rolling-average-in-sql-server by cc-by-sa and MIT license