[jira] [Closed] (IOTDB-305) a sql to query a group of devices separately

["Jialin Qiao (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/IOTDB-305?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Jialin Qiao closed IOTDB-305.
-----------------------------
    Fix Version/s: 0.10.0-SNAPSHOT
       Resolution: Fixed

> 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
>            Priority: Major
>              Labels: pull-request-available
>             Fix For: 0.10.0-SNAPSHOT
>
>          Time Spent: 10m
>  Remaining Estimate: 0h
>
> 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)