[SAMZA-551] SQL grammar support for window operator

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Won't Fix
• Affects Version/s: 0.9.0
• Component/s: sql
• Assignee: Unassigned
• Reporter: Yi Pan
• Created: 09/Feb/15 23:42
• Updated: 17/Apr/15 01:47
• Resolved: 26/Mar/15 23:34

Description

Consider that we want to have a count of stock trades (as a infinite stream) happened in the last hour, but only every 11min. It is easy to write the first part in sqlstream as:

   SELECT STREAM rowtime, count(*) OVER (ORDER BY rowtime RANGE INTERVAL '1' HOUR PROCEDING)
       FROM Trades

The above will create a stream of counts that happened every hour continuously as rows are scanned.

Now here is the question:

1. how do we have the count every 11min instead of as the row comes in? As we discussed before, there are examples that we can create by doing truncating / grouping on the rowtime to "sample" the continuous moving counting window to get a count every 11min. But that has two issues:
   • From implementation point of view, there is no efficiency improvement since the system still computes the count for each and every row comes in
   • If Samza implement a more efficient tumbling window operator, there is no easy way to identify the section of SQL statement that can map to the more efficient tumbling window operator, as the sampling is done via math / group-by aggregation instead of window spec
2. if there is no row in Trades between 12:00pm to 2:00pm, how do we tell the system to still generate 0 counts for the time moments: 12:11pm, 12:22pm, 12:33pm, etc.? Or, those rows are delayed in the delivery in the system and user wants to ignore late-arrival of messages after 5min timeout to close the counting window? How can we support that use case w/o breaking SQL grammar?

Both the above issues seem to require some extension to the window spec in SQL grammar. Julian, what do you think? Is it creating too many language/parser/planner problems in SQL?