IBM developerWorks: Wikis - WebSphere eXtreme Scale V6.1 User Guide

See the WebSphere eXtreme Scale Wiki for links to eXtreme Scale Version 7.0 documentation.
If you log in with your developerWorks ID, you can leave comments and feedback for the development team.

Typically, the unweighted average price is not interesting to analysts. The weighted average price better reflects the average price. The SQL developer writes two SQLs to calculate the weighted average price.

last5MinuteAvgPrice and last5MinuteAvgPrice SQL

1. CREATE VIEW last5MinuteQuote AS
2.     SELECT t, issue, volume, price*volume AS amount FROM stockQuote FETCH LATEST 5 MINUTES;
3.
4. CREATE VIEW last5MinuteAvgPrice AS
5.     SELECT issue, totalAmount/totalVolume As avgPrice from
6          (SELECT issue, sum(amount) as totalAmount, sum(volume) As totalVolume FROM last5MinuteQuote group by issue);

These two SQL statements are used to calculate the weighted average price. However, the data ouput of the view last5MinuteQuote is of little concern; it is the output from the view last5MinuteAvgPrice that is most important. Therefore, when the view last5MinuteQuote is created, the map name is not set. Therefore, the results from this view will not be stored in an ObjectGrid map.

The following program StreamQueryApp2 shows how an intermediate view can be used.

StreamQueryApp1.java

import com.ibm.websphere.objectgrid.ObjectGrid;
import com.ibm.websphere.objectgrid.ObjectGridManager;
import com.ibm.websphere.objectgrid.ObjectGridManagerFactory;
import com.ibm.websphere.objectgrid.ObjectMap;
import com.ibm.websphere.objectgrid.Session;
import com.ibm.websphere.objectgrid.streamquery.StreamQuerySet;
import com.ibm.websphere.projector.Tuple;
import com.ibm.websphere.projector.md.EntityMetadata;
import com.ibm.websphere.projector.md.TupleMetadata;

public class StreamQueryApp2 {

    public static void main(String\[\] args) throws Exception {
        ObjectGridManager ogManager = ObjectGridManagerFactory.getObjectGridManager();

        // Create an ObjectGrid
        ObjectGrid og = ogManager.createObjectGrid("og1");

        // Define the map to contain the stock quote
        og.defineMap("stockQuote");

        // Define the map to contain the average price data in the latest 5 minutes.
        og.defineMap("last5MinuteAvgPrice");

        // Add a stream query set to the ObjectGrid
        StreamQuerySet sqSet = og.createStreamQuerySet();

        // Add a stream to the stream query set and set the class name
        // to represent the data stored in the stream map
        sqSet.addStreamMetadata().setValueClass(StockQuote.class);

        sqSet
                .addViewMetadata()
                .setSql(
                        "CREATE VIEW last5MinuteQuote AS SELECT t, issue, volume, price*volume AS amount FROM stockQuote FETCH LATEST 5 MINUTES;");

        // Add a view to the stream query set and set the class name
        // to represent the data stored in the viewmap
        sqSet
                .addViewMetadata()
                .setMapName("last5MinuteAvgPrice")
                .setSql(
                        "CREATE VIEW last5MinuteAvgPrice AS SELECT issue, totalAmount/totalVolume As avgPrice from "
                                \+"(SELECT issue, sum(amount) as totalAmount, sum(volume) As totalVolume FROM "
                                + "last5MinuteQuote group by issue);");

        // Deploy the stream query set
        sqSet.setDeployed(true);

        Session session = og.getSession();

        ObjectMap streamMap = session.getMap("stockQuote");
        ObjectMap viewMap = session.getMap("last5MinuteAvgPrice");

        streamMap.insert("IBM", new StockQuote("IBM", 95.00f, 1000));
        streamMap.update("IBM", new StockQuote("IBM", 96.00f, 2000));
        streamMap.update("IBM", new StockQuote("IBM", 100.00f, 3000));

        streamMap.insert("CSCO", new StockQuote("CSCO", 6.00f, 10000));
        streamMap.update("CSCO", new StockQuote("CSCO", 6.20f, 2500));

        // Sleep 1 second to make sure the view data is derived.
        Thread.sleep(1000);

        System.out.println("check the data");
        // The data stored in the view map is in the format of tuples.
        // So we need to create tuple keys to access the tuple values.
        EntityMetadata emd = viewMap.getEntityMetadata();
        TupleMetadata keyMD = emd.getKeyMetadata();
        Tuple ibmKey = keyMD.createTuple();
        ibmKey.setAttribute(0, "IBM");
        Tuple ibmValue = (Tuple) viewMap.get(ibmKey);
        System.out.println("Average price in the latest 5 minutes for IBM: " + ibmValue.getAttribute(0));

        Tuple cscoKey = keyMD.createTuple();
        cscoKey.setAttribute(0, "CSCO");
        Tuple cscoValue = (Tuple) viewMap.get(cscoKey);
        System.out.println("Average price in the latest 5 minutes for CSCO: " + cscoValue.getAttribute(0));

        // Undeploy the stream query to stop all the threads running in the stream query engine
        sqSet.setDeployed(false);
    }
}

This program is very similar to the previous one except that it has an intermediate view last5MinuteQuote. The output of the program is the weighted average price for both stocks as follows:

StreamQueryApp2 output

Average price in the latest 5 minutes for IBM: 97.833336
Average price in the latest 5 minutes for CSCO: 6.04

Wiki Disclaimer and License

© Copyright IBM Corporation 2007,2009. All Rights Reserved.