# Thread: Top N contiguous rows.

1. Registered User
Join Date
Jul 2003
Posts
2,296

## Unanswered: Top N contiguous rows.

ok, I need some help please.
I have a data set that I need to select the top 4 contiguous values and then average those 4.
ie: they have to be the top 4 contiguous hours of a timeframe

data:
Code:
```HOUR        THE_VALUE
---------- ----------
01:00               5
02:00               2
03:00               9
04:00               5
05:00               7
06:00               8
07:00               2
08:00               3
09:00               9
10:00               3```
so in my example above I would want to identify those 4 rows in bold
and average only those 4.

HOW THE HELL DO I DO THIS?
My brain is fried and I need a new set of eyes.

if the values were:
Code:
```1
2
3
4
3
2
1```
then either end is acceptable to average but I would expect 2.5 and not
an average of them all which would be 2.285 ...

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
untested --

select avg(the_value) from (
select distinct the_value from the_table
order by the_value desc ) as t
where rownum <= 4

3. Registered User
Join Date
Nov 2006
Location
Indianapolis
Posts
90
That doesn't seem like it would give you the contiguous 4 time slots (but I didn't try it either)

There might be an easier way than this, but you could build a work table that has the different possible time combinations, name each combination setno

Code:
```setno   hour
1        1:00
1        2:00
1        3:00
1        4:00
2        2:00
2        3:00
2        4:00
2        5:00
3        3:00....

-- find top set:
select setno from (
(select setno, avg(the_value) from
(select a.setno, d.the_value
from a.work_table, d.real_table
where a.hour=d.hour
group by setno order by 2) where rownum<2))```
This should give you the top 4 time slots...

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
oh, contiguous

no idea why i didn't catch onto that concept, sorry

5. Registered User
Join Date
Aug 2004
Location
France
Posts
754
Here is a solution with analytics :

Code:
```SELECT Hour, HourPlus1, HourPlus2, HourPlus3, AvgValue
FROM
(
SELECT Hour, HourPlus1, HourPlus2, HourPlus3, AvgValue, ROWNUM rn
FROM
(
SELECT Hour, HourPlus1, HourPlus2, HourPlus3, (The_Value + The_Value_HourPlus1 + The_Value_HourPlus2 + The_Value_HourPlus3) / 4 AvgValue
FROM (SELECT Hour,
LEAD(Hour, 1) OVER (ORDER BY Hour ASC) HourPlus1,
LEAD(Hour, 2) OVER (ORDER BY Hour ASC) HourPlus2,
LEAD(Hour, 3) OVER (ORDER BY Hour ASC) HourPlus3,
The_Value,
LEAD(The_Value, 1) OVER (ORDER BY Hour ASC) The_Value_HourPlus1,
LEAD(The_Value, 2) OVER (ORDER BY Hour ASC) The_Value_HourPlus2,
LEAD(The_Value, 3) OVER (ORDER BY Hour ASC) The_Value_HourPlus3
FROM The_Table)
WHERE HourPlus1 IS NOT NULL
AND HourPlus2 IS NOT NULL
AND HourPlus3 IS NOT NULL
ORDER BY (The_Value + The_Value_HourPlus1 + The_Value_HourPlus2 + The_Value_HourPlus3) / 4 DESC
)
)
WHERE rn = 1;```
Code:
```rbaraer@Ora10g> CREATE TABLE The_Table(
Hour DATE,
The_Value NUMBER(10));
2    3
Table created.

rbaraer@Ora10g> INSERT INTO The_Table(Hour, The_Value) VALUES (TO_DATE('01:00', 'HH24:MI'), 5);
INSERT INTO The_Table(Hour, The_Value) VALUES (TO_DATE('02:00', 'HH24:MI'), 2);
INSERT INTO The_Table(Hour, The_Value) VALUES (TO_DATE('03:00', 'HH24:MI'), 9);
INSERT INTO The_Table(Hour, The_Value) VALUES (TO_DATE('04:00', 'HH24:MI'), 5);
INSERT INTO The_Table(Hour, The_Value) VALUES (TO_DATE('05:00', 'HH24:MI'), 7);
INSERT INTO The_Table(Hour, The_Value) VALUES (TO_DATE('06:00', 'HH24:MI'), 8);
INSERT INTO The_Table(Hour, The_Value) VALUES (TO_DATE('07:00', 'HH24:MI'), 2);
INSERT INTO The_Table(Hour, The_Value) VALUES (TO_DATE('08:00', 'HH24:MI'), 3);
INSERT INTO The_Table(Hour, The_Value) VALUES (TO_DATE('09:00', 'HH24:MI'), 9);
INSERT INTO The_Table(Hour, The_Value) VALUES (TO_DATE('10:00', 'HH24:MI'), 3);

1 row created.

rbaraer@Ora10g>
1 row created.

rbaraer@Ora10g>
1 row created.

rbaraer@Ora10g>
1 row created.

rbaraer@Ora10g>
1 row created.

rbaraer@Ora10g>
1 row created.

rbaraer@Ora10g>
1 row created.

rbaraer@Ora10g>
1 row created.

rbaraer@Ora10g>
1 row created.

rbaraer@Ora10g>
1 row created.

rbaraer@Ora10g> COMMIT;

Commit complete.

rbaraer@Ora10g> SELECT Hour, HourPlus1, HourPlus2, HourPlus3, (The_Value + The_Value_HourPlus1 + The_Value_HourPlus2 + The_Value_HourPlus3) / 4 AvgValue
FROM (SELECT Hour,
2    3                  LEAD(Hour, 1) OVER (ORDER BY Hour ASC) HourPlus1,
4                  LEAD(Hour, 2) OVER (ORDER BY Hour ASC) HourPlus2,
5                  LEAD(Hour, 3) OVER (ORDER BY Hour ASC) HourPlus3,
6                  The_Value,
7                  LEAD(The_Value, 1) OVER (ORDER BY Hour ASC) The_Value_HourPlus1,
8                  LEAD(The_Value, 2) OVER (ORDER BY Hour ASC) The_Value_HourPlus2,
9                  LEAD(The_Value, 3) OVER (ORDER BY Hour ASC) The_Value_HourPlus3
10              FROM The_Table)
11          WHERE HourPlus1 IS NOT NULL
12          AND HourPlus2 IS NOT NULL
13          AND HourPlus3 IS NOT NULL;

HOUR             HOURPLUS1        HOURPLUS2        HOURPLUS3          AVGVALUE
---------------- ---------------- ---------------- ---------------- ----------
01/08/2007 01:00 01/08/2007 02:00 01/08/2007 03:00 01/08/2007 04:00       5.25
01/08/2007 02:00 01/08/2007 03:00 01/08/2007 04:00 01/08/2007 05:00       5.75
01/08/2007 03:00 01/08/2007 04:00 01/08/2007 05:00 01/08/2007 06:00       7.25
01/08/2007 04:00 01/08/2007 05:00 01/08/2007 06:00 01/08/2007 07:00        5.5
01/08/2007 05:00 01/08/2007 06:00 01/08/2007 07:00 01/08/2007 08:00          5
01/08/2007 06:00 01/08/2007 07:00 01/08/2007 08:00 01/08/2007 09:00        5.5
01/08/2007 07:00 01/08/2007 08:00 01/08/2007 09:00 01/08/2007 10:00       4.25

7 rows selected.

rbaraer@Ora10g> SELECT Hour, HourPlus1, HourPlus2, HourPlus3, AvgValue
2  FROM
3  (
4      SELECT Hour, HourPlus1, HourPlus2, HourPlus3, AvgValue, ROWNUM rn
5      FROM
6      (
7          SELECT Hour, HourPlus1, HourPlus2, HourPlus3, (The_Value + The_Value_HourPlus1 + The_Value_HourPlus2 + The_Value_HourPlus3) / 4 AvgValue
8          FROM (SELECT Hour,
9                  LEAD(Hour, 1) OVER (ORDER BY Hour ASC) HourPlus1,
10                  LEAD(Hour, 2) OVER (ORDER BY Hour ASC) HourPlus2,
11                  LEAD(Hour, 3) OVER (ORDER BY Hour ASC) HourPlus3,
12                  The_Value,
13                  LEAD(The_Value, 1) OVER (ORDER BY Hour ASC) The_Value_HourPlus1,
14                  LEAD(The_Value, 2) OVER (ORDER BY Hour ASC) The_Value_HourPlus2,
15                  LEAD(The_Value, 3) OVER (ORDER BY Hour ASC) The_Value_HourPlus3
16              FROM The_Table)
17          WHERE HourPlus1 IS NOT NULL
18          AND HourPlus2 IS NOT NULL
19          AND HourPlus3 IS NOT NULL
20          ORDER BY (The_Value + The_Value_HourPlus1 + The_Value_HourPlus2 + The_Value_HourPlus3) / 4 DESC
21      )
22  )
23  WHERE rn = 1;

HOUR             HOURPLUS1        HOURPLUS2        HOURPLUS3          AVGVALUE
---------------- ---------------- ---------------- ---------------- ----------
01/08/2007 03:00 01/08/2007 04:00 01/08/2007 05:00 01/08/2007 06:00       7.25

rbaraer@Ora10g>```
The main drawback is that it is very specific to your current problem .

