perl_db_script
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
perl_db_script [2009/08/25 02:59] jt |
perl_db_script [2009/08/25 04:42] jt |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| <code perl> | <code perl> | ||
| #!/usr/bin/perl -w | #!/usr/bin/perl -w | ||
| - | ##Jason Thomas | + | #Jason Thomas |
| use strict; | use strict; | ||
| use lib "/home/jthomas/lib/perl5/site_perl/5.8.8/"; | use lib "/home/jthomas/lib/perl5/site_perl/5.8.8/"; | ||
| Line 8: | Line 8: | ||
| - | my $host = "host"; | + | my $host = "hostname"; |
| my $database = "keytest"; | my $database = "keytest"; | ||
| my $user = "admin"; | my $user = "admin"; | ||
| my $pw = "admin"; | my $pw = "admin"; | ||
| my $beginDate = "2009-07-01"; | my $beginDate = "2009-07-01"; | ||
| - | my $endDate = "2009-07-06"; | + | my $endDate = "2009-07-31"; |
| - | my @slotId = qw|698549|; | + | my @slotId = ("731147","731148","799084","799086","799088","799090","799091","698549"); |
| my $connect = Mysql -> connect ($host, $database, $user, $pw); | my $connect = Mysql -> connect ($host, $database, $user, $pw); | ||
| - | my @textId = findTextId($beginDate, $endDate); | + | findTextId($beginDate, $endDate); |
| - | my @dataSet = getDataSet(@textId); | + | |
| - | + | ||
| - | + | ||
| - | + | ||
| - | #print @results2."\n"; | + | |
| - | + | ||
| - | #print $stat->percentile(85)."\n"; | + | |
| - | #print $stat->quantile(3); | + | |
| sub findTextId{ | sub findTextId{ | ||
| my ($beginDate, $endDate) = @_; | my ($beginDate, $endDate) = @_; | ||
| + | |||
| + | for ( my $i = 0; $i < scalar @slotId; $i++){ | ||
| + | |||
| my @textId; | my @textId; | ||
| - | my $findTextIdQuery = "SELECT | + | |
| + | my $findTextIdQuery = "SELECT | ||
| a.slot_id, | a.slot_id, | ||
| concat(c.trans_element_conn, | concat(c.trans_element_conn, | ||
| Line 42: | Line 38: | ||
| FROM | FROM | ||
| (SELECT element_text_id text_id,slot_id, | (SELECT element_text_id text_id,slot_id, | ||
| - | count(element_text_id)/(SELECT count(*) c FROM base_trans WHERE date_time>=\'$beginDate\' AND date_time<=\'$endDate\' AND slot_id=\'$slotId[0]\')*100 coverage, | + | count(element_text_id)/(SELECT count(*) c FROM base_trans WHERE date_time>=\'$beginDate\' AND date_time<=\'$endDate\' AND slot_id=\'$slotId[$i]\')*100 coverage, |
| avg(measurement_element+time_to_first_byte+base_page_or_content_download+client_time) response_time, | avg(measurement_element+time_to_first_byte+base_page_or_content_download+client_time) response_time, | ||
| max(element_conn_id) conn_id | max(element_conn_id) conn_id | ||
| Line 48: | Line 44: | ||
| WHERE measurement_element>0 | WHERE measurement_element>0 | ||
| AND date_time >= \'$beginDate\' and date_time <=\'$endDate\' | AND date_time >= \'$beginDate\' and date_time <=\'$endDate\' | ||
| - | AND slot_id = \'$slotId[0]\' | + | AND slot_id = \'$slotId[$i]\' |
| GROUP BY element_text_id) a, | GROUP BY element_text_id) a, | ||
| global_trans_element_text b, | global_trans_element_text b, | ||
| Line 58: | Line 54: | ||
| LIMIT 12;"; | LIMIT 12;"; | ||
| + | print $findTextIdQuery."\n"; | ||
| my $execute = $connect->query($findTextIdQuery); | my $execute = $connect->query($findTextIdQuery); | ||
| ## print $execute."test"; | ## print $execute."test"; | ||
| - | while (my @results = $execute->fetchrow()){ | + | while (my @results = $execute->fetchrow()){ |
| - | ## print $results[2]."\n"; | + | print $results[2]."\n"; |
| - | push(@textId, $results[2]); | + | push(@textId, $results[2]); |
| + | } | ||
| + | getDataSet($slotId[$i], @textId); | ||
| } | } | ||
| - | |||
| - | return @textId; | ||
| - | |||
| } | } | ||
| sub getDataSet{ | sub getDataSet{ | ||
| - | my (@textId) = @_; | + | my ($slotIdSingle, @textId) = @_; |
| my @dataSet; | my @dataSet; | ||
| for ( my $i = 0; $i < scalar @textId; $i++){ | for ( my $i = 0; $i < scalar @textId; $i++){ | ||
| Line 81: | Line 76: | ||
| WHERE date_time>=\'$beginDate\' | WHERE date_time>=\'$beginDate\' | ||
| AND date_time<\'$endDate\' AND element_text_id=\'$textId[$i]\' | AND date_time<\'$endDate\' AND element_text_id=\'$textId[$i]\' | ||
| - | AND slot_id = \'$slotId[0]\' | + | AND slot_id = \'$slotIdSingle\' |
| GROUP BY trans_id) a;"; | GROUP BY trans_id) a;"; | ||
| + | print $selectElements."\n"; | ||
| my $execute = $connect->query($selectElements); | my $execute = $connect->query($selectElements); | ||
| while (my @results = $execute->fetchrow()){ | while (my @results = $execute->fetchrow()){ | ||
| Line 91: | Line 86: | ||
| my $responseTime85 = calc85th(@dataSet); | my $responseTime85 = calc85th(@dataSet); | ||
| - | writeDB($slotId[0], $textId[$i], "85", $responseTime85); | + | writeDB($slotIdSingle, $textId[$i], "85", $responseTime85); |
| my $responseTime95 = calc95th(@dataSet); | my $responseTime95 = calc95th(@dataSet); | ||
| - | writeDB($slotId[0], $textId[$i], "95", $responseTime95); | + | writeDB($slotIdSingle, $textId[$i], "95", $responseTime95); |
| } | } | ||
| - | return @dataSet; | ||
| } | } | ||
perl_db_script.txt · Last modified: 2020/08/10 02:35 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
