Hi, The path from root to the last but one is device, which is like a table in relational databases.
> CREATE TIMESERIES root.ChangSha.A.aa WITH DATATYPE=INT64, ENCODING=RLE; In this case, root.ChangSha.A is device. > CREATE TIMESERIES root.ChangSha.aa WITH DATATYPE=INT64, ENCODING=RLE; In this case, root.ChangSha is a device. Usually, we do not create a device under another device, which is weired. Thanks, -- Jialin Qiao School of Software, Tsinghua University 乔嘉林 清华大学 软件学院 > -----原始邮件----- > 发件人: "刘大伟" <[email protected]> > 发送时间: 2019-11-14 14:18:42 (星期四) > 收件人: [email protected] > 抄送: > 主题: [jira] [Created] (IOTDB-305) a sql to query a group of devices separately > > Hi, > > I have a question， > > SET STORAGE GROUP TO root.ChangSha; > CREATE TIMESERIES root.ChangSha.A.aa WITH DATATYPE=INT64, ENCODING=RLE; > CREATE TIMESERIES root.ChangSha.aa WITH DATATYPE=INT64, ENCODING=RLE; > > > insert into root.ChangSha.A(timestamp,aa) values(1,1); > insert into root.ChangSha(timestamp,aa) values(1,1); > > > > Select * from root.ChangSha where aa = 1 > > Result will print : > > +-----------------------------------+------------------------------------+------------------+ > | Time |root.ChangSha.A.aa| > root.ChangSha.aa| > +-----------------------------------+------------------------------------+------------------+ > | 1970-01-01T08:00:00.001+08:00| 1 | > 1| > | 1970-01-01T08:00:00.002+08:00| null | > 1| > +-----------------------------------+------------------------------------+—————————+ > > Which field is device and how to group by ? > > > > 在 2019年11月14日，下午12:21，Jialin Qiao <[email protected]> 写道： > > > > 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) > >
