Home » SQL & PL/SQL » SQL & PL/SQL » help with analitycal functiol
help with analitycal functiol [message #229864] Tue, 10 April 2007 03:32 Go to next message
dusoo
Messages: 41
Registered: March 2007
Member
Hi everyone,
sofar i'm using plsql script to calculate something, but this solution is not really good, so im curious if there is any analitycal function that will be simple placed into the query ..

data im having in TABLE TEMP_A

time, name

2. 3. 2007 17:08:56 SETUP_PROCESSED
2. 3. 2007 17:09:05 SETUP_PROCESSED
2. 3. 2007 17:09:16 STREAM_TERMINATED
2. 3. 2007 17:09:27 SETUP_PROCESSED
2. 3. 2007 17:10:09 STREAM_TERMINATED
2. 3. 2007 17:10:11 STREAM_TERMINATED
2. 3. 2007 17:10:30 SETUP_PROCESSED
2. 3. 2007 17:10:34 DOWNLOAD_PROCESSED
2. 3. 2007 17:10:38 DOWNLOAD_TERMINATED
.
.
.
What i need is simple thing (easily done in plsql Smile ) when name = SETUP_PROCESSED or DOWNLOAD_PROCESSED then xxx = xxx + 1, when name = %TERMINATED then xxx = xxx - 1 ...Ive done this in plsql.proc during which im inserting record by record into other temp table from which im reading the output - in that table ill have this data

2. 3. 2007 17:08:56 1
2. 3. 2007 17:09:05 2
2. 3. 2007 17:09:16 1
2. 3. 2007 17:09:27 2
2. 3. 2007 17:10:09 1
2. 3. 2007 17:10:11 0
2. 3. 2007 17:10:30 1
2. 3. 2007 17:10:34 2
2. 3. 2007 17:10:38 1
.
.
.

Thanks for any advice !!!

dusoo
Re: help with analitycal function [message #229873 is a reply to message #229864] Tue, 10 April 2007 03:51 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You were right: an analytic function would do just fine for your requirement if I got it right:
WITH yourtable AS
     (SELECT TO_DATE ('2.3.2007 17:08:56', 'dd.mm.yyyy hh24:mi:ss') thedate
           , 'SETUP_PROCESSED' thename
           , 1 expected
      FROM   DUAL
      UNION ALL
      SELECT TO_DATE ('2.3.2007 17:09:05', 'dd.mm.yyyy hh24:mi:ss') thedate
           , 'SETUP_PROCESSED' thename
           , 2 expected
      FROM   DUAL
      UNION ALL
      SELECT TO_DATE ('2.3.2007 17:09:16', 'dd.mm.yyyy hh24:mi:ss') thedate
           , 'STREAM_TERMINATED' thename
           , 1 expected
      FROM   DUAL
      UNION ALL
      SELECT TO_DATE ('2.3.2007 17:09:27', 'dd.mm.yyyy hh24:mi:ss') thedate
           , 'SETUP_PROCESSED' thename
           , 2 expected
      FROM   DUAL
      UNION ALL
      SELECT TO_DATE ('2.3.2007 17:10:09', 'dd.mm.yyyy hh24:mi:ss') thedate
           , 'STREAM_TERMINATED' thename
           , 1 expected
      FROM   DUAL
      UNION ALL
      SELECT TO_DATE ('2.3.2007 17:10:11', 'dd.mm.yyyy hh24:mi:ss') thedate
           , 'STREAM_TERMINATED' thename
           , 0 expected
      FROM   DUAL
      UNION ALL
      SELECT TO_DATE ('2.3.2007 17:10:30', 'dd.mm.yyyy hh24:mi:ss') thedate
           , 'SETUP_PROCESSED' thename
           , 1 expected
      FROM   DUAL
      UNION ALL
      SELECT TO_DATE ('2.3.2007 17:10:34', 'dd.mm.yyyy hh24:mi:ss') thedate
           , 'DOWNLOAD_PROCESSED' thename
           , 2 expected
      FROM   DUAL
      UNION ALL
      SELECT TO_DATE ('2.3.2007 17:10:38', 'dd.mm.yyyy hh24:mi:ss') thedate
           , 'DOWNLOAD_TERMINATED' thename
           , 1 expected
      FROM   DUAL)
SELECT   thedate
       , SUM (DECODE (thename
                    , 'SETUP_PROCESSED', 1
                    , 'DOWNLOAD_PROCESSED', 1
                    , -1
                     )
             ) OVER (ORDER BY thedate) thesum
      , expected
FROM     yourtable
ORDER BY thedate
/


when I ran the above select (forget the with part, start reading at the select and change the names of columns/tables), I got the following:
THEDATE       THESUM   EXPECTED
--------- ---------- ----------
02-MAR-07          1          1
02-MAR-07          2          2
02-MAR-07          1          1
02-MAR-07          2          2
02-MAR-07          1          1
02-MAR-07          0          0
02-MAR-07          1          1
02-MAR-07          2          2
02-MAR-07          1          1


MHE
Re: help with analitycal function [message #229889 is a reply to message #229873] Tue, 10 April 2007 04:36 Go to previous message
dusoo
Messages: 41
Registered: March 2007
Member
Hi,
thank you,

that worked perfectly.


d.
Previous Topic: Table Code
Next Topic: Delete Duplicates
Goto Forum:
  


Current Time: Fri Dec 09 07:59:45 CST 2016

Total time taken to generate the page: 0.04705 seconds