8 Replies Latest reply: May 11, 2012 12:59 AM by 900697 RSS

    How to fetch each row data

    900697
      Hi,

      I have written a CQL query with rows 3 records. In that query is it possible to fetch first record and last record specific field values.

      for example,
      Column: c1, c2, c3
      record1: id1, 2.3, 3.5
      record2: id2, 2.3, 3.6
      record3: id21, 2.3, 3.7
      record3: id25, 2.3, 3.9

      I need to get moving average of three rows of c3 and get first record id and last record ids.

      output1: id1, id1, avg(c3)=3.5
      output2: id1, id2, avg(c3)=3.55
      output3: id1, id21, avg(c3)=3.6
      output4: id2, id25, avg(c3)=3.733

      Please help on above.

      Thanks,
      Sri
        • 1. Re: How to fetch each row data
          Unmesh
          Hi,

          Assuming your input is coming through a stream similar to the one mentioned below:
          S1 (c1 char(10), c2 float, c3 float)

          The following query should give you the expected output:

          select T.firstC1, T.lastC1, T.avgC3 from S1
          match_recognize(
          measures
          first(m1.c1) as firstC1,
          m2.c1 as lastC1,
          avg(c3) as avgC3
          all matches
          pattern (A B? C? | D+ C)
          subset m1=(A,D) m2=(A,B,C)
          define
          A as prev(A.c1) is null,
          B as (count(*) = 2),
          C as (count(*) = 3),
          D as ((prev(D.c1) is not null) and (count(*) < 3))
          ) as T

          Regards
          • 2. Re: How to fetch each row data
            900697
            Hi,

            I have tried for 5 records, Observed wired behavior that is after first 5 records getting average of first 4 records and is repeating every moving average and then after 10 records, average first 4 and average of next 4 records repeating after calculating every moving average.

            Query for 5 rows data pattern is as shown :

                                     pattern (A B? C? E? F? | D+ F)
                                     subset m1=(A,D) m2=(A,B,C,E,F)
                                     define
                                     A as prev(A.DataIndex) is null,
                                     B as (count(*) = 2),
                                     C as (count(*) = 3),
                                     E as (count(*) = 4),
                                     F as (count(*) = 5),
                                     D as ((prev(D.DataIndex) is not null) and (count(*) < 5))


            Why records repeating?
            Please help.

            Thanks,
            Sri
            • 3. Re: How to fetch each row data
              900697
              For 3 records also data is repeating after first 3 records and then every rcord.

              Thanks,
              Sri
              • 4. Re: How to fetch each row data
                Unmesh
                Hi,

                Not quite sure I understand what is the problem that you are seeing.

                Here's what I got when I ran the query:

                Input Data:

                c1, c2, c3
                id1, 2.3, 3.5
                id2, 2.3, 3.6
                id21, 2.3, 3.7
                id25, 2.3, 3.9
                id26, 2.3, 4.0
                id27, 2.3, 4.1

                Query:

                select T.firstC1, T.lastC1, T.avgC3 from S1
                match_recognize(
                measures
                first(m1.c1) as firstC1,
                m2.c1 as lastC1,
                avg(c3) as avgC3
                all matches
                pattern (A B? C? | D+ C)
                subset m1=(A,D) m2=(A,B,C)
                define
                A as prev(A.c1) is null,
                B as (count(*) = 2),
                C as (count(*) = 3),
                D as ((prev(D.c1) is not null) and (count(*) < 3))
                ) as T

                Output:

                firstc1, lastC1, avgC3
                id1,id1,3.5 - avg of the only record
                id1,id2,3.55 - avg of two records
                id1,id21,3.6000001 - avg of last three records
                id2,id25,3.7333336 - avg of last three records
                id21,id26,3.8666668 - avg of last three records
                id25,id27,4.0 - avg of last three records

                Doesn't this output confirm with what you had mentioned as expected output?
                Can you please run this query as is on the input data mentioned above and verify for yourself?
                If you don't get the expected result, please paste the input data supplied, complete query text and output obtained.

                Regards
                • 5. Re: How to fetch each row data
                  900697
                  Hi,

                  Solution suggested is working fine.

                  Thanks,
                  Sri
                  • 6. Re: How to fetch each row data
                    900697
                    I have one more question.

                    The average output is given as input to pattern match processor.

                    The double differentiation differentiation of first 2 average input data in CQL pattern match is written as:
                    <view id="FetchRows">
                         <![CDATA[ istream(select * from mdepthChannel)
                                     ]]>
                    </view>
                    <query id="ExampleQuery">
                         <![CDATA[ select *      
                                     from FetchRows 
                                     MATCH_RECOGNIZE (
                                     Measures
                                             v1.wellUid as wellUid,
                                             v1.arrivalTime as arrivalTime,
                                          v1.wellboreUid as wellboreUid,
                                          v1.mdepthAverage as mdepthAverage,
                                          v1.wopAverage as wopAverage,
                                          v2.mdepthAverage as v2mdepthAverage,
                                          v2.wopAverage as v2wopAverage,
                                          v3.mdepthAverage as v3mdepthAverage,
                                          v3.wopAverage as v3wopAverage,
                                          getCalculateDerivative(v2.mdepthAverage,getCalculateDerivative(v1.mdepthAverage,v1.wopAverage,v2.mdepthAverage,v2.wopAverage),v3.mdepthAverage,getCalculateDerivative(v2.mdepthAverage,v2.wopAverage,v3.mdepthAverage,v3.wopAverage)) as derValue
                                     ALL MATCHES
                                     PATTERN (v1 v2 v3)
                                     DEFINE
                                     v3 as (getCalculateDerivative(v2.mdepthAverage,getCalculateDerivative(v1.mdepthAverage,v1.wopAverage,v2.mdepthAverage,v2.wopAverage),v3.mdepthAverage,getCalculateDerivative(v2.mdepthAverage,v2.wopAverage,v3.mdepthAverage,v3.wopAverage)) <= 0.0)               
                                        ) as T
                                     ]]>           
                    </query>

                    Where "getCalculateDerivative" is the java function returns slope of straight line.

                    The output of this query returns correct data till 7th record after that it repeats 6nd record.
                    That 8th record is the same as 6th record data.
                    Why that 8th record is the 6nd record repeats.

                    The desired output is same as input record if pattern matches.

                    Please help on this.

                    Thanks,
                    Sri

                    Edited by: 897694 on Mar 27, 2012 2:15 AM
                    • 7. Re: How to fetch each row data
                      Unmesh
                      Hi,

                      Please share your input data, output obtained and the implementation of getCalculateDerivative function.

                      Regards
                      • 8. Re: How to fetch each row data
                        900697
                        Hi,

                        Thank you so much that's working as expected.

                        One more question.

                        When i going to validate query in "WLEVS" console its throwing validation failure exception as shown below:

                        Validate failed.
                        Caused by: Aggregation does not reference a correlation name.
                        Reference a correlation attribute in the parameter to the aggregation.

                        Do you have any idea why this error.
                        Since my query has to update at run time for different mnemonics its causing an issue.

                        Please help on this.

                        Thanks,
                        Sri