Hi Aneela,
At the moment, Siddhi does not support sub-queries. But, if you just need
to count all the events, you can try Charini's answer. However, if you are
having an event table (i.e. in-memory table) of Employees and you want to
get the count of records in that event table (or get record count when the
table gets updated), you can try a query similar to this;
/* Enter a unique ExecutionPlan */
@Plan:name('TestExecutionPlan')
/* define streams/tables and write queries here ... */
@Import('DEL_STREAM:1.0.0')
define stream DEL (id int);
@Import('INSERT_STREAM:1.0.0')
define stream INST (EMPLOYEE_ID int, EMPLOYEE_NAME string);
@Export('COUNT_STREAM:1.0.0')
define stream COUNT (EMPLOYEE_ID int, COUNT long);
define table EMPLOYEE_TABLE (EMPLOYEE_ID int, EMPLOYEE_NAME string);
define trigger START at 'start';
from INST
insert into EMPLOYEE_TABLE;
from DEL
delete EMPLOYEE_TABLE
on EMPLOYEE_TABLE.EMPLOYEE_ID == id;
from START
select UUID() as EVENT_ID, -1 as EMPLOYEE_ID
insert into INST_PROCESSED;
from INST
select UUID() as EVENT_ID, EMPLOYEE_ID
insert into INST_PROCESSED;
from DEL
select UUID() as EVENT_ID, id as EMPLOYEE_ID
insert into INST_PROCESSED;
from INST_PROCESSED#window.time(10 sec)
select *
insert expired events into INST_EXPIRED;
from INST_PROCESSED join EMPLOYEE_TABLE
select EVENT_ID, EMPLOYEE_TABLE.EMPLOYEE_ID
insert into INST_TBL_STREAM;
from INST_PROCESSED#window.length(1) join INST_TBL_STREAM
select INST_PROCESSED.EVENT_ID, INST_TBL_STREAM.EMPLOYEE_ID
insert into JOINED_STREAM;
from JOINED_STREAM#window.timeBatch(5 sec)
select EVENT_ID, count() as COUNT
group by EVENT_ID
insert into COUNT_STREAM;
from INST_PROCESSED#window.length(1) join COUNT_STREAM
on COUNT_STREAM.EVENT_ID==EVENT_ID
select INST_PROCESSED.EVENT_ID, EMPLOYEE_ID, COUNT_STREAM.COUNT
insert into COUNT_INNER_STREAM;
from every(e1=INST_PROCESSED) ->
e2=COUNT_INNER_STREAM[e1.EVENT_ID==EVENT_ID] OR
e3=INST_EXPIRED[e1.EVENT_ID==EVENT_ID]
select e1.EMPLOYEE_ID, e2.COUNT
insert into FILTER_COUNT;
from FILTER_COUNT[(COUNT is null)]
select EMPLOYEE_ID, 0L as COUNT
insert into COUNT;
from FILTER_COUNT[not (COUNT is null)]
select EMPLOYEE_ID, COUNT
insert into COUNT;
Regards,
Grainier.
On Wed, Aug 31, 2016 at 7:55 AM, Charini Nanayakkara <[email protected]>
wrote:
> Hi Aneela,
>
> If you need to count all the records (without grouping by employee_id) you
> will have to do something similar to the following. (This is just one way
> of addressing your requirement)
>
> (define an in-memory table to store count)
>
> define table CountTable (count long);
>
> from inputStream#window.timeBatch(2 min)
> select count() as count
> insert into CountTable;
>
> from inputStream#window.timeBatch(4 min)
> select employee_id
> group by employee_id
> insert into TempStream;
>
> from TempStream as t join CountTable as c
> select t.employee_id, c.count
> insert into OutputStream;
>
>
> The execution plan would work if you have prior knowledge that all the
> input events would arrive within 2 minutes. In the second query a larger
> batch time is used to ensure that, the count is already written to table
> CountTable, by the time events start being sent to TempStream.
>
> Thank you
> Charini
>
>
>
> On Mon, Aug 29, 2016 at 11:59 PM, Aneela Safdar <[email protected]>
> wrote:
>
>> Hi,
>>
>> How can I achieve this sql in siddhi query languge:
>>
>> select employee_id, (select count(*) from employees)
>> from employees
>>
>> I want just a two columns of a stream, one legitimate and other is count
>> of all records.
>>
>> Thanks,
>>
>> Regards,
>> Aneela Safdar
>>
>> _______________________________________________
>> Dev mailing list
>> [email protected]
>> http://wso2.org/cgi-bin/mailman/listinfo/dev
>>
>>
>
>
> --
> *Charini Vimansha Nanayakkara*
> Software Engineer at WSO2
>
> Mobile: 0714126293
> E-mail: [email protected]
> Blog: http://www.charini.me/
>
> <http://wso2.com/signature>
>
> _______________________________________________
> Dev mailing list
> [email protected]
> http://wso2.org/cgi-bin/mailman/listinfo/dev
>
>
--
Grainier Perera
Software Engineer
Mobile : +94716122384
WSO2 Inc. | http://wso2.com
lean.enterprise.middleware
_______________________________________________
Dev mailing list
[email protected]
http://wso2.org/cgi-bin/mailman/listinfo/dev
