Explicit Window Example (Cache Query)

In SQL, the order in which the clauses are presented in a query string is not the order in which they are processed.

For example, following is a fairly complex example formatted to make each clause clear:

select
    tick.symbol, trade.counterpartyId, avg(tick.volume), count(*),
from
    /Trade trade,
    /StockTick
      {policy: maintain last 5 sliding
         where symbol = "TIBX" or symbol = "GOOG"
         by symbol}
    tick
where
    trade.settlestatus = "FULLY_SETTLED"
    and
    trade.securityId = tick.symbol
group by
    tick.symbol,
    trade.counterpartyId
having
    count(*) > 2;

In fact, the clauses are processed in the following order, as shown in How a Query String is Processed:

from (including stream clause)

where

group by (including having)

select

Of special interest is how the where clause in the stream policy operates with the main where clause; and how the stream policy can create multiple windows.