Analytical Functions Question

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Yuri G.
    #1

    Analytical Functions Question


    Hi,

    I'm trying to come up with a solution to seemingly simple
    database query, which I'm sure could be done with Oracle9 analytical
    functions, but somehow the solution is elusive:

    I have a table of the following structure:

    create table values (
    ....
    val NUMERIC(10),
    tm TIMESTAMP
    ....
    );

    (only relevant fields are shown)

    Records are constantly inserted into table with tm equal SYSDATE at the
    time of insertion.

    I need a query which will produce SUM(val) over time period of last N minutes
    partitioned by M minutes with total for each period of M as well as running
    total.
    For example, if N = 60 min and M = 10 min it will be SUM(val) for every 10
    minute interval within last hour as well as running total of this sums.

    Could anybody point me to an example or tutorial for using Oracle analytical
    functions. - This is not a homework. I'm sure that this is easily done with
    analytical functions, but I can't get the time window right and always get
    sum for 10 minutes relative to current point instead of interval between the
    fixed values. Oracle documentation is a little obscure with regards to
    analytical functions and all the examples are about the running totals of
    salaries and such.

    Thanks,

    Yuri.


    Tags: None
    • Tony
      #2
      Re: Analytical Functions Question

      "Yuri G." <y-u-r-i-g@verizon.netwr ote in message news:<pan.2004. 02.05.06.46.32. 304323@verizon. net>...
      Hi,
      >
      I'm trying to come up with a solution to seemingly simple
      database query, which I'm sure could be done with Oracle9 analytical
      functions, but somehow the solution is elusive:
      >
      I have a table of the following structure:
      >
      create table values (
      ...
      val NUMERIC(10),
      tm TIMESTAMP
      ...
      );
      >
      (only relevant fields are shown)
      >
      Records are constantly inserted into table with tm equal SYSDATE at the
      time of insertion.
      >
      I need a query which will produce SUM(val) over time period of last N minutes
      partitioned by M minutes with total for each period of M as well as running
      total.
      For example, if N = 60 min and M = 10 min it will be SUM(val) for every 10
      minute interval within last hour as well as running total of this sums.
      >
      Could anybody point me to an example or tutorial for using Oracle analytical
      functions. - This is not a homework. I'm sure that this is easily done with
      analytical functions, but I can't get the time window right and always get
      sum for 10 minutes relative to current point instead of interval between the
      fixed values. Oracle documentation is a little obscure with regards to
      analytical functions and all the examples are about the running totals of
      salaries and such.
      >
      Thanks,
      >
      Yuri.
      No analytical function needed, just some date arithmetic:

      SQLselect * from valuet;

      TM VAL
      -------------------- ----------
      05-FEB-2004 14:14:43 1
      05-FEB-2004 14:15:43 2
      05-FEB-2004 14:16:43 3
      05-FEB-2004 14:17:43 4
      05-FEB-2004 14:18:43 5
      05-FEB-2004 14:19:43 6
      05-FEB-2004 14:20:43 7
      05-FEB-2004 14:21:43 8
      05-FEB-2004 14:22:43 9
      05-FEB-2004 14:23:43 10
      05-FEB-2004 14:24:43 11
      05-FEB-2004 14:25:43 12
      05-FEB-2004 14:26:43 13
      05-FEB-2004 14:27:43 14

      14 rows selected.

      SQLselect trunc(tm) + floor((tm-trunc(tm))*24*6 0/10)/24/60*10, sum(val)
      2 from valuet
      3 where tm >= sysdate-1/24 -- Last 60 minutes
      4 group by trunc(tm) + floor((tm-trunc(tm))*24*6 0/10)/24/60*10;

      TRUNC(TM)+FLOOR ((TM- SUM(VAL)
      -------------------- ----------
      05-FEB-2004 14:10:00 21
      05-FEB-2004 14:20:00 84

        Comment

        • AK
          #3
          Re: Analytical Functions Question

          Privet Yuri,

          I'd create a permanent calendar table, containing a row for each minute, like this:
          NUM TIME_FROM TIME_TO
          1 02/04/2003 00:00:00 02/04/2003 00:00:01
          2 02/04/2003 00:00:01 02/04/2003 00:00:02
          ....
          60 02/04/2003 00:00:59 02/04/2003 00:01:00

          and so on

          That done, it would be easy to write the query you are asking about

          here are DB2 links, the syntax seems to be the same:
          http://www-106.ibm.com/developerworks/db2/library/techarticle/lyle/0110lyle.html

          http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0401kuznetsov/

            Comment

            • Dr Drudge
              #4
              Re: Analytical Functions Question

              ak_tiredofspam@ yahoo.com (AK) wrote in message news:<46e627da. 0402050649.5a9e caaf@posting.go ogle.com>...
              Privet Yuri,
              >
              I'd create a permanent calendar table, containing a row for each minute, like this:
              NUM TIME_FROM TIME_TO
              1 02/04/2003 00:00:00 02/04/2003 00:00:01
              2 02/04/2003 00:00:01 02/04/2003 00:00:02
              ...
              60 02/04/2003 00:00:59 02/04/2003 00:01:00
              >
              and so on
              >
              That done, it would be easy to write the query you are asking about
              >
              here are DB2 links, the syntax seems to be the same:
              http://www-106.ibm.com/developerworks/db2/library/techarticle/lyle/0110lyle.html

              http://www-106.ibm.com/developerwork...0401kuznetsov/

              Utter drivel...
              That is just about the worst advice I have ever seen posted- even I can see that.

                Comment

                Previous template Next
                Working...