Nulls represent unknown data. MineSet supports nulls in the data access tools, the mining tools, and the visualization tools. This chapter will help you understand how MineSet handles nulls.
Unknown data values are often represented as nulls in data sources. While you can associate different semantics with nulls, usually nulls represent missing or unknown values. For example, if a data record consists of fields representing firstname, middlename, and 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 various reasons. For example, they can be a way of representing unknown data, or they can result from 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. Therefore, 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 (with 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 individual tools in the MineSet Enterprise Edition Reference Guide for this information.
Given that nulls represent unknown values, it is straightforward to give meaning to expressions involving nulls.
Arithmetic operations with nulls always give a null result. For example:
(5 + ?) evaluates to ? (adding 5 to an unknown yields yet another unknown).
(6 / ?) evaluates to ?.
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 the result just from the known value. For example:
“? 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.
Given two unknown values, it is unknown whether the two are equal or unequal. This can be confusing when you are using a search panel. For example, if you search for all values not equal to 0, nulls do not appear, yet neither do they appear if you search for values equal to 0. Because of this, search panels allow you to search explicitly for nulls. (Some search panels allow you to treat nulls as zeros; see the individual tool discussions in the MineSet Enterprise Edition Reference Guide for more information.)
MineSet stays close to the semantics of SQL and relational databases when aggregating columns that might have null values. Therefore, null values are ignored when SUM, AVG, MIN, MAX, and COUNT are computed. For example, consider a data file with records representing the number of pets a person has. The schema of this record is name, num_pets. Null (unknown) values are represented by “?.”
Name | NUM_PETS |
|---|---|
Tesler | 3 |
Rathmann | ? |
Haber | 1 |
Bhargava | 0 |
Sangudi | ? |
The computations are as follows:
SUM(NUM_PETS) = 4.
COUNT(NUM_PETS) = 3 (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 (the value 0 is different from ?, and is not ignored).
A special case is an aggregation in which all the values being aggregated are themselves null. An even more specialized case is if there are no values being aggregated: for example, 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 contains nulls.
You can choose 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 (on IRIX), or by checking the “Use nulls in aggregation” checkbox at the bottom of the aggregation operator (on Windows). For example, if you know that the column being binned has no nulls, or you intend to study only the data corresponding to non-null values, you can choose to ignore the bin for nulls.