1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Summerizing Data in 30 minute increments... Data comes every 5 minutes....

Discussion in 'SQL PL/SQL' started by beaujest, Jul 12, 2011.

  1. beaujest

    beaujest Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Oregon
    Hi, I'm new at aggregating data but, I could use some help with what are the best functions to use in a single pass, joins or inline views are fine... Basically the data comes in like...
    Date Time unique identifier Value to be summerized_1 value to be averaged_2
    12/09/1962 00:00 1 100 200
    12/09/1962 00:05 2 101 199
    12/09/0962 00:10 5 100 201
    etc....(I never get 00:01 for example)

    I would like the data to have one row for every 30 minutes starting at 00:00 and 00:30 (48 times a day)
    12/09/1962 00:00 1 602 200
    12/09/1962 00:30 1 650 199
    Primary key includes date, time and unique indentifier...
    Thanks Much...
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your sample data (which has an error) doesn't produce the sample result you posted. Post some accurate, useful example data so we can assist you.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Here is one way to do what you're asking:

    Code (SQL):
     
    SQL>
    SQL> CREATE TABLE sum_avg_test(
      2        dt      DATE NOT NULL,
      3        TIME    varchar2(5) NOT NULL,
      4        uniq_id NUMBER NOT NULL,
      5        val1    NUMBER,
      6        val2    NUMBER,
      7        CONSTRAINT sumavgpk
      8        PRIMARY KEY(dt,TIME,uniq_id)
      9  );
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
      2        FOR i IN 0..287 loop
      3         INSERT INTO sum_avg_test
      4         VALUES (trunc(sysdate)+(i*5)/1440,
      5          to_char(trunc(sysdate)+(i*5)/1440, 'HH24:MI'),
      6          i+1,
      7          i+107, i+303);
      8        END loop;
      9  
     10        commit;
     11  
     12  END;
     13  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT dt, TIME, uniq_id, run_sum, run_avg
      2  FROM
      3  (SELECT dt, TIME, uniq_id, SUM(val1) OVER (ORDER BY dt, TIME, uniq_id) run_sum, avg(val2) OVER (ORDER BY dt, TIME, uniq_id) run_avg
      4  FROM sum_avg_test)
      5  WHERE (to_number(to_char(dt, 'MI')) = 0 OR MOD(to_number(to_char(dt, 'MI')), 30) = 0);
     
    DT        TIME     UNIQ_ID    RUN_SUM    RUN_AVG                                
    --------- ----- ---------- ---------- ----------                                
    12-JUL-11 00:00          1        107        303                                
    12-JUL-11 00:30          7        770        306                                
    12-JUL-11 01:00         13       1469        309                                
    12-JUL-11 01:30         19       2204        312                                
    12-JUL-11 02:00         25       2975        315                                
    12-JUL-11 02:30         31       3782        318                                
    12-JUL-11 03:00         37       4625        321                                
    12-JUL-11 03:30         43       5504        324                                
    12-JUL-11 04:00         49       6419        327                                
    12-JUL-11 04:30         55       7370        330                                
    12-JUL-11 05:00         61       8357        333                                
     
    DT        TIME     UNIQ_ID    RUN_SUM    RUN_AVG                                
    --------- ----- ---------- ---------- ----------                                
    12-JUL-11 05:30         67       9380        336                                
    12-JUL-11 06:00         73      10439        339                                
    12-JUL-11 06:30         79      11534        342                                
    12-JUL-11 07:00         85      12665        345                                
    12-JUL-11 07:30         91      13832        348                                
    12-JUL-11 08:00         97      15035        351                                
    12-JUL-11 08:30        103      16274        354                                
    12-JUL-11 09:00        109      17549        357                                
    12-JUL-11 09:30        115      18860        360                                
    12-JUL-11 10:00        121      20207        363                                
    12-JUL-11 10:30        127      21590        366                                
     
    DT        TIME     UNIQ_ID    RUN_SUM    RUN_AVG                                
    --------- ----- ---------- ---------- ----------                                
    12-JUL-11 11:00        133      23009        369                                
    12-JUL-11 11:30        139      24464        372                                
    12-JUL-11 12:00        145      25955        375                                
    12-JUL-11 12:30        151      27482        378                                
    12-JUL-11 13:00        157      29045        381                                
    12-JUL-11 13:30        163      30644        384                                
    12-JUL-11 14:00        169      32279        387                                
    12-JUL-11 14:30        175      33950        390                                
    12-JUL-11 15:00        181      35657        393                                
    12-JUL-11 15:30        187      37400        396                                
    12-JUL-11 16:00        193      39179        399                                
     
    DT        TIME     UNIQ_ID    RUN_SUM    RUN_AVG                                
    --------- ----- ---------- ---------- ----------                                
    12-JUL-11 16:30        199      40994        402                                
    12-JUL-11 17:00        205      42845        405                                
    12-JUL-11 17:30        211      44732        408                                
    12-JUL-11 18:00        217      46655        411                                
    12-JUL-11 18:30        223      48614        414                                
    12-JUL-11 19:00        229      50609        417                                
    12-JUL-11 19:30        235      52640        420                                
    12-JUL-11 20:00        241      54707        423                                
    12-JUL-11 20:30        247      56810        426                                
    12-JUL-11 21:00        253      58949        429                                
    12-JUL-11 21:30        259      61124        432                                
     
    DT        TIME     UNIQ_ID    RUN_SUM    RUN_AVG                                
    --------- ----- ---------- ---------- ----------                                
    12-JUL-11 22:00        265      63335        435                                
    12-JUL-11 22:30        271      65582        438                                
    12-JUL-11 23:00        277      67865        441                                
    12-JUL-11 23:30        283      70184        444                                
     
    48 ROWS selected.
     
    SQL>

     
     
  4. beaujest

    beaujest Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Oregon
    Thank you David, This should really help! How do you guys learn this to be able to spit it out so fast?
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    22 years of professional experience helps, along with 'playing' with Oracle as much as I can to learn new features. You do understand how the query works?
     
  6. beaujest

    beaujest Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Oregon
    Not entirely but, probably only because I didn't explain the problem completely. I need one line of data for every 30 minute block with only the total or avg. from the previous or subsequent 6 units of time..ie 00:00, 00:05, ... 00:25 is one block and 00:35 ... 00:55 is the other block of 6 time units... don't need a running total. I think you put me on the right track maybe with some kind of windowing clause.. I'm still experimenting... I need some kind of group by count() = 6 or something. Thanks for your valuable time.

    beaujest