Nulls represent unknown data. MineSet supports nulls in the data access tools, the mining tools, and the visualization tools. The purpose of this appendix is to give you a better understanding of the way MineSet handles nulls.
Unknown data values are often represented as nulls in data sources. While it is possible to associate different semantics with nulls, the most common is that nulls represent missing or unknown values. For example, if a data record is made up of fields representing firstname, middlename, lastname, and if a person's middlename is not known, it can be represented by the null value.
Nulls can occur in data for a variety of reasons: They can occur naturally in data as a means of representing unknown data, or they can come about as the result of doing certain kinds of aggregations. For example, if there are no flights between San Francisco and MineSet City, a query such as "find the average flight time from San Francisco to MineSet City" yields a null value.
MineSet generally follows the semantics of relational databases when dealing with nulls, and treats them as unknown values.
Some databases, such as Oracle RDBMS, do not distinguish between null and empty strings. In such a case, it is not possible to distinguish between an unknown middle name and a person who does not have a middle name. On the other hand, Sybase RDBMS distinguishes between null and empty strings. Hence MineSet can distinguish empty and null strings when reading from Sybase, but not from Oracle.
In data files, as well as in the visual tools, nulls are represented by the string "?" (question mark). Thus, if Joe Miner's middle name is unknown, his name is represented in our example data file (having schema firstname, middlename, lastname) as:
Joe ? Miner |
In general, the color gray is often associated with null values in the visualizations. The graphical representation of nulls varies from tool to tool. See the chapters on the individual tools for a discussion of how they represent them graphically.
Given that nulls represent unknown values, it becomes straightforward to give meaning to expressions involving nulls.
Arithmetic operations involving nulls always give a null result. For example:
(5 + ?) evaluates to ? (adding 5 to an unknown yields yet another unknown);
(6 / ?) evaluates to ?
In addition to taking on the values of TRUE and FALSE, Boolean variables can also be null. If a Boolean valued variable has a null (unknown) value, the result of combining it with another Boolean variable in an expression is also unknown, unless it is possible to determine just from the known value what the result is. In particular:
"? AND FALSE is FALSE", because FALSE ANDed with anything is always FALSE
"? AND TRUE is ?"
"? OR FALSE is ?"
"? OR TRUE is TRUE", because TRUE ORed with anything is always TRUE
"NOT ? is ?"
Relational operations (==, !=, <, >, <=, and >=) involving nulls always evaluate to null. Some particular cases worth emphasizing are:
"? == ? "evaluates to ?, not TRUE
"? != ?" evaluates to ?, not FALSE
"? != x"evaluates to ?, not FALSE
Given two unknown values, it is unknown whether the two are equal or unequal. This behavior can be confusing when using a search panel. For example, when searching for all values not equal to 0, nulls do not show up, yet neither do they show up when searching for values equal to 0. Because of this, search panels provide the ability to search explicitly for nulls. (Some search panels provide the option of treating nulls as zeros; see the individual tool discussions for more information.)
MineSet stays close to the semantics of SQL and relational databases when aggregating columns that might have null values. Thus, null values are ignored when computing SUM, AVG, MIN, MAX, and COUNT. This is best illustrated by an example. Consider a data file having records representing the number of pets a person has. The schema of this record is name, num_pets, and null (unknown) values are represented by "?".
Name | NUM_PETS |
|---|---|
Tesler | 3 |
Rathmann | ? |
Haber | 1 |
Bhargava | 0 |
Sangudi | ? |
Then,
SUM(NUM_PETS) = 4
COUNT(NUM_PETS) = 3 (and not 5, even though there are 5 rows of data)
AVG(NUM_PETS) = 1.33
MAX(NUM_PETS) = 3
MIN(NUM_PETS) = 0
In these aggregations, null values are basically ignored (note that the value 0 is different from ?, and is not ignored).
A special case of this is an aggregation where all the values being aggregated are themselves null. An even more specialized case is when there are no values being aggregated: for instance, when summing an empty column. In both these cases, the sum, average, min, and max are ?, while the count is 0.
In an ascending sorted sequence, null values always appear before non-null values. In a descending sorted sequence, null values always appear after non-null values.
MineSet lets you bin numeric data into bins or discrete intervals. It also lets you (via the aggregation panel in the Tool Manager) create arrays on these bins. When a column of values is binned, all null values are put in a bin labeled "?". Such a bin label is always created, whether or not the data being binned has nulls in it. You have control over whether to use this bin for nulls in your application. You can do so by allowing arrays to ignore or keep bins for nulls by setting the desired option in the Tool Manager's Preferences dialog. For example, if you know that the column being binned has no nulls, or you intend to study the data corresponding to non-null values only, you can choose to ignore the bin for nulls.