Horizontal aggregations for building tabular data sets

In a data mining project, a signicant portion of time is devoted to building a data set suitable for analysis. In a re-lational database environment, building such data set usu-ally requires joining tables and aggregating columns with SQL queries. Existing SQL aggregations are limited since they return a single number per aggregated group, produc-ing one row for each computed number. These aggrega-tions help, but a signicant eort is still required to build data sets suitable for data mining purposes, where a tabu-lar format is generally required. This work proposes very simple, yet powerful, extensions to SQL aggregate func-tions to produce aggregations in tabular form, returning a set of numbers instead of one number per row. We call this new class of functions horizontal aggregations. Hor-izontal aggregations help building answer sets in tabular form (e.g. point-dimension, observation-variable, instance-feature), which is the standard form needed by most data mining algorithms. Two common data preparation tasks are explained, including transposition/aggregation and trans-forming categorical attributes into binary dimensions. We propose two strategies to evaluate horizontal aggregations using standard SQL. The rst strategy is based only on re-lational operators and the second one uses the "case " con-struct. Experiments with large data sets study the proposed query optimization strategies. 1