2ndSem

10.8

1 min read

Consider the following query using the tumbling window operator:

SELECT itemid, System.Timestamp as window_end, SUM(amount)
FROM order TIMESTAMP BY datetime
GROUP BY itemid, TUMBLINGWINDOW(hour, 1)

Give an equivalent query using normal SQL constructs, without using the tumbling window operator. You can assume that the timestamp can be converted to an integer value that represents the number of seconds elapsed since (say) midnight, January 1, 1970, using the function to_seconds(timestamp). You can also assume that the usual arithmetic functions are available, along with the function floor(a) which returns the largest integer .

Remember that the schema of the relation order is order(orderid, datetime, itemid, amount). Divide by 3600, and take floor, group by that. To output the timestamp of the window end, add 1 to hour and multiply by 3600.

WITH o(itemid, hour, amount) AS ( 
    SELECT itemid, floor(to_seconds(datetime)/3600), amount
    FROM order 
)
SELECT itemid, (hour + 1) * 3600 as window_end, SUM(amount)
FROM o
GROUP BY itemid, hour;

Graph View