HTH & Regards,

rbaraer

6. Registered User
Join Date
Jul 2003
Posts
2,296
Yeah, i forgot to mention that I never know how many hours I will have. I just know I need the top FOUR (or less) contiguous hours of any set.

The "or less" part being if the set is <= 4

So, I need a solution against any random range of hours

7. Registered User
Join Date
Jul 2003
Posts
2,296
Originally Posted by Indy_tomcat
That doesn't seem like it would give you the contiguous 4 time slots (but I didn't try it either)

There might be an easier way than this, but you could build a work table that has the different possible time combinations, name each combination setno

This should give you the top 4 time slots...
This confuses me (heh)

How would this work? You join the hours somehow?

8. Registered User
Join Date
Aug 2004
Location
France
Posts
754
Originally Posted by The_Duck
Yeah, i forgot to mention that I never know how many hours I will have. I just know I need the top FOUR (or less) contiguous hours of any set.

The "or less" part being if the set is <= 4

So, I need a solution against any random range of hours
"Or less" is not a problem because the bolded part ensures you have 4 hours, if you don't put it, you will have any combination of 1, 2, 3 or 4 contiguous hours :
Code:
```SELECT Hour, HourPlus1, HourPlus2, HourPlus3, AvgValue
FROM
(
SELECT Hour, HourPlus1, HourPlus2, HourPlus3, AvgValue, ROWNUM rn
FROM
(
SELECT Hour, HourPlus1, HourPlus2, HourPlus3, (The_Value + The_Value_HourPlus1 + The_Value_HourPlus2 + The_Value_HourPlus3) / 4 AvgValue
FROM (SELECT Hour,
LEAD(Hour, 1) OVER (ORDER BY Hour ASC) HourPlus1,
LEAD(Hour, 2) OVER (ORDER BY Hour ASC) HourPlus2,
LEAD(Hour, 3) OVER (ORDER BY Hour ASC) HourPlus3,
The_Value,
LEAD(The_Value, 1) OVER (ORDER BY Hour ASC) The_Value_HourPlus1,
LEAD(The_Value, 2) OVER (ORDER BY Hour ASC) The_Value_HourPlus2,
LEAD(The_Value, 3) OVER (ORDER BY Hour ASC) The_Value_HourPlus3
FROM The_Table)
WHERE HourPlus1 IS NOT NULL
AND HourPlus2 IS NOT NULL
AND HourPlus3 IS NOT NULL
ORDER BY (The_Value + The_Value_HourPlus1 + The_Value_HourPlus2 + The_Value_HourPlus3) / 4 DESC
)
)
WHERE rn = 1;```
Just add NVL( , 0) when you sum the different The_Value_HourPlusX :
Code:
```rbaraer@Ora10g> SELECT Hour, HourPlus1, HourPlus2, HourPlus3, (The_Value + NVL(The_Value_HourPlus1, 0) + NVL(The_Value_HourPlus2, 0) + NVL(The_Value_HourPlus3, 0)) / 4 AvgValue
FROM (SELECT Hour,
2    3                  LEAD(Hour, 1) OVER (ORDER BY Hour ASC) HourPlus1,
4                  LEAD(Hour, 2) OVER (ORDER BY Hour ASC) HourPlus2,
5                  LEAD(Hour, 3) OVER (ORDER BY Hour ASC) HourPlus3,
6                  The_Value,
7                  LEAD(The_Value, 1) OVER (ORDER BY Hour ASC) The_Value_HourPlus1,
8                  LEAD(The_Value, 2) OVER (ORDER BY Hour ASC) The_Value_HourPlus2,
9                  LEAD(The_Value, 3) OVER (ORDER BY Hour ASC) The_Value_HourPlus3
10              FROM The_Table)
11          ORDER BY (The_Value + NVL(The_Value_HourPlus1, 0) + NVL(The_Value_HourPlus2, 0) + NVL(The_Value_HourPlus3, 0)) / 4 DESC;

HOUR             HOURPLUS1        HOURPLUS2        HOURPLUS3          AVGVALUE
---------------- ---------------- ---------------- ---------------- ----------
01/08/2007 03:00 01/08/2007 04:00 01/08/2007 05:00 01/08/2007 06:00       7.25
01/08/2007 02:00 01/08/2007 03:00 01/08/2007 04:00 01/08/2007 05:00       5.75
01/08/2007 04:00 01/08/2007 05:00 01/08/2007 06:00 01/08/2007 07:00        5.5
01/08/2007 06:00 01/08/2007 07:00 01/08/2007 08:00 01/08/2007 09:00        5.5
01/08/2007 01:00 01/08/2007 02:00 01/08/2007 03:00 01/08/2007 04:00       5.25
01/08/2007 05:00 01/08/2007 06:00 01/08/2007 07:00 01/08/2007 08:00          5
01/08/2007 07:00 01/08/2007 08:00 01/08/2007 09:00 01/08/2007 10:00       4.25
01/08/2007 08:00 01/08/2007 09:00 01/08/2007 10:00                        3.75
01/08/2007 09:00 01/08/2007 10:00                                            3
01/08/2007 10:00                                                           .75

10 rows selected.

rbaraer@Ora10g> SELECT Hour, HourPlus1, HourPlus2, HourPlus3, AvgValue
2  FROM
3  (
4      SELECT Hour, HourPlus1, HourPlus2, HourPlus3, AvgValue, ROWNUM rn
5      FROM
6      (
7          SELECT Hour, HourPlus1, HourPlus2, HourPlus3, (The_Value + NVL(The_Value_HourPlus1, 0) + NVL(The_Value_HourPlus2, 0) + NVL(The_Value_HourPlus3, 0)) / 4 AvgValue
8          FROM (SELECT Hour,
9                  LEAD(Hour, 1) OVER (ORDER BY Hour ASC) HourPlus1,
10                  LEAD(Hour, 2) OVER (ORDER BY Hour ASC) HourPlus2,
11                  LEAD(Hour, 3) OVER (ORDER BY Hour ASC) HourPlus3,
12                  The_Value,
13                  LEAD(The_Value, 1) OVER (ORDER BY Hour ASC) The_Value_HourPlus1,
14                  LEAD(The_Value, 2) OVER (ORDER BY Hour ASC) The_Value_HourPlus2,
15                  LEAD(The_Value, 3) OVER (ORDER BY Hour ASC) The_Value_HourPlus3
16              FROM The_Table)
17          ORDER BY (The_Value + NVL(The_Value_HourPlus1, 0) + NVL(The_Value_HourPlus2, 0) + NVL(The_Value_HourPlus3, 0)) / 4 DESC
18      )
19  )
20  WHERE rn = 1;

HOUR             HOURPLUS1        HOURPLUS2        HOURPLUS3          AVGVALUE
---------------- ---------------- ---------------- ---------------- ----------
01/08/2007 03:00 01/08/2007 04:00 01/08/2007 05:00 01/08/2007 06:00       7.25

rbaraer@Ora10g>```
More to follow

