ExploreData

This section is intended for those who know at least a little SQL.

The more the better, actually.

Exploring Data

By now you know how to run SQL scripts and produce charts using Entrance. This section will you to some fairly simple techniques for exploring data using MySQL scripts in conjunction with Entrance graphical tools. They turn out to be to be wildly effective.

Quick Review of PLOT

PLOT is an extension keyword you can embed in SQL scripts to draw a charta in place of generating text output after executing a MySQL SELECT command. If you needed to, you could recover the original SELECT statement by deleting the PLOT keyword and the text between it and the SELECT keyword

The easiest way to use PLOT to make a chart is to open a table and use one of the "Make a chart" dialogs available on the Charts menu to generate the PLOT script automatically. After the chart is drawn, click the SQL tab to see it.

With a little practice though, you will find it is easy to convert any SELECT statement to a PLOT statement. The document "How to make a chart" will get you started with this, and PLOT syntax is a detailed reference for the syntax.

Color Overrides and "Data Painting"

Color overrides instruct Entrance to use the colors in a resultset column to override the color for a given row. Color names like RED, BLUE, GREEN and so on will work.

For example, this script draws an XY Chart with color overrides:

  PLOT XY

    X, GRAY FILLED TRIANGLE, COLOR OVERRIDE

  SELECT x, y, highlight FROM foo;

If all of the entries in the "highlight" column are empty, we get an XY chart with GRAY triangles. However, if some rows have RED in the highlight column we will see red triangles at their points.

This means we can use UPDATE commands to "paint" rows with color. For example, we can highlight some of the points with RED color and others with ORANGE like this:

  -- clear any existing highlights first

  UPDATE foo

    SET highlight = '';

  UPDATE foo

    SET highlight = 'RED'

    WHERE x = y + 3;

  UPDATE foo

    SET highlight = 'ORANGE'

    WHERE x = y;

  PLOT XY

    X, VERY LIGHT BLUE FILLED TRIANGLE, COLOR OVERRIDE

  SELECT x, y, highlight from foo;

The resulting chart would show our orange and red highlights, which might be something like this:

Data Painting

In the previous section we saw how UPDATEs could be used with PLOT and COLOR OVERRIDEs to paint data points in a chart with color This is "data painting". You can also do data painting while viewing a chart using a technique that is a lot like "brushing" data points, as described by William Cleveland in his book "The Elements of Graphing Data".

To paint data in an Entrance chart this way you need a color override, as in this script:

  PLOT CORRELATION

      TRIANGLE, TRIANGLE, TRIANGLE, COLOR OVERRIDE

    WITH

      FRAME 20 20 400 400

  SELECT population, area, gdp, color from country;

Often you will add a column temporarily to serve as the color override, using either SQL ALTER or "New Column..." from the table popup menu.

When you run the query you get a chart something like the one below. With the mouse you drag out a rectangle over points in the chart that interest you:

and paint them "red" or another color. You are actually changing the color value of rows corresponding to points in the table. When the chart is automatically redrawn, you see the changes:

Data Painting and the "Match Box"

Whichever data painting technique you used, rows in the original table are now marked with a color. You can use the Entrance match box to filter the table for rows with that mark, as shown below:

You can quickly calculate means and medians for the marked data points using Tools | Calculate statistitics.... which limits the calculation to matching rows (as of version 0.81.3).

Table views update automatically when you return to a table that has changed since your last view. Try this: go back to the chart and paint some more points red, then return to the table.

You will see the new rows displayed automatically.

Data Painting and SQL Scripts

You can easily act on the marked rows from your own SQL scripts. For example, you can select them:

    SELECT name, area FROM country

        WHERE color = 'red';

update them:

   UPDATE TABLE country

        SET name = concat(name, ' **')

        WHERE color = 'red';

or ignore them, eg. as a way of temporarily eliminating outliers:

   SELECT avg(area) FROM country

        WHERE color != 'red';

WHENLINES

This section describes another technique for "tagging" rows on a time series chart so that they can be picked out on a chart.

WHENLINES are a specialized chart series type that can associated with columns containing strings. They draw a vertical line through the chart over each tick mark corresponding to a row with a non-empty label in its WHENLINES column. WHENLINES are always labelled, but you can use a string containing a single blank when you want to draw a line without a label.

Confused? Sorry. Maybe this example will help. With this data in table NEW1:

    1         100       ''

    2         200       'Oregon'

    3         300       'California'

this script:

    PLOT LINECHART

        XLABELS, RED LINE, BLUE WHENLINE

    WITH

        COLLAR

    SELECT * FROM NEW1;

draws two blue "WHENLINES" labeled Oregon and California like this:

Please note: This web page and other Entrance documentation web pages are copyrighted material and have not been released under the terms of the GPL.

Java and the Java Coffee Cup Logo are trademarks or registered trademarks of Sun Microsystems, Inc. in the U.S. and other countries. Solaris is a trademark of Sun Microsystems, Inc. MySQL is a registered trademark of MySQL AB in the United States, the European Union and other countries. Windows is a trademark of Microsoft Corporation. Macintosh and Apple Numbers are trademarks of Apple Computer, Inc.