SQL:查找响应时间的最大百分比变化

Question

我正试图按照从一个时间段到下一个时间周期响应时间的最大总体减少顺序计算和列出网站。

我并不严格需要使用一个查询来完成这项工作，我可以运行多个查询。

网站：

| id | url                    |
| 1  | stackoverflow.com      |
| 2  | serverfault.com        |
| 3  | stackexchange.com      |

响应：

| id | website_id | response_time | created_at |
| 1  | 1          | 93.26         | 2014-01-28 11:51:39
| 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:36
| 2  | 1          | 50.00         | 2014-01-28 11:58:37
| 2  | 2          | 100.00        | 2014-01-28 11:58:38
| 2  | 2          | 80            | 2014-01-28 11:58:39

理想情况下，结果看起来像：

| percentage_change | website_id | 
| 52                | 1 | 
| 20                | 2 | 

我已经计算出了最大的响应时间，但不知道如何进行另一个查询来计算最低的响应时间。然后计算，然后排序。

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

感谢

Answer 1

您需要等效的lag()或lead()函数。在MySQL中，我使用一个相关的子查询：

select website_id, max(1 - (prev_response_time / response_time)) * 100
from (select t.*,
(select t2.response_time
from table t2
where t2.website_id = t.website_id and
t2.created_at < t.created_at
order by t2.created_at desc
limit 1
) as prev_response_time
from table t
) t
group by website_id;

编辑：

如果你想从最高到最低的变化：

select website_id, (1 - min(response_time) / max(response_time)) * 100
from table t
group by website_id;

Answer 2

使用两个序列号：-

SELECT a.id, a.url, MAX(100 * (LeadingResponse.response_time - TrailingResponse.response_time) / LeadingResponse.response_time)
FROM
(
SELECT website_id, created_at, response_time, @aCnt1 := @aCnt1 + 1 AS SeqCnt
FROM responses
CROSS JOIN
(
SELECT @aCnt1:=1
) Deriv1
ORDER BY website_id, created_at
) TrailingResponse
INNER JOIN
(
SELECT website_id, created_at, response_time, @aCnt2 := @aCnt2 + 1 AS SeqCnt
FROM responses
CROSS JOIN
(
SELECT @aCnt2:=2
) Deriv2
ORDER BY website_id, created_at
) LeadingResponse
ON LeadingResponse.SeqCnt = TrailingResponse.SeqCnt
AND LeadingResponse.website_id = TrailingResponse.website_id
INNER JOIN websites a
ON LeadingResponse.website_id = a.id
GROUP BY a.id, a.url

SQL对此进行了篡改：-

http://www.sqlfiddle.com/#！2/ace08/1

编辑-不同的方法。只有当响应表上的id按日期/时间顺序时，这才有效。

SELECT a.id, a.url, MAX(100 * (r2.response_time - r1.response_time) / r2.response_time)
FROM responses r1
INNER JOIN responses r2
ON r1.website_id = r2.website_id
INNER JOIN
(
SELECT r1.website_id, r1.id, MAX(r2.id) AS prev_id
FROM responses r1
INNER JOIN responses r2
ON r1.website_id = r2.website_id
AND r1.id > r2.id
GROUP BY r1.website_id, r1.id
) ordering_query
ON r1.website_id = ordering_query.website_id
AND r1.id = ordering_query.id
AND r2.id = ordering_query.prev_id
INNER JOIN websites a
ON r1.website_id = a.id
GROUP BY a.id, a.url

您可以根据response_time字段而不是id来做类似的事情，但这需要网站的response_time是唯一的。

编辑

刚刚看到，你不只是想要连续的变化，而是想要最高到最低的响应。假设最低不一定要在最高之后：-

SELECT id, url, MAX(100 * (max_response - min_response) / max_response)
FROM
(
SELECT a.id, a.url, MIN(r1.response_time) AS min_response, MAX(r1.response_time) AS max_response
FROM responses r1
INNER JOIN websites a
ON r1.website_id = a.id
GROUP BY a.id, a.url
) Sub1

如果你只对较高的响应时间之后的较低响应时间感兴趣：-

