This discussion is archived
8 Replies Latest reply: May 10, 2012 10:59 PM by 900697 RSS

How to fetch each row data

900697 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi,

    Solution suggested is working fine.

    Thanks,
    Sri
  • 6. Re: How to fetch each row data
    900697 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi,

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

    Regards
  • 8. Re: How to fetch each row data
    900697 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points