SQL Query for 15 minute Maximum values
I have a table which is updated every 10 seconds (around, not exactly because this is updated using data sent via a GSM modem).
I have to do few things using this data.
1. Get 15 minute maximum values for whole 15 minute intervals for last 24 hours.
E.g: If the time now is say 2013-08-15 18:52
Resulting query should be like...
Timestamp MaxValue
2013-08-14 18:45 53.7
2013-08-14 19:00 56.5
2013-08-14 19:15 56
...
...
...
2013-08-15 18:00 55.4
2013-08-15 18:15 54.5
2013-08-15 18:30 49.3
2013-08-15 18:45 55.8
2. Get the percentiles (say 90th) for whole 15 minute intervals for last 24 hours as above.
3. Get the logarithmic average for whole 15 minute intervals for last 24 hours as above.
Below is how the original table looks like.
Timestamp Value
2013-08-14 18:51:33.000 53.7
2013-08-14 18:52:33.000 56.5
2013-08-14 18:53:32.000 56
2013-08-14 18:54:36.000 55.4
2013-08-14 18:55:33.000 54.5
2013-08-14 18:56:33.000 49.3
2013-08-14 18:57:33.000 55.8
2013-08-14 18:58:32.000 55.6
2013-08-14 18:59:38.000 54.6
2013-08-14 19:00:32.000 49.2
2013-08-14 19:01:33.000 56.4
2013-08-14 19:02:32.000 55
2013-08-14 19:03:33.000 48.9
2013-08-14 19:04:38.000 55.6
2013-08-14 19:05:32.000 55.8
2013-08-14 19:06:32.000 54.6
I have to do few things using this data.
1. Get 15 minute maximum values for whole 15 minute intervals for last 24 hours.
E.g: If the time now is say 2013-08-15 18:52
Resulting query should be like...
Timestamp MaxValue
2013-08-14 18:45 53.7
2013-08-14 19:00 56.5
2013-08-14 19:15 56
...
...
...
2013-08-15 18:00 55.4
2013-08-15 18:15 54.5
2013-08-15 18:30 49.3
2013-08-15 18:45 55.8
2. Get the percentiles (say 90th) for whole 15 minute intervals for last 24 hours as above.
3. Get the logarithmic average for whole 15 minute intervals for last 24 hours as above.
Below is how the original table looks like.
Timestamp Value
2013-08-14 18:51:33.000 53.7
2013-08-14 18:52:33.000 56.5
2013-08-14 18:53:32.000 56
2013-08-14 18:54:36.000 55.4
2013-08-14 18:55:33.000 54.5
2013-08-14 18:56:33.000 49.3
2013-08-14 18:57:33.000 55.8
2013-08-14 18:58:32.000 55.6
2013-08-14 18:59:38.000 54.6
2013-08-14 19:00:32.000 49.2
2013-08-14 19:01:33.000 56.4
2013-08-14 19:02:32.000 55
2013-08-14 19:03:33.000 48.9
2013-08-14 19:04:38.000 55.6
2013-08-14 19:05:32.000 55.8
2013-08-14 19:06:32.000 54.6
ASKER
Thanks a bunch PortletPaul for your quick response, answer and for the explanation too.
You made my day!!! (well... 1/3 of the day :) )
Yes, my field name is LastUpdated.
Could you please help with the log average and percentiles too.
With the percentiles I need to sort the raw values (for each 15 min - about 90 rows approximately of 10 second intervals) from highest to lowest and get the 90% th value from tthe bottom (for 90th Percentile). So, it will be around the 81st raw value from the bottom.(90% of 90 rows).
Hope I made it clear.
You made my day!!! (well... 1/3 of the day :) )
Yes, my field name is LastUpdated.
Could you please help with the log average and percentiles too.
With the percentiles I need to sort the raw values (for each 15 min - about 90 rows approximately of 10 second intervals) from highest to lowest and get the 90% th value from tthe bottom (for 90th Percentile). So, it will be around the 81st raw value from the bottom.(90% of 90 rows).
Hope I made it clear.
not sure I even understand the log average I'm afraid, had a look at wikipedia
http://en.wikipedia.org/wiki/Logarithmic_mean
and it requires 2 non-negative numbers
so is that Value of this row and Value of the previous row?
(this bit would be easier in SQL 2012 using LAG() )
also, for percentile calculation, there is a percent_rank() in sql 2012
so, do you really use sql 2008 or do you happen to have sql 2012?
---
tip, always use real table and field names, in the long run it is easier for all involved
http://en.wikipedia.org/wiki/Logarithmic_mean
and it requires 2 non-negative numbers
so is that Value of this row and Value of the previous row?
(this bit would be easier in SQL 2012 using LAG() )
also, for percentile calculation, there is a percent_rank() in sql 2012
so, do you really use sql 2008 or do you happen to have sql 2012?
---
tip, always use real table and field names, in the long run it is easier for all involved
ASKER
Thanks again.
We are using 2008 and dont have 2012.
I know how to do them in Excel.
E2:E95 = Row values
F2:F95 = Intermediate value (10^(E2/10))
K2 = Final Value (10*LOG(AVERAGE(F2:F95)))
90th Percentile:
=PERCENTILE(C2:C95,0.1)
Log Average Intermediate: Column F(conversion units)
=10^(E2/10)
and then using the result...
Log Average: Column K(Leq)
=10*LOG(AVERAGE(F2:F95))
Excel Results are here
Column E - Leq (Starting 38.65)
F - Conversion Units (Starting 7327.245331) - this is an intermediate value for K
I - L90 (90th Percentile) 29 here (for that 15 minute interval)
K - Log Average - 35 here (for that 15 minute interval)
So, I am looking for the values I and K for each 15 min intervals throughout a day (24*4 = 96 values appx)
We are using 2008 and dont have 2012.
I know how to do them in Excel.
E2:E95 = Row values
F2:F95 = Intermediate value (10^(E2/10))
K2 = Final Value (10*LOG(AVERAGE(F2:F95)))
90th Percentile:
=PERCENTILE(C2:C95,0.1)
Log Average Intermediate: Column F(conversion units)
=10^(E2/10)
and then using the result...
Log Average: Column K(Leq)
=10*LOG(AVERAGE(F2:F95))
Excel Results are here
SPL MAX Leq conversion units L1 L10 L90 Lmax Leq
1 18/10/2013 8:24 37.7 39.6 38.65 7328.245331 40 37 29 49 35
2 18/10/2013 8:24 34.6 33.7 34.15 2600.159563
3 18/10/2013 8:24 28.7 33.5 31.1 1288.249552
4 18/10/2013 8:24 28.5 37.5 33 1995.262315
5 18/10/2013 8:24 32.5 33.7 33.1 2041.737945
6 18/10/2013 8:24 28.7 40.4 34.55 2851.018268
7 18/10/2013 8:24 35.4 37.3 36.35 4315.190768
8 18/10/2013 8:25 32.3 33.8 33.05 2018.366364
9 18/10/2013 8:25 28.8 33.8 31.3 1348.962883
10 18/10/2013 8:25 28.8 37.3 33.05 2018.366364
11 18/10/2013 8:25 32.3 33.9 33.1 2041.737945
12 18/10/2013 8:25 28.9 40.7 34.8 3019.95172
13 18/10/2013 8:25 35.7 34 34.85 3054.921113
14 18/10/2013 8:26 29 33.9 31.45 1396.368361
15 18/10/2013 8:26 28.9 37.5 33.2 2089.296131
16 18/10/2013 8:26 32.5 33.8 33.15 2065.380156
17 18/10/2013 8:26 28.8 34.2 31.5 1412.537545
18 18/10/2013 8:26 29.2 37.5 33.35 2162.718524
19 18/10/2013 8:26 32.5 34.3 33.4 2187.761624
20 18/10/2013 8:27 29.3 37.7 33.5 2238.721139
21 18/10/2013 8:27 32.7 34.2 33.45 2213.09471
22 18/10/2013 8:27 29.2 34.2 31.7 1479.108388
23 18/10/2013 8:27 29.2 37.5 33.35 2162.718524
24 18/10/2013 8:27 32.5 34.5 33.5 2238.721139
25 18/10/2013 8:27 29.5 40.8 35.15 3273.406949
26 18/10/2013 8:28 35.8 34.6 35.2 3311.311215
27 18/10/2013 8:28 29.6 34.5 32.05 1603.245391
28 18/10/2013 8:28 29.5 41 35.25 3349.654392
29 18/10/2013 8:28 36 34.5 35.25 3349.654392
30 18/10/2013 8:28 29.5 39.9 34.7 2951.209227
31 18/10/2013 8:29 34.9 34.9 34.9 3090.295433
32 18/10/2013 8:29 29.9 40.8 35.35 3427.677865
33 18/10/2013 8:29 35.8 37.9 36.85 4841.723676
34 18/10/2013 8:29 32.9 34.7 33.8 2398.832919
35 18/10/2013 8:29 29.7 40.8 35.25 3349.654392
36 18/10/2013 8:30 35.8 37.8 36.8 4786.300923
37 18/10/2013 8:30 32.8 34.4 33.6 2290.867653
38 18/10/2013 8:30 29.4 42.2 35.8 3801.893963
39 18/10/2013 8:30 37.2 34.6 35.9 3890.45145
40 18/10/2013 8:30 29.6 34.8 32.2 1659.586907
41 18/10/2013 8:30 29.8 37.8 33.8 2398.832919
42 18/10/2013 8:31 32.8 34.7 33.75 2371.373706
43 18/10/2013 8:31 29.7 41.2 35.45 3507.51874
44 18/10/2013 8:31 36.2 38 37.1 5128.61384
45 18/10/2013 8:31 33 34.6 33.8 2398.832919
46 18/10/2013 8:31 29.6 41 35.3 3388.441561
47 18/10/2013 8:31 36 34.8 35.4 3467.368505
48 18/10/2013 8:32 29.8 40.9 35.35 3427.677865
49 18/10/2013 8:32 35.9 35 35.45 3507.51874
50 18/10/2013 8:32 30 34.9 32.45 1757.923614
51 18/10/2013 8:32 29.9 41.3 35.6 3630.780548
52 18/10/2013 8:32 36.3 37.9 37.1 5128.61384
53 18/10/2013 8:32 32.9 35.1 34 2511.886432
54 18/10/2013 8:33 30.1 38.1 34.1 2570.395783
55 18/10/2013 8:33 33.1 35.1 34.1 2570.395783
56 18/10/2013 8:33 30.1 35.2 32.65 1840.772001
57 18/10/2013 8:33 30.2 38.1 34.15 2600.159563
58 18/10/2013 8:33 33.1 36.1 34.6 2884.031503
59 18/10/2013 8:33 31.1 48.9 40 10000
60 18/10/2013 8:34 43.9 41.2 42.55 17988.70915
61 18/10/2013 8:34 36.2 35.8 36 3981.071706
62 18/10/2013 8:34 30.8 41.7 36.25 4216.965034
63 18/10/2013 8:34 36.7 38.9 37.8 6025.595861
64 18/10/2013 8:34 33.9 35.8 34.85 3054.921113
65 18/10/2013 8:34 30.8 42.2 36.5 4466.835922
66 18/10/2013 8:35 37.2 35.6 36.4 4365.158322
67 18/10/2013 8:35 30.6 35.6 33.1 2041.737945
68 18/10/2013 8:35 30.6 39.2 34.9 3090.295433
69 18/10/2013 8:35 34.2 35.6 34.9 3090.295433
70 18/10/2013 8:35 30.6 42.2 36.4 4365.158322
71 18/10/2013 8:35 37.2 37.9 37.55 5688.529308
72 18/10/2013 8:36 32.9 35.4 34.15 2600.159563
73 18/10/2013 8:36 30.4 41.7 36.05 4027.170343
74 18/10/2013 8:36 36.7 35.1 35.9 3890.45145
75 18/10/2013 8:36 30.1 35.5 32.8 1905.460718
76 18/10/2013 8:36 30.5 39.3 34.9 3090.295433
77 18/10/2013 8:36 34.3 35.1 34.7 2951.209227
78 18/10/2013 8:37 30.1 43 36.55 4518.559444
79 18/10/2013 8:37 38 39.5 38.75 7498.942093
80 18/10/2013 8:37 34.5 35 34.75 2985.382619
81 18/10/2013 8:37 30 43.3 36.65 4623.810214
82 18/10/2013 8:37 38.3 34.9 36.6 4570.881896
83 18/10/2013 8:37 29.9 43.2 36.55 4518.559444
84 18/10/2013 8:38 38.2 39.5 38.85 7673.614894
85 18/10/2013 8:38 34.5 35 34.75 2985.382619
86 18/10/2013 8:38 30 42.8 36.4 4365.158322
87 18/10/2013 8:38 37.8 37.7 37.75 5956.621435
88 18/10/2013 8:38 32.7 34.9 33.8 2398.832919
89 18/10/2013 8:38 29.9 44.3 37.1 5128.61384
90 18/10/2013 8:39 39.3 34.8 37.05 5069.907083
91 18/10/2013 8:39 29.8 43 36.4 4365.158322
92 18/10/2013 8:39 38 35.4 36.7 4677.351413
93 18/10/2013 8:39 30.4 44 37.2 5248.074602
94 18/10/2013 8:39 39 5 22 158.4893192 Column E - Leq (Starting 38.65)
F - Conversion Units (Starting 7327.245331) - this is an intermediate value for K
I - L90 (90th Percentile) 29 here (for that 15 minute interval)
K - Log Average - 35 here (for that 15 minute interval)
So, I am looking for the values I and K for each 15 min intervals throughout a day (24*4 = 96 values appx)
Is there some magic missing?
Your log data holds 2 fields LastUpdated and Value
is column C (raw) Value?
what is column D?
and how does one arrive at column E?
or is column E the raw value? (in which case how do you arrive at column C)
:( confused ):
Your log data holds 2 fields LastUpdated and Value
is column C (raw) Value?
what is column D?
and how does one arrive at column E?
or is column E the raw value? (in which case how do you arrive at column C)
:( confused ):
ASKER
Sorry about not removing the stuff not relevant.
OK, for Log Averages here is the table...
Raw Values(Column C) = Raw Value (starting with 38.65)
Conversion Units (Column D) = Excel equation => =10^(C2/10) (starting with 7328.245331)
Leq (Column E) = Excel equation => =10*LOG(AVERAGE(D2:D95)) -- This is the last figure I need (96 of these per day) (starting with 35)
For Percentiles
Raw Values(Column C) = Raw Value (starting with 37.7)
L90 (Column D) = 90th Percentile (Excel equation=> =PERCENTILE(C2:C95,0.1)) (starting with 29)
OK, for Log Averages here is the table...
Raw Values(Column C) = Raw Value (starting with 38.65)
Conversion Units (Column D) = Excel equation => =10^(C2/10) (starting with 7328.245331)
Leq (Column E) = Excel equation => =10*LOG(AVERAGE(D2:D95)) -- This is the last figure I need (96 of these per day) (starting with 35)
Raw conversion units Leq
1 18/10/2013 8:24 38.65 7328.245331 35
2 18/10/2013 8:24 34.15 2600.159563
3 18/10/2013 8:24 31.1 1288.249552
4 18/10/2013 8:24 33 1995.262315
5 18/10/2013 8:24 33.1 2041.737945
6 18/10/2013 8:24 34.55 2851.018268
7 18/10/2013 8:24 36.35 4315.190768
8 18/10/2013 8:25 33.05 2018.366364
9 18/10/2013 8:25 31.3 1348.962883
10 18/10/2013 8:25 33.05 2018.366364
11 18/10/2013 8:25 33.1 2041.737945
12 18/10/2013 8:25 34.8 3019.95172
13 18/10/2013 8:25 34.85 3054.921113
14 18/10/2013 8:26 31.45 1396.368361
15 18/10/2013 8:26 33.2 2089.296131
16 18/10/2013 8:26 33.15 2065.380156
17 18/10/2013 8:26 31.5 1412.537545
18 18/10/2013 8:26 33.35 2162.718524
19 18/10/2013 8:26 33.4 2187.761624
20 18/10/2013 8:27 33.5 2238.721139
21 18/10/2013 8:27 33.45 2213.09471
22 18/10/2013 8:27 31.7 1479.108388
23 18/10/2013 8:27 33.35 2162.718524
24 18/10/2013 8:27 33.5 2238.721139
25 18/10/2013 8:27 35.15 3273.406949
26 18/10/2013 8:28 35.2 3311.311215
27 18/10/2013 8:28 32.05 1603.245391
28 18/10/2013 8:28 35.25 3349.654392
29 18/10/2013 8:28 35.25 3349.654392
30 18/10/2013 8:28 34.7 2951.209227
31 18/10/2013 8:29 34.9 3090.295433
32 18/10/2013 8:29 35.35 3427.677865
33 18/10/2013 8:29 36.85 4841.723676
34 18/10/2013 8:29 33.8 2398.832919
35 18/10/2013 8:29 35.25 3349.654392
36 18/10/2013 8:30 36.8 4786.300923
37 18/10/2013 8:30 33.6 2290.867653
38 18/10/2013 8:30 35.8 3801.893963
39 18/10/2013 8:30 35.9 3890.45145
40 18/10/2013 8:30 32.2 1659.586907
41 18/10/2013 8:30 33.8 2398.832919
42 18/10/2013 8:31 33.75 2371.373706
43 18/10/2013 8:31 35.45 3507.51874
44 18/10/2013 8:31 37.1 5128.61384
45 18/10/2013 8:31 33.8 2398.832919
46 18/10/2013 8:31 35.3 3388.441561
47 18/10/2013 8:31 35.4 3467.368505
48 18/10/2013 8:32 35.35 3427.677865
49 18/10/2013 8:32 35.45 3507.51874
50 18/10/2013 8:32 32.45 1757.923614
51 18/10/2013 8:32 35.6 3630.780548
52 18/10/2013 8:32 37.1 5128.61384
53 18/10/2013 8:32 34 2511.886432
54 18/10/2013 8:33 34.1 2570.395783
55 18/10/2013 8:33 34.1 2570.395783
56 18/10/2013 8:33 32.65 1840.772001
57 18/10/2013 8:33 34.15 2600.159563
58 18/10/2013 8:33 34.6 2884.031503
59 18/10/2013 8:33 40 10000
60 18/10/2013 8:34 42.55 17988.70915
61 18/10/2013 8:34 36 3981.071706
62 18/10/2013 8:34 36.25 4216.965034
63 18/10/2013 8:34 37.8 6025.595861
64 18/10/2013 8:34 34.85 3054.921113
65 18/10/2013 8:34 36.5 4466.835922
66 18/10/2013 8:35 36.4 4365.158322
67 18/10/2013 8:35 33.1 2041.737945
68 18/10/2013 8:35 34.9 3090.295433
69 18/10/2013 8:35 34.9 3090.295433
70 18/10/2013 8:35 36.4 4365.158322
71 18/10/2013 8:35 37.55 5688.529308
72 18/10/2013 8:36 34.15 2600.159563
73 18/10/2013 8:36 36.05 4027.170343
74 18/10/2013 8:36 35.9 3890.45145
75 18/10/2013 8:36 32.8 1905.460718
76 18/10/2013 8:36 34.9 3090.295433
77 18/10/2013 8:36 34.7 2951.209227
78 18/10/2013 8:37 36.55 4518.559444
79 18/10/2013 8:37 38.75 7498.942093
80 18/10/2013 8:37 34.75 2985.382619
81 18/10/2013 8:37 36.65 4623.810214
82 18/10/2013 8:37 36.6 4570.881896
83 18/10/2013 8:37 36.55 4518.559444
84 18/10/2013 8:38 38.85 7673.614894
85 18/10/2013 8:38 34.75 2985.382619
86 18/10/2013 8:38 36.4 4365.158322
87 18/10/2013 8:38 37.75 5956.621435
88 18/10/2013 8:38 33.8 2398.832919
89 18/10/2013 8:38 37.1 5128.61384
90 18/10/2013 8:39 37.05 5069.907083
91 18/10/2013 8:39 36.4 4365.158322
92 18/10/2013 8:39 36.7 4677.351413
93 18/10/2013 8:39 37.2 5248.074602
94 18/10/2013 8:39 22 158.4893192 For Percentiles
Raw Values(Column C) = Raw Value (starting with 37.7)
L90 (Column D) = 90th Percentile (Excel equation=> =PERCENTILE(C2:C95,0.1)) (starting with 29)
Raw L90
1 18/10/2013 8:24 37.7 29
2 18/10/2013 8:24 34.6
3 18/10/2013 8:24 28.7
4 18/10/2013 8:24 28.5
5 18/10/2013 8:24 32.5
6 18/10/2013 8:24 28.7
7 18/10/2013 8:24 35.4
8 18/10/2013 8:25 32.3
9 18/10/2013 8:25 28.8
10 18/10/2013 8:25 28.8
11 18/10/2013 8:25 32.3
12 18/10/2013 8:25 28.9
13 18/10/2013 8:25 35.7
14 18/10/2013 8:26 29
15 18/10/2013 8:26 28.9
16 18/10/2013 8:26 32.5
17 18/10/2013 8:26 28.8
18 18/10/2013 8:26 29.2
19 18/10/2013 8:26 32.5
20 18/10/2013 8:27 29.3
21 18/10/2013 8:27 32.7
22 18/10/2013 8:27 29.2
23 18/10/2013 8:27 29.2
24 18/10/2013 8:27 32.5
25 18/10/2013 8:27 29.5
26 18/10/2013 8:28 35.8
27 18/10/2013 8:28 29.6
28 18/10/2013 8:28 29.5
29 18/10/2013 8:28 36
30 18/10/2013 8:28 29.5
31 18/10/2013 8:29 34.9
32 18/10/2013 8:29 29.9
33 18/10/2013 8:29 35.8
34 18/10/2013 8:29 32.9
35 18/10/2013 8:29 29.7
36 18/10/2013 8:30 35.8
37 18/10/2013 8:30 32.8
38 18/10/2013 8:30 29.4
39 18/10/2013 8:30 37.2
40 18/10/2013 8:30 29.6
41 18/10/2013 8:30 29.8
42 18/10/2013 8:31 32.8
43 18/10/2013 8:31 29.7
44 18/10/2013 8:31 36.2
45 18/10/2013 8:31 33
46 18/10/2013 8:31 29.6
47 18/10/2013 8:31 36
48 18/10/2013 8:32 29.8
49 18/10/2013 8:32 35.9
50 18/10/2013 8:32 30
51 18/10/2013 8:32 29.9
52 18/10/2013 8:32 36.3
53 18/10/2013 8:32 32.9
54 18/10/2013 8:33 30.1
55 18/10/2013 8:33 33.1
56 18/10/2013 8:33 30.1
57 18/10/2013 8:33 30.2
58 18/10/2013 8:33 33.1
59 18/10/2013 8:33 31.1
60 18/10/2013 8:34 43.9
61 18/10/2013 8:34 36.2
62 18/10/2013 8:34 30.8
63 18/10/2013 8:34 36.7
64 18/10/2013 8:34 33.9
65 18/10/2013 8:34 30.8
66 18/10/2013 8:35 37.2
67 18/10/2013 8:35 30.6
68 18/10/2013 8:35 30.6
69 18/10/2013 8:35 34.2
70 18/10/2013 8:35 30.6
71 18/10/2013 8:35 37.2
72 18/10/2013 8:36 32.9
73 18/10/2013 8:36 30.4
74 18/10/2013 8:36 36.7
75 18/10/2013 8:36 30.1
76 18/10/2013 8:36 30.5
77 18/10/2013 8:36 34.3
78 18/10/2013 8:37 30.1
79 18/10/2013 8:37 38
80 18/10/2013 8:37 34.5
81 18/10/2013 8:37 30
82 18/10/2013 8:37 38.3
83 18/10/2013 8:37 29.9
84 18/10/2013 8:38 38.2
85 18/10/2013 8:38 34.5
86 18/10/2013 8:38 30
87 18/10/2013 8:38 37.8
88 18/10/2013 8:38 32.7
89 18/10/2013 8:38 29.9
90 18/10/2013 8:39 39.3
91 18/10/2013 8:39 29.8
92 18/10/2013 8:39 38
93 18/10/2013 8:39 30.4
94 18/10/2013 8:39 39
Thanks!
But I will be unable to agree with your figures IF my rounding of minutes is correct.
Currently your data might fit inside a 15 minute period, but you are free to choose when that period starts e.g. you can start at:
18/10/2013 8:24
and finish at
18/10/2013 8:39
but if I'm rounding off into 15 minute chunks starting at 00:00:00 then
18/10/2013 8:24 through 18/10/2013 8:29:59.99999 is in one chunk
and
18/10/2013 8:30 through 18/10/2013 8:44.59.99999 is in another chunk
I think I have the percentile worked out, working on that log thingy
But I will be unable to agree with your figures IF my rounding of minutes is correct.
Currently your data might fit inside a 15 minute period, but you are free to choose when that period starts e.g. you can start at:
18/10/2013 8:24
and finish at
18/10/2013 8:39
but if I'm rounding off into 15 minute chunks starting at 00:00:00 then
18/10/2013 8:24 through 18/10/2013 8:29:59.99999 is in one chunk
and
18/10/2013 8:30 through 18/10/2013 8:44.59.99999 is in another chunk
I think I have the percentile worked out, working on that log thingy
OK, believe I have both calculations now. Here's the latest query:
SQL 2008 does not have a "percentile" and the closest is percent_rank in SQL 2012
that calculation can be mimicked by calculating rank() and count() over the rounded date/time range, like so:
1.0 * (rank-1) / (count-1)
replacing "rank-1" with "(rank() over (partition BY RoundTime ORDER BY MX)-1)" and
replacing "count-1" with " (count(*) over (partition BY RoundTime)-1)"
we arrive at the code of line 10 above.
So we can calculate the percentile rank of each row. In the outer query we then use a case expression to look for percentile ranks <= 0.1 {this is the equivalent of the Excel percentile(--range--,0.1) } and we take the maximum value that falls into that range of percentiles.
For log average:
just like the Excel sheet we calculate the power(10,value) for each row (equivalent of 10^(C2/10)).
Then in the outer query we take an average of that calculation over the time range, get the log (base 10) of that number and multiply by 10.
Here's my summary result:
SELECT
RoundTime
, max(CASE WHEN pct_rnk <=0.1 THEN mx END) AS pct_90
, 10*log10(avg(pwr)) AS log_avg
FROM (
SELECT
spl
, RoundTime
, mx
, 1.0 * (rank() over (partition BY RoundTime ORDER BY MX)-1) / (count(*) over (partition BY RoundTime)-1) AS pct_rnk
, CONVERSION
, power(10.0,[conversion]/10.0) pwr
FROM LogData
CROSS APPLY (SELECT convert(datetime,convert(date,LastUpdated)) AS day_only) AS ca1
CROSS APPLY (SELECT dateadd(MINUTE, (datediff(MINUTE,day_only,LastUpdated) / 15) * 15, day_only) AS RoundTime) AS ca2
) x
GROUP BY
RoundTime
;SQL 2008 does not have a "percentile" and the closest is percent_rank in SQL 2012
that calculation can be mimicked by calculating rank() and count() over the rounded date/time range, like so:
1.0 * (rank-1) / (count-1)
replacing "rank-1" with "(rank() over (partition BY RoundTime ORDER BY MX)-1)" and
replacing "count-1" with " (count(*) over (partition BY RoundTime)-1)"
we arrive at the code of line 10 above.
So we can calculate the percentile rank of each row. In the outer query we then use a case expression to look for percentile ranks <= 0.1 {this is the equivalent of the Excel percentile(--range--,0.1) } and we take the maximum value that falls into that range of percentiles.
For log average:
just like the Excel sheet we calculate the power(10,value) for each row (equivalent of 10^(C2/10)).
Then in the outer query we take an average of that calculation over the time range, get the log (base 10) of that number and multiply by 10.
Here's my summary result:
| ROUNDTIME | PCT_90 | LOG_AVG |
|--------------------------------|--------|-----------------|
| October, 18 2013 08:15:00+0000 | 29.2 | 35.475119445584 | CREATE TABLE LogData
([SPL] int, [LastUpdated] datetime, [MX] decimal(18,6), [conversion] decimal(18,6))
;
INSERT INTO LogData
([SPL], [LastUpdated], [MX], [conversion])
VALUES
(1, '2013-10-18 08:24:00', 37.7, 38.65),
(2, '2013-10-18 08:24:00', 34.6, 34.15),
(3, '2013-10-18 08:24:00', 28.7, 31.1),
(4, '2013-10-18 08:24:00', 28.5, 33),
(5, '2013-10-18 08:24:00', 32.5, 33.1),
(6, '2013-10-18 08:24:00', 28.7, 34.55),
(7, '2013-10-18 08:24:00', 35.4, 36.35),
(8, '2013-10-18 08:25:00', 32.3, 33.05),
(9, '2013-10-18 08:25:00', 28.8, 31.3),
(10, '2013-10-18 08:25:00', 28.8, 33.05),
(11, '2013-10-18 08:25:00', 32.3, 33.1),
(12, '2013-10-18 08:25:00', 28.9, 34.8),
(13, '2013-10-18 08:25:00', 35.7, 34.85),
(14, '2013-10-18 08:26:00', 29, 31.45),
(15, '2013-10-18 08:26:00', 28.9, 33.2),
(16, '2013-10-18 08:26:00', 32.5, 33.15),
(17, '2013-10-18 08:26:00', 28.8, 31.5),
(18, '2013-10-18 08:26:00', 29.2, 33.35),
(19, '2013-10-18 08:26:00', 32.5, 33.4),
(20, '2013-10-18 08:27:00', 29.3, 33.5),
(21, '2013-10-18 08:27:00', 32.7, 33.45),
(22, '2013-10-18 08:27:00', 29.2, 31.7),
(23, '2013-10-18 08:27:00', 29.2, 33.35),
(24, '2013-10-18 08:27:00', 32.5, 33.5),
(25, '2013-10-18 08:27:00', 29.5, 35.15),
(26, '2013-10-18 08:28:00', 35.8, 35.2),
(27, '2013-10-18 08:28:00', 29.6, 32.05),
(28, '2013-10-18 08:28:00', 29.5, 35.25),
(29, '2013-10-18 08:28:00', 36, 35.25),
(30, '2013-10-18 08:28:00', 29.5, 34.7),
(31, '2013-10-18 08:29:00', 34.9, 34.9),
(32, '2013-10-18 08:29:00', 29.9, 35.35),
(33, '2013-10-18 08:29:00', 35.8, 36.85),
(34, '2013-10-18 08:29:00', 32.9, 33.8),
(35, '2013-10-18 08:29:00', 29.7, 35.25),
(36, '2013-10-18 08:30:00', 35.8, 36.8),
(37, '2013-10-18 08:30:00', 32.8, 33.6),
(38, '2013-10-18 08:30:00', 29.4, 35.8),
(39, '2013-10-18 08:30:00', 37.2, 35.9),
(40, '2013-10-18 08:30:00', 29.6, 32.2),
(41, '2013-10-18 08:30:00', 29.8, 33.8),
(42, '2013-10-18 08:31:00', 32.8, 33.75),
(43, '2013-10-18 08:31:00', 29.7, 35.45),
(44, '2013-10-18 08:31:00', 36.2, 37.1),
(45, '2013-10-18 08:31:00', 33, 33.8),
(46, '2013-10-18 08:31:00', 29.6, 35.3),
(47, '2013-10-18 08:31:00', 36, 35.4),
(48, '2013-10-18 08:32:00', 29.8, 35.35),
(49, '2013-10-18 08:32:00', 35.9, 35.45),
(50, '2013-10-18 08:32:00', 30, 32.45),
(51, '2013-10-18 08:32:00', 29.9, 35.6),
(52, '2013-10-18 08:32:00', 36.3, 37.1),
(53, '2013-10-18 08:32:00', 32.9, 34),
(54, '2013-10-18 08:33:00', 30.1, 34.1),
(55, '2013-10-18 08:33:00', 33.1, 34.1),
(56, '2013-10-18 08:33:00', 30.1, 32.65),
(57, '2013-10-18 08:33:00', 30.2, 34.15),
(58, '2013-10-18 08:33:00', 33.1, 34.6),
(59, '2013-10-18 08:33:00', 31.1, 40),
(60, '2013-10-18 08:34:00', 43.9, 42.55),
(61, '2013-10-18 08:34:00', 36.2, 36),
(62, '2013-10-18 08:34:00', 30.8, 36.25),
(63, '2013-10-18 08:34:00', 36.7, 37.8),
(64, '2013-10-18 08:34:00', 33.9, 34.85),
(65, '2013-10-18 08:34:00', 30.8, 36.5),
(66, '2013-10-18 08:35:00', 37.2, 36.4),
(67, '2013-10-18 08:35:00', 30.6, 33.1),
(68, '2013-10-18 08:35:00', 30.6, 34.9),
(69, '2013-10-18 08:35:00', 34.2, 34.9),
(70, '2013-10-18 08:35:00', 30.6, 36.4),
(71, '2013-10-18 08:35:00', 37.2, 37.55),
(72, '2013-10-18 08:36:00', 32.9, 34.15),
(73, '2013-10-18 08:36:00', 30.4, 36.05),
(74, '2013-10-18 08:36:00', 36.7, 35.9),
(75, '2013-10-18 08:36:00', 30.1, 32.8),
(76, '2013-10-18 08:36:00', 30.5, 34.9),
(77, '2013-10-18 08:36:00', 34.3, 34.7),
(78, '2013-10-18 08:37:00', 30.1, 36.55),
(79, '2013-10-18 08:37:00', 38, 38.75),
(80, '2013-10-18 08:37:00', 34.5, 34.75),
(81, '2013-10-18 08:37:00', 30, 36.65),
(82, '2013-10-18 08:37:00', 38.3, 36.6),
(83, '2013-10-18 08:37:00', 29.9, 36.55),
(84, '2013-10-18 08:38:00', 38.2, 38.85),
(85, '2013-10-18 08:38:00', 34.5, 34.75),
(86, '2013-10-18 08:38:00', 30, 36.4),
(87, '2013-10-18 08:38:00', 37.8, 37.75),
(88, '2013-10-18 08:38:00', 32.7, 33.8),
(89, '2013-10-18 08:38:00', 29.9, 37.1),
(90, '2013-10-18 08:39:00', 39.3, 37.05),
(91, '2013-10-18 08:39:00', 29.8, 36.4),
(92, '2013-10-18 08:39:00', 38, 36.7),
(93, '2013-10-18 08:39:00', 30.4, 37.2),
(94, '2013-10-18 08:39:00', 39, 22)
;
update logdata
set LastUpdated = dateadd(minute,-9,LastUpdated)
;
update logdata
set LastUpdated = dateadd(second,-1,LastUpdated)
where lastUpdated >= '2013-10-18 08:30:00'
;
**Query 1**:
select
RoundTime
, max(case when pct_rnk <=0.1 then mx end) as pct_90
, 10*log10(avg(pwr)) as log_avg
from (
select
spl
, RoundTime
, mx
, 1.0 * (rank() over (partition by RoundTime order by MX)-1) / (count(*) over (partition by RoundTime)-1) as pct_rnk
, conversion
, power(10.0,conversion/10.0) pwr
FROM LogData
CROSS APPLY (SELECT convert(datetime,convert(date,LastUpdated)) AS day_only) AS ca1
CROSS APPLY (SELECT dateadd(MINUTE, (datediff(MINUTE,day_only,LastUpdated) / 15) * 15, day_only) AS RoundTime) AS ca2
) x
group by
RoundTime
**[Results][2]**:
| ROUNDTIME | PCT_90 | LOG_AVG |
|--------------------------------|--------|-----------------|
| October, 18 2013 08:15:00+0000 | 29.2 | 35.475119445584 |
**Query 2**:
select
*
from (
select
spl
, RoundTime
, mx
, 1.0 * (rank() over (partition by RoundTime order by MX)-1) / (count(*) over (partition by RoundTime)-1) as pct_rnk
, conversion
, power(10.0,conversion/10.0) pwr
FROM LogData
CROSS APPLY (SELECT convert(datetime,convert(date,LastUpdated)) AS day_only) AS ca1
CROSS APPLY (SELECT dateadd(MINUTE, (datediff(MINUTE,day_only,LastUpdated) / 15) * 15, day_only) AS RoundTime) AS ca2
) x
order by mx
**[Results][3]**:
| SPL | ROUNDTIME | MX | PCT_RNK | CONVERSION | PWR |
|-----|--------------------------------|------|----------------|------------|---------|
| 4 | October, 18 2013 08:15:00+0000 | 28.5 | 0 | 33 | 1995.3 |
| 6 | October, 18 2013 08:15:00+0000 | 28.7 | 0.010752688172 | 34.55 | 2851 |
| 3 | October, 18 2013 08:15:00+0000 | 28.7 | 0.010752688172 | 31.1 | 1288.2 |
| 9 | October, 18 2013 08:15:00+0000 | 28.8 | 0.032258064516 | 31.3 | 1349 |
| 10 | October, 18 2013 08:15:00+0000 | 28.8 | 0.032258064516 | 33.05 | 2018.4 |
| 17 | October, 18 2013 08:15:00+0000 | 28.8 | 0.032258064516 | 31.5 | 1412.5 |
| 15 | October, 18 2013 08:15:00+0000 | 28.9 | 0.064516129032 | 33.2 | 2089.3 |
| 12 | October, 18 2013 08:15:00+0000 | 28.9 | 0.064516129032 | 34.8 | 3020 |
| 14 | October, 18 2013 08:15:00+0000 | 29 | 0.086021505376 | 31.45 | 1396.4 |
| 22 | October, 18 2013 08:15:00+0000 | 29.2 | 0.096774193548 | 31.7 | 1479.1 |
| 23 | October, 18 2013 08:15:00+0000 | 29.2 | 0.096774193548 | 33.35 | 2162.7 |
| 18 | October, 18 2013 08:15:00+0000 | 29.2 | 0.096774193548 | 33.35 | 2162.7 |
| 20 | October, 18 2013 08:15:00+0000 | 29.3 | 0.129032258064 | 33.5 | 2238.7 |
| 38 | October, 18 2013 08:15:00+0000 | 29.4 | 0.139784946236 | 35.8 | 3801.9 |
| 25 | October, 18 2013 08:15:00+0000 | 29.5 | 0.150537634408 | 35.15 | 3273.4 |
| 28 | October, 18 2013 08:15:00+0000 | 29.5 | 0.150537634408 | 35.25 | 3349.7 |
| 30 | October, 18 2013 08:15:00+0000 | 29.5 | 0.150537634408 | 34.7 | 2951.2 |
| 27 | October, 18 2013 08:15:00+0000 | 29.6 | 0.182795698924 | 32.05 | 1603.2 |
| 40 | October, 18 2013 08:15:00+0000 | 29.6 | 0.182795698924 | 32.2 | 1659.6 |
| 46 | October, 18 2013 08:15:00+0000 | 29.6 | 0.182795698924 | 35.3 | 3388.4 |
| 43 | October, 18 2013 08:15:00+0000 | 29.7 | 0.21505376344 | 35.45 | 3507.5 |
| 35 | October, 18 2013 08:15:00+0000 | 29.7 | 0.21505376344 | 35.25 | 3349.7 |
| 41 | October, 18 2013 08:15:00+0000 | 29.8 | 0.236559139784 | 33.8 | 2398.8 |
| 48 | October, 18 2013 08:15:00+0000 | 29.8 | 0.236559139784 | 35.35 | 3427.7 |
| 91 | October, 18 2013 08:15:00+0000 | 29.8 | 0.236559139784 | 36.4 | 4365.2 |
| 89 | October, 18 2013 08:15:00+0000 | 29.9 | 0.268817204301 | 37.1 | 5128.6 |
| 83 | October, 18 2013 08:15:00+0000 | 29.9 | 0.268817204301 | 36.55 | 4518.6 |
| 51 | October, 18 2013 08:15:00+0000 | 29.9 | 0.268817204301 | 35.6 | 3630.8 |
| 32 | October, 18 2013 08:15:00+0000 | 29.9 | 0.268817204301 | 35.35 | 3427.7 |
| 50 | October, 18 2013 08:15:00+0000 | 30 | 0.311827956989 | 32.45 | 1757.9 |
| 81 | October, 18 2013 08:15:00+0000 | 30 | 0.311827956989 | 36.65 | 4623.8 |
| 86 | October, 18 2013 08:15:00+0000 | 30 | 0.311827956989 | 36.4 | 4365.2 |
| 75 | October, 18 2013 08:15:00+0000 | 30.1 | 0.344086021505 | 32.8 | 1905.5 |
| 78 | October, 18 2013 08:15:00+0000 | 30.1 | 0.344086021505 | 36.55 | 4518.6 |
| 54 | October, 18 2013 08:15:00+0000 | 30.1 | 0.344086021505 | 34.1 | 2570.4 |
| 56 | October, 18 2013 08:15:00+0000 | 30.1 | 0.344086021505 | 32.65 | 1840.8 |
| 57 | October, 18 2013 08:15:00+0000 | 30.2 | 0.387096774193 | 34.15 | 2600.2 |
| 73 | October, 18 2013 08:15:00+0000 | 30.4 | 0.397849462365 | 36.05 | 4027.2 |
| 93 | October, 18 2013 08:15:00+0000 | 30.4 | 0.397849462365 | 37.2 | 5248.1 |
| 76 | October, 18 2013 08:15:00+0000 | 30.5 | 0.419354838709 | 34.9 | 3090.3 |
| 67 | October, 18 2013 08:15:00+0000 | 30.6 | 0.430107526881 | 33.1 | 2041.7 |
| 68 | October, 18 2013 08:15:00+0000 | 30.6 | 0.430107526881 | 34.9 | 3090.3 |
| 70 | October, 18 2013 08:15:00+0000 | 30.6 | 0.430107526881 | 36.4 | 4365.2 |
| 62 | October, 18 2013 08:15:00+0000 | 30.8 | 0.462365591397 | 36.25 | 4217 |
| 65 | October, 18 2013 08:15:00+0000 | 30.8 | 0.462365591397 | 36.5 | 4466.8 |
| 59 | October, 18 2013 08:15:00+0000 | 31.1 | 0.483870967741 | 40 | 10000 |
| 11 | October, 18 2013 08:15:00+0000 | 32.3 | 0.494623655913 | 33.1 | 2041.7 |
| 8 | October, 18 2013 08:15:00+0000 | 32.3 | 0.494623655913 | 33.05 | 2018.4 |
| 5 | October, 18 2013 08:15:00+0000 | 32.5 | 0.516129032258 | 33.1 | 2041.7 |
| 19 | October, 18 2013 08:15:00+0000 | 32.5 | 0.516129032258 | 33.4 | 2187.8 |
| 16 | October, 18 2013 08:15:00+0000 | 32.5 | 0.516129032258 | 33.15 | 2065.4 |
| 24 | October, 18 2013 08:15:00+0000 | 32.5 | 0.516129032258 | 33.5 | 2238.7 |
| 21 | October, 18 2013 08:15:00+0000 | 32.7 | 0.559139784946 | 33.45 | 2213.1 |
| 88 | October, 18 2013 08:15:00+0000 | 32.7 | 0.559139784946 | 33.8 | 2398.8 |
| 42 | October, 18 2013 08:15:00+0000 | 32.8 | 0.58064516129 | 33.75 | 2371.4 |
| 37 | October, 18 2013 08:15:00+0000 | 32.8 | 0.58064516129 | 33.6 | 2290.9 |
| 34 | October, 18 2013 08:15:00+0000 | 32.9 | 0.602150537634 | 33.8 | 2398.8 |
| 53 | October, 18 2013 08:15:00+0000 | 32.9 | 0.602150537634 | 34 | 2511.9 |
| 72 | October, 18 2013 08:15:00+0000 | 32.9 | 0.602150537634 | 34.15 | 2600.2 |
| 45 | October, 18 2013 08:15:00+0000 | 33 | 0.63440860215 | 33.8 | 2398.8 |
| 58 | October, 18 2013 08:15:00+0000 | 33.1 | 0.645161290322 | 34.6 | 2884 |
| 55 | October, 18 2013 08:15:00+0000 | 33.1 | 0.645161290322 | 34.1 | 2570.4 |
| 64 | October, 18 2013 08:15:00+0000 | 33.9 | 0.666666666666 | 34.85 | 3054.9 |
| 69 | October, 18 2013 08:15:00+0000 | 34.2 | 0.677419354838 | 34.9 | 3090.3 |
| 77 | October, 18 2013 08:15:00+0000 | 34.3 | 0.68817204301 | 34.7 | 2951.2 |
| 80 | October, 18 2013 08:15:00+0000 | 34.5 | 0.698924731182 | 34.75 | 2985.4 |
| 85 | October, 18 2013 08:15:00+0000 | 34.5 | 0.698924731182 | 34.75 | 2985.4 |
| 2 | October, 18 2013 08:15:00+0000 | 34.6 | 0.720430107526 | 34.15 | 2600.2 |
| 31 | October, 18 2013 08:15:00+0000 | 34.9 | 0.731182795698 | 34.9 | 3090.3 |
| 7 | October, 18 2013 08:15:00+0000 | 35.4 | 0.74193548387 | 36.35 | 4315.2 |
| 13 | October, 18 2013 08:15:00+0000 | 35.7 | 0.752688172043 | 34.85 | 3054.9 |
| 33 | October, 18 2013 08:15:00+0000 | 35.8 | 0.763440860215 | 36.85 | 4841.7 |
| 36 | October, 18 2013 08:15:00+0000 | 35.8 | 0.763440860215 | 36.8 | 4786.3 |
| 26 | October, 18 2013 08:15:00+0000 | 35.8 | 0.763440860215 | 35.2 | 3311.3 |
| 49 | October, 18 2013 08:15:00+0000 | 35.9 | 0.795698924731 | 35.45 | 3507.5 |
| 47 | October, 18 2013 08:15:00+0000 | 36 | 0.806451612903 | 35.4 | 3467.4 |
| 29 | October, 18 2013 08:15:00+0000 | 36 | 0.806451612903 | 35.25 | 3349.7 |
| 44 | October, 18 2013 08:15:00+0000 | 36.2 | 0.827956989247 | 37.1 | 5128.6 |
| 61 | October, 18 2013 08:15:00+0000 | 36.2 | 0.827956989247 | 36 | 3981.1 |
| 52 | October, 18 2013 08:15:00+0000 | 36.3 | 0.849462365591 | 37.1 | 5128.6 |
| 63 | October, 18 2013 08:15:00+0000 | 36.7 | 0.860215053763 | 37.8 | 6025.6 |
| 74 | October, 18 2013 08:15:00+0000 | 36.7 | 0.860215053763 | 35.9 | 3890.5 |
| 66 | October, 18 2013 08:15:00+0000 | 37.2 | 0.881720430107 | 36.4 | 4365.2 |
| 71 | October, 18 2013 08:15:00+0000 | 37.2 | 0.881720430107 | 37.55 | 5688.5 |
| 39 | October, 18 2013 08:15:00+0000 | 37.2 | 0.881720430107 | 35.9 | 3890.5 |
| 1 | October, 18 2013 08:15:00+0000 | 37.7 | 0.913978494623 | 38.65 | 7328.2 |
| 87 | October, 18 2013 08:15:00+0000 | 37.8 | 0.924731182795 | 37.75 | 5956.6 |
| 79 | October, 18 2013 08:15:00+0000 | 38 | 0.935483870967 | 38.75 | 7498.9 |
| 92 | October, 18 2013 08:15:00+0000 | 38 | 0.935483870967 | 36.7 | 4677.4 |
| 84 | October, 18 2013 08:15:00+0000 | 38.2 | 0.956989247311 | 38.85 | 7673.6 |
| 82 | October, 18 2013 08:15:00+0000 | 38.3 | 0.967741935483 | 36.6 | 4570.9 |
| 94 | October, 18 2013 08:15:00+0000 | 39 | 0.978494623655 | 22 | 158.5 |
| 90 | October, 18 2013 08:15:00+0000 | 39.3 | 0.989247311827 | 37.05 | 5069.9 |
| 60 | October, 18 2013 08:15:00+0000 | 43.9 | 1 | 42.55 | 17988.7 |
[1]: http://sqlfiddle.com/#!6/1f107/26ASKER
Hi PortletPaul,
Again a BIG THANK YOU !!! for your effort.
Sorry to be late as I was in a training for 3 days.
I have made slight modifications to your code and got the results below.
Not sure whether I am correct here.
[conversion] was an intermediate results field used to calculate the final figure for log averages.
I will do the same calculation in Excel to compare and let you know.
QUERY:
RESULT
Again a BIG THANK YOU !!! for your effort.
Sorry to be late as I was in a training for 3 days.
I have made slight modifications to your code and got the results below.
Not sure whether I am correct here.
[conversion] was an intermediate results field used to calculate the final figure for log averages.
I will do the same calculation in Excel to compare and let you know.
QUERY:
SELECT
RoundTime
, max(CASE WHEN pct_rnk <=0.1 THEN Profile1Max END) AS pct_90
, 10*log10(avg(pwr)) AS log_avg
FROM (
SELECT
Profile1SPL
, RoundTime
, Profile1Max
, 1.0 * (rank() over (partition BY RoundTime ORDER BY Profile1Max)-1) / (count(*) over (partition BY RoundTime)-1) AS pct_rnk
--, CONVERSION
, power(10.0,Profile1SPL/10.0) pwr
FROM tblData
CROSS APPLY (SELECT convert(datetime,convert(date,LastUpdated)) AS day_only) AS ca1
CROSS APPLY (SELECT dateadd(MINUTE, (datediff(MINUTE,day_only,LastUpdated) / 15) * 15, day_only) AS RoundTime) AS ca2
WHERE LastUpdated >= DATEADD(DAY, - 1, GETDATE())
) x
GROUP BY
RoundTime
ORDER BY RoundTime DESC RESULT
RoundTime pct_90 log_avg
2013-11-21 12:30:00.000 48.6 41.0962210607613
2013-11-21 12:15:00.000 48.3 41.027225936539
2013-11-21 12:00:00.000 46.6 40.9559594671983
2013-11-21 11:45:00.000 46.5 40.626818537446
2013-11-21 11:30:00.000 49.6 40.1710675946921
2013-11-21 11:15:00.000 49.6 42.0051599849093
2013-11-21 11:00:00.000 55 49.0805636735977
2013-11-21 10:45:00.000 55 44.5929421612782
2013-11-21 10:30:00.000 47.9 42.0143837288056
2013-11-21 10:15:00.000 43.6 38.6689407535204
2013-11-21 10:00:00.000 43.6 38.855074988565
2013-11-21 09:45:00.000 47 35.5209367119439
2013-11-21 09:30:00.000 46.5 37.0223293205764
2013-11-21 09:15:00.000 46.5 43.3415207584883
2013-11-21 09:00:00.000 42.7 36.1604770278021
2013-11-21 08:45:00.000 49.3 46.591476650127
2013-11-21 08:30:00.000 42.2 34.2426885109341
2013-11-21 08:15:00.000 42.2 41.6085462543056
2013-11-21 08:00:00.000 47.5 47.2133036630461
2013-11-21 07:45:00.000 46.5 50.4995275713551
2013-11-21 07:30:00.000 47.1 50.1065446902524
2013-11-21 07:15:00.000 45.6 38.1131522276916
2013-11-21 07:00:00.000 44.3 40.7576429037151
2013-11-21 06:45:00.000 44.6 39.2511519743263
2013-11-21 06:30:00.000 45.8 40.9247680970029
2013-11-21 06:15:00.000 46.3 39.9523675901929
2013-11-21 06:00:00.000 46.2 40.0533580586587
2013-11-21 05:45:00.000 47 41.2210550277655
2013-11-21 05:30:00.000 47.3 63.8522987496146
2013-11-21 05:15:00.000 45.2 39.8888114310436
2013-11-21 05:00:00.000 45 38.4699040428038
2013-11-21 04:45:00.000 44.7 38.5041030516464
2013-11-21 04:30:00.000 45.2 38.6234382548523
2013-11-21 04:15:00.000 45 39.1906937149834
2013-11-21 04:00:00.000 45.5 39.1317015219302
2013-11-21 03:45:00.000 45.6 40.4040570659257
2013-11-21 03:30:00.000 51.7 41.4124840049934
2013-11-21 03:15:00.000 46 39.9616391218388
2013-11-21 03:00:00.000 43.9 40.5008623916
2013-11-21 02:45:00.000 42.8 50.1860696236656
2013-11-21 02:30:00.000 42.7 35.8714908710706
2013-11-21 02:15:00.000 42.7 36.2149848270692
2013-11-21 02:00:00.000 43.2 36.2802276061611
2013-11-21 01:45:00.000 43.2 39.3158596958275
2013-11-21 01:30:00.000 46 39.9320843657334
2013-11-21 01:15:00.000 44.3 38.5928316980589
2013-11-21 01:00:00.000 43.9 37.7768650634313
2013-11-21 00:45:00.000 42.1 36.237346586401
2013-11-21 00:30:00.000 41.8 36.6590219206403
2013-11-21 00:15:00.000 43.4 36.7877407270377
2013-11-21 00:00:00.000 43.7 37.4356166279691
2013-11-20 23:45:00.000 43.4 37.7080593842999
2013-11-20 23:30:00.000 44.2 37.8546504386512
2013-11-20 23:15:00.000 43.8 37.6786311736249
2013-11-20 23:00:00.000 43.2 38.9932195239975
2013-11-20 22:45:00.000 47.7 42.056619824346
2013-11-20 22:30:00.000 44.4 41.6898213016489
2013-11-20 22:15:00.000 43.6 37.178976385976
2013-11-20 22:00:00.000 43.5 36.9316980384705
2013-11-20 21:45:00.000 43.8 36.7512506144218
2013-11-20 21:30:00.000 43.2 36.6808509202342
2013-11-20 21:15:00.000 44 37.2528292941613
2013-11-20 21:00:00.000 44.6 37.7352217807028
2013-11-20 20:45:00.000 44.2 37.549360685269
2013-11-20 20:30:00.000 45.5 40.3264700937799
2013-11-20 20:15:00.000 44 40.9080145217
2013-11-20 20:00:00.000 46.8 41.7761683117893
2013-11-20 19:45:00.000 47.4 39.7432304103787
2013-11-20 19:30:00.000 43.7 41.9559077888977
2013-11-20 19:15:00.000 41.8 35.2635367310855
2013-11-20 19:00:00.000 41.8 34.8151667670832
2013-11-20 18:45:00.000 42.8 39.9518257518398
2013-11-20 18:30:00.000 48.4 38.5622199016268
2013-11-20 18:15:00.000 43.5 38.7290740806834
2013-11-20 18:00:00.000 42.4 36.3329170206036
2013-11-20 17:45:00.000 44.6 38.3585901357174
2013-11-20 17:30:00.000 44.1 37.3230095549285
2013-11-20 17:15:00.000 44.8 36.8309083003418
2013-11-20 17:00:00.000 44.5 37.2819222909208
2013-11-20 16:45:00.000 43.2 36.999904694127
2013-11-20 16:30:00.000 43.1 37.2229273338889
2013-11-20 16:15:00.000 43.8 38.5562233311211
2013-11-20 16:00:00.000 46.4 38.9579512670805
2013-11-20 15:45:00.000 48.1 39.9695311348039
2013-11-20 15:30:00.000 48.1 41.4395532163997
2013-11-20 15:15:00.000 44.8 37.6779096925193
2013-11-20 15:00:00.000 44.8 37.189353270936
2013-11-20 14:45:00.000 45.2 39.8761432073611
2013-11-20 14:30:00.000 48.8 44.8643906282321
2013-11-20 14:15:00.000 47.4 42.4319310463433
2013-11-20 14:00:00.000 49.7 44.5791297626209
2013-11-20 13:45:00.000 48.8 41.257385616175
2013-11-20 13:30:00.000 47.6 40.9373270471593
2013-11-20 13:15:00.000 47.5 39.9557382646957
2013-11-20 13:00:00.000 47.3 40.1554665980193
2013-11-20 12:45:00.000 45.3 37.4396175315547
2013-11-20 12:30:00.000 44.7 42.2826483442148
Looks OK to me, seems you commented out [conversion] which is fine and added a where clause, also fine. Did I miss anything?
More importantly I guess we'll wait to see if the calculations are correct.
More importantly I guess we'll wait to see if the calculations are correct.
ASKER
Hi, I am back...
I got a sample set of data and added to an Excel sheet to compare the results with the results from your query.
It seems the values are having big differences.
Not sure my changes to the query caused this.
I have attached the Excel sheet for your reference.
Can you please check it (the highlighted values) and let me know what could be wrong.
Really sorry to be a pain, but this is a big headache to me and now I want to get this done soon.
Latest Query
View - v24HourRawData
I got a sample set of data and added to an Excel sheet to compare the results with the results from your query.
It seems the values are having big differences.
Not sure my changes to the query caused this.
I have attached the Excel sheet for your reference.
Can you please check it (the highlighted values) and let me know what could be wrong.
Really sorry to be a pain, but this is a big headache to me and now I want to get this done soon.
Latest Query
SELECT
RoundTime
, max(CASE WHEN pct_rnk <=0.99 THEN Profile1SPL END) AS pct_01 -- Percentile 01 using SPL
, max(CASE WHEN pct_rnk <=0.9 THEN Profile1SPL END) AS pct_10 -- Percentile 10 using SPL
, max(CASE WHEN pct_rnk <=0.1 THEN Profile1SPL END) AS pct_90 -- Percentile 90 using SPL
, 10*log10(avg(pwr)) AS log_avg -- Log Avg using Leq
FROM (
SELECT
Profile1SPL
, RoundTime
, 1.0 * (rank() over (partition BY RoundTime ORDER BY Profile1SPL)-1) / (count(*) over (partition BY RoundTime)-1) AS pct_rnk
, power(10.0,Profile1Leq/10.<wbr ></wbr>0) pwr -- Log Avg using Leq
FROM v24HourRawData
CROSS APPLY (SELECT convert(datetime,convert(d<wbr ></wbr>ate,LastUp<wbr ></wbr>dated)) AS day_only) AS ca1
CROSS APPLY (SELECT dateadd(MINUTE, (datediff(MINUTE,day_only,<wbr ></wbr>LastUpdate<wbr ></wbr>d) / 15) * 15, day_only) AS RoundTime) AS ca2
WHERE LastUpdated >= DATEADD(DAY, - 1, GETDATE())
) x
GROUP BY
RoundTime
ORDER BY RoundTime DESC
;View - v24HourRawData
ID LastUpdated Profile1SPL Profile1LEQ
365647 2013-11-25 11:59:52.000 37.5 39.2
365646 2013-11-25 11:59:32.000 27.2 39.5
365645 2013-11-25 11:59:21.000 36.5 39.7
365644 2013-11-25 11:59:11.000 28.5 39.6
365643 2013-11-25 11:59:02.000 28.2 39.3
365642 2013-11-25 11:58:52.000 38.5 39
365641 2013-11-25 11:58:41.000 28.3 38.7
365640 2013-11-25 11:58:32.000 28.7 37.9
365639 2013-11-25 11:58:22.000 41.3 31.7
365638 2013-11-25 11:58:11.000 30 40.7
365637 2013-11-25 11:58:02.000 33.3 40.7
365636 2013-11-25 11:57:51.000 38.4 40.7
365635 2013-11-25 11:57:41.000 29.2 40.8
365634 2013-11-25 11:57:33.000 28.8 40.8
365633 2013-11-25 11:57:22.000 28.6 40.8
365632 2013-11-25 11:57:11.000 28.4 40.8
365631 2013-11-25 11:57:01.000 37.4 40.8
365630 2013-11-25 11:56:51.000 28.7 40.9
365629 2013-11-25 11:56:40.000 37.7 40.9
365628 2013-11-25 11:56:30.000 42.3 40.9
365627 2013-11-25 11:56:21.000 28.2 40.9
365626 2013-11-25 11:56:10.000 42.2 40.9
365625 2013-11-25 11:56:02.000 28.7 40.9
365624 2013-11-25 11:55:51.000 28.3 40.9
365623 2013-11-25 11:55:41.000 43.9 40.9
365622 2013-11-25 11:55:30.000 28.3 40.9
365621 2013-11-25 11:55:20.000 40.1 40.9
365620 2013-11-25 11:55:11.000 43.8 40.9
365619 2013-11-25 11:55:00.000 28.5 40.9
365618 2013-11-25 11:54:49.000 42.7 40.9
365617 2013-11-25 11:54:42.000 43.2 40.9
365616 2013-11-25 11:54:25.000 36.6 40.9
365615 2013-11-25 11:54:17.000 35.1 41
365614 2013-11-25 11:54:08.000 39.3 41
365613 2013-11-25 11:53:55.000 28.5 41.1
365612 2013-11-25 11:53:45.000 38.1 41.1
365611 2013-11-25 11:53:37.000 45.1 41.1
365610 2013-11-25 11:53:25.000 28.2 41.2
365609 2013-11-25 11:53:15.000 40.6 41.2
365608 2013-11-25 11:53:04.000 33.1 41.2
365607 2013-11-25 11:52:56.000 41.4 41.2
365606 2013-11-25 11:52:47.000 42.5 41.2
365605 2013-11-25 11:52:34.000 40.6 41.2
365604 2013-11-25 11:52:26.000 38.6 41.2
365603 2013-11-25 11:52:15.000 42.3 41.2
365602 2013-11-25 11:52:04.000 28.4 41.2
365601 2013-11-25 11:51:54.000 38.6 41.2
365600 2013-11-25 11:51:44.000 28.6 41.2
365599 2013-11-25 11:51:34.000 28.9 41.3
365598 2013-11-25 11:51:23.000 29 41.3
365597 2013-11-25 11:51:04.000 28.7 41.4
365596 2013-11-25 11:50:53.000 28.3 41.4
365595 2013-11-25 11:50:43.000 28.3 41.4
365594 2013-11-25 11:50:26.000 40.1 41.4
365593 2013-11-25 11:50:13.000 28.3 41.4
365592 2013-11-25 11:50:02.000 28.7 41.4
365591 2013-11-25 11:49:43.000 45.6 41.2
365590 2013-11-25 11:49:19.000 41.9 40.4
365589 2013-11-25 11:49:10.000 28.8 40.4
365588 2013-11-25 11:48:59.000 48 40.1
365587 2013-11-25 11:48:52.000 47.8 39.6
365586 2013-11-25 11:48:39.000 29 39.2
365585 2013-11-25 11:48:30.000 28.4 39
365584 2013-11-25 11:48:19.000 43.6 38.9
365583 2013-11-25 11:48:09.000 28.8 38.8
365582 2013-11-25 11:47:59.000 39.7 38.7
365581 2013-11-25 11:47:49.000 28.6 38.5
365580 2013-11-25 11:47:38.000 28.5 38.5
365579 2013-11-25 11:47:28.000 42.6 38.3
365578 2013-11-25 11:47:19.000 29.9 38.1
365577 2013-11-25 11:47:08.000 37.5 38
365576 2013-11-25 11:46:58.000 42.3 37.8
365575 2013-11-25 11:46:48.000 33 37.6
365574 2013-11-25 11:46:38.000 40.5 37.4
365573 2013-11-25 11:46:28.000 28.6 37.1
365572 2013-11-25 11:46:18.000 28.6 36.8
365571 2013-11-25 11:46:08.000 43 36.3
365570 2013-11-25 11:45:58.000 28.7 35.9
365569 2013-11-25 11:45:48.000 41.2 35.7
365568 2013-11-25 11:45:40.000 28.6 35.3
365567 2013-11-25 11:45:27.000 34.9 35.4
365566 2013-11-25 11:45:19.000 28.3 35.4
365565 2013-11-25 11:45:07.000 28.7 35.4ASKER
Excel spreadsheet as per above comment...
Test-2013-11-25.xls
Test-2013-11-25.xls
ASKER
Query Result
RoundTime pct_01 pct_10 pct_90 log_avg
2013-11-25 12:30:00.000 38.3 37.4 28.2 34.9465461191538
2013-11-25 12:15:00.000 38.9 36.9 28.1 34.199407193288
2013-11-25 12:00:00.000 40.9 38.1 27.2 36.8297643306629
2013-11-25 11:45:00.000 47.8 42.7 28.3 40.0816843286949
2013-11-25 11:30:00.000 44.2 39.2 28.5 38.2664756683601
2013-11-25 11:15:00.000 43.6 41.3 28.3 38.7652666873982ASKER
P.S: I have added 2 more percentiles I wanted (1st & 10th in addition to the 90th we already had)
Also...
I am getting the following error time-to-time from the above query:
Msg 8134, Level 16, State 1, Procedure 15MinGraphData, Line 7
Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.
Again, thanks a lot for all the help.
Also...
I am getting the following error time-to-time from the above query:
Msg 8134, Level 16, State 1, Procedure 15MinGraphData, Line 7
Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.
Again, thanks a lot for all the help.
This is not an error, it is a warning which means exactly what is says:
Warning: Null value is eliminated by an aggregate or other SET operation.
i.e. inside an aggregate function a NULL value was encountered and it has been ignored.
This warning can be suppressed using:
SET ANSI_WARNINGS { ON | OFF }
Divide by zero is an error, however I'm not sure what you want to do here.
Look at line 11 of the latest query, this has a division.
, 1.0 * (rank() over (partition BY RoundTime ORDER BY Profile1SPL)-1) / (count(*) over (partition BY RoundTime)-1) AS pct_rnk
what do you want to do if
(count(*) over (partition BY RoundTime)-1
is zero?
Warning: Null value is eliminated by an aggregate or other SET operation.
i.e. inside an aggregate function a NULL value was encountered and it has been ignored.
This warning can be suppressed using:
SET ANSI_WARNINGS { ON | OFF }
Divide by zero is an error, however I'm not sure what you want to do here.
Look at line 11 of the latest query, this has a division.
, 1.0 * (rank() over (partition BY RoundTime ORDER BY Profile1SPL)-1) / (count(*) over (partition BY RoundTime)-1) AS pct_rnk
what do you want to do if
(count(*) over (partition BY RoundTime)-1
is zero?
ASKER
Thank you.
I would like to display the RoundTime and 'NULL' for the outputs.
Did you get any chance to compare the excel sheet results with the query output to see why is it different?
I would like to display the RoundTime and 'NULL' for the outputs.
Did you get any chance to compare the excel sheet results with the query output to see why is it different?
>>"I would like to display the RoundTime and 'NULL' for the outputs."
so for this you use a case expression, such as
, case when count(*) over (partition BY RoundTime) > 1 then
1.0 * (rank() over (partition BY RoundTime ORDER BY Profile1SPL)-1) / (count(*) over (partition BY RoundTime)-1)
end AS pct_rnk
>>Did you get any chance to compare the excel sheet results with the query output to see why is it different?
no, did you compare them?, What are the results of that?
so for this you use a case expression, such as
, case when count(*) over (partition BY RoundTime) > 1 then
1.0 * (rank() over (partition BY RoundTime ORDER BY Profile1SPL)-1) / (count(*) over (partition BY RoundTime)-1)
end AS pct_rnk
>>Did you get any chance to compare the excel sheet results with the query output to see why is it different?
no, did you compare them?, What are the results of that?
ASKER
Thanks, again. that fixed the warning issue.
Yes I checked the spreadsheet and the results are having a big difference.
I have added the results from the query too in the spreadsheet (Comment ID: 39673508).
Could you please help me to fix that?
I can't understand the using of "MAX" function in the query?
Yes I checked the spreadsheet and the results are having a big difference.
I have added the results from the query too in the spreadsheet (Comment ID: 39673508).
Could you please help me to fix that?
I can't understand the using of "MAX" function in the query?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi PortletPaul,
That explanation helped.
I have done some tests and got expected results.
So, now I have got everything I wanted with this.
Thanks a million for all the help provided throughout these days.
I wonder if I can add more points apart from the default 500 because your help was much more than that for me.
That explanation helped.
I have done some tests and got expected results.
So, now I have got everything I wanted with this.
Thanks a million for all the help provided throughout these days.
I wonder if I can add more points apart from the default 500 because your help was much more than that for me.
ASKER
Excellent !!!
:) no 500 is the maximum but I appreciate the sentiment - thank you. Really pleased I could help out.
All the best, Paul
All the best, Paul
Open in new window
The cross apply simply saves doing this calculation numerous times.That calc just strips time from the [time_stamp] but leaves it as datetime.
Then calculate the number of minutes between that point and the [time_stamp]
that value / 15 results in the number of whole 15 minute intervals
multiply that by 15 to get back to minutes
add those minutes to the [day_only] = rounded to 15 minutes
{+ edit}
because "timestamp" is a SQL term, and also because it also doesn't relate to dates or times either, I have used [time_stamp] in my code above. I would NOT recommend using "timestamp" as a field name.