SQL, Genetic Programming, & Data Mining
by Brian Connolly

Example 1:

(a)
create table t_profile_column_values
   (id  int not null,
    test int NULL,
    parent1 int NULL,
    parent2 int NULL,
    c_name varchar(30) not null,
    operator char(2) not null, /* '==' or '!=' */
    c_value varchar(255) not null
)


(b)
SELECT P.ProfileID, M.TargetValue, count(*)
FROM t_mine_relations M, profiles P
WHERE {profile P matches M}
GROUP BY P.ProfileID, M.TargetValue


Example 2:

SELECT p.id as id, m.target as target_value,count(*) as number_selected
FROM .dbo.uf_generated_profile_table() p LEFT OUTER JOIN t_mine_relations m 
ON 
(p.column1 is null or
(left(p.column1,2) = '==' and 
   substring(p.column1,4,len(p.column1)-3) = m.column1) or 
(left(p.column1,2) = '!=' and 
   substring(p.column1,4,len(p.column1)-3) != m.column1) 
) and 
 ... { similar clauses for all t_mine_relations columns ... columnN} 
GROUP BY id, m.target


Example 3:

exec up_evolutionary_data_mining
   @profile_count =200,
   @number_generations=500,
   @target_value = '1',
   @population_fill_proportion = .05,
   @column_portion = .05,
   @equality_percent= .50,
   @mutation_add_column_probability= .50,
   @mutation_probability= .9


  
Listing One
/* To evaluate all the profiles in t_profile_column_values, we need to 
restructure them as rows of the form id, col1, col2,...coln, where colN 
is either: NUll - column doesn't matter; "==:N" - mine relation column 
values = N; or "!=:N" - mine relation column values != N. We generate the 
create function statement that performs this transformation. */

drop procedure up_generate_profile_table_function
go
create procedure up_generate_profile_table_function
as
declare
   @sql_initial varchar(8000),
   @sql varchar(8000),
   @next varchar(30),
   @columns_done int
begin
select *
into #t1
from v_columns
order by colorder
--select * from #t1

select @sql = 'drop function uf_generated_profile_table'
exec(@sql)
select @sql = ''
select @sql = @sql + 
'create function uf_generated_profile_table() ' + char(13) + char(10)
select @sql = @sql + 'returns table ' + char(13) + char(10)
select @sql = @sql + 'as return (' + char(13) + char(10)
select @sql = @sql + 'select p.id,' + char(13) + char(10)

select @columns_done = 0
while ( exists (select * from  #t1))
begin
    select top 1 @next = name
    from #t1
    select  @sql = @sql + @next +  
' = (SELECT operator + '':'' + c_value FROM t_profile_column_values ' 
+   'WHERE c_name=''' + @next + ''' and id = p.id)'
    delete from #t1 where name = @next
    if exists (select * from  #t1)
        select  @sql = @sql + ',' + char(13) +char(10)
    else
        select  @sql = @sql + char(13) +char(10)
select @columns_done = @columns_done + 1
if ((@columns_done % 50) = 0)
   begin
     select @sql_initial = @sql
     select @sql = ''
   end
end
select @sql = @sql + 
' from t_profile_column_values p' + char(13) +char(10) 
select @sql = @sql + ' group by id )' + char(13) +char(10) 
--select @sql_initial, @sql
exec(@sql_initial + @sql)
end
go
/* Create the function above. */
exec up_generate_profile_table_function
go
  




2


