Cumulative Sum RRS feed

  • Question

  • Hi all,

    I'm trying to build an azure stream analytics query which output should be something like this:

    Category Hour Day Qt Sold Qt Sold Cumulative
    Cat A 8 12 12
    Cat A 9 15 27
    Cat A 10 20 47
    Cat B 8 5 5
    Cat B 9 4 9
    Cat B 10 7 16

    It's relatively straightforward to get this format without the Qt cumulative. 

    To obtain the Qt Cumulative i've tried to use an sql like SUM([Qt]) OVER (PARTITION BY [Category],[Hour of Day]  order by [Hour of Day] )  but it doesn't return the cumulative values.

    In the query im using a HoppingWindow(hour, 12 , 1).

    Appreciate any help that you could give to me because i'm a bit lost with this.


    Thursday, June 8, 2017 2:33 PM

All replies

  • The implementation of 12hrs cumulative could be tricky, especially the 12 hours window crosses 2 dates like 9PM - 9AM window.

    If you switch to 24 hours cumulative, here's a sample solution:

    WITH hourly AS
            DATETIMEFROMPARTS(DATEPART(year, System.Timestamp), DATEPART(month, System.Timestamp), DATEPART(day, System.Timestamp), 0, 0, 0, 0) as date,
            System.Timestamp Windowend,
            SUM(Qt) hourlySum
        FROM input TIMESTAMP BY OrderTimeUTC
        GROUP BY TumblingWindow(hour, 1), Category
    ,hopping AS
            System.Timestamp HopEnd,
            SUM(hourlySum) SUMQt
        FROM hourly
        GROUP BY HoppingWindow(hour, 24, 1), Category, date
        --HAVING DAY(HopEnd) = DAY(date) OR DATEPART(hour, HopEnd) = DATEPART(hour, date)
    ,filtered as
            SUMQt = CASE
                WHEN DAY(HopEnd) = DAY(date) OR DATEPART(hour, HopEnd) = DATEPART(hour, date) THEN SUMQt
                ELSE 0
        FROM hopping
    SELECT Category,
            MAX(SUMQt) AS QtDailyCumulative
    FROM filtered
    GROUP BY TumblingWindow(hour, 1), Category, hopend

    This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, June 20, 2017 7:41 PM