[prev in list] [next in list] [prev in thread] [next in thread]
List: esb-java-dev
Subject: Re: [Dev] sub query in siddhi QL
From: Grainier Perera <grainier () wso2 ! com>
Date: 2016-08-31 6:52:33
Message-ID: CABBNUS90F+XitWTwfLCYfGdUmMUrA7af_4mCUFzqO93bzS-XDg () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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 <charinin@wso2.com>
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 <ansaf_130@yahoo.com>
> 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
>> Dev@wso2.org
>> http://wso2.org/cgi-bin/mailman/listinfo/dev
>>
>>
>
>
> --
> *Charini Vimansha Nanayakkara*
> Software Engineer at WSO2
>
> Mobile: 0714126293
> E-mail: charinin@wso2.com
> Blog: http://www.charini.me/
>
> <http://wso2.com/signature>
>
> _______________________________________________
> Dev mailing list
> Dev@wso2.org
> http://wso2.org/cgi-bin/mailman/listinfo/dev
>
>
--
Grainier Perera
Software Engineer
Mobile : +94716122384
WSO2 Inc. | http://wso2.com
lean.enterprise.middleware
[Attachment #5 (text/html)]
<div dir="ltr"><div>Hi Aneela,</div><div><br></div><div>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;</div><div><br></div><blockquote style="margin:0 0 0 \
40px;border:none;padding:0px"><div><font face="monospace, monospace" size="1">/* \
Enter a unique ExecutionPlan */</font></div><div><font face="monospace, monospace" \
size="1">@Plan:name('TestExecutionPlan')</font></div><div><font \
face="monospace, monospace" size="1"><br></font></div><div><font face="monospace, \
monospace" size="1">/* define streams/tables and write queries here ... \
*/</font></div><div><font face="monospace, monospace" \
size="1">@Import('DEL_STREAM:1.0.0')</font></div><div><font face="monospace, \
monospace" size="1">define stream DEL (id int);</font></div><div><font \
face="monospace, monospace" size="1"><br></font></div><div><font face="monospace, \
monospace" size="1">@Import('INSERT_STREAM:1.0.0')</font></div><div><font \
face="monospace, monospace" size="1">define stream INST (EMPLOYEE_ID int, \
EMPLOYEE_NAME string);</font></div><div><font face="monospace, monospace" \
size="1"><br></font></div><div><font face="monospace, monospace" \
size="1">@Export('COUNT_STREAM:1.0.0')</font></div><div><font \
face="monospace, monospace" size="1">define stream COUNT (EMPLOYEE_ID int, COUNT \
long);</font></div><div><font face="monospace, monospace" \
size="1"><br></font></div><div><font face="monospace, monospace" size="1">define \
table EMPLOYEE_TABLE (EMPLOYEE_ID int, EMPLOYEE_NAME string);</font></div><div><font \
face="monospace, monospace" size="1"><br></font></div><div><font face="monospace, \
monospace" size="1">define trigger START at 'start';</font></div><div><font \
face="monospace, monospace" size="1"><br></font></div><div><font face="monospace, \
monospace" size="1">from INST</font></div><div><font face="monospace, monospace" \
size="1">insert into EMPLOYEE_TABLE;</font></div><div><font face="monospace, \
monospace" size="1"><br></font></div><div><font face="monospace, monospace" \
size="1">from DEL </font></div><div><font face="monospace, monospace" \
size="1">delete EMPLOYEE_TABLE</font></div><div><font face="monospace, monospace" \
size="1"> on EMPLOYEE_TABLE.EMPLOYEE_ID == id;</font></div><div><span \
class="gmail-Apple-tab-span" style="white-space:pre"><font face="monospace, \
monospace" size="1"> </font></span></div><div><font face="monospace, monospace" \
size="1">from START</font></div><div><font face="monospace, monospace" \
size="1">select UUID() as EVENT_ID, -1 as EMPLOYEE_ID</font></div><div><font \
face="monospace, monospace" size="1">insert into \
INST_PROCESSED;</font></div><div><font face="monospace, monospace" \
size="1"><br></font></div><div><font face="monospace, monospace" size="1">from \
INST</font></div><div><font face="monospace, monospace" size="1">select UUID() as \
EVENT_ID, EMPLOYEE_ID</font></div><div><font face="monospace, monospace" \
size="1">insert into INST_PROCESSED;</font></div><div><font face="monospace, \
monospace" size="1"><br></font></div><div><font face="monospace, monospace" \
size="1">from DEL</font></div><div><font face="monospace, monospace" size="1">select \
UUID() as EVENT_ID, id as EMPLOYEE_ID</font></div><div><font face="monospace, \
monospace" size="1">insert into INST_PROCESSED;</font></div><div><font \
face="monospace, monospace" size="1"><br></font></div><div><font face="monospace, \
monospace" size="1">from INST_PROCESSED#window.time(10 sec)</font></div><div><font \
face="monospace, monospace" size="1">select *</font></div><div><font face="monospace, \
monospace" size="1">insert expired events into INST_EXPIRED;</font></div><div><font \
face="monospace, monospace" size="1"><br></font></div><div><font face="monospace, \
monospace" size="1">from INST_PROCESSED join EMPLOYEE_TABLE</font></div><div><font \
face="monospace, monospace" size="1">select EVENT_ID, \
EMPLOYEE_TABLE.EMPLOYEE_ID</font></div><div><font face="monospace, monospace" \
size="1">insert into INST_TBL_STREAM;</font></div><div><font face="monospace, \
monospace" size="1"><br></font></div><div><font face="monospace, monospace" \
size="1">from INST_PROCESSED#window.length(1) join \
INST_TBL_STREAM</font></div><div><font face="monospace, monospace" size="1">select \
INST_PROCESSED.EVENT_ID, INST_TBL_STREAM.EMPLOYEE_ID</font></div><div><font \
face="monospace, monospace" size="1">insert into \
JOINED_STREAM;</font></div><div><font face="monospace, monospace" \
size="1"><br></font></div><div><font face="monospace, monospace" size="1">from \
JOINED_STREAM#window.timeBatch(5 sec)</font></div><div><font face="monospace, \
monospace" size="1">select EVENT_ID, count() as COUNT</font></div><div><font \
face="monospace, monospace" size="1">group by EVENT_ID</font></div><div><font \
face="monospace, monospace" size="1">insert into COUNT_STREAM;</font></div><div><font \
face="monospace, monospace" size="1"><br></font></div><div><font face="monospace, \
monospace" size="1">from INST_PROCESSED#window.length(1) join \
COUNT_STREAM</font></div><div><font face="monospace, monospace" size="1">on \
COUNT_STREAM.EVENT_ID==EVENT_ID</font></div><div><font face="monospace, monospace" \
size="1">select INST_PROCESSED.EVENT_ID, EMPLOYEE_ID, \
COUNT_STREAM.COUNT</font></div><div><font face="monospace, monospace" size="1">insert \
into COUNT_INNER_STREAM;</font></div><div><font face="monospace, monospace" \
size="1"><br></font></div><div><font face="monospace, monospace" size="1">from \
every(e1=INST_PROCESSED) -> e2=COUNT_INNER_STREAM[e1.EVENT_ID==EVENT_ID] OR \
e3=INST_EXPIRED[e1.EVENT_ID==EVENT_ID]</font></div><div><font face="monospace, \
monospace" size="1">select e1.EMPLOYEE_ID, e2.COUNT</font></div><div><font \
face="monospace, monospace" size="1">insert into FILTER_COUNT;</font></div><div><font \
face="monospace, monospace" size="1"><br></font></div><div><font face="monospace, \
monospace" size="1">from FILTER_COUNT[(COUNT is null)]</font></div><div><font \
face="monospace, monospace" size="1">select EMPLOYEE_ID, 0L as \
COUNT</font></div><div><font face="monospace, monospace" size="1">insert into \
COUNT;</font></div><div><font face="monospace, monospace" \
size="1"><br></font></div><div><font face="monospace, monospace" size="1">from \
FILTER_COUNT[not (COUNT is null)]</font></div><div><font face="monospace, monospace" \
size="1">select EMPLOYEE_ID, COUNT</font></div><div><font face="monospace, monospace" \
size="1">insert into \
COUNT;</font></div></blockquote><div><br></div><div>Regards,</div><div>Grainier.</div></div><div \
class="gmail_extra"><br><div class="gmail_quote">On Wed, Aug 31, 2016 at 7:55 AM, \
Charini Nanayakkara <span dir="ltr"><<a href="mailto:charinin@wso2.com" \
target="_blank">charinin@wso2.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr">Hi Aneela,<div><br></div><div>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)</div><div><br></div><div>(define an in-memory table to store \
count)</div><div><br></div><div>define table CountTable (count \
long);<br></div><div><br></div><div>from inputStream#window.timeBatch(2 \
min)</div><div>select count() as count</div><div>insert into \
CountTable;</div><div><br></div><div>from inputStream#window.timeBatch(4 \
min)</div><div>select employee_id</div><div>group by employee_id</div><div>insert \
into TempStream;</div><div><br></div><div>from TempStream as t join CountTable as \
c</div><div>select t.employee_id, c.count</div><div>insert into \
OutputStream;</div><div><br></div><div><br></div><div>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.</div><div><br></div><div>Thank \
you</div><div>Charini</div><div><br></div><div><br></div></div><div \
class="gmail_extra"><br><div class="gmail_quote"><div><div class="h5">On Mon, Aug 29, \
2016 at 11:59 PM, Aneela Safdar <span dir="ltr"><<a \
href="mailto:ansaf_130@yahoo.com" target="_blank">ansaf_130@yahoo.com</a>></span> \
wrote:<br></div></div><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div class="h5"><div><div \
style="color:#000;background-color:#fff;font-family:HelveticaNeue,Helvetica \
Neue,Helvetica,Arial,Lucida \
Grande,sans-serif;font-size:12px"><div><span>Hi,</span></div><div><span><br></span></div><div><span>How \
can I achieve this sql in siddhi query \
languge:</span></div><div><span><br></span></div><div dir="ltr"><font face="bookman \
old style, new york, times, serif"><span>select employee_id, (select count(*) from \
employees)<br>from employees</span></font></div><div \
dir="ltr"><span><br></span></div><div dir="ltr"><span>I want just a two columns of a \
stream, one legitimate and other is count of all records.</span></div><div \
dir="ltr"><span><br></span></div><div dir="ltr"><span>Thanks,</span></div><div> \
</div><div><div>Regards,</div><div>Aneela \
Safdar</div></div></div></div><br></div></div>______________________________<wbr>_________________<br>
Dev mailing list<br>
<a href="mailto:Dev@wso2.org" target="_blank">Dev@wso2.org</a><br>
<a href="http://wso2.org/cgi-bin/mailman/listinfo/dev" rel="noreferrer" \
target="_blank">http://wso2.org/cgi-bin/mailma<wbr>n/listinfo/dev</a><br> \
<br></blockquote></div><span class="HOEnZb"><font color="#888888"><br><br \
clear="all"><div><br></div>-- <br><div data-smartmail="gmail_signature"><div \
dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div dir="ltr"><div \
style="font-size:12.8px"><div><div><b>Charini Vimansha Nanayakkara</b></div>Software \
Engineer at WSO2<br></div><div><br></div><font face="georgia, serif">Mobile: \
0714126293<br></font></div><div style="font-size:12.8px"><font face="georgia, \
serif">E-mail: <a href="mailto:charinin@wso2.com" \
target="_blank">charinin@wso2.com</a></font></div><div style="font-size:12.8px"><font \
face="georgia, serif">Blog: <a href="http://www.charini.me/" \
target="_blank">http://www.charini.me/</a><a></a></font></div><div><br></div><div><a \
href="http://wso2.com/signature" target="_blank"><img \
src="http://c.content.wso2.com/signatures/wso2-signature-general.png"></a><br></div><div \
style="font-size:12.8px"></div></div></div></div></div></div></div></div> \
</font></span></div> <br>______________________________<wbr>_________________<br>
Dev mailing list<br>
<a href="mailto:Dev@wso2.org">Dev@wso2.org</a><br>
<a href="http://wso2.org/cgi-bin/mailman/listinfo/dev" rel="noreferrer" \
target="_blank">http://wso2.org/cgi-bin/<wbr>mailman/listinfo/dev</a><br> \
<br></blockquote></div><br><br clear="all"><div><br></div>-- <br><div \
class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><div \
dir="ltr"><span><font color="#888888">Grainier Perera<br>Software \
Engineer<br></font></span></div><div><span><font color="#888888">Mobile : \
+94716122384<br></font></span></div><div dir="ltr"><span><font color="#888888">WSO2 \
Inc. | <a href="http://wso2.com" \
target="_blank">http://wso2.com</a><br>lean.enterprise.middleware</font></span><br></div></div></div></div>
</div>
_______________________________________________
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic