Here's how I applied some basic Linear Regression techniques on the job.
Ok, I have to first say that this one was real fun but I think that means that I'm geeky...
We wanted to see all transactions whose response time standard deviation trended upwards. That sounds simple but it's not quite that simple, especially done in SQL. That analysis requires a basic linear regression which would probably be easily implemented in a statistical analysis tool (SAS, etc) but all I have right now is SQL. Either way, I made it work and it was fun.
The SQL below calculates b1 which is the slope of the line formed by the x,y coordinates of our transactional data. The coordinates are formed as (xs,stddev_1), (xs,stddev_2), (st,sttdev_n), etc. If the b1 is >0 then the slope is positive, if b1<0 b1 =" 0">
So I wrote the SQL below and got all transactions by os whose response time standard deviation trended upwards. This is really interesting too because we get more info than if it trended up, we get a metric on how much it trended up. I've ordered the SQL below by b1 in descending order so we can see what transactions trended up the MOST first! Very cool.
My full data set is attached and a screenshot of some of the data is below.
SELECT aaaa.os ,
aaaa.transaction_name,
ROUND(aaaa.b1,2) AS b1
FROM
(SELECT aaa.os ,
aaa.transaction_name,
SUM(numerator)/SUM(denominator) AS b1
FROM
(SELECT aa.os ,
aa.transaction_name ,
b.apdex_dimension ,
aa.xbar ,
aa.ybar ,
b.x ,
b.y ,
(x-xbar)*(y-ybar) AS numerator,
(x-xbar)*(x-xbar) AS denominator
FROM
(SELECT a.os ,
a.transaction_name,
AVG(a.x) AS xbar ,
AVG(a.y) AS ybar
FROM
(SELECT t.os ,
t.apdex_dimension ,
txn.transaction_name ,
IF(t.apdex_dimension = 'xs',1,IF(t.apdex_dimension = 'sm',2,IF(t.apdex_dimension = 'md',3,IF(t.apdex_dimension = 'st',4,IF(t.apdex_dimension = 'lg',5,IF(t.apdex_dimension = 'xl',6,IF(t.apdex_dimension = 'xt',7,NULL))))))) AS x,
stddev(trt.response_time) AS y
FROM test t
LEFT JOIN test_result_transactions trt
ON t.id = trt.test_id
LEFT JOIN transaction txn
ON trt.transaction_id = txn.transaction_id
WHERE t.project = 'as 8.0 sp3 pvt'
AND t.test_type = 'apdex'
GROUP BY t.os ,
t.apdex_dimension ,
txn.transaction_name ,
IF(t.apdex_dimension = 'xs',1,IF(t.apdex_dimension = 'sm',2,IF(t.apdex_dimension = 'md',3,IF(t.apdex_dimension = 'st',4,IF(t.apdex_dimension = 'lg',5,IF(t.apdex_dimension = 'xl',6,IF(t.apdex_dimension = 'xt',7,NULL)))))))
) a
GROUP BY a.os ,
a.transaction_name
) aa
LEFT JOIN
(SELECT t.os ,
t.apdex_dimension ,
txn.transaction_name ,
IF(t.apdex_dimension = 'xs',1,IF(t.apdex_dimension = 'sm',2,IF(t.apdex_dimension = 'md',3,IF(t.apdex_dimension = 'st',4,IF(t.apdex_dimension = 'lg',5,IF(t.apdex_dimension = 'xl',6,IF(t.apdex_dimension = 'xt',7,NULL))))))) AS x,
stddev(trt.response_time) AS y
FROM test t
LEFT JOIN test_result_transactions trt
ON t.id = trt.test_id
LEFT JOIN transaction txn
ON trt.transaction_id = txn.transaction_id
WHERE t.project = 'as 8.0 sp3 pvt'
AND t.test_type = 'apdex'
GROUP BY t.os ,
t.apdex_dimension ,
txn.transaction_name ,
IF(t.apdex_dimension = 'xs',1,IF(t.apdex_dimension = 'sm',2,IF(t.apdex_dimension = 'md',3,IF(t.apdex_dimension = 'st',4,IF(t.apdex_dimension = 'lg',5,IF(t.apdex_dimension = 'xl',6,IF(t.apdex_dimension = 'xt',7,NULL)))))))
) b ON aa.os = b.os
AND aa.transaction_name = b.transaction_name
) aaa
GROUP BY aaa.os,
aaa.transaction_name
) aaaa
WHERE aaaa.b1 >0
ORDER BY aaaa.b1 DESC
No comments:
Post a Comment