u-sql

Calculate moving average of a dataset

Sample representation of my dataset is below and I want to calculate the moving 7 day average of the total employees for a given set of dept, subgroup and team at a given date.

enter image description here

Is there some way I can pass this rowset to a C# method that can calculate the moving average? Is there another more efficient way to do this.

Solution

  • based on your data I did the following queries:

        SELECT * FROM
(VALUES
    (new DateTime(2019,01,01),"D1","S1","T1",20),
    (new DateTime(2019,01,02),"D1","S1","T1",33),
    (new DateTime(2019,01,03),"D1","S1","T1",78),
    (new DateTime(2010,01,05),"D1","S2","T2",77)
) AS T(date,Dept, Subgroup, Team, Total_Employees);

@moving_Average_Last_7_Days =
    SELECT DISTINCT
           current.date,
           current.Dept,
           current.Subgroup,
           current.Team,
           current.Total_Employees,
           AVG(lasts.Total_Employees) OVER(PARTITION BY lasts.Dept, lasts.Subgroup, lasts.Team,current.date) AS Moving_Average_Last_7_Days
    FROM @moving_Average_Last_7_Days AS current
         CROSS JOIN
             @moving_Average_Last_7_Days AS lasts
         WHERE (current.date - lasts.date).Days BETWEEN 0 AND 6 
;

    Please tell me if is this that you want to achieve!