#!/usr/bin/perl

$t = time();

require "./baza.pl";

$dbh->do("SET autocommit=0");


$ot = '.';
$isx = 'miltbase.txt';
$tabBD = 'n10tn0';

$dbh->do("DROP TABLE $tabBD");

$dbh->do("CREATE TABLE $tabBD
(
id int(11) PRIMARY KEY auto_increment,


d0 VARCHAR(100),
myData1 VARCHAR(12)


)");



open D, "$ot/$isx";
@u = <D>;
close D;

$kolStr = $#u;


for $str (0..$kolStr){
  
  $u[$str] =~ s/[\r\n]//g;
  
  #print $u[$str];
  print $str;
	
	($d, $dn) = split (/\t/, $u[$str]);
  
$dbh->do("INSERT INTO $tabBD VALUES (null, '$dn', '$d')");

print "\n";
}

$dbh->do("COMMIT");
$dbh->disconnect;

$t = (time()-$t)/60;
print "Прошло ";
printf('%0.2f', $t);
print " min";

mysql> select * from n10tn0 limit 10;
+----+-------------------------------------------------------------+------------+
| id | d0                                                          | myData1    |
+----+-------------------------------------------------------------+------------+
|  1 | 13 14 15 17 21 26 29 31 32 34 38 39 40 42 44 51 60 63 67 76 | 31.05.2007 |
|  2 | 2 9 13 14 15 24 27 32 33 41 48 50 52 54 61 62 63 65 67 73   | 01.06.2007 |
|  3 | 2 4 5 10 15 18 21 31 32 37 39 47 49 52 55 60 63 66 68 71    | 02.06.2007 |
|  4 | 2 9 12 18 21 25 27 29 36 39 40 46 50 53 56 61 64 68 69 77   | 03.06.2007 |
|  5 | 2 10 12 17 19 20 26 30 32 40 45 50 55 62 69 70 72 73 77 79  | 04.06.2007 |
|  6 | 1 3 4 7 18 19 23 27 33 35 38 46 50 55 56 57 60 63 76 79     | 05.06.2007 |
|  7 | 10 12 24 27 34 37 38 41 46 47 52 53 56 58 59 62 64 66 70 77 | 06.06.2007 |
|  8 | 1 7 9 10 12 14 16 21 22 26 30 32 34 41 43 47 49 50 65 79    | 07.06.2007 |
|  9 | 5 6 7 15 17 21 23 25 26 36 41 48 54 55 57 59 65 67 74 79    | 08.06.2007 |
| 10 | 2 5 8 16 19 22 23 28 35 40 47 48 57 65 66 67 68 70 74 76    | 09.06.2007 |
+----+-------------------------------------------------------------+------------+
10 rows in set (0.00 sec)

#!/usr/bin/perl

$t = time();

require "./baza.pl";

$tab0 = 'n10tn0';

$tab1 = 'n10tn1';

$sovpadenij = 10;

$dbh->do("SET autocommit=0");

$dbh->do("DROP TABLE $tab1");

$dbh->do("CREATE TABLE $tab1
(
id int(11) PRIMARY KEY auto_increment,
d1 VARCHAR(100),
str VARCHAR(50),
sovpalo int(3)
)");


$s = "SELECT * FROM $tab0 LIMIT 1";
$sth = $dbh->prepare($s);
$sth->execute;

while (@r = $sth->fetchrow_array){
  
  @a = split / /, $r[1];

  print "$r[0]\n";


  $s2 = "SELECT * FROM $tab0";
  $sth2 = $dbh->prepare($s2);
  $sth2->execute;
  while (@r2 = $sth2->fetchrow_array){
    next if $r2[0] <= $r[0];
        
    @b = (); %isect = (); %inion = ();

    @b = split / /, $r2[1];

    #print @a;print "\n";
    #print @b;print "\n";


    for $e (@a, @b) {$inion{$e}++ && $isect{$e}++;}

    @isect = keys %isect;

    $is = scalar(@isect);

    if($is >= $sovpadenij){
      $r = join('_', sort {$a<=>$b} @isect );
      
      $dbh->do("INSERT INTO $tab1 VALUES (null, '$r', '$r[0]-$r2[0]', $is)");

    }
    
    #print "$r2[0]\n";
     

    }
  }

$dbh->do("COMMIT");
   

$sth->finish;
$sth2->finish;
$dbh->disconnect;

$t = (time()-$t)/60;
print "Прошло ";
printf('%0.2f', $t);
print " min";

mysql> select * from n10tn1 limit 10;
+----+----------------------------------------+--------+---------+
| id | d1                                     | str    | sovpalo |
+----+----------------------------------------+--------+---------+
|  1 | 13_15_17_29_31_32_34_38_51_60_76       | 1-25   |      11 |
|  2 | 13_14_15_17_21_26_29_31_34_40_51       | 1-269  |      11 |
|  3 | 13_14_15_26_38_40_51_60_67_76          | 1-347  |      10 |
|  4 | 15_29_32_34_39_42_44_51_60_63_67       | 1-373  |      11 |
|  5 | 17_21_26_31_34_40_42_60_63_67          | 1-398  |      10 |
|  6 | 14_15_17_21_29_34_38_40_51_63_67       | 1-498  |      11 |
|  7 | 26_32_38_40_42_44_51_63_67_76          | 1-856  |      10 |
|  8 | 13_17_21_26_29_31_32_34_60_67_76       | 1-1013 |      11 |
|  9 | 13_14_15_21_26_29_38_42_44_51_60_63_76 | 1-1037 |      13 |
| 10 | 14_17_26_29_31_34_38_42_63_76          | 1-1350 |      10 |
+----+----------------------------------------+--------+---------+
10 rows in set (0.00 sec)