SQL: Find the biggest percentage change in response time

[sathish_nadu]

I'm trying to calculate and list the websites in order of biggest overall reduction in response time from one time period to the next.

I don't strictly need to use a single query to do this, I can potentially run multiple queries.

| 1  | 1          | 93.26         | 2014-01-28 11:51:39**websites:**

| id | url                    |
| 1  | stackoverflow.com      |
| 2  | serverfault.com        |
| 3  | stackexchange.com      |
**
responses:**

| id | website_id | response_time | created_at |

| 2  | 1          | 99.46         | 2014-01-28 11:52:38
| 2  | 1          | 94.51         | 2014-01-28 11:53:38
| 2  | 1          | 104.46        | 2014-01-28 11:54:38
| 2  | 1          | 85.46         | 2014-01-28 11:56:38
| 2  | 1          | 100.00        | 2014-01-28 11:57:38
| 2  | 1          | 50.00         | 2014-01-28 11:58:38
| 2  | 2          | 100.00        | 2014-01-28 11:58:38
| 2  | 2          | 80            | 2014-01-28 11:58:38
**Ideally the result would look like:
**
| percentage_change | website_id | 
| 52                | 1 | 
| 20                | 2 | 


I've got as far as figuring out the largest response time, but no idea how to do another query to calculate the lowest response time and then do the math, then sort the maths.

SELECT * FROM websites
LEFT JOIN (
SELECT distinct * 
       FROM responses
       ORDER BY response_time desc) responsetable
ON websites.id=responsetable.website_id group by website_id

Thanks

[rch1231]

Hello,

I was looking over what you have and what you said you wanted to do and I have a couple of ideas on how to do it but I am a little confused by your wording.

If what you are trying to do is give the min, max or avg response times then you could use something like:

select websites.url,
min(responses.response_time) as 'Minimum Response',
max(responses.response_time) as 'Maximum Response',
avg(responses.response_time) as 'Average Response'
from responses
left join websites on websites.id=responsetable.website_id 
group by website.url

It really depends on what table you start in and I think it will run fast if you run from the responses table and join to get the names like website.url.

The other suggestion I would make is about how you are getting your response times. There is a tool out there called mtr which does a ping and traceroute together for several runs and compiles the results for you showing you where there may be any packet loss along the way. For windows there is winmtr.