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)
