9 Replies Latest reply on Feb 25, 2019 1:14 PM by user12087924

    How to move to the next record in a table and perform some calculations

    user12087924

      Oracle 18.3

       

      I have an history table "History" which contains the values of a sensor on a specific date and and statistics table STATS_TBL_S1 that contains some calculations on the date provided.

      CREATE TABLE HISTORY 
         (    "SDATE" DATE NOT NULL ENABLE, 
          "SENSOR1" NUMBER(2,0)
         );
      Insert into HISTORY (SDATE,SENSOR1) values (to_date('2018-09-20 00:00:00','YYYY-MM-DD HH24:MI:SS'),1);
      Insert into HISTORY (SDATE,SENSOR1) values (to_date('2018-09-22 00:00:00','YYYY-MM-DD HH24:MI:SS'),4);
      Insert into HISTORY (SDATE,SENSOR1) values (to_date('2018-09-25 00:00:00','YYYY-MM-DD HH24:MI:SS'),7);
      Insert into HISTORY (SDATE,SENSOR1) values (to_date('2018-09-27 00:00:00','YYYY-MM-DD HH24:MI:SS'),9);
      Insert into HISTORY (SDATE,SENSOR1) values (to_date('2018-09-29 00:00:00','YYYY-MM-DD HH24:MI:SS'),7);
      Insert into HISTORY (SDATE,SENSOR1) values (to_date('2018-10-02 00:00:00','YYYY-MM-DD HH24:MI:SS'),7);
      Insert into HISTORY (SDATE,SENSOR1) values (to_date('2018-10-04 00:00:00','YYYY-MM-DD HH24:MI:SS'),10);
      Insert into HISTORY (SDATE,SENSOR1) values (to_date('2018-10-06 00:00:00','YYYY-MM-DD HH24:MI:SS'),12);
      Insert into HISTORY (SDATE,SENSOR1) values (to_date('2018-10-09 00:00:00','YYYY-MM-DD HH24:MI:SS'),1);
      Insert into HISTORY (SDATE,SENSOR1) values (to_date('2018-10-11 00:00:00','YYYY-MM-DD HH24:MI:SS'),4);
      Insert into HISTORY (SDATE,SENSOR1) values (to_date('2018-10-13 00:00:00','YYYY-MM-DD HH24:MI:SS'),1);
      Insert into HISTORY (SDATE,SENSOR1) values (to_date('2018-10-16 00:00:00','YYYY-MM-DD HH24:MI:SS'),12);
      Insert into HISTORY (SDATE,SENSOR1) values (to_date('2018-10-18 00:00:00','YYYY-MM-DD HH24:MI:SS'),5);
      Insert into HISTORY (SDATE,SENSOR1) values (to_date('2018-10-20 00:00:00','YYYY-MM-DD HH24:MI:SS'),8);
      Insert into HISTORY (SDATE,SENSOR1) values (to_date('2018-10-23 00:00:00','YYYY-MM-DD HH24:MI:SS'),3);
      Insert into HISTORY (SDATE,SENSOR1) values (to_date('2018-10-25 00:00:00','YYYY-MM-DD HH24:MI:SS'),10);
      Insert into HISTORY (SDATE,SENSOR1) values (to_date('2018-10-27 00:00:00','YYYY-MM-DD HH24:MI:SS'),15);
      Insert into HISTORY (SDATE,SENSOR1) values (to_date('2018-10-30 00:00:00','YYYY-MM-DD HH24:MI:SS'),2);
      
        CREATE TABLE STATS_TBL_S1
         ("ANALYSIS_DATE" DATE, 
          "SENSOR1" NUMBER(2,0), 
          "LASTDATE" DATE, 
          "CFIELD_REP" NUMBER, 
          "MINDIST" NUMBER, 
          "LOWER_LIMIT" NUMBER, 
          "DISTMEDIAN" NUMBER, 
          "HIGHER_LIMIT" NUMBER, 
          "MAXDIST" NUMBER, 
          "NDISTTODAY" NUMBER
         );
      
      Insert into STATS_TBL_S1 (ANALYSIS_DATE,SENSOR1,LASTDATE,CFIELD_REP,MINDIST,LOWER_LIMIT,DISTMEDIAN,HIGHER_LIMIT,MAXDIST,NDISTTODAY) values (to_date('2018-10-25 00:00:00','YYYY-MM-DD HH24:MI:SS'),7,to_date('2018-10-02 00:00:00','YYYY-MM-DD HH24:MI:SS'),3,3,3,4,4,4,23);
      Insert into STATS_TBL_S1 (ANALYSIS_DATE,SENSOR1,LASTDATE,CFIELD_REP,MINDIST,LOWER_LIMIT,DISTMEDIAN,HIGHER_LIMIT,MAXDIST,NDISTTODAY) values (to_date('2018-10-25 00:00:00','YYYY-MM-DD HH24:MI:SS'),10,to_date('2018-10-25 00:00:00','YYYY-MM-DD HH24:MI:SS'),2,21,21,21,21,21,0);
      Insert into STATS_TBL_S1 (ANALYSIS_DATE,SENSOR1,LASTDATE,CFIELD_REP,MINDIST,LOWER_LIMIT,DISTMEDIAN,HIGHER_LIMIT,MAXDIST,NDISTTODAY) values (to_date('2018-10-25 00:00:00','YYYY-MM-DD HH24:MI:SS'),3,to_date('2018-10-23 00:00:00','YYYY-MM-DD HH24:MI:SS'),1,0,0,0,0,0,2);
      Insert into STATS_TBL_S1 (ANALYSIS_DATE,SENSOR1,LASTDATE,CFIELD_REP,MINDIST,LOWER_LIMIT,DISTMEDIAN,HIGHER_LIMIT,MAXDIST,NDISTTODAY) values (to_date('2018-10-25 00:00:00','YYYY-MM-DD HH24:MI:SS'),4,to_date('2018-10-11 00:00:00','YYYY-MM-DD HH24:MI:SS'),2,19,19,19,19,19,14);
      Insert into STATS_TBL_S1 (ANALYSIS_DATE,SENSOR1,LASTDATE,CFIELD_REP,MINDIST,LOWER_LIMIT,DISTMEDIAN,HIGHER_LIMIT,MAXDIST,NDISTTODAY) values (to_date('2018-10-25 00:00:00','YYYY-MM-DD HH24:MI:SS'),12,to_date('2018-10-16 00:00:00','YYYY-MM-DD HH24:MI:SS'),2,10,10,10,10,10,9);
      Insert into STATS_TBL_S1 (ANALYSIS_DATE,SENSOR1,LASTDATE,CFIELD_REP,MINDIST,LOWER_LIMIT,DISTMEDIAN,HIGHER_LIMIT,MAXDIST,NDISTTODAY) values (to_date('2018-10-25 00:00:00','YYYY-MM-DD HH24:MI:SS'),1,to_date('2018-10-13 00:00:00','YYYY-MM-DD HH24:MI:SS'),3,4,4,12,19,19,12);
      Insert into STATS_TBL_S1 (ANALYSIS_DATE,SENSOR1,LASTDATE,CFIELD_REP,MINDIST,LOWER_LIMIT,DISTMEDIAN,HIGHER_LIMIT,MAXDIST,NDISTTODAY) values (to_date('2018-10-25 00:00:00','YYYY-MM-DD HH24:MI:SS'),5,to_date('2018-10-18 00:00:00','YYYY-MM-DD HH24:MI:SS'),1,0,0,0,0,0,7);
      Insert into STATS_TBL_S1 (ANALYSIS_DATE,SENSOR1,LASTDATE,CFIELD_REP,MINDIST,LOWER_LIMIT,DISTMEDIAN,HIGHER_LIMIT,MAXDIST,NDISTTODAY) values (to_date('2018-10-25 00:00:00','YYYY-MM-DD HH24:MI:SS'),9,to_date('2018-09-27 00:00:00','YYYY-MM-DD HH24:MI:SS'),1,0,0,0,0,0,28);
      Insert into STATS_TBL_S1 (ANALYSIS_DATE,SENSOR1,LASTDATE,CFIELD_REP,MINDIST,LOWER_LIMIT,DISTMEDIAN,HIGHER_LIMIT,MAXDIST,NDISTTODAY) values (to_date('2018-10-25 00:00:00','YYYY-MM-DD HH24:MI:SS'),8,to_date('2018-10-20 00:00:00','YYYY-MM-DD HH24:MI:SS'),1,0,0,0,0,0,5);
      

       

       

      Provided a date as starting point, in this case the 2018-10-25 I would like to perform the calculations for every date higher than 2018-10-25

                 

      HISTORY TABLESTATTITICS TABLE for SENSOR1
      DATE Sensor1ANALISYS_DATE Sensor1 LAST_DATE CFIELD_REP MINDIST LOWER_LIMIT DISTMEDIAN HIGHER_LIMIT MAXDIST NDISTTODAY
      2018-09-20 00:00:0012018-10-25 00:00:0012018-10-13 00:00:0034412191912
      2018-09-22 00:00:0042018-10-25 00:00:0032018-10-23 00:00:001000002
      2018-09-25 00:00:0072018-10-25 00:00:0042018-10-11 00:00:002191919191914
      2018-09-27 00:00:0092018-10-25 00:00:0052018-10-18 00:00:001000007
      2018-09-29 00:00:0072018-10-25 00:00:0072018-10-02 00:00:0033344423
      2018-10-02 00:00:0072018-10-25 00:00:0082018-10-20 00:00:001000005
      2018-10-04 00:00:00102018-10-25 00:00:0092018-09-27 00:00:0010000028
      2018-10-06 00:00:00122018-10-25 00:00:00102018-10-25 00:00:00221212121210
      2018-10-09 00:00:0012018-10-25 00:00:00122018-10-16 00:00:00210101010109
      2018-10-11 00:00:004
      2018-10-13 00:00:001
      2018-10-16 00:00:0012
      2018-10-18 00:00:005
      2018-10-20 00:00:008
      2018-10-23 00:00:003
      2018-10-25 00:00:0010
      2018-10-27 00:00:0015
      2018-10-30 00:00:002

       

      Statement that do the calculations:

      MERGE INTO STATS_TBL_S1 trgtbl
         USING (
            WITH DISTCALC AS (
               SELECT MAX(t.sdate) OVER() MAX_DATE
                     ,t.sdate
                     ,t.Sensor1
                     ,count(*) over (partition by Sensor1) CFIELD_REP
                     ,NVL(t.sdate -lag (t.sdate) over (partition by Sensor1 order by t.sdate ASC), 0) DISTANCE
               FROM History t
               WHERE t.sdate <= '2018-10-25'
               ),
               STATS AS (
                  SELECT DISTCALC.MAX_DATE ANALYSIS_DATE 
                        ,Sensor1
                        ,MAX(DISTCALC.sdate) LASTDATE
                        ,CFIELD_REP
                        ,MIN(ABS(DISTCALC.distance)) MinDist
                        ,ROUND(MEDIAN(DISTCALC.distance)-STDDEV_POP(DISTCALC.distance)) LOWER_LIMIT
                        ,ROUND(MEDIAN(DISTCALC.distance)) DistMedian
                        ,ROUND(MEDIAN(DISTCALC.distance)+STDDEV_POP(DISTCALC.distance)) HIGHER_LIMIT
                        ,MAX(DISTCALC.distance) MaxDist
                  FROM DISTCALC 
                  WHERE DISTCALC.distance > 0
                  OR (DISTCALC.sdate = DISTCALC.MAX_DATE AND DISTCALC.distance = 0)
                  OR (DISTCALC.CFIELD_REP = 1 AND DISTCALC.distance = 0)
                  GROUP BY Sensor1,CFIELD_REP, DISTCALC.MAX_DATE
               )
               SELECT STATS.*
                     ,ROUND(STATS.ANALYSIS_DATE - STATS.LASTDATE) NDISTTODAY
               FROM STATS
         ) SrcTbl
      ON (TrgTbl.ANALYSIS_DATE = SrcTbl.ANALYSIS_DATE AND TrgTbl.Sensor1 = SrcTbl.Sensor1) 
      WHEN MATCHED THEN
          UPDATE SET trgtbl.LASTDATE         = srctbl.LASTDATE
                    ,trgtbl.CFIELD_REP       = srctbl.CFIELD_REP
                    ,trgtbl.MINDIST          = srctbl.MINDIST
                    ,trgtbl.LOWER_LIMIT      = srctbl.LOWER_LIMIT
                    ,trgtbl.DISTMEDIAN       = srctbl.DISTMEDIAN
                    ,trgtbl.HIGHER_LIMIT     = srctbl.HIGHER_LIMIT
                    ,trgtbl.MAXDIST          = srctbl.MAXDIST
                    ,trgtbl.NDISTTODAY       = srctbl.NDISTTODAY
      WHEN NOT MATCHED THEN
          INSERT (trgtbl.ANALYSIS_DATE
                 ,trgtbl.Sensor1
                 ,trgtbl.LASTDATE
                 ,trgtbl.CFIELD_REP
                 ,trgtbl.MINDIST
                 ,trgtbl.LOWER_LIMIT
                 ,trgtbl.DISTMEDIAN
                 ,trgtbl.HIGHER_LIMIT
                 ,trgtbl.MAXDIST
                 ,trgtbl.NDISTTODAY)
          VALUES (srctbl.ANALYSIS_DATE
                 ,srctbl.Sensor1
                 ,srctbl.LASTDATE
                 ,srctbl.CFIELD_REP
                 ,srctbl.MINDIST
                 ,srctbl.LOWER_LIMIT
                 ,srctbl.DISTMEDIAN
                 ,srctbl.HIGHER_LIMIT
                 ,srctbl.MAXDIST
                 ,srctbl.NDISTTODAY);  
      

       

      How to change the statement in order to do the calculations for 2018-10-25, 2018-10-27 and 2018-10-30 ?

        • 1. Re: How to move to the next record in a table and perform some calculations
          top.gun

          Please review this document, and rephrase your question to suit:

          Community Posting Etiquette

          • 2. Re: How to move to the next record in a table and perform some calculations
            Mustafa KALAYCI

            Hello,

             

            you already have one sql statements that does your job? what do you need exactly? Do you want to get result data using a select statement instead of merge ?

            • 3. Re: How to move to the next record in a table and perform some calculations
              Frank Kulash

              Hi,

               

              Thanks for posting the CREATE TABLE and INSERT statements for the sample data.

              Don't forget to post the exact results you want from the given data: in this case, that means show what stats_tbs_s1 should contain after the MERGE is finished.

               

              On line 10 of the MERGE statement, you have

              WHERE t.sdate <= '2018-10-25'

              Don't try to compare a DATE (like history.sdate) to a string (like '2018-10-25').  Use TO_DATE, like you did in the INSERT statements, or use a DATE literal, like this:

              WHERE t.sdate <=  DATE  '2018-10-25'

               

              When you say "do the calculations for 2018-10-25, 2018-10-27 and 2018-10-30", do you mean you want to pass a multiple paramenters to the statement (3 in this example), or do you want to pass only 1 parameter (2018-10-25) and have the calculations done for that date and every later date already in the history table?

              • 4. Re: How to move to the next record in a table and perform some calculations
                user12087924

                the statement only does the calculation for one specific date I want for this example all dates higher than 2018-10-25 that are in the history table

                • 5. Re: How to move to the next record in a table and perform some calculations
                  user12087924

                  the exact result of the statement is on STATTITICS TABLE for SENSOR1

                  What I want is to have the calculations done for that date and every later date already in the history table. On this example there are only 3 dates but it can be many more.

                  I want to avoid to make a FOR ... LOOP going through the dates higher than 2018-10-25 on the history table

                  • 6. Re: How to move to the next record in a table and perform some calculations
                    John Thorton

                    user12087924 wrote:

                     

                    the exact result of the statement is on STATTITICS TABLE for SENSOR1

                    What I want is to have the calculations done for that date and every later date already in the history table. On this example there are only 3 dates but it can be many more.

                    WHERE t.sdate in (select SDATE from history)

                    • 7. Re: How to move to the next record in a table and perform some calculations
                      user12087924

                      does not work

                      the  calculation are made always with all historical data it means if I am analyzing 2018-10-25 I need to calculate all data existing in history table below 2018-10-25 (<= 2018-10-25)

                      if I am analyzing 2018-10-27 I need to calculate all data existing in history table below 2018-10-27 (<= 2018-10-27)

                      for 2018-10-30 I need to calculate all data existing in history table below 2018-10-30 (<= 2018-10-30)

                      etc... until no more records in the history table to be analyzed

                      • 8. Re: How to move to the next record in a table and perform some calculations
                        Mustafa KALAYCI

                        I am too sleepy but I guess (based on your query) that will do what you want:

                         

                        WITH DISTCALC AS (  
                                 SELECT MAX(t.sdate) OVER(partition by t2.sdate) MAX_DATE  
                                       ,t.sdate  
                                       ,t.Sensor1  
                                       ,count(*) over (partition by Sensor1,t2.sdate) CFIELD_REP  
                                       ,NVL(t.sdate -lag (t.sdate) over (partition by Sensor1, t2.sdate order by t.sdate ASC), 0) DISTANCE  ,
                                       t2.sdate calculation_date
                                 FROM History t
                                        join (select distinct  r.sdate from History r where r.sdate >= to_date('2018-10-25', 'yyyy-mm-dd') )t2
                                          on t.sdate <= t2.sdate
                                 --WHERE t.sdate <= to_date('2018-10-25', 'yyyy-mm-dd')  
                                 ),  
                                 STATS AS (  
                                    SELECT DISTCALC.MAX_DATE ANALYSIS_DATE   
                                          ,calculation_date
                                          ,Sensor1  
                                          ,MAX(DISTCALC.sdate) LASTDATE  
                                          ,CFIELD_REP  
                                          ,MIN(ABS(DISTCALC.distance)) MinDist  
                                          ,ROUND(MEDIAN(DISTCALC.distance)-STDDEV_POP(DISTCALC.distance)) LOWER_LIMIT  
                                          ,ROUND(MEDIAN(DISTCALC.distance)) DistMedian  
                                          ,ROUND(MEDIAN(DISTCALC.distance)+STDDEV_POP(DISTCALC.distance)) HIGHER_LIMIT  
                                          ,MAX(DISTCALC.distance) MaxDist  
                                    FROM DISTCALC   
                                    WHERE DISTCALC.distance > 0  
                                    OR (DISTCALC.sdate = DISTCALC.MAX_DATE AND DISTCALC.distance = 0)  
                                    OR (DISTCALC.CFIELD_REP = 1 AND DISTCALC.distance = 0)  
                                    GROUP BY calculation_date,Sensor1,CFIELD_REP, DISTCALC.MAX_DATE  
                                 )
                                 SELECT STATS.*  
                                       ,ROUND(STATS.ANALYSIS_DATE - STATS.LASTDATE) NDISTTODAY  
                                 FROM STATS;
                        

                         

                        I added a join to your first with clause. That will multiply your data for every date after 2018.10.25.