Horizontal Aggregations in SQL to Prepare DataSets for Data Mining Analysis

   December 9, 2017    Comments Off on Horizontal Aggregations in SQL to Prepare DataSets for Data Mining Analysis    Posted in: .Net, IEEE 2017

Horizontal Aggregations in SQL to Prepare Data Sets for Data Mining Analysis

  ABSTRACT:

 Preparing a data set for analysis is generally the most time consuming task in a data mining project, requiring many complex SQL queries, joining tables and aggregating columns. Existing SQL aggregations have limitations to prepare data sets because they return one column per aggregated group. In general, a significant manual effort is required to build data sets, where a horizontal layout is required. We propose simple, yet powerful, methods to generate SQL code to return aggregated columns in a horizontal tabular layout, returning a set of numbers instead of one number per row. This new class of functions is called horizontal aggregations. Horizontal aggregations build data sets with a horizontal denormalized layout (e.g. point-dimension, observation-variable, instance-feature), which is the standard layout required by most data mining algorithms. We propose three fundamental methods to evaluate horizontal aggregations: CASE: Exploiting the programming CASE construct; SPJ: Based on standard relational algebra operators (SPJ queries); PIVOT: Using the PIVOT operator, which is offered by some DBMSs. Experiments with large tables compare the proposed query evaluation methods. Our CASE method has similar speed to the PIVOT operator and it is much faster than the SPJ method. In general, the CASE and PIVOT methods exhibit linear scalability, whereas the SPJ method does not.

 

 

Existing System:

 

An existing to preparing a data set for analysis is generally the most time consuming task in a data mining project, requiring many complex SQL queries, joining tables and aggregating columns. Existing SQL aggregations have limitations to prepare data sets because they return one column per aggregated group.

 

Disadvantages of Existing System:

 

  • Existing SQL aggregations have limitations to prepare data sets.
  • To return one column per aggregated group.

 

Previous Process Flow:

 

 

 

Proposed System:

 

Our proposed horizontal aggregations provide several unique features and advantages. First, they represent a template to generate SQL code from a data mining tool. Such SQL code automates writing SQL queries, optimizing them and testing them for correctness.

 

 

Advantages of Proposed system:

 

  • The SQL code reduces manual work in the data preparation phase in a data mining project.

 

  • The SQL code is automatically generated it is likely to be more efficient than SQL code written by an end user.

 

  • The data sets can be created in less time.

 

  • The data set can be created entirely inside the DBMS.

 

Proposed Process Flow:

 

 

System Requirements:

Hardware Requirements:

  • System : Pentium IV 2.4 GHz.
  • Hard Disk : 40 GB.
  • Floppy Drive : 1.44 Mb.
  • Monitor : 15 VGA Colour.
  • Mouse : Logitech.
  • Ram : 512 Mb.

 

Software Requirements:

  • Operating system : Windows XP.
  • Coding Language : ASP.Net with C#
  • Data Base : SQL Server 2005

 

REFERENCE:

 

Carlos Ordonez, Zhibo Chen, “Horizontal Aggregations in SQL to Prepare Data Sets for Data Mining Analysis”, IEEE Transactions on Knowledge and Data Engineering, 2011.

  

Comments are closed.