In addition, IoTDB supports two other results set format: ‘align by device’ and ‘disable align’.
The ‘align by device’ indicates that the deviceId is considered as a column. Therefore, there are totally limited columns in the dataset.
NOTEļ¼
1.You can see the result of ‘align by device’ as one relational table,
Time + Device
is the primary key of this Table.2.The result is order by
Device
firstly, and then byTime
order.
The SQL statement is:
select * from root.ln.** where time <= 2017-11-01T00:01:00 align by device
The result shows below:
+-----------------------------+-----------------+-----------+------+--------+ | Time| Device|temperature|status|hardware| +-----------------------------+-----------------+-----------+------+--------+ |2017-11-01T00:00:00.000+08:00|root.ln.wf01.wt01| 25.96| true| null| |2017-11-01T00:01:00.000+08:00|root.ln.wf01.wt01| 24.36| true| null| |1970-01-01T08:00:00.001+08:00|root.ln.wf02.wt02| null| true| v1| |1970-01-01T08:00:00.002+08:00|root.ln.wf02.wt02| null| false| v2| |2017-11-01T00:00:00.000+08:00|root.ln.wf02.wt02| null| true| v2| |2017-11-01T00:01:00.000+08:00|root.ln.wf02.wt02| null| true| v2| +-----------------------------+-----------------+-----------+------+--------+ Total line number = 6 It costs 0.012s
The ‘disable align’ indicates that there are 2 columns for each time series in the result set. Disable Align Clause can only be used at the end of a query statement. Disable Align Clause cannot be used with Aggregation, Fill Statements, Group By or Group By Device Statements, but can with Limit Statements. The display principle of the result table is that only when the column (or row) has existing data will the column (or row) be shown, with nonexistent cells being empty.
The SQL statement is:
select * from root.ln.** where time <= 2017-11-01T00:01:00 disable align
The result shows below:
+-----------------------------+--------------------------+-----------------------------+------------------------+-----------------------------+-----------------------------+-----------------------------+------------------------+ | Time|root.ln.wf02.wt02.hardware| Time|root.ln.wf02.wt02.status| Time|root.ln.wf01.wt01.temperature| Time|root.ln.wf01.wt01.status| +-----------------------------+--------------------------+-----------------------------+------------------------+-----------------------------+-----------------------------+-----------------------------+------------------------+ |1970-01-01T08:00:00.001+08:00| v1|1970-01-01T08:00:00.001+08:00| true|2017-11-01T00:00:00.000+08:00| 25.96|2017-11-01T00:00:00.000+08:00| true| |1970-01-01T08:00:00.002+08:00| v2|1970-01-01T08:00:00.002+08:00| false|2017-11-01T00:01:00.000+08:00| 24.36|2017-11-01T00:01:00.000+08:00| true| |2017-11-01T00:00:00.000+08:00| v2|2017-11-01T00:00:00.000+08:00| true| null| null| null| null| |2017-11-01T00:01:00.000+08:00| v2|2017-11-01T00:01:00.000+08:00| true| null| null| null| null| +-----------------------------+--------------------------+-----------------------------+------------------------+-----------------------------+-----------------------------+-----------------------------+------------------------+ Total line number = 4 It costs 0.018s