Top 90% calculations

[Petteri Toukoniitty]

Hi,


I have a database containing info about response times to user
requests. In addition to the response time, each record contains date
when the request was made. Now I need to count two averages for each
month: average for top 90% of the response times (ordered by response
time to get the top 90%) and other average for the remaining 10%.


Example table:
Date(month) Response time (minutes)
January 1
January 2
January 5
January 4
February 8
February 2


The query should then return something like this:
Date(month) Avg top 90% Avg remaining
January X Y
February X2 Y2


Where X would be the average response time for the fastest 90% of the
response times, and Y would be the average for the remaining requests
that have the longest response times.


How could I do this with Access?


Thanks in advance,
Petteri

 

[[MVP] S.Clark]

I'll admit that I don't totally understand, but you can use the Average
operator in a query.

SELECT [ID], Average([Field]) FROM [Tablename] GROUP BY [ID]

You can also make a crosstab query to transpose data to be a column.

Using the two together, probably in two or more queries, you can get to
where you want to go.

 

[Petteri Toukoniitty]

Hi Steve,

and thank you for your answer!

I'm still having some problems with the query. I know how to use the average
function, but the problem is that it can't be
used directly in this case as it would calculate average amount for each
month.
What I need is that the query should
1) Take 90% of the fastest response times per month (e.g. sort the response
times for each month, then take top-90% of these per month)
2) Calculate average for these top 90% response times per month
3) Calculate average for the remaining 10%

So this way each month would have two averages, one for the faster reponse
times and one for the slower ones.

So my guess is that I should somehow combine the average-function and the
"SELECT TOP 90 PERCENT" attribute in access/SQL,
but I don't know how... Does anyone have clue how to do this?


Cheers,
Petteri

I'll admit that I don't totally understand, but you can use the Average
operator in a query.

SELECT [ID], Average([Field]) FROM [Tablename] GROUP BY [ID]

You can also make a crosstab query to transpose data to be a column.

Using the two together, probably in two or more queries, you can get to
where you want to go.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

Hi,


I have a database containing info about response times to user
requests. In addition to the response time, each record contains date
when the request was made. Now I need to count two averages for each
month: average for top 90% of the response times (ordered by response
time to get the top 90%) and other average for the remaining 10%.


Example table:
Date(month) Response time (minutes)
January 1
January 2
January 5
January 4
February 8
February 2


The query should then return something like this:
Date(month) Avg top 90% Avg remaining
January X Y
February X2 Y2


Where X would be the average response time for the fastest 90% of the
response times, and Y would be the average for the remaining requests
that have the longest response times.


How could I do this with Access?


Thanks in advance,
Petteri

 

[Gary Walter]

Hi Petteri,

I might assume you have a lot of data and your table has a pk like "ID" in tblResponse below.


tblResponse ID RespDate RespTime
1 5/1/2005 3
2 5/1/2005 6
3 5/2/2005 4
4 5/3/2005 2
5 5/4/2005 4
6 5/6/2005 3
7 5/7/2005 8
8 5/8/2005 5
9 5/9/2005 5
10 5/10/2005 3
11 5/11/2005 2
12 5/12/2005 4
13 5/13/2005 1
14 5/14/2005 8
15 5/15/2005 4
16 5/16/2005 3
17 5/17/2005 6
18 5/18/2005 7
19 5/19/2005 7
20 5/20/2005 4
21 6/1/2005 3
22 6/1/2005 5
23 6/2/2005 3
24 6/3/2005 6
25 6/4/2005 9
26 6/6/2005 2
27 6/7/2005 4
28 6/8/2005 5
29 6/9/2005 7
30 6/10/2005 3
31 6/11/2005 1
32 6/12/2005 4
33 6/13/2005 6
34 6/14/2005 3
35 6/15/2005 7
36 6/16/2005 4
37 6/17/2005 4
38 6/18/2005 2
39 6/19/2005 7
40 6/20/2005 5
41 6/21/2005 5
42 6/22/2005 3
43 6/23/2005 9
44 6/24/2005 4
45 6/25/2005 6


I also might assume that you want
to show your results in a report.

If the above is true, then while the
following probably can be achieved in a
Steve-Dassin-like crosstab, it might
help to break the process down into
query steps.

The gist of this method is to rank
the times within a year/month group.
Then you can use this rank to give each
time a "rank %", i.e., Rank/Count of group.

You can then group on (RankPercent<=.9)
to get your 2 groups which you can apply
aggregates to.

I might suggest creating a scratch table
(say "tblScratch") with following fields:

RespYear Long
RespMonth Long
RespTime Long
ID Long
Rank Long
RankPerCent Single

When you start the process, clear tblScratch
of all records. Then run the following append qry:

INSERT INTO tblScratch ( RespYear, RespMonth, RespTime, ID, Rank )
SELECT
Year([RespDate]) AS RespYear,
Month([RespDate]) AS RespMonth,
t1.RespTime,
t1.ID,
(SELECT COUNT(*) FROM tblResponse t2
WHERE
Year(t2.RespDate)=Year(t1.RespDate)
AND
Month(t2.RespDate)=Month(t1.RespDate)
AND
t2.RespTime*1000+t2.ID*.001 < t1.RespTime*1000+t1.ID*.001)+1 AS Rank
FROM tblResponse AS t1
ORDER BY
Year([RespDate]),
Month([RespDate]),
t1.RespTime,
t1.ID;

then run the following update qry:

UPDATE tblScratch AS S
SET S.RankPerCent = .[Rank]/DCount("*","tblScratch","[RespYear]=" & .[RespYear] & " AND [RespMonth]=" & .[RespMonth]);

with above data, your tblScratch would now look like:

tblScratch RespYear RespMonth RespTime ID Rank RankPerCent
2005 5 1 13 1 0.05
2005 5 2 4 2 0.1
2005 5 2 11 3 0.15
2005 5 3 1 4 0.2
2005 5 3 6 5 0.25
2005 5 3 10 6 0.3
2005 5 3 16 7 0.35
2005 5 4 3 8 0.4
2005 5 4 5 9 0.45
2005 5 4 12 10 0.5
2005 5 4 15 11 0.55
2005 5 4 20 12 0.6
2005 5 5 8 13 0.65
2005 5 5 9 14 0.7
2005 5 6 2 15 0.75
2005 5 6 17 16 0.8
2005 5 7 18 17 0.85
2005 5 7 19 18 0.9
2005 5 8 7 19 0.95
2005 5 8 14 20 1
2005 6 1 31 1 0.04
2005 6 2 26 2 0.08
2005 6 2 38 3 0.12
2005 6 3 21 4 0.16
2005 6 3 23 5 0.2
2005 6 3 30 6 0.24
2005 6 3 34 7 0.28
2005 6 3 42 8 0.32
2005 6 4 27 9 0.36
2005 6 4 32 10 0.4
2005 6 4 36 11 0.44
2005 6 4 37 12 0.48
2005 6 4 44 13 0.52
2005 6 5 22 14 0.56
2005 6 5 28 15 0.6
2005 6 5 40 16 0.64
2005 6 5 41 17 0.68
2005 6 6 24 18 0.72
2005 6 6 33 19 0.76
2005 6 6 45 20 0.8
2005 6 7 29 21 0.84
2005 6 7 35 22 0.88
2005 6 7 39 23 0.92
2005 6 9 25 24 0.96
2005 6 9 43 25 1



From here, you could go several ways.

One way would be to base your report on the
following query:

SELECT
S.RespYear,
S.RespMonth,
Count(S.RespTime) AS Cnt,
Min(S.RespTime) AS MinRespTime,
Max(S.RespTime) AS MaxRespTime,
Avg(S.RespTime) AS AvgRespTime,
[RankPerCent]<=0.9 AS InTop90
FROM tblScratch AS S
GROUP BY
S.RespYear,
S.RespMonth,
[RankPerCent]<=0.9;

qryrptResponseTime RespYear RespMonth Cnt MinRespTime MaxRespTime AvgRespTime InTop90
2005 5 18 1 7 4.05555555555556 -1
2005 5 2 8 8 8 0
2005 6 22 1 7 4.18181818181818 -1
2005 6 3 7 9 8.33333333333333 0

Hi Steve,

and thank you for your answer!

I'm still having some problems with the query. I know how to use the average
function, but the problem is that it can't be
used directly in this case as it would calculate average amount for each
month.
What I need is that the query should
1) Take 90% of the fastest response times per month (e.g. sort the response
times for each month, then take top-90% of these per month)
2) Calculate average for these top 90% response times per month
3) Calculate average for the remaining 10%

So this way each month would have two averages, one for the faster reponse
times and one for the slower ones.

So my guess is that I should somehow combine the average-function and the
"SELECT TOP 90 PERCENT" attribute in access/SQL,
but I don't know how... Does anyone have clue how to do this?


Cheers,
Petteri

I'll admit that I don't totally understand, but you can use the Average
operator in a query.

SELECT [ID], Average([Field]) FROM [Tablename] GROUP BY [ID]

You can also make a crosstab query to transpose data to be a column.

Using the two together, probably in two or more queries, you can get to
where you want to go.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

Hi,


I have a database containing info about response times to user
requests. In addition to the response time, each record contains date
when the request was made. Now I need to count two averages for each
month: average for top 90% of the response times (ordered by response
time to get the top 90%) and other average for the remaining 10%.


Example table:
Date(month) Response time (minutes)
January 1
January 2
January 5
January 4
February 8
February 2


The query should then return something like this:
Date(month) Avg top 90% Avg remaining
January X Y
February X2 Y2


Where X would be the average response time for the fastest 90% of the
response times, and Y would be the average for the remaining requests
that have the longest response times.


How could I do this with Access?


Thanks in advance,
Petteri

 

[Petteri Toukoniitty]

Great, this works!

Thank you Gary and Steve for your help!


BR,
Petteri Toukoniitty

Hi Petteri,

I might assume you have a lot of data and your table has a pk like "ID" in
tblResponse below.


tblResponseIDRespDateRespTime
15/1/20053
25/1/20056
35/2/20054
45/3/20052
55/4/20054
65/6/20053
75/7/20058
85/8/20055
95/9/20055
105/10/20053
115/11/20052
125/12/20054
135/13/20051
145/14/20058
155/15/20054
165/16/20053
175/17/20056
185/18/20057
195/19/20057
205/20/20054
216/1/20053
226/1/20055
236/2/20053
246/3/20056
256/4/20059
266/6/20052
276/7/20054
286/8/20055
296/9/20057
306/10/20053
316/11/20051
326/12/20054
336/13/20056
346/14/20053
356/15/20057
366/16/20054
376/17/20054
386/18/20052
396/19/20057
406/20/20055
416/21/20055
426/22/20053
436/23/20059
446/24/20054
456/25/20056


I also might assume that you want
to show your results in a report.

If the above is true, then while the
following probably can be achieved in a
Steve-Dassin-like crosstab, it might
help to break the process down into
query steps.

The gist of this method is to rank
the times within a year/month group.
Then you can use this rank to give each
time a "rank %", i.e., Rank/Count of group.

You can then group on (RankPercent<=.9)
to get your 2 groups which you can apply
aggregates to.

I might suggest creating a scratch table
(say "tblScratch") with following fields:

RespYear Long
RespMonth Long
RespTime Long
ID Long
Rank Long
RankPerCent Single

When you start the process, clear tblScratch
of all records. Then run the following append qry:

INSERT INTO tblScratch ( RespYear, RespMonth, RespTime, ID, Rank )
SELECT
Year([RespDate]) AS RespYear,
Month([RespDate]) AS RespMonth,
t1.RespTime,
t1.ID,
(SELECT COUNT(*) FROM tblResponse t2
WHERE
Year(t2.RespDate)=Year(t1.RespDate)
AND
Month(t2.RespDate)=Month(t1.RespDate)
AND
t2.RespTime*1000+t2.ID*.001 < t1.RespTime*1000+t1.ID*.001)+1 AS Rank
FROM tblResponse AS t1
ORDER BY
Year([RespDate]),
Month([RespDate]),
t1.RespTime,
t1.ID;

then run the following update qry:

UPDATE tblScratch AS S
SET S.RankPerCent = .[Rank]/DCount("*","tblScratch","[RespYear]=" &
.[RespYear] & " AND [RespMonth]=" & .[RespMonth]);

with above data, your tblScratch would now look like:

tblScratchRespYearRespMonthRespTimeIDRankRankPerCent
2005511310.05
200552420.1
2005521130.15
200553140.2
200553650.25
2005531060.3
2005531670.35
200554380.4
200554590.45
20055412100.5
20055415110.55
20055420120.6
2005558130.65
2005559140.7
2005562150.75
20055617160.8
20055718170.85
20055719180.9
2005587190.95
20055814201
2005613110.04
2005622620.08
2005623830.12
2005632140.16
2005632350.2
2005633060.24
2005633470.28
2005634280.32
2005642790.36
20056432100.4
20056436110.44
20056437120.48
20056444130.52
20056522140.56
20056528150.6
20056540160.64
20056541170.68
20056624180.72
20056633190.76
20056645200.8
20056729210.84
20056735220.88
20056739230.92
20056925240.96
20056943251



From here, you could go several ways.

One way would be to base your report on the
following query:

SELECT
S.RespYear,
S.RespMonth,
Count(S.RespTime) AS Cnt,
Min(S.RespTime) AS MinRespTime,
Max(S.RespTime) AS MaxRespTime,
Avg(S.RespTime) AS AvgRespTime,
[RankPerCent]<=0.9 AS InTop90
FROM tblScratch AS S
GROUP BY
S.RespYear,
S.RespMonth,
[RankPerCent]<=0.9;

qryrptResponseTimeRespYearRespMonthCntMinRespTimeMaxRespTimeAvgRespTimeInTop
90
2005518174.05555555555556-1
2005528880
2005622174.18181818181818-1
200563798.333333333333330

Hi Steve,

and thank you for your answer!

I'm still having some problems with the query. I know how to use the average
function, but the problem is that it can't be
used directly in this case as it would calculate average amount for each
month.
What I need is that the query should
1) Take 90% of the fastest response times per month (e.g. sort the response
times for each month, then take top-90% of these per month)
2) Calculate average for these top 90% response times per month
3) Calculate average for the remaining 10%

So this way each month would have two averages, one for the faster reponse
times and one for the slower ones.

So my guess is that I should somehow combine the average-function and the
"SELECT TOP 90 PERCENT" attribute in access/SQL,
but I don't know how... Does anyone have clue how to do this?


Cheers,
Petteri

I'll admit that I don't totally understand, but you can use the Average
operator in a query.

SELECT [ID], Average([Field]) FROM [Tablename] GROUP BY [ID]

You can also make a crosstab query to transpose data to be a column.

Using the two together, probably in two or more queries, you can get to
where you want to go.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

Hi,


I have a database containing info about response times to user
requests. In addition to the response time, each record contains date
when the request was made. Now I need to count two averages for each
month: average for top 90% of the response times (ordered by response
time to get the top 90%) and other average for the remaining 10%.


Example table:
Date(month) Response time (minutes)
January 1
January 2
January 5
January 4
February 8
February 2


The query should then return something like this:
Date(month) Avg top 90% Avg remaining
January X Y
February X2 Y2


Where X would be the average response time for the fastest 90% of the
response times, and Y would be the average for the remaining requests
that have the longest response times.


How could I do this with Access?


Thanks in advance,
Petteri