SELECT id, url, MAX(100 * (max_response - min_following_response) / max_response)
FROM
(
SELECT a.id, a.url, MAX(r1.response_time) AS max_response, MIN(r2.response_time) AS min_following_response
FROM responses r1
INNER JOIN  responses r2
ON r1.website_id = r2.website_id 
AND (r1.created_at < r2.created_at
OR (r1.created_at = r2.created_at
AND r1.id < r2.id))
INNER JOIN websites a
ON r1.website_id = a.id
GROUP BY a.id, a.url
) Sub1

(假设响应表上的id字段是唯一的，并且按顺序创建)

Answer 3

从你的"我已经计算出了最大的响应时间，但不知道如何再做一个查询来计算最低的响应时间。然后计算，然后排序。"我知道你想要最小的响应时间和最大的响应时间，然后计算。

drop table #test
create table #test(
id int, website_id int, response_time decimal, created_at datetime)
insert into #test (id , website_id , response_time , created_at) values ( 1  , 1          , 93.26, '2014-01-28 11:51:39')
insert into #test (id , website_id , response_time , created_at) values ( 2  , 1          , 99.46         , '2014-01-28 11:52:38')
insert into #test (id , website_id , response_time , created_at) values ( 2  , 1          , 94.51         , '2014-01-28 11:53:38')
insert into #test (id , website_id , response_time , created_at) values ( 2  , 1          , 104.46        , '2014-01-28 11:54:38')
insert into #test (id , website_id , response_time , created_at) values ( 2  , 1          , 85.46         , '2014-01-28 11:56:38')
insert into #test (id , website_id , response_time , created_at) values ( 2  , 1          , 100.00        , '2014-01-28 11:57:38')
insert into #test (id , website_id , response_time , created_at) values ( 2  , 1          , 50.00         , '2014-01-28 11:58:38')
insert into #test (id , website_id , response_time , created_at) values ( 2  , 2          , 100.00        , '2014-01-28 11:58:38')
insert into #test (id , website_id , response_time , created_at) values ( 2  , 2          , 80            , '2014-01-28 11:58:38')
select * from #test
select distinct  MINT.website_id,MINT.MINRT,maxT.MINRT,(MINT.MINRT/maxT.MINRT)*100--Do your calculation here---
from #test t0
inner join(select min(response_time) as MINRT,website_id from #test   group by website_id  )  MINT
on  MINT.website_id = t0.website_id
inner join(select max(response_time) as MINRT,website_id from #test   group by website_id  )  maxT
on  maxT.website_id = t0.website_id

Answer 4

您想将每个网站的最小响应时间除以最大响应时间吗？那就是：

select 
websites.id as website_id, 
100 - min(response_time) / max(response_time) * 100 as percentage_change
from websites
left join responses on websites.id = responses.website_id
group by websites.id;

(我假设response_time永远不可能为零。如果可以的话，你必须使用case语句。)

Answer 5

按website_id对响应时间进行分组，找到MIN(response_time)和MAX(response_time)，并比较MAX()之后是否出现MIN()，只过滤性能提高的网站。

<?php
$rows = $db->fetchAll('
select
r.website_id, min(r.response_time) min_time, max(r.response_time) max_time,
(select
rmin.created_at
FROM
responses rmin
WHERE
rmin.response_time = min(r.response_time) AND
rmin.website_id = r.website_id
ORDER BY rmin.created_at
LIMIT 1) min_created_at,
(select
rmax.created_at
FROM
responses rmax
WHERE
rmax.response_time = max(r.response_time) AND
rmax.website_id = r.website_id
ORDER BY rmax.created_at DESC
LIMIT 1) max_created_at
FROM
responses r
GROUP BY
r.website_id');
foreach($rows as $row) {
if($row['max_created_at'] < $row['min_created_at']) {
echo 'Website '.$row['website_id'].
' improved by '.
(100 - (($row['min_time'] / $row['max_time']) * 100)).
"%n";
}
}

然而，对于大型数据集，查询可能会非常缓慢。您必须优化索引和/或查询
sqlfiddle：http://www.sqlfiddle.com/#！2/fa8f9/8