Hi, > "group by device" sql can't satisfy this demand. It is because the where > condition of "group by device" is shared by all devices.
The "shared by all devices" is a little hard to comprehend. Many users are from relational databases. In relational databases, when querying a table, predicates are applied to each row. By default, IoTDB adopts table format: "time, series1, series2, ..., seriesN". For this scenario, what users really want is querying on a narrow table : "time, device, sensor1, sensor2, ..., sensor3". Maybe we need to introduce the concept of "Table", and enable users to choose which table that want to use by a "group by device" clause. Thanks, -- Jialin Qiao School of Software, Tsinghua University 乔嘉林 清华大学 软件学院 > -----原始邮件----- > 发件人: "Lei Rui (Jira)" <[email protected]> > 发送时间: 2019-11-14 11:19:00 (星期四) > 收件人: [email protected] > 抄送: > 主题: [jira] [Created] (IOTDB-305) a sql to query a group of devices separately > > 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)