9. Registered User
Join Date
Aug 2004
Location
France
Posts
754
Of course the previous post does not cover all combinations of 1, 2, 3 and 4 contiguous hours, but you could get it with UNION ALLs :

Code:
```rbaraer@Ora10g> SELECT *
2          FROM
3          (
4              SELECT Hour,
5                  HourPlus1,
6                  HourPlus2,
7                  HourPlus3,
8                  (The_Value + NVL(The_Value_HourPlus1, 0) + NVL(The_Value_HourPlus2, 0) + NVL(The_Value_HourPlus3, 0)) / 4 AvgValue
9              FROM (SELECT Hour,
10                      LEAD(Hour, 1) OVER (ORDER BY Hour ASC) HourPlus1,
11                      LEAD(Hour, 2) OVER (ORDER BY Hour ASC) HourPlus2,
12                      LEAD(Hour, 3) OVER (ORDER BY Hour ASC) HourPlus3,
13                      The_Value,
14                      LEAD(The_Value, 1) OVER (ORDER BY Hour ASC) The_Value_HourPlus1,
15                      LEAD(The_Value, 2) OVER (ORDER BY Hour ASC) The_Value_HourPlus2,
16                      LEAD(The_Value, 3) OVER (ORDER BY Hour ASC) The_Value_HourPlus3
17                  FROM The_Table)
18              UNION ALL
19              SELECT Hour,
20                  HourPlus1,
21                  HourPlus2,
22                  NULL,
23                  (The_Value + NVL(The_Value_HourPlus1, 0) + NVL(The_Value_HourPlus2, 0)) / 3 AvgValue
24              FROM (SELECT Hour,
25                      LEAD(Hour, 1) OVER (ORDER BY Hour ASC) HourPlus1,
26                      LEAD(Hour, 2) OVER (ORDER BY Hour ASC) HourPlus2,
27                      The_Value,
28                      LEAD(The_Value, 1) OVER (ORDER BY Hour ASC) The_Value_HourPlus1,
29                      LEAD(The_Value, 2) OVER (ORDER BY Hour ASC) The_Value_HourPlus2
30                  FROM The_Table)
31              UNION ALL
32              SELECT Hour,
33                  HourPlus1,
34                  NULL,
35                  NULL,
36                  (The_Value + NVL(The_Value_HourPlus1, 0)) / 2 AvgValue
37              FROM (SELECT Hour,
38                      LEAD(Hour, 1) OVER (ORDER BY Hour ASC) HourPlus1,
39                      The_Value,
40                      LEAD(The_Value, 1) OVER (ORDER BY Hour ASC) The_Value_HourPlus1
41                  FROM The_Table)
42              UNION ALL
43              SELECT Hour,
44                  NULL,
45                  NULL,
46                  NULL,
47                  The_Value AvgValue
48              FROM (SELECT Hour,
49                      The_Value
50                  FROM The_Table)
51          )
52          ORDER BY AvgValue DESC;

HOUR             HOURPLUS1        HOURPLUS2        HOURPLUS3          AVGVALUE
---------------- ---------------- ---------------- ---------------- ----------
01/08/2007 03:00                                                             9
01/08/2007 09:00                                                             9
01/08/2007 06:00                                                             8
01/08/2007 05:00 01/08/2007 06:00                                          7.5
01/08/2007 03:00 01/08/2007 04:00 01/08/2007 05:00 01/08/2007 06:00       7.25
01/08/2007 03:00 01/08/2007 04:00 01/08/2007 05:00                           7
01/08/2007 05:00                                                             7
01/08/2007 03:00 01/08/2007 04:00                                            7
01/08/2007 04:00 01/08/2007 05:00 01/08/2007 06:00                  6.66666667
01/08/2007 04:00 01/08/2007 05:00                                            6
01/08/2007 08:00 01/08/2007 09:00                                            6
01/08/2007 09:00 01/08/2007 10:00                                            6
01/08/2007 02:00 01/08/2007 03:00 01/08/2007 04:00 01/08/2007 05:00       5.75
01/08/2007 05:00 01/08/2007 06:00 01/08/2007 07:00                  5.66666667
01/08/2007 04:00 01/08/2007 05:00 01/08/2007 06:00 01/08/2007 07:00        5.5
01/08/2007 06:00 01/08/2007 07:00 01/08/2007 08:00 01/08/2007 09:00        5.5
01/08/2007 02:00 01/08/2007 03:00                                          5.5
01/08/2007 01:00 01/08/2007 02:00 01/08/2007 03:00                  5.33333333
01/08/2007 02:00 01/08/2007 03:00 01/08/2007 04:00                  5.33333333
01/08/2007 01:00 01/08/2007 02:00 01/08/2007 03:00 01/08/2007 04:00       5.25
01/08/2007 05:00 01/08/2007 06:00 01/08/2007 07:00 01/08/2007 08:00          5
01/08/2007 08:00 01/08/2007 09:00 01/08/2007 10:00                           5
01/08/2007 06:00 01/08/2007 07:00                                            5
01/08/2007 04:00                                                             5
01/08/2007 01:00                                                             5
01/08/2007 07:00 01/08/2007 08:00 01/08/2007 09:00                  4.66666667
01/08/2007 06:00 01/08/2007 07:00 01/08/2007 08:00                  4.33333333
01/08/2007 07:00 01/08/2007 08:00 01/08/2007 09:00 01/08/2007 10:00       4.25
01/08/2007 09:00 01/08/2007 10:00                                            4
01/08/2007 08:00 01/08/2007 09:00 01/08/2007 10:00                        3.75
01/08/2007 01:00 01/08/2007 02:00                                          3.5
01/08/2007 09:00 01/08/2007 10:00                                            3
01/08/2007 08:00                                                             3
01/08/2007 10:00                                                             3
01/08/2007 07:00 01/08/2007 08:00                                          2.5
01/08/2007 02:00                                                             2
01/08/2007 07:00                                                             2
01/08/2007 10:00                                                           1.5
01/08/2007 10:00                                                             1
01/08/2007 10:00                                                           .75

40 rows selected.

rbaraer@Ora10g> SELECT Hour, HourPlus1, HourPlus2, HourPlus3, AvgValue
2  FROM
3  (
4      SELECT Hour, HourPlus1, HourPlus2, HourPlus3, AvgValue, ROWNUM rn
5      FROM
6      (
7          SELECT *
8          FROM
9          (
10              SELECT Hour,
11                  HourPlus1,
12                  HourPlus2,
13                  HourPlus3,
14                  (The_Value + NVL(The_Value_HourPlus1, 0) + NVL(The_Value_HourPlus2, 0) + NVL(The_Value_HourPlus3, 0)) / 4 AvgValue
15              FROM (SELECT Hour,
16                      LEAD(Hour, 1) OVER (ORDER BY Hour ASC) HourPlus1,
17                      LEAD(Hour, 2) OVER (ORDER BY Hour ASC) HourPlus2,
18                      LEAD(Hour, 3) OVER (ORDER BY Hour ASC) HourPlus3,
19                      The_Value,
20                      LEAD(The_Value, 1) OVER (ORDER BY Hour ASC) The_Value_HourPlus1,
21                      LEAD(The_Value, 2) OVER (ORDER BY Hour ASC) The_Value_HourPlus2,
22                      LEAD(The_Value, 3) OVER (ORDER BY Hour ASC) The_Value_HourPlus3
23                  FROM The_Table)
24              UNION ALL
25              SELECT Hour,
26                  HourPlus1,
27                  HourPlus2,
28                  NULL,
29                  (The_Value + NVL(The_Value_HourPlus1, 0) + NVL(The_Value_HourPlus2, 0)) / 3 AvgValue
30              FROM (SELECT Hour,
31                      LEAD(Hour, 1) OVER (ORDER BY Hour ASC) HourPlus1,
32                      LEAD(Hour, 2) OVER (ORDER BY Hour ASC) HourPlus2,
33                      The_Value,
34                      LEAD(The_Value, 1) OVER (ORDER BY Hour ASC) The_Value_HourPlus1,
35                      LEAD(The_Value, 2) OVER (ORDER BY Hour ASC) The_Value_HourPlus2
36                  FROM The_Table)
37              UNION ALL
38              SELECT Hour,
39                  HourPlus1,
40                  NULL,
41                  NULL,
42                  (The_Value + NVL(The_Value_HourPlus1, 0)) / 2 AvgValue
43              FROM (SELECT Hour,
44                      LEAD(Hour, 1) OVER (ORDER BY Hour ASC) HourPlus1,
45                      The_Value,
46                      LEAD(The_Value, 1) OVER (ORDER BY Hour ASC) The_Value_HourPlus1
47                  FROM The_Table)
48              UNION ALL
49              SELECT Hour,
50                  NULL,
51                  NULL,
52                  NULL,
The_Value AvgValue
53   54              FROM (SELECT Hour,
55                      The_Value
56                  FROM The_Table)
57          )
58          ORDER BY AvgValue DESC
59      )
60  )
61  WHERE rn = 1;

HOUR             HOURPLUS1        HOURPLUS2        HOURPLUS3          AVGVALUE
---------------- ---------------- ---------------- ---------------- ----------
01/08/2007 03:00                                                             9

rbaraer@Ora10g>```
HTH & regards,

rbaraer

10. Registered User
Join Date
May 2004
Location
Dominican Republic
Posts
721
So, the set should be .. > 1 AND <= 4 ?

11. Registered User
Join Date
Jul 2003
Posts
2,296
I think I have the solution.
I used the LEAD tip from RBARAER to apply my logic.

PHP Code:
``` select avg_contig_values from (    select hour,     the_value, lead1, lead2, lead3,     the_value+lead1+lead2+lead3 total_values,     (the_value+lead1+lead2+lead3) / 4 avg_contig_values,     max(the_value+lead1+lead2+lead3) over () correct    from (        select          hour,          the_value,         lead(the_value, 1,0) over (order by hour) lead1,         lead(the_value, 2,0) over (order by hour) lead2,         lead(the_value, 3,0) over (order by hour) lead3        from perfs)) where correct = total_values;  ```
-- edit --
argh! That screws me up if the whole set is less than 4 hours though since it is dividing by 4.
I think I need a count of total hours to divide against ...
Last edited by The_Duck; 08-14-07 at 11:45.

12. Registered User
Join Date
Jul 2003
Posts
2,296
got it:
Code:
```select
correct
/
(case when set_total < 4 then set_total else 4 end ) avg_contig_values
from (
select
hour,
count(hour) over () set_total,
from (
select
hour,
the_value,
from perfs)
) where correct = total_values;```

13. Registered User
Join Date
Aug 2004
Location
France
Posts
754
Originally Posted by The_Duck
-- edit --
argh! That screws me up if the whole set is less than 4 hours though since it is dividing by 4.
I think I need a count of total hours to divide against ...
Can't you replace 4 with :
Code:
```(CASE (WHEN the_value IS NOT NULL) THEN 1 ELSE 0 END
+ CASE (WHEN lead1 IS NOT NULL) THEN 1 ELSE 0 END
+ CASE (WHEN lead2 IS NOT NULL) THEN 1 ELSE 0 END
+ CASE (WHEN lead3 IS NOT NULL) THEN 1 ELSE 0 END)```
??

Regards,

rbaraer

14. Registered User
Join Date
Nov 2006
Location
Indianapolis
Posts
90