At SQLstream, we have come across an interesting query pattern that seems to be difficult to express in standard SQL (SQL:2003 or SQL:2008). It turns out to be applicable to regular SQL as well as streaming SQL, and therefore it would make sense as an extension to the SQL standard.<br /><br />First some background, for those of you who don't fall asleep every night reading the SQL standard. There are two kinds of aggregation in standard SQL: windowed aggregation, of the form<br /><blockquote><span style="font-style: italic;">function</span>(<span style="font-style: italic;">arg</span> {, <span style="font-style: italic;">arg</span>}) OVER <span style="font-style: italic;">window-specification</span></blockquote>and grouped aggregation, which is of the form<br /><blockquote style="font-family:courier new;"><span style="font-style: italic;">function</span>(<span style="font-style: italic;">arg</span> {, <span style="font-style: italic;">arg</span>})</blockquote>and requires a GROUP BY clause. (If the GROUP BY clause is not present, 'GROUP BY ()' is assumed.)<br /><br />According to the standard, these two forms should never meet. It is illegal to use a windowed aggregation in a SELECT that has a GROUP BY, or to mix grouped aggregation and windowed aggregation in the same SELECT. (It's OK to use one in a sub-query and another in an enclosing query.)<br /><br />However, here is a very reasonable query that is difficult to express in standard SQL: Given a record of every trade on a stock exchange, give me the volume and closing price of each ticker symbol. You might try<br /><blockquote style="font-family: courier new;"><b>SELECT<br />&nbsp;&nbsp;&nbsp;&nbsp;day,<br />&nbsp;&nbsp;&nbsp;&nbsp;ticker,<br />&nbsp;&nbsp;&nbsp;&nbsp;SUM(shares) AS volume,<br />&nbsp;&nbsp;&nbsp;&nbsp;LAST_VALUE(price) AS closingPrice<br />FROM Trades<br />GROUP BY day, ticker</b></blockquote>but this is illegal SQL. Why is it illegal? Because the LAST_VALUE function (like FIRST_VALUE and RANK) is a windowed aggregate function and is only meaningful on an ordered set.<br /><br />To introduce the notion of ordering, I propose that the following query should be valid:<br /><blockquote style="font-family: courier new;"><b>SELECT<br />&nbsp;&nbsp;&nbsp;&nbsp;day,<br />&nbsp;&nbsp;&nbsp;&nbsp;ticker,<br />&nbsp;&nbsp;&nbsp;&nbsp;SUM(shares) AS volume,<br />&nbsp;&nbsp;&nbsp;&nbsp;LAST_VALUE(price) OVER (ORDER BY timeOfDay) AS closingPrice<br />FROM Trades<br />GROUP BY day, ticker</b></blockquote>With the OVER clause, LAST_VALUE is now a windowed aggregate function within the context of a GROUP BY query, which was previously illegal. Every windowed aggregate is applied to a window, so what is the window in this case? We want the window to contain all of the rows with the same day and ticker value, and to be sorted by timeOfDay. In other words, the window inherits the GROUP BY columns as its implicit PARTITION BY clause. It is as if they had written<br /><blockquote style="font-family: courier new;"><b>LAST_VALUE(price) OVER (PARTITION BY day, ticker ORDER BY timeOfDay)</b></blockquote>Now, if you know that I work for SQLstream, you will guess that I am motivated to make this work for streaming queries. A streaming aggregation query over the Trades stream would look like this:<br /><blockquote style="font-family: courier new;"><b>SELECT STREAM<br />&nbsp;&nbsp;&nbsp;&nbsp;day,<br />&nbsp;&nbsp;&nbsp;&nbsp;ticker,<br />&nbsp;&nbsp;&nbsp;&nbsp;SUM(shares) AS volume,<br />&nbsp;&nbsp;&nbsp;&nbsp;LAST_VALUE(price) OVER (ORDER BY timeOfDay) AS closingPrice<br />FROM Trades<br />GROUP BY day, ticker</b></blockquote>This is identical to the traditional, non-streaming SQL above, except for the STREAM keyword that tells SQLstream that the result of the query should be a stream.<br /><br />In idiomatic SQLstream SQL, we would typically express the query as follows:<br /><blockquote style="font-family: courier new;"><b>SELECT STREAM<br />&nbsp;&nbsp;&nbsp;&nbsp;FLOOR(t.ROWTIME TO DAY),<br />&nbsp;&nbsp;&nbsp;&nbsp;ticker,<br />&nbsp;&nbsp;&nbsp;&nbsp;SUM(shares) AS volume,<br />&nbsp;&nbsp;&nbsp;&nbsp;LAST_VALUE(price) OVER () AS closingPrice<br />FROM Trades<br />GROUP BY FLOOR(t.ROWTIME TO DAY), ticker</b></blockquote>This form uses SQLstream's system ROWTIME column and the 'FLOOR(datetime expression TO time unit)' operator, and so can dispense with the day and timeOfDay columns. Also, streams are ordered by ROWTIME by default, so we can abbreviate 'OVER (ORDER BY ROWTIME)' to 'OVER ()'. This form is more terse, and more typical of how the query would be written in a SQLstream application, but the previous form works also.<br /><br />The end result is powerful and, I think, consistent with the spirit of standard SQL.

More...