Re: [Dev] [CEP] How to write a Siddhi query to get the total count from an Event Table
sorry I didnt read your entire message. So whats the error you get when you use group by statement? On Fri, Nov 14, 2014 at 1:56 PM, Seshika Fernando sesh...@wso2.com wrote: You have to use *group by initialState* after the select statement On Fri, Nov 14, 2014 at 1:19 PM, Supun Muthutantrige sup...@wso2.com wrote: Hi seshika, Yes tried that but it doesn't give the desired output. The query that we have used as follows, from forCalculation#window.length(1) join MarkovModel on forCalculation.initialState == MarkovModel.Initial_state select initialState as Event, sum(MarkovModel.countP) as totalCount insert into ckStream; this was tried using group by as well, Still it doesn't give the result any thoughts? Thank you Regards *Supun Rasitha Muthutantrige* Software Engineer | Intern WSO2 Inc: http://wso2.com lean.enterprise.middleware Mobile: 0758374608 ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
Re: [Dev] [CEP] How to write a Siddhi query to get the total count from an Event Table
It doesn't give any errors, but the output look like, The query we have used as follows, from forCalculation join MarkovModel on forCalculation.initialState == MarkovModel.Initial_state select initialState as Event, sum(MarkovModel.countP) as totalCount group by forCalculation.initialState insert into ckStream; Thank you Regards *Supun Rasitha Muthutantrige* Software Engineer | Intern WSO2 Inc: http://wso2.com lean.enterprise.middleware Mobile: 0758374608 ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
Re: [Dev] [CEP] How to write a Siddhi query to get the total count from an Event Table
Before executing the code, the Event table looks like, After executing the code, The results according to the above mentioned query Thank you Regards *Supun Rasitha Muthutantrige* Software Engineer | Intern WSO2 Inc: http://wso2.com lean.enterprise.middleware Mobile: 0758374608 ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
Re: [Dev] [CEP] How to write a Siddhi query to get the total count from an Event Table
Can you send us the entire execution plan and the data that you are using. We need to debug this. On Fri, Nov 14, 2014 at 2:36 PM, Supun Muthutantrige sup...@wso2.com wrote: Before executing the code, the Event table looks like, After executing the code, The results according to the above mentioned query Thank you Regards *Supun Rasitha Muthutantrige* Software Engineer | Intern WSO2 Inc: http://wso2.com lean.enterprise.middleware Mobile: 0758374608 ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
Re: [Dev] [CEP] How to write a Siddhi query to get the total count from an Event Table
I have attached the execution plan and the input stream. The regex used to read from file adaptor, *state:(\S+)\.* Thank you Regards *Supun Rasitha Muthutantrige* Software Engineer | Intern WSO2 Inc: http://wso2.com lean.enterprise.middleware Mobile: 0758374608 ?xml version=1.0 encoding=UTF-8? executionPlan name=ExePlan_MarkovModel statistics=enable trace=enable xmlns=http://wso2.org/carbon/eventprocessor; descriptionExecution plan for building the markov mocdel/description siddhiConfiguration property name=siddhi.persistence.snapshot.time.interval.minutes0/property property name=siddhi.enable.distributed.processingfalse/property /siddhiConfiguration importedStreams stream as=inStream name=MarkovModel_inStream version=1.0.0/ /importedStreams queryExpressions![CDATA[ define table MarkovModel (State_Transition string, Initial_state string, countP double) from ('datasource.name'='FD_ToolBox_DB', 'database.name'='FDtoolbox', 'table.name'='MarkovModel'); define table MarkovModelAll (state string, countA double) from ('datasource.name'='FD_ToolBox_DB', 'database.name'='FDtoolbox', 'table.name'='MarkovModelAllCount'); from every a = inStream - b = inStream select a.Event as initialState, b.Event as transState, concat(a.Event,b.Event) as StateTransition insert into forCalculation; from forCalculation[((StateTransition!=MarkovModel.State_Transition) in MarkovModel)] select StateTransition, initialState, 1.0 as stateTrans_count insert into insertIntoModel; from forCalculation[((StateTransition==MarkovModel.State_Transition) in MarkovModel)]#window.length(1) join MarkovModel on forCalculation.StateTransition == MarkovModel.State_Transition select StateTransition, initialState, (MarkovModel.countP+1) as updatedState_count insert into updateModel; //from forCalculation#window.length(1) join MarkovModel //on forCalculation.initialState == MarkovModel.Initial_state from forCalculation join MarkovModel on forCalculation.initialState == MarkovModel.Initial_state select initialState as Event, sum(MarkovModel.countP) as totalCount group by forCalculation.initialState insert into ckStream; //from forCalculation join MarkovModel //on forCalculation.initialState == MarkovModel.Initial_state //select initialState as Event, sum(MarkovModel.countP) as totalCount //group by MarkovModel.Initial_state //insert into ckStream; //from inStream //select Event, count(Event) as totalCount //group by Event //insert into ckStream; from updateModel join MarkovModelAll on updateModel.initialState == MarkovModelAll.state select StateTransition, initialState, (updatedState_count/MarkovModelAll.countA) as prob insert into probStream; //from ckStream#window.length(1) join MarkovModel //on ckStream.Event == MarkovModel.Initial_state //select MarkovModel.State_Transition as StateTransition, MarkovModel.Initial_state as initialState, (MarkovModel.countP/convert(ckStream.totalCount,double)) as prob //insert into probCheck; ]]/queryExpressions exportedStreams stream name=insertIntoModel valueOf=insertIntoModel version=1.0.0/ stream name=updateModel valueOf=updateModel version=1.0.0/ stream name=ckStream valueOf=ckStream version=1.0.0/ stream name=probStream valueOf=probStream version=1.0.0/ /exportedStreams /executionPlan state:HTS. state:HFS. state:HTM. state:HTS. state:HTL. state:HFS. state:MTS. state:HTS. state:HTL. state:MFM. state:HTS. state:HFS. ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
[Dev] [CEP] How to write a Siddhi query to get the total count from an Event Table
Hii all, There is a mysql table with the following content. The format of the in stream state:HTS state:HFS state:HTM state:HTS state:HTL state:HFS state:MTS state:HTS state:HTL state:MFM state:HTS state:HFS The following siddhi query is used to populate the above table. -- define table MarkovModel (State_Transition string, Initial_state string, countP double) from ('datasource.name'='FD_ToolBox_DB', 'database.name'='FDtoolbox', 'table.name'='MarkovModel'); from every a = inStream - b = inStream select a.Event as initialState, b.Event as transState, concat(a.Event,b.Event) as StateTransition insert into forCalculation; from forCalculation[((StateTransition!=MarkovModel.State_Transition) in MarkovModel)] select StateTransition, initialState, 1.0 as stateTrans_count insert into insertIntoModel; from forCalculation[((StateTransition==MarkovModel.State_Transition) in MarkovModel)]#window.length(1) join MarkovModel on forCalculation.StateTransition == MarkovModel.State_Transition select StateTransition, initialState, (MarkovModel.countP+1) as updatedState_count insert into updateModel; -- Want to know how get the total count of *Initial_state*'s from the above table as a continuation to the above query. Ex: HFS - 2 HTL - 2 HTM - 1 HTS - 4 MFM - 1 MTS - 1 Would appreciate any suggestions. Thank you Regards *Supun Rasitha Muthutantrige* Software Engineer | Intern WSO2 Inc: http://wso2.com lean.enterprise.middleware Mobile: 0758374608 ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
Re: [Dev] [CEP] How to write a Siddhi query to get the total count from an Event Table
Why dont you use the sum(Initial_state) function with a group by Initial_state? On Fri, Nov 14, 2014 at 12:12 PM, Supun Muthutantrige sup...@wso2.com wrote: Hii all, There is a mysql table with the following content. The format of the in stream state:HTS state:HFS state:HTM state:HTS state:HTL state:HFS state:MTS state:HTS state:HTL state:MFM state:HTS state:HFS The following siddhi query is used to populate the above table. -- define table MarkovModel (State_Transition string, Initial_state string, countP double) from ('datasource.name'='FD_ToolBox_DB', 'database.name'='FDtoolbox', 'table.name'='MarkovModel'); from every a = inStream - b = inStream select a.Event as initialState, b.Event as transState, concat(a.Event,b.Event) as StateTransition insert into forCalculation; from forCalculation[((StateTransition!=MarkovModel.State_Transition) in MarkovModel)] select StateTransition, initialState, 1.0 as stateTrans_count insert into insertIntoModel; from forCalculation[((StateTransition==MarkovModel.State_Transition) in MarkovModel)]#window.length(1) join MarkovModel on forCalculation.StateTransition == MarkovModel.State_Transition select StateTransition, initialState, (MarkovModel.countP+1) as updatedState_count insert into updateModel; -- Want to know how get the total count of *Initial_state*'s from the above table as a continuation to the above query. Ex: HFS - 2 HTL - 2 HTM - 1 HTS - 4 MFM - 1 MTS - 1 Would appreciate any suggestions. Thank you Regards *Supun Rasitha Muthutantrige* Software Engineer | Intern WSO2 Inc: http://wso2.com lean.enterprise.middleware Mobile: 0758374608 ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
Re: [Dev] [CEP] How to write a Siddhi query to get the total count from an Event Table
HI supun, you can use the count function and grouping it by Initial_state(group by Initial_state) Thanks On Fri, Nov 14, 2014 at 12:23 PM, Seshika Fernando sesh...@wso2.com wrote: Why dont you use the sum(Initial_state) function with a group by Initial_state? On Fri, Nov 14, 2014 at 12:12 PM, Supun Muthutantrige sup...@wso2.com wrote: Hii all, There is a mysql table with the following content. The format of the in stream state:HTS state:HFS state:HTM state:HTS state:HTL state:HFS state:MTS state:HTS state:HTL state:MFM state:HTS state:HFS The following siddhi query is used to populate the above table. -- define table MarkovModel (State_Transition string, Initial_state string, countP double) from ('datasource.name'='FD_ToolBox_DB', 'database.name'='FDtoolbox', 'table.name'='MarkovModel'); from every a = inStream - b = inStream select a.Event as initialState, b.Event as transState, concat(a.Event,b.Event) as StateTransition insert into forCalculation; from forCalculation[((StateTransition!=MarkovModel.State_Transition) in MarkovModel)] select StateTransition, initialState, 1.0 as stateTrans_count insert into insertIntoModel; from forCalculation[((StateTransition==MarkovModel.State_Transition) in MarkovModel)]#window.length(1) join MarkovModel on forCalculation.StateTransition == MarkovModel.State_Transition select StateTransition, initialState, (MarkovModel.countP+1) as updatedState_count insert into updateModel; -- Want to know how get the total count of *Initial_state*'s from the above table as a continuation to the above query. Ex: HFS - 2 HTL - 2 HTM - 1 HTS - 4 MFM - 1 MTS - 1 Would appreciate any suggestions. Thank you Regards *Supun Rasitha Muthutantrige* Software Engineer | Intern WSO2 Inc: http://wso2.com lean.enterprise.middleware Mobile: 0758374608 ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev -- Shashika Prabath Karunatilaka, Software Engineer, WSO2, Inc: http://wso2.com/ mobile : +94 77 7487792 ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
Re: [Dev] [CEP] How to write a Siddhi query to get the total count from an Event Table
Hi Shashika, I don't think the count() function will work here. They need a sum of the values in the countP field. Therefore, they will have to use sum(countP). So for Initial state HTS, count() will give 2 while sum() will give 4. I think I have used the wrong field name in my previous email. Sorry about that. seshika On Fri, Nov 14, 2014 at 12:27 PM, Shashika Karunatilaka shashi...@wso2.com wrote: HI supun, you can use the count function and grouping it by Initial_state(group by Initial_state) Thanks On Fri, Nov 14, 2014 at 12:23 PM, Seshika Fernando sesh...@wso2.com wrote: Why dont you use the sum(Initial_state) function with a group by Initial_state? On Fri, Nov 14, 2014 at 12:12 PM, Supun Muthutantrige sup...@wso2.com wrote: Hii all, There is a mysql table with the following content. The format of the in stream state:HTS state:HFS state:HTM state:HTS state:HTL state:HFS state:MTS state:HTS state:HTL state:MFM state:HTS state:HFS The following siddhi query is used to populate the above table. -- define table MarkovModel (State_Transition string, Initial_state string, countP double) from ('datasource.name'='FD_ToolBox_DB', 'database.name'='FDtoolbox', 'table.name'='MarkovModel'); from every a = inStream - b = inStream select a.Event as initialState, b.Event as transState, concat(a.Event,b.Event) as StateTransition insert into forCalculation; from forCalculation[((StateTransition!=MarkovModel.State_Transition) in MarkovModel)] select StateTransition, initialState, 1.0 as stateTrans_count insert into insertIntoModel; from forCalculation[((StateTransition==MarkovModel.State_Transition) in MarkovModel)]#window.length(1) join MarkovModel on forCalculation.StateTransition == MarkovModel.State_Transition select StateTransition, initialState, (MarkovModel.countP+1) as updatedState_count insert into updateModel; -- Want to know how get the total count of *Initial_state*'s from the above table as a continuation to the above query. Ex: HFS - 2 HTL - 2 HTM - 1 HTS - 4 MFM - 1 MTS - 1 Would appreciate any suggestions. Thank you Regards *Supun Rasitha Muthutantrige* Software Engineer | Intern WSO2 Inc: http://wso2.com lean.enterprise.middleware Mobile: 0758374608 ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev -- Shashika Prabath Karunatilaka, Software Engineer, WSO2, Inc: http://wso2.com/ mobile : +94 77 7487792 ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
Re: [Dev] [CEP] How to write a Siddhi query to get the total count from an Event Table
Hi seshika, Yes tried that but it doesn't give the desired output. The query that we have used as follows, from forCalculation#window.length(1) join MarkovModel on forCalculation.initialState == MarkovModel.Initial_state select initialState as Event, sum(MarkovModel.countP) as totalCount insert into ckStream; this was tried using group by as well, Still it doesn't give the result any thoughts? Thank you Regards *Supun Rasitha Muthutantrige* Software Engineer | Intern WSO2 Inc: http://wso2.com lean.enterprise.middleware Mobile: 0758374608 ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev