locked
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.

    Thanks 


    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
    (
        SELECT
            Category,
            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
    (
        SELECT
            Category,
            System.Timestamp HopEnd,
            date,
            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
    (
        SELECT 
            Category,
            HopEnd,
            date,
            SUMQt = CASE
                WHEN DAY(HopEnd) = DAY(date) OR DATEPART(hour, HopEnd) = DATEPART(hour, date) THEN SUMQt
                ELSE 0
            END
        FROM hopping
    )
    
    SELECT Category,
            HopEnd,
            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