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 [2020/08/10 02:35] (current) |
||
---|---|---|---|
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; | ||
} | } | ||