Lei Rui created IOTDB-305:
-----------------------------
Summary: a sql to query a group of devices separately
Key: IOTDB-305
URL: https://issues.apache.org/jira/browse/IOTDB-305
Project: Apache IoTDB
Issue Type: New Feature
Reporter: Lei Rui
First of all, if Bob knows exactly what devices to query (root.sg.d1 and
root.sg.d2 in this case) , he can write sqls for every device:
{code:java}
sql1: select * from root.sg.d1 where s1=1
sql2: select * from root.sg.d2 where s1=1{code}
However, when there are many devices or devices are not specified in advance,
Bob wants to query like:
{code:java}
select * from root.sg.d1,root.sg.d2 where s1=1 <device separate>(a demo
conception)
/ select * from root.sg.* where s1=1 <device separate>(a demo conception)
{code}
to return the result same as the concatenation of the results of the above two
queries (i.e., sql1 and sql2) while eliminating the trouble of writing two
separate sqls.
"group by device" sql can't satisfy this demand. It is because the where
condition of "group by device" is shared by all devices. For example,
{code:java}
select * from root.sg.* where s1=1 group by device
{code}
equals
{code:java}
select * from root.sg.d1 where root.sg.d1.s1=1 and root.sg.d2.s1=1
select * from root.sg.d2 where root.sg.d1.s1=1 and root.sg.d2.s1=1
{code}
Note the "and" in the where condition. The following example further
demonstrates the effect:
{code:java}
SET STORAGE GROUP TO root.ChangSha;
CREATE TIMESERIES root.ChangSha.A.aa WITH DATATYPE=INT64, ENCODING=RLE;
CREATE TIMESERIES root.ChangSha.A.ab WITH DATATYPE=INT64, ENCODING=RLE;
CREATE TIMESERIES root.ChangSha.A.ac WITH DATATYPE=INT64, ENCODING=RLE;
CREATE TIMESERIES root.ChangSha.B.aa WITH DATATYPE=INT64, ENCODING=RLE;
CREATE TIMESERIES root.ChangSha.B.ab WITH DATATYPE=INT64, ENCODING=RLE;
CREATE TIMESERIES root.ChangSha.B.ad WITH DATATYPE=INT64, ENCODING=RLE;
insert into root.ChangSha.A(timestamp,aa,ab,ac) values(1,1,1,1);
insert into root.ChangSha.B(timestamp,aa,ab,ad) values(2,1,2,2);
select * from root.ChangSha.A, root.ChangSha.B where aa=1 group by device{code}
The above select query equals
{code:java}
select * from root.ChangSha.A where root.ChangSha.A.aa=1 and
root.ChangSha.B.aa=1
select * from root.ChangSha.B where root.ChangSha.A.aa=1 and
root.ChangSha.B.aa=1{code}
This select query will get an empty result because there is no timestamp under
which both root.ChangSha.A.aa=1 and root.ChangSha.B.aa=1.
What Bob wants is:
{code:java}
select * from root.ChangSha.A, root.ChangSha.B where aa=1 <device separate>(a
demo conception)
{code}
equals
{code:java}
select * from root.ChangSha.A where root.ChangSha.A.aa=1
select * from root.ChangSha.B where root.ChangSha.B.aa=1{code}
to get the result like:
||Time||Device||aa||ab||ac||ad||
|1970-01-01T08:00:00.001+08:00|root.ChangSha.A|1|1|1|null|
|1970-01-01T08:00:00.002+08:00|root.ChangSha.B|1|2|null|2|
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
