Results 1 to 14 of 14
  1. #1
    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 ...
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    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. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, contiguous

    no idea why i didn't catch onto that concept, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    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
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  6. #6
    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
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    Quote 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?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote 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
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  9. #9
    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
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

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

  11. #11
    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_valuelead1lead2lead3,
         
    the_value+lead1+lead2+lead3 total_values,
         (
    the_value+lead1+lead2+lead3) / 4 avg_contig_values,
         
    max(the_value+lead1+lead2+lead3over () correct
        from 
    (
            
    select 
             hour

             
    the_value,
             
    lead(the_value1,0over (order by hourlead1,
             
    lead(the_value2,0over (order by hourlead2,
             
    lead(the_value3,0over (order by hourlead3
            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 12:45.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    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,
         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;
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote 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)
    ??

    Just saw your update. Glad you got it and glad I could help a little .

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  14. #14
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    Very nice, using Lead...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •