This subsection describes a basic query definition example involving stock prices, in which the window, relation, and stream operations are used.
The following figure gives an example of the stock price information stream data handled in this example.
Figure 2-18 Example of stock price information stream data
![[Figure]](figure/zc021800.gif)
The stream data handled in this example is data that contains stock price information related to the stock code (stockID), the stock name (stockName), the stock price (currentPrice), and the trading volume (tradingVolume), and is time stamped by Stream Data Platform - AF.
This stream data is used to calculate the rate of increase in the stock price. The rate of price increase is calculated by comparing the current data with the data from one minute ago.
To calculate the rate of price increase, the following two types of queries are used:
- 1-minute data calculation query
REGISTER QUERY stockDStream
DSTREAM ( SELECT * FROM stock[RANGE 1 MINUTE] ); |
- The RANGE window with the interval set to one minute keeps tuples in the input relation for one minute.
- DSTREAM is specified to output tuples once their one-minute lifespan has expired. In this way, data that is one minute old is output as stream data.
- Price increase rate calculation query
REGISTER QUERY risingRate
ISTREAM ( SELECT stock.stockID, stock.stockName,
stock.currentPrice / stockDStream.currentPrice AS stockRate
FROM stock[PARTITION BY stock.stockID ROWS 1],
stockDStream[PARTITION BY stockDStream.stockID ROWS 1]
WHERE stock.stockID = stockDStream.stockID ); |
- The PARTITION BY window is used to hold the latest tuple for each stock.
- The current stock price information stream data is merged with the stream data from one minute ago to calculate the price increase rate.
The following figure shows the flow of operations for these queries.
Figure 2-19 Flow of operations for calculating stock price increase rate
![[Figure]](figure/zc021900.gif)
An explanation of the processing flow is provided below. The numbers in the explanation correspond to the numbers in the figure.
- Window operation for the query stockDStream
A window operation is executed.
The stream stock entered by specifying FROM stock is held for one minute because of the specification of [RANGE 1 MINUTE].
- Stream operation for the query stockDStream
Data that is older than one minute is output by the stream operation DSTREAM as stream data (1-minute old data).
- Window operation for the query risingRate
Two types of data, i.e., the stream stock and the result stream (1-minute old data) that was output in operation 2, are input because of the specification of FROM stock..., stockDStream....
Based on the specification of [PARTITION BY... ROWS 1], the input data is grouped for each stock code (stockID) and the latest data item is held for each stock code. As a result, the following data is held for each stock code:
- The latest data item out of the current stock price data in the input stream stock
- The latest data item out of the stock price from one minute ago in the input stream stockDStream (result stream of the query stockDStream)
- Relation operation for the query stockDStream
For each stock code, the stock code, the stock name, and the rate of price increase (current stock price
price from one minute ago) are linked.
- Stream operation for the query stockDStream
When the data from one minute ago and the current data for the same stock arrive, the linked data in 4 is output as a result stream.