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
